Forum OpenACS Q&A: Oracle import too slow?

Posted by Andrew Piskorski on 08/03/02 08:43 AM

For the Oracle users here, do you use exp/imp to clone your data from Production to Dev, for testing and development purposes? If not, what other methods do you use?

I was somewhat surprised by just how slow the import step is; in my case, maybe 45 minutes or so for less than 430 MB of data (the size of the tablespace file). This is certainly still feasible on a weekly or nightly basis, but it sure is annoying. And then when my data grows...

Other possible options I've thought of are:

Use the transport_tablespace=y option to imp and exp. This is supposed to be as very fast, as fast as just copying the raw database files. But there are limitations. E.g., you have to plug the tablespace it into a different instance, and you can't rename it.
Use sqlldr to get the data in. Use imp only to create the tables, etc., not to put in any data. This will definitely be faster than imp, but means writing scripts to dump the data out in a format sqlldr can understand (there is no option to have exp do that, unfortunately...), etc.
Do our own clone of the data entirely within Oracle. If Dev and Prod are on the same instance, Dev just needs read access to Prod. If they're on different instances, you could probably create a database link between the two. Of course, you have to take measures to get a consistant copy, and oops, make sure rollback buffers can handle this one massive transaction, etc.

I'm very curious about what solutions you've used...

2: Response to Oracle import too slow? (response to 1)

Posted by C. R. Oldham on 08/04/02 09:25 PM

Andrew,

Please correct me if I'm wrong--I'm pretty sure transportable tablespaces is an Oracle Extended Edition feature only.

--cro

3: Response to Oracle import too slow? (response to 1)

Posted by Jade Rubick on 08/12/02 06:44 PM

Andrew, what I do is just copy across the entire /ora8 directory. I
have a script tar and gzip it up, and it sftp's the ora8.tar.gz file
over every day. Whenever I want to have a copy of the production
data, I just unzip and untar it. It seems to work just fine.

Caveat: Oracle has to be shut down while the copy is being
made on both ends. I'm on an Intranet, so that's not a big deal.

4: Response to Oracle import too slow? (response to 1)

Posted by Bruno Mattarollo on 09/05/02 12:47 PM

I have a question similar to Andrews'.

I need to import data from a production database into two different tablespaces (two different users). Using 'exp' doesn't allow me to do that, as it always try to create in the tablespace with the username used while doing 'exp'. I want to import into 'development' and 'staging' but I don't want to have two different oracle instances, I want to use two different users. Is this possible without having to do, as Andrew says, create scripts that will write data out in formats for SQLLLDR?

Thank you in advance,

5: Response to Oracle import too slow? (response to 1)

Posted by Simon Buckle on 09/05/02 05:19 PM

You can use sqlplus to format the data you want and then spool the data to a file. You can then use sqlldr to load in the data. Try something like this:

SQL> set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
SQL> spool mydata.txt
SQL> select column1 || ',' || column2 || ',' || column3
         from <tablename>;
SQL> spool off

This works fine for a table or two but I'm not sure how useful it is if you want to do a full export of the tablespace.

6: Response to Oracle import too slow? (response to 1)

Posted by Jeff Davis on 09/05/02 05:43 PM

Bruno, one way to do it is to edit the user and tablespace directly in the exported binary (which worked for me with versions 8.0.5-8.1.6, never tried
it with any other versions). cf http://www.galileo.edu/obonilla/writing/oracle/

The other way is to change the permissions for the user which unfortunately I am not entirely sure how to do...
something like "revoke unlimited tablespace from username;" and maybe some other stuff. I think there might still be problems with clobs though.
This page talks about it for v7 but I would guess things are similiar for v8.
http://www.fors.com/orasupp/rdbms/dba/18214_1.HTM

7: Response to Oracle import too slow? (response to 1)

Posted by Simon Buckle on 09/05/02 06:15 PM

You could also try using the FROMUSER and TOUSER options when using import:

imp user1/<password> fromuser=user2 touser=user1 file=export.dmp

8: Response to Oracle import too slow? (response to 1)

Posted by Andrew Piskorski on 09/05/02 06:30 PM

Bruno, I'm currently using exp/imp with fromuser/touser as Simon said above. E.g.:

$ exp myprod/password consistent=Y owner=myprod file=/tmp/myprod.dmp
$ imp mydev/password rows=y show=n ignore=n fromuser=myprod touser=mydev file=/tmp/myprod.dmp

That will definitely work for what you want to do too. But, it was the slowness of the import step that led me to start this thread...

9: Response to Oracle import too slow? (response to 1)

Posted by Jeff Davis on 09/05/02 07:06 PM

Andrew, if you are doing the export and import on the same instance you might want to check if you have objects in the wrong tablespaces. I think the query is

select	OWNER,
	SEGMENT_NAME,
	SEGMENT_TYPE,
	TABLESPACE_NAME,
	BYTES
from 	dba_segments
order 	by OWNER, SEGMENT_NAME

You can end up with clobs from the prod tablespace in the dev tablespace and vice versa depending on the permissions for the importing users.

10: Response to Oracle import too slow? (response to 1)

Posted by Michael Bryzek on 09/05/02 07:43 PM

One more way to do an exp/imp from one user to another is to do a full export as described above, and then to do an import using the CREATE=Y parameter. This will give you the create statements for all the objects, but will not create them in your user's schema. You can then edit the create statements to make sure all your objects belong to the correct tablespaces, load the create script, and then rerun imp with IGNORE=Y (necessary since you've already created all the objects).

Other simple things to check with import:

-- make sure you have enough space for your redo logs. IMP uses sql statements to insert data and thus generates lots of redo.

-- turn off archive logs

Another option to consider (enterprise edition only) is to do less frequent imports and to spend more time synchronizing only the statistics between the two databases.

11: Response to Oracle import too slow? (response to 1)

Posted by Andrew Piskorski on 09/05/02 08:10 PM

Jeff, that's an excellent point about imported CLOBs wanting to go back to the same tablespace they were originally created in. I'd forgotten about that, but in my case I have no LOB types at all in the schema so it's not a problem.

Michael, can you comment more on "synchronizing only the statistics between the two databases"? I haven't heard of that.

12: Response to Oracle import too slow? (response to 1)

Posted by Michael Bryzek on 09/05/02 08:32 PM

Regarding CLOBs, I once tried using transportable tablespaces but ran into problems because my LOB's were in a separate tablespace. Maybe there is a workaround, but I think transporatable tablespaces are limited in that all objects must belong to the same tablespace.

The DBMS_STAT pl/sql package contains procedures for transferring statistics (http://oradoc.photo.net/ora817/DOC/appdev.817/a76936/dbms_st2.htm#1010202)

Looking over the docs again, it looks like this feature is available in standard edition.

13: Re: Oracle import too slow? (response to 1)

Posted by John Miller on 07/07/06 02:23 AM

We using Fastreader to extract data into flat files, and it creates also a input for sql loader
Nice thing about fastreader ( www.wisdomforce.com ) that it is very fast and has built in compression on the fly which reduce the disk space required. we using fastreader to write data into pipe and load data into sql server