No, blocks won't fill up to PCTFREE in case a block
is already above PCTUSED and the next insert would drop amount of free space
less than PCTFREE. Blocks are just unlinked from freelist in this case. That
means if you normally have 10byte inserts and occasionally have 4000 byte
inserts in your table, then you might be wasting space due to premature unlink
of blocks in freelist.
So, as Arup already said, PCTFREE and PCTUSED
definitely affect block space utilization, even in insert-only
environment.
Tanel.
----- Original Message -----
Sent: Wednesday, October 22, 2003 6:39
AM
Subject: Re: using temp tables for
staging databases?
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). >
|