Home » RDBMS Server » Performance Tuning » Delaying insert...
Delaying insert... [message #299156] Sun, 10 February 2008 03:52 Go to next message
Messages: 79
Registered: November 2006
I am observing that as the amount of data on my database
in incresing the same insertion is taking longer time..
My db storage is terab and server mem is 32gb.

what factor i should consider to tune the db.

Is it some thing to do with SGA ?
Currently the SGA is set at 650mb.

What should be the recommended SGA for a terab db.

I await your valuable suggestions.
Re: Delaying insert... [message #299157 is a reply to message #299156] Sun, 10 February 2008 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 65148
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which insertion? Post the statement.

Please read OraFAQ Forum Guide and read How to Identify Performance Problem and Bottleneck and provide the requested information for Performances question.


Re: Delaying insert... [message #299210 is a reply to message #299156] Sun, 10 February 2008 11:41 Go to previous message
Kevin Meade
Messages: 2102
Registered: December 1999
Location: Connecticut USA
Senior Member
While you are answering Michel's questions, you can take a look at this too. The author does provide a test case so you can try that if you like. A convincing article. The article pins on two basic points:

1) small indexes fit in cache, large one don't
2) rows inserted in key order take advantage of cached index pages, rows inserted randomly (based on the index columns), don't benefit very much from index pages being pre-cached.

So, large indexes, accepting inserts in an order that is "random" for the columns they are built on, means disk IO to get the index pages in memory so that can be updated, accompanied by the fact that these pages will likely be flushed before they are needed again.

Suggestion is: load data in sorted order for the index so you can take advantage of cached index pages and reduce page thrasing related to index updates.

Reality is: you likely have several indexes on your table, and thus any given pre-sort of the data before inserting will in most cases only benefit one index. In the end, if this is your problem, there may be little you can do other than drop indexes before a load and rebuild which is only good if you got lots of data to insert.

Read the article, its pretty good.


I DO NOT CLAIM THIS IS YOUR PROBLEM. It is a good read however

Good luck, Kevin
Previous Topic: Improvong performance of SQL script
Next Topic: sql tuning
Goto Forum:

Current Time: Mon Aug 21 13:01:51 CDT 2017

Total time taken to generate the page: 0.03915 seconds