Buffer busy waits on primary key index

Buffer busy waits on primary key index

 

> QUESTION POSED on: 25 January 2007

 

We have an 800+ gig data warehousing database. Recently we ran a batch program opening up four threads (four sessions doing inserts) and inserting into a table price_sku_ld_dm having 1 billion+ records.

 

The table price_sku_ld_dm is range partitioned on day_idnt. It has a primary key based on sku,location and day_idnt.

 

The table and the primary key index have freelist of 20. The index has pctfree of 5%.

 

I can see that while this program is running, there are buffer busy waits on the primary key index.

Amongst those four threads, one is doing sequential read, two threads are having buffer busy waits on the same block in the index tablespaces datafile. The fourth thread is on a completely different block in the index datafile.

 

I can see through tkprof that the insert statement faced buffer busy waits for 190 times and the duration was 1.93 (seconds?). The insert process took about nine hours to finish.

What can be done to improve performance of the insert statements? Secondly can we explore reverse key indexes? Or will rebuilding the index with higher pctfree help?
 

 
> EXPERT RESPONSE

Are the indexes in place during the insert? If so, drop them before the insert and re-create them afterward.