Q
Problem solve Get help with specific problems with your technologies, process and projects.

Buffer busy waits on primary key index

While a program is running on our table, there are buffer busy waits on the primary key index. What can be done to improve performance of the insert statements?

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?

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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close