Re: Import time taking so long <<<===============================================================

Yes, it could make a tremendous difference. You need to tune your database for the task you are trying to accomplish. I have a little checklist I use to improve the speed of an import on our 7.3.x databases. I have seen a 50 % or greater improvement in run times.

1. Increase size of redo log files (30M gives me a logswitch about every 3 minutes, which is the goal Oracle support told me to shoot for on an intensive operation such as an import) 1-a. Turn off archiving if enabled.
2. Place each redo logfile on a seperate drive (or ping-pong them across as many as feasible) to eliminate disk drive contention during the import.
3. modify init.ora to set the dbwriters = # or data file disk drives * 2
4. modify init.ora to set checkpoint_process = true.
5. verify init.ora parameter log_imultaneous_copies is correct for the number of processors on the server or remove the parameter if on a single cpu machine
6. Double Check the size of rollback and temp tablespaces and be sure they are big enough (sorry, but a relative term :) )
7. Double check the initial & next extent sizes for rollback & temp to be sure they can expand to their maximum size, and be sure to set just one large rollback segment for the import.
8. Use the import option commit=y for databases with large tables.
9. modify init.ora to increase log_buffer to 1,024,000

Then, of course, try to time the import so it is run during a non-peak time on the server.

When I'm done with the import, I put everything back the way it was. Hope some of these ideas help.

A Ustby wrote:

> Another Oracle question/observation....
>
> We have an instance (v7.3.3) that has 1000+ tables in it (purchased
> application). A couple of the tables have 1.5 million rows in them. One of
> these tables has 14 indexes on it. On the same box we run both production and
> test instances. We have the Oracle parameters (memory, sort area, etc) set
> lower for the test instance. When we load production instance, it takes 17
> hours. When we load the same data into the test instance, it takes 41 hours.
> Both loads were done when the box was quiet.
>
> We are using the Oracle import utility. It appears the excess time is in the
> index build phase. Could this be affected (that much !) by the Oracle sort parm
> size ? Any other insight ?
>
> The thing takes so long, we haven't done too much experimenting.
>
> Thanks.......... Art

--
Linda Montgomery
Database Analyst
James Madison University
montgolh_at_jmu.edu Received on Tue Feb 23 1999 - 12:40:06 CST