Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: using temp tables for staging databases?

Re: using temp tables for staging databases?

From: Arup Nanda <orarup_at_hotmail.com>
Date: Tue, 21 Oct 2003 07:19:32 -0800
Message-ID: <F001.005D3DDE.20031021071932@fatcity.com>


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

>
> 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: Binley.Lim_at_xtra.co.nz
>
> 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: ListGuru_at_fatcity.com (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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.com

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: ListGuru_at_fatcity.com (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).
Received on Tue Oct 21 2003 - 10:19:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US