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

Performance probem with INSERT statement

I am facing a performance problem with an INSERT statement inserting records in one transaction table. The table has around 20 columns and all are NOT NULL columns. It has seven indexes and many constraints. There are many concurrent inserts happening (maybe around 1,000). We have run the database performance health check script obtained from Metalink and tuned accordingly. The problem still occurs.

I am facing a performance problem with an INSERT statement inserting records in one transaction table. The table has around 20 columns and all are NOT NULL columns. It has seven indexes and many constraints. There are many concurrent inserts happening (maybe around 1,000 concurrent inserts).

We have run the database performance health check script obtained from Metalink (Note:1019592.6) and tuned accordingly. The problem still occurs. We don't have the SQL trace or explain plan of the statement and it is difficult to get as it's a production system. The table and indexes are analyzed and statistics are up to date.

Following is an entry obtained from Statspack:

Physical reads: 101,419
Executions: 129,699 
Reads per Execution: 0.8
% Total: 0.1
The Oracle database version is 8.1.7.4.

I know the information provided is not enough but this is what I have now from the customer. Any help will be greatly appreciated.

Without trace information, it's hard to tell, but you may well be running into contention for the table's freelist, which identifies free blocks in which rows can be inserted. Even if you can't trace, you can query v$session_wait to see what processes are waiting for. If you see a large number of "buffer busy waits" for segment headers, freelist contention is probably the culprit. One way to ease this contention is to create multiple freelist groups, so that different processes can inspect and modify freelists in parallel.
This was last published in December 2005

Dig Deeper on Oracle and SQL

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