| Delaying insert... [message #299156] |
Sun, 10 February 2008 03:52  |
ataufique
Messages: 79
Registered: November 2006
|
Member |
|
|
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.
Thanks
|
|
|
|
|
|
| Re: Delaying insert... [message #299210 is a reply to message #299156] |
Sun, 10 February 2008 11:41  |
 |
Kevin Meade
Messages: 2103
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:
| Quote: | 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.
http://www.jlcomp.demon.co.uk/faq/slowdown.html
I DO NOT CLAIM THIS IS YOUR PROBLEM. It is a good read however
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
