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
<[EMAIL PROTECTED]>...
>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 counter-
productive 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: [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).
