Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Import time taking so long <<<===============================================================
Sorry to be picky, but...
Linda H Montgomery wrote:
> 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)
Maybe look at building the indexes without having them logged in the first place - i.e. defer them and add them later (as I think someone already pointed out). And if you have multiple CPU's build them in parallel. And if you want to go to extremes, pull the redo onto it's own disk drive(s) and raw partition(s).
>
> 1-a. Turn off archiving if enabled.
Er, be very, very careful before doing this. Understand it's consequences and take a full backup beforehand.
>
> 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.
That's redo log member set, not redo logfile. They are used in a cyclic fashion. Split group members across drives, not logfiles. And pull archive members off to their own disks.
>
> 3. modify init.ora to set the dbwriters = # or data file disk drives * 2
Again, be careful. On some platforms (i.e. AIX with async IO) this is not necessary and can actually make things worse. Read up on your platform specific docs.
>
> 4. modify init.ora to set checkpoint_process = true.
Not with Oracle8.
>
> 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 :) )
Well, only double them if they are not already sufficient. Monitor them during the import.
>
> 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.
>
I'd just add the point that I test and monitor a few different cases. It's hard to get the right mix first time out.
Steve Phelan
(Oracle 7 & 8 OCP) Received on Tue Feb 23 1999 - 13:46:35 CST