Re: BCHR Tuning

There was a flurry of notes about Richard Niemiec's most recent article in Oracle Magazine, about which I passed the comment:

>
>Having said that, I thought the article was far better
>than usual. There was still plenty of scope for
>criticism, but it seemed to convey more useful
>information than usual, even though presentation
>and ordering were somewhat garbled in places, and
>there were several small errors and misunderstandings.
>

Jared, I think, suggested that it would be it would be appropriate to produce some sort of critique of the article to shed light on some of the criticisms that it had received.

The following question came up on comp.databases.oracle.server today, so I took a few minutes to answer it - and thought I would offer my comments to the rest of the list for review. Since the question was about the paragraph on buffer busy waits, I have dissected just that paragraph.

<Start of Question>
Don Burleson wrote in message
<998d28f7.0301161859.201a5063_at_posting.google.com>...
>Rich Niemiec, president of IOUG writes in Oracle magazine (page 100,
>Jan 2003):
>
>Buffer Busy - "This is a wait for a buffer that is being used in an
>unshareable way or is being read into the buffer cache. . . . wait
>is on a segment header. If this is the case, increase the freelist
>groups or increase the pctused to pctfree gap."
>
>I was always under the impression (from Oracle Support) that:
>
>1 - An instance is only able to attach to one freelist group, and
>multiple freelists groups are only for OPS/RAC systems.
>
>2 - The best way to reduce segment header contention is to add
>multiple freelists, not freelist groups.
>
>Has anyone ever tried multiple freelists groups or increasing the
>PCTUSED-PCTFREE "gap" top reduce segment header contention?

<End of Question>

<Start of Reply>
I had to read the full text to check if there was any omission in your extract that might explain the oddity:

Quoted text is at the margin, commentary indented:

<Start of quoted article>

4. Buffer Busy. This is a wait for a buffer that is being used in an unshareable way or is being read into the buffer cache.

    Sloppy wording, but technically not wrong, however     (a) there are ways that a buffer can be used in an     unsharable way that are not BBWs (e.g. write complete     waits - although the newest versions of Oracle bypass     this one). (b) grammar - if you eliminate the middle clause,     you get "a buffer that ... is being read into the buffer     cache" - data blocks are read into the buffer cache, the     buffers are already there. (c) if a block is being read     into the buffer cache, the buffer IS being used in an     unsharable way, so the 'or' would be better as 'e.g.'

Buffer busy waits should not be greater than 1 percent.

    One percent of what ? Physical reads, consistent gets,     consistent gets plus current gets, consistent gets plus     current gets plus buffer is pinned count ?

    If my buffer busy waits are much less than one percent     of (say) my consistent gets, should I dismiss the issue -     even if all those waits are for segment headers ? Should     I dismiss the issue if the wait time is significant ?

Check the Buffer Wait Statistics section (or V$WAITSTAT) to find out if the wait is on a segment header. If this is the case, increase the freelist groups or increase the pctused to pctfree gap.

    Step one for segment headers is to examine the freelists,     not the freelist groups. There are side-effects to freelist     groups that you do not want to introduce if the problem can      be addressed through freelists.

    WHEN does increasing the pctused to pctfree gap help ? And     should this be done by increasing the PCTFREE or decreasing     the PCTUSED - or perhaps by changing both in the same direction     but by different amounts. This comment displays the worst     aspect of the 'quick tip' psychology - it seems to be designed to     show that the author has some deep insight into subtle mechanics,     but in the absence of an explanation the comment could encourage     the novice DBA to do something inappropriate.

If the wait is on an undo header, you can address this by adding rollback segments;

    I can agree with that - on the other hand, if the wait time     is not significant, this might increase I/O activity and help     to overload the I/O subsystem - so I might advise caution,     and I might advise reducing the size of the rollback segments     at the same time.

if it's on an undo
block, you need to reduce the data density on the table driving this consistent read

    This addresses the issue of one user trying to read     an undo block whilst another user is modifying it -     which could happen when one set of processes needs     to read data in consistent mode VERY shortly after     something else has changed it AND the undo blocks     containing the required undo are still the most current     undo blocks and are still subject to change. Data density     is not really the issue. (Of course, you might ask how     you find out which table is driving the consistent read)

or increase the DB_CACHE_SIZE.

    I guess this is because a larger cache means you may     keep undo blocks cached longer - so you reduce the     probability of re-reading them - and having two processes     trying to read a single block simultaneously is one way     of getting a BBW.

If the wait is on a data block, you can move data to another block to avoid this hot block,

    Could work if the problem comes from multiple processes     modifying the same block - could make matters worse if     the problem comes from multiple processes trying to read     blocks from disk concurrently. Of course, whilst you may     be able to do this for table blocks, index entries have to     where they are supposed to go, so you can't move them     to another block.

increase the freelists on the table,

    Good - but only relevant if the BBWs are due to inserts,     and this advice doesn't help if the BBWs are on index     blocks, of course.

or use Locally Managed Tablespaces (LMTs).

    perhaps he's thinking of auto segment space management.     But there is no reason why a data block from an LMT should     be treated differently from a data block from a DMT.

If it's on an index block,
you should rebuild the index, partition the index, or use a reverse key index.

    But V$WAITSTAT doesn't have a category 'index block' - so     how about a comment on how you find out if the problem is     table blocks or index blocks ? All three solutions may be of     benefit in the right circumstances - and horribly counterproductive      otherwise.

To prevent buffer busy waits related to data blocks, you can also use a smaller block size: fewer records fall within a single block in this case, so it's not as "hot."

    Good point - especially in Oracle 9; and especially if the     waits are for UNDO blocks, although that might (depending on     the nature of the activity) increase the waits on segment header     blocks

When a DML
(insert/update/ delete) occurs, Oracle Database writes information into the block, including all users who are "interested" in the state of the block (Interested Transaction List, ITL).

    Technically it's the transactions that have recently changed     the block, not users who are interested (surely anyone reading     the block is likely to be interested ?) but I'll let that pass as a

    minor verbal slip.

To decrease waits
in this area, you can increase the initrans, which will create the space in the block to allow multiple ITL slots.

    You don't get buffer busy waits when initrans is too small,     you get processes waiting on ITL slots, and these appear     as enqueue waits for TX enqueues in Share mode (mode 4).

You can also
increase the pctfree on the table where this block exists (this writes the ITL information up to the number specified by maxtrans, when there are not enough slots built with the initrans that is specified)

    I think he was trying to say this ensures you start off with     extra space in the block that may allow the ITL to grow     above the initial allocation.

<end of quoted article>

So - to answer your question:
The manuals have maintained for many years that freelist GROUPS apply only to OPS - but they have been wrong for a long time. Freelist groups work in single instance Oracle.

Switching to multiple freelists is usually sufficient to deal with segment header contention; and if it works, a better strategy that multiple freelist groups. Moreover, you can switch to multiple freelists dynamically in recent versions of Oracle, whereas a switch to multiple freelist groups requires a segment rebuild. It is important to remember that the freelist and freelist groups value are best as primes if you set just one, and should be co-prime if you use both because of the 'hash' method that Oracle uses to assign processes to a freelis

<End of Reply>

My first question - of course - has to be to ask if there are any significant errors or omissions in my commentary. If not, then am I expecting too much from the author, or is it just my intense dislike of shallow articles that fail to explain or justify that is colouring my opinion of the content ?

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____England______January 21/23
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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