All that is nice, but from my practice so far, by far the most frequent cause of the buffer busy waits id DBWR being unable to catch up. This can come as a consequence of several things: - Poorly written transaction that modifies thousands of blocks during peak time hours. Typical example is bill generation, which generates the table from which the bills are printed, and it'usually done during peak hours. It generally slows down everybody else, causes a lot of screaming and cannot be resolved by increasing the cache hit ratio. Moving bill generation to operational data store, combined with replication and spreading the load over a period of time can solve these. Alternative solution is not available ever since Richard Kuklinsky, the Ice Man, is off the market.
- Slow peripherals and insufficient I/O bandwidth, usually caused by magazine reading PHB. DBA needs to develop a healthy cynical attitude and desperately try spreading the workload throughout the 24 hours and all 7 days in a week. Disproportionately high number of these sites are running windoze and are easily recognized when the IT manager tells you about the wonderful Matrox Millennium card that he has in the database server and quotes the number of OpenGL operations his new database server can do.
- Very high transaction rates and inability of the CPUs to handle the load. In this case there are so many transactions that DBWR is unable to catch up. That happens when the system is in desperate need of a good upgrade. This usually happens in places where the system is stabilized and the business users say that they have what they need and that no major work should be done on the boxes. Candidates are sites which are running things like 7.3.4 and 8.0.6 today. Of course, when an upgrade actually is needed, panic spreads and hit and run consultants are brought in to make things worse.
On 10/21/2003 11:19:32 AM, Arup Nanda wrote:
Binley,
The cause of Buffer Busy Waits (BBW) is not exclusively the setting of
PCTUSED and PCTFREE; they just two of the causes. To understand the
connection, let me explain a little bit on the cause of BBWs.
When a session requests some data element from a table, the server
process of the session gets the block from the disk to the cache
(assume the block is not present in the cache). The event of the block
coming from the disk to occupy a buffer in the caceh is pretty
straight forward. Now, imagaine, at the exact same time another
session selects a row from the same block. A *different* row but from
the *same* block. That session will search the cache buffer chain and
see that the buffer is not present and will attempt the same
maneuevre, i.e. get the buffer from the disk. However, the first
session is currently moving the buffer; the second session has to
*wait* till the process is complete. This wait is known as buffer busy
wait (BBW); but I guess you already knew that. The two sessions are
not in conflict over the same row, but the same buffer; so it's not
locking contention.
How can we eliminate BBWs? Unfortunately we can't bring it to zero. There is always a probability that two sessions will try to get the same block. The only exception is when a block contains only one row. In that case the sessions will select different blocks for different rows. Again, this is not practical.
We can reduce BBW by reducing the *possibility* that two sessions will
not try to access the same block. This can be done using several ways:
(1) reducing the block size (2) making a block less compact, so that each block holds less number of rows. The fewer the number of rows in a block, the lesser the probability that two sessions will access rows in the same block.
The first option is not a very practical one in most cases. The second
option is. It can be effected by allocating less space in a block,
which can be done by using a large value of PCTFREE, e.g. 40 and/or
small value of PCTUSED, such as 40, instead of 99. Other ways to
achieve the same result is using a higher value of INITRANS, or
anything that will cause less number of rows to fill up a block. Less
rows => less chance of BBW occuring.
I wrote a paper in Select Journal a few months ago explaining this
very situation. Although the article is on Segment Level Statistics,
it has an example which you can simulate to see the effect of
PCTFREE/PCTUSED/INITRANS on Buffer Busy Waits. It can be downlaoded
from my website at www.proligence.com/downloads.html and choose New
Tool on the Block - Segment Level Statistics. Please feel free to give
it a whirl.
Further qualifying the case for higher PCTUSED and lower PCTFREE in datawarehouse environments, the chance that two sessions will access the row in same block is much less in DW than in OLTP. Hence the values can be different in DW.
HTH.
Arup Nanda
----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 10:24 AM
>
> I'm unclear how BBW is related to PCTUSED. PCTUSED is used to
control when
> blocks are returned to the freelist due to deletions. Blocks
already-off the
> freelist, and above PCTUSED, remain unavailable for inserts.
>
> PCTUSED does not prevent a "block contains too many rows" -since a
low
> PCTFREE will pack the rows tightly anyway. If BBW wait is a problem,
then
> there are other causes. PCTUSED is not one of them, or at least
should not
> be an attempted solution.
>
> > I will also add to Tim's response of justifying a smaller PCTUSED.
In
> > addition to the freelist problem he mentioned, there is also a
greater
> > chance of buffer busy waits occuring when a block contains too
many rows.
> In
> > an OLTP database that is certainly likely to happen - another case
for the
> > default 40 setting for the parameter. In DW, however, the chances
of BBW
> are
> > low, hence a higher setting may be possible.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Binley Lim
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting
services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
Mladen Gogala Oracle DBA
Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
