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>You can easily determine the waiting SQL statement based on the waiting session's v$session.sql_id column. Eventually, you will probably need to talk with application developers, as situations like these are typically very application-specific. I have found this situation commonly (but not always) is the result of Oracle sequence numbers not being used. When asked why Oracle sequence numbers are not being used, you may receive a response such as, "Well, we want our application to be database vendor independent." This is another way of saying, "Well, we decided to write our application at the lowest common denominator to make it easy to port."
</p><p>The problem is that this rapid buffer access puts an incredible high concurrency requirement on the buffer's internal structures. When the internal structures are being changed, the block will be unavailable to other processes, issuing the buffer busy waits event. Because changing the architecture of the application is usually not a realistic option (but should be considered), creatively finding ways to spread the popular row information or popular rows is a solid approach. For example, if each row contains an application sequence number (like the next check number), then move the rows into their own blocks, add a large fixed-length column, or set pct_free to a very high value to keep the number of rows to a minimum. This solution is painful, but not as painful as continual buffer busy waits.
</p><p>Multiple busy table blocks are by far the most likely buffer busy waits (pre-Oracle Database 10g) and read by other session (Oracle Database 10g and later) wait event situation. In this case, each time we check for busy buffers (for example, Figure 6-25), they are different, the busy buffers are data segments, and they are not header blocks. When this situation occurs, the cause is usually centered around queries or a mix of DML and queries.
</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!
|
