Craig Shallahamer's Blog
|
You were brought to this page based on an internet search
and as a free service to Oracle DBAs.
The text below is an except from the book,
Oracle Performance Firefighting, written by
Craig Shallahamer of
OraPub, Inc.
Figures and tables are not included on this page, only their reference.
To order the book in either print or PDF form, click
here.
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
-------------------------------
<p>While index ordering enables quick searching, in a high-concurrency insert situation, it can cause severe performance problems. For example, suppose an index is based on an increasing sequence number (for example, 1, 2, 3, and so on), which is commonly called a monotonically increasing value. If a table column contains this sequence number and that column is indexed, the respective index entries will be placed next to each other in an index leaf block. Why? Because an index must maintain sorted order, and this index is based on an ascending sequential number.
</p><p>The problem can occur when many sessions concurrently insert rows. As each insert statement gets the next sequence number, the index entry will likely be physically placed into the same index leaf block or less likely into the adjacent index leaf block. If the concurrency increases enough, sessions will need to wait a relatively long time for another session to complete its leaf block change. When the session waits, it will post a buffer busy waits event, and the buffer it is waiting on will be an index leaf block. This situation can become extremely serious and a massive performance killer.
</p><p>This is actually quite easy to simulate. Simply create a table with a column that will contain a sequence number. Create an index on the sequence number column, and then create the sequence with an increasing value of one. Now have multiple sessions (perhaps only ten) begin concurrently inserting rows into the table. Make sure each of these sessions uses the next sequence number in its insert statement. Since the sequence numbers will be sequential, the indexed column contains the sequence number, and the index entries must be stored in sorted order, it is very likely that nearly all of the concurrent sessions will be making entries into the same index leaf block! With this load occurring, run any wait event report, or simply look at the v$session or v$session_wait views, and observe the sessions posting buffer busy waits events. If you determine the object based on the file number and block number (p1 and p2), it will be the index! Exciting yes, but very bad for performance.
</p>
©2009, 2010 by Craig Shallahamer. This is copyrighted material.
Please—Out of respect for those involved in the creation of the book and also for
their familes, we ask you to respect the copyright both in intent and deed. Thank you.
|
|
Know what's important before it's too late!
OraPub's
Performance Training
is like no other...
More Class Pics...
Get student testimonials!
|
