Re: using temp tables for staging databases?

As I mentioned, BBW has many causes and solutions, one of them is to reduce the packing factor of the block.

Other causes of BBW - monotonically ascending indices, typically PKs based on sequence; can be solved by using reverse key indices. But anyway, the OP asked for reason for using the non-default values PCTFREE and PCTUSED and one of the considerations is BBW, as my posting alluded to.

Arup Nanda

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, October 21, 2003 11:54 AM

> 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" <ORACLE-L_at_fatcity.com>
> > 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: 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).
> > >
>
> 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: mladen_at_wangtrading.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).
>

-- 
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).