Yes, I understand BBW, and how PCTFREE affects
block density, and hence BBW. My point, which you appear to have missed, is
PCTUSED does not affect block density which is determined by PCTFREE.
Lets take a simple case of 10% PCTFREE, and 40%
PCTUSED. Blocks will fill up to 90% _no matter_ what you make the PCTUSED figure
to be. Only if you have lots of updates/deletes that PCTUSED comes into
play but then you have a 90% packed block to begin with, so its effect is
marginal. PCTFREE alone will reduce block density if that is the aim.
In your paper, you changed several parameters at the same time - including
freelists which does not affect block density. From a statistical/testing point
of view you cannot really draw any conclusions.
Lower block density may reduce BBWs, but you
still have to process the same number of rows. And you have to process a higher
number of blocks which incurs an additional cost. Question becomes - has the
problem been simply shifted somewhere else?
----- Original Message -----
Sent: Wednesday, October 22, 2003 4:19
AM
Subject: Re: using temp tables for
staging databases?
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
----- From: "Binley Lim" <[EMAIL PROTECTED]> To: "Multiple
recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday,
October 21, 2003 10:24 AM Subject: Re: using temp tables for staging
databases?
> > 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). >
|