How to improve the performance
of import?
Please excuse this obvious FAQ, but I've been asked to assist a client
in
improving the performance of the IMPORT process on his system. This
client
is moving a complete database instance on a nightly basis from one
HP 9000
machine to another. They are claiming that it takes an hour for
the export
and over three hours for the import.
I know there are some tips and tricks for improving the import process,
such
as increasing the size of SORT_AREA_SIZE and by creating indexes separately,
but I can't seem to locate the FAQ, and the Server Utilities manual
isn't
much help.
So, how do YOU improve the performance of your import.
BTW, there are over 2,000 tables, 1,700 indexes and 400 stored procedures
in
this database. Running Oracle 7.3.4 on an HP K380, 1 GB memory,
HPUX 10.20.
Ans1:
if you are moving the entire database - why export/import? Why
not just backup
and restore to the other machine. That way you don't have to rebuild
indexes,
recreate procedures, etc, etc, etc.... Database sounds smallish
given the times
above (maybe 3-4gig?) It would go oh so fast to cold backup,
and restore it on
the other machine.
If you must use export/import, are you 'parallelizing' your operation?
(eg:
export schemas in parallel on the source machine and import them all
simultaneously on the target machine -- or as many in parallel as the
machine
can stand...)
Ans2:
But, if you really need to export/import (because or db versions or
OS versions),
there is an excellent document called "Techniques for fast database
reorg" from
Nigel Noble. It has all the hints to speedup this process.
If you need a copy, i could send you a DOC file of it.
Marc Mazerolle