tuning performance of disk sorts (imp)

[DBAwhosaysNIE]

dudes/dudettes,

I am in the process of migrating some legacy 8.1.7.4 databases to 10.1.x. it looks like my bottleneck is creating indexes (disk sorts) after the rows are imported.

are there parameters you can tweak to squeeze this or is pretty much limited by your storage subsystem?

 

[SantaMufasa]

DBA-Duder,

I suggest your boosting your "sort_area_size" to the largest number you can afford. This causes more of the index sorts to occur in memory, which is the best tuning thing you can do for sorts.

Once you've done that, and since your migration is a one-time shot (hopefully), then you sit back and enjoy (...It's not something that occurs daily, so it's not a big deal.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stefanhei]

In case your WORKAREA_SIZE_POLICY is set to AUTO tweak PGA_AGGREGATE_TARGET instead, as SORT_AREA_SIZE is no longer used then.
In case you use SORT_AREA_SIZE also set SORT_AREA_RETAINED_SIZE to the same value.

Good luck with your migration!

Stefan