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>Fortunately, the solutions are very straightforward and they work very well. If you are using manual segment space management, then segment space management is controlled by what are called free lists. As shown in Figure 6-30, I picture a free list as a jellyfish-like structure, with each of the stinging strings a list containing nodes, which point to free blocks. Oracle's free list approach usually works fine, but in high-concurrency situations, the existing free lists simply cannot handle the workload. Fortunately, we can easily alter the table to create additional free lists in another one of the segment's blocks. The result is a header block popularity reduction, and hence a reduction in buffer busy wait contention. Simply keep adding free lists until the contention subsides. The number of free lists can be found in the free lists column in the dba_segments view. If the column is empty, then we know free lists are not being used and automatic segment space management (ASSM) is being used.
</p><p>Another option, which focuses more on the longer term, can be performed during a maintenance opportunity. This is to move high-concurrency segments into a locally managed tablespace with ASSM enabled. ASSM does not use free lists, but instead uses bitmaps to manage free space. While this usually allows for increased table segment header block concurrency, I have spoken with a few of my students who have unexpectedly experienced increased buffer busy activity when converting to ASSM. For some reason (and they never found out), an unknown number of events (for example, workload, Oracle release, application design) had contributed to stressing ASSM in an unexpected way.
</p><p>Undo segments are different from table segments because they contain information related to transaction rollback and read consistency. One of the Oracle structures central for rollback and read consistency to properly occur is called the transaction table. Simply put, the transaction table is a map to an undo segment's contents. Every undo segment contains a single transaction table, which is located in the undo segment's header block. When intense DML, especially when combined with read consistency activity, occurs, the transaction table can become the point of contention, resulting in buffer busy waits events on an undo segment header block. Before moving into how to solve this issue, an introduction to the transaction table is warranted.
</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!
|
