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>One final note: Because there will be intense index insertion in every leaf block, to reduce the frequency of leaf block splitting, consider adding the pctfree 50 storage parameter when creating reverse key indexes.
</p><p>To give you an idea of the performance impact of using a reverse key index, I created the test situation I described earlier (in "The Situation" section). Over the 120-second test interval, using a nonreverse index, 75% of all wait time was related to buffer busy waits events on an index block with a transaction rate of 0.80 trx/sec. (Each transaction inserted 50,000 rows.) I reran the test with the only difference being the creation of a reverse key index. Over the 120-second test interval, 28% of the wait time was related to buffer busy waits events, with a transaction rate of 0.95 trx/sec. That's great news, because the wait time decreased and the transaction rate increased!
</p><p>The bad news is while resolving the buffer busy wait issue, we could have significantly impacted query performance. Suppose we optimized a query based on a nonreversed sequence number column index. But suppose because of buffer busy waits, we dropped the nonreversed key index and created a reversed key index. Now those nicely ordered sequence numbers are scattered all over the index leaf blocks. That high-performing index range scan may not perform so well now.9 In fact, the cost-based optimizer (CBO) should recognize this situation and devise another execution plan strategy. Otherwise, the query could potentially touch every index leaf block and also a large number of data blocks!
</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!
|
