impdp performance (with partitioned tables) bit me in the ...

From: <Christopher.Taylor2_at_parallon.net>
Date: Tue, 18 Sep 2012 09:00:45 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E885159DF59C_at_NADCWPMSGCMS10.hca.corpad.net>

Listers,
Version: 10.2.0.4

PARFILE:
...
...

Parallel=6
EXCLUDE=TABLE_STATISTICS
EXCLUDE=INDEX_STATISTICS
DUMPFILEFILE1,

FILE2,
FILE3,
FILE4,
FILE5,

FILE6
...
...

I'm a fair hand with expdp and impdp but I'm still learning my way around partitioned tables.

Yesterday I had to refresh 2 databases and I kicked off an impdp job for the first one and the data itself loaded almost exactly 1 hour.

However, 5 hours later, the impdp finally finished - I was in shock.

So when I did the 2nd database, I setup a monitoring script to watch v$session_longops.

I noticed the following issues on the import: 1.) Index creations get analyzed even with EXCLUDE=INDEX_STATISTICS - found the solution to this on Metalink for 10.2.0.1 -> 10.2.0.4 (indexes get analyzed on creation by design - can be disabled with a parameter)

2.) PARTITIONS get ANALYZED by default on the IMPDP - This one caught me by surprise. This becomes an issue because we have 5 partitioned tables with over 8000 partitions - (I have no idea why [yet])

Any of you seen the behavior in #2? And if so, do you (or anyone) know if you can do an EXCLUDE=PARTITION_STATISTICS? (or in some other way prevent partitions from being analyzed during the import)

(My strategy for dealing with statistics was going to be to IMPORT statistics using a STATTAB after the import to get around the performance hit of gathering statistics)

Any suggestions (OTHER than not having tables with 8000 partitions lol)

Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 18 2012 - 09:00:45 CDT