expdp…so sloooooowwww..???…Time to spice it up!

At times when I do an export (expdp) from a database, the export just seems to last for ages…and then some..

While I was waiting one time for the export to finish, I found out the expdp in Oracle 11g make use of the streams_pool_size. I had AMM running,which should be responsible to up the pool,  but still the process was slow. After checking, it turned out the streams_pool_size was 0 (as it is supposed to be with AMM enabled). The command to check the current set streams_pool_size is:

SQL> show parameters streams_pool_size;

So I killed the job, fired up an sql session to the database and gave the next command:

SQL> alter system set streams_pool_size=40M;

of when running a RAC (RacOne) instance:

SQL> alter system set streams_pool_size=40M scope=both sid='testdb';

After this change I started the expdp again, and it was done in a jiffy…Of course it depends on the size of the export, but if the sga_max_size allows it, you can always increase this value.

Until next time!

Advertisements

About GemsOfProgramming

Beeing a previously enthusiastic Java programmer, I rolled into the Oracle Database Administration world. It turned out I got a knack for this, and since approx. 2000 I'm a full time DBA. My experiences touches lot of Oracle products like Forms and Reports 9/10, JDAPI, Application Server, Weblogic Fusion and of course: Oracle Enterprise Databases, JavaFX, Swing and other Java components.
This entry was posted in Databases, Technical Stuff and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s