BUFFER BUSY WAITS
Paul L.
on my datafiles for indexes, and some tables.
I tried to increase the initrans parameter as suggested by Rich Niemic in
his tuning book, but it didn't help.
Does any one have any experience with this ?
Thanks.
--
Paul
--
Paul S. LaBarbera
University of Phoenix Online Faculty
pau...@email.uophx.edu
--
Ganesh Raja
the data Level. If the Buffer Busy Waits ocur at the Segment Header then there
is contention for the Freelist. If it occurs at the Row level then the server
process is waiting for another server process to rel the buffer. This value is
also directly prpoytional to the Free Buffers Scanned.. A Increasing Value
should always take you towrads tuning the Buffer Cache.
Make the Buffer Cache Big and also try to cache your tables so that they are not
thrown out after a FTS.
HTH
[Additions and Corrections Always Welcome.]
Best Regards,
Ganesh R
Ricky Sanchez
1. Take the Rich Niemic book and toss it in the trash. The man is a
complete idiot.
2. Get a Statspack report for the busy period. If "buffer busy waits"
are not in the top five wait events at the beginning of the report, you
do not have a buffer busy waits problem. You have some other bottleneck,
as evidenced by the #1 wait event. If you have not installed Statspack,
do so immediately. You can find the scripts on $ORACLE_HOME/rdbms/admin.
Takes about five minutes. Works great. Read the doc.
3. Buffer busy waits may have a number of root causes. Get a statspack
report for the busy period and look at the "buffer busy" section. See if
you have Data, Segment Header, Undo or Undo Header waits. If the main
wait is data, you have to further analyze them and see if they are table
or index waits. The treatment of each is quite different. Some sql is at
the end of this message to help out. Play with it, it is coming off the
top of my head and I am syntax-error prone.
Diagnostic Notes:
Undo / Undo header waits: generally, increase the number of rollback
segments to spread the transaction load among more rollback segments,
which headers are also known as "transaction table".
Segment header: you might need to implement Freelist Groups. This
creates additional header blocks for the table to store freelist
information. Freelist groups were initially meant for parallel server,
but work fine for exclusive instances as well. Requires recreating the
table.
Data/Table: Probably means you need to increase freelists for the table,
or perhaps implement freelist groups as mentioned above. This is
probably the case if the sql operation is an insert. If the sql op is an
update, you might have a silly application design problem, with many
users updating the same rows at the same time. In that case, fix the
application.
Data/Index: Poor application design is probably the issue. Most likely
heavy inserts with a monotonic sequentially increasing index, as when a
sequence is used. Key values get skewed, since each new value must go
into the same block as the previous key. Index key values are physically
ordered, so there are two ways around this: 1) use something other than
a sequence to assign key values or 2) use a "reverse key" index. Reverse
key indexes do a byte reversal on key values before insertion, so each
new sequence number get stuffed into a different block than the previous
value. You might also have too many indexes. If so, analyze the
application and get rid of some indexes.
Well, that is the short course on buffer busy waits. More to tell, but
no space or time now. Check Metalink for better info. Maybe Jonathan
will jump in here with the obvious stuff I missed.
And get rid of that Rich Niemic crap.
- ricky
SQL to diagnose buffer busy waits:
to find the hot block, run repeatedly during busy period. Spool results,
look at top "p1" and "p2" value pairs:
select event, p1, p2 from v$session_wait where event = 'buffer busy';
p1 is the file#, p2 is the block. Plug values into this query:
select segment_name, segment_type, owner, tablespace_name
from sys.dba_extents
where file_id = <p1>
and <p2> between block_id and (block_id+(blocks-1));
The idea is to find what buffer is busiest, then figure out what object
is involved. Read above notes to figure out how to respond to each
object type.
Nuno Souto
>1. Take the Rich Niemic book and toss it in the trash. The man is a
>complete idiot.
thank you. I thought that too, but I've been told by local "powers
that be" at Oracle Professional Services that he's God and anyone else
from TUSC is an archangel.
Mind you, this was a few years ago when anyone from Australia was
"rubbish" and anyone from overseas was God.
That has changed somewhat too (thanks, SA!). And about time.
>Data/Index: Poor application design is probably the issue. Most likely
>snip of good stuff.
>value. You might also have too many indexes. If so, analyze the
>application and get rid of some indexes.
or if you're faced with a third party app where you can't change a
thing without breaking the "warranty", whatever that means nowadays
(Peoplesoft and others spring to mind):
look at speeding up access to these index blocks by splitting your
buffer cache using the DEFAULT/RECYCLE/KEEP mantra and allocating
"problem" indexes to the right cache. Highly dependent on nature of
table usage which of RECYCLE or KEEP one uses.
>
>And get rid of that Rich Niemic crap.
>
hope you got a good backing, and agreed.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Yong Huang
good for DBA starters? Mr. Niemic's book is probably the most
deceiving among all DBA books. But please refrain from using words
like "idiot"!
Yong Huang
Ricky Sanchez <rsan...@more.net> wrote in message news:<3C7194ED...@more.net>...
Keld Nielsen
When do you start to contribute with something which is of any use in this
forum ?
"Nuno Souto" <nso...@optushome.com.au.nospam> wrote in message
news:3c723412...@news-vip.optusnet.com.au...
Bricklen
Nuno has contributed some very insightful and valuable information in
previous posts. I've always found his posts thoughtful and they
generally bring a grin to my face.
Cheers,
Bricklen
damorgan
If you aren't finding things of value ... perhaps you are looking in the wrong
place for enlightenment. <g>
Daniel Morgan
Connor McDonald
I did not rate the book highly - but the man *did* sit down and put pen
to paper which was more than most of us (including myself) have done.
So I don't think its appropriate to launch a tirade upon him.
hth
connor
--
==============================
Connor McDonald
"Some days you're the pigeon, some days you're the statue..."
Ricky Sanchez
part of the Oracle doc set. And the new 9i book on the Oracle
Performance Method is also part of the doc set. It offers a good
methodology, although it is a long read and focusses heavily on the
server. The DBA 101 Performance Tuning book by Vaidyanatha, et al
presents a reasonable methodology, so that would be a good candidate. On
the other hand, I just tried to find information on buffer busy waits
and only found a discussion of undo segments and undo header waits.
Nothing on data segments or headers. Bummer.
Regarding the term "idiot", I must agree the term does not do the
illustrious Mister Niemic justice. Plenty of legitimately impaired
people out there would be offended to think I place him in their
company. Perhaps "blithering idiot" is more to your liking?
- ricky
Nuno Souto
Keld Nielsen doodled thusly:
>You and your 'doodled thusly' !
>
>When do you start to contribute with something which is of any use in this
>forum ?
>
And your contribution is a personal attack?
Get lost.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Nuno Souto
one thing I must admit, Ricky: you don't mince words.
:-D
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Nuno Souto
>
>If you aren't finding things of value ... perhaps you are looking in the wrong
>place for enlightenment. <g>
>
he's looking at the "X doodled thusly" bit...
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Ricky Sanchez
>
>
> I did not rate the book highly - but the man *did* sit down and put pen
> to paper which was more than most of us (including myself) have done.
> So I don't think its appropriate to launch a tirade upon him.
>
> hth
> connor
> --
If you have read the book, or any of his articles on performance tuning
- and know something of the subject yourself - you would have to agree
he says idiotic things. Like, the answer to all shared pool latch
contention is to increase the size of the shared pool until the problem
goes away. Just dumb crap from someone who clearly knows little of
Oracle internals. Not just dumb, but dangerous.
So, when such a person endeavours to write a book on the subject-
indeed, assumes a role of leadership in the Oracle community - he owes
it to himself and the community to get it right. Niemic does not get it
right. The fact that he bothered to publish his crap affords him no
credit at all, only further discredit. To blatantly mislead is worse
than doing nothing at all.
Calling him an idiot is hardly launching a tirade. Besides, he *is* an
idiot.
- ricky
Bryan W. Taylor
call the book "deceiving"?
I can't possibly imagine any reason to call him an idiot.
Are their specific things that he says in the book that you feel are
technically inaccurate?
yon...@yahoo.com (Yong Huang) wrote...
> Can you or anyone take some time to write a FAQ about what books are
> good for DBA starters? Mr. Niemic's book is probably the most
> deceiving among all DBA books. But please refrain from using words
> like "idiot"!
>
> Yong Huang
>
Andrew Mobbs
>Buffer Busy Waits Can Occur at Two places one is the segment header and another
>the data Level. If the Buffer Busy Waits ocur at the Segment Header then there
>is contention for the Freelist. If it occurs at the Row level then the server
>process is waiting for another server process to rel the buffer. This value is
>also directly prpoytional to the Free Buffers Scanned.. A Increasing Value
>should always take you towrads tuning the Buffer Cache.
A few more than two places, e.g. in 8.1.7 -
SQL> SELECT class FROM v$waitstat;
CLASS
------------------
data block
sort block
save undo block
segment header
save undo header
free list
extent map
bitmap block
bitmap index block
unused
system undo header
system undo block
undo header
undo block
14 rows selected.
(13 discounting the "unused")
There's no such thing as a row-level buffer busy wait in Oracle, it's
always block level, as the name suggests. A wait for a row level lock
is an enqueue.
A buffer busy wait is always when one session has a buffer locked
and another session wishes to access that buffer. The first session is
modifying that buffer for some reason; possibly reading a block off disk
into that buffer, or updating that buffer.
>Make the Buffer Cache Big and also try to cache your tables so that they are not
>thrown out after a FTS.
On a FTS, only db_file_multiblock_read_count blocks will be stored,
at the LRU end of the buffer cache chain (unless you have a CACHE
hint). This means that the buffer cache is not swamped by a FTS.
If you think you know better than Oracle which objects are best kept
in cache, create a suitable sized KEEP pool and put those objects into
that pool.
--
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/
Paul Drake
If you're in the greater New York area, tune in on 21 March 2002 for
"War of the tuning methodologies" as Rich Niemic and Gaja Vaidyanatha
square off at Columbia university. tickets are $35 at the door.
Seriously - http://www.nyoug.org/events.htm
Nuno Souto
>Seriously - http://www.nyoug.org/events.htm
>
that will be an interesting meeting. Gaja's methods explained in the
book, are quite logical and make a lot of sense with the kind of
hardware and software we use nowadays (and knowledgeable people using
them). Whereas Rich's methods make more sense for earlier versions,
smaller systems and less complex/critical environments, with more
common people using them.
Clash of cultures? I don't think so, just different contexts.
Wish I could attend..
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
Paul Drake
next best thing to being at IOUG-A or having an Oak Table. ;)
Paul
RFre465789
idiot.>>
Harsh words. Have you ever actually written a book? What have you contributed
to the Oracle community (obviously you contribute here)?
The writing process is at best grueling... schedules are crazy... it's allot
easier to judge others than to get off our own kiester and better the work.
I challenge you to write a work the size of his for everyone to look at with a
critical eye. Let's see how many errors and mistakes you make... let's see how
you fare during the writing process.
Robert
(Who doesn't work for TUSC or Rich, but does count him among my friends)
Robert G. Freeman
Author
Oracle Press's Oracle9i New Features
Sybex's Mastering Oracle9i
Coriolis' Oracle8 to 8i Upgrade Exam Cram
Coriolis' Oracle 7.3 to 8 Upgrade Exam Cram
Ricky Sanchez
Lots of people write books who don't really know what they are talking
about. Given the amount of work that goes into any book, technical
authors certainly don't write for the royalties. They write to gain
recognition and to create a reputation to enhance their professional
careers. They wanna be famous for something.
So, it is incumbant upon the authors to provide content commensurate
with the reputation they wish to create for themselves. If you want to
be known as an Oracle performance expert, you write about Oracle
performance expertise. Good for the authors and good for the readers,
but only if the authors know what the hell they are talking about. When
authors are dead wrong, readers are misled. In this case, readers are
Oracle users and administrators finding themselves with the frustration
and confusion of the original poster, to wit:
"I tried to increase the initrans parameter as suggested by Rich Niemic
in
his tuning book, but it didn't help."
The reason Niemic's suggestion did not help is because Niemic's approach
to performance tuning is wrong-headed. He has been told this time and
again by many authoritative voices, but still he propagates his
nonsense. He refuses to listen to others and to revise his ideas to
comply with reality. That is an idiotic thing to do, hence my assertion.
Yes, he wrote a book on the subject. Spent lots of time an energy
putting his ideas on paper. Lots of people bought and read the book and
believe the great orator from TUSC. They take his advice and then wonder
why they don't get good results.
Just because you say it, doesn't make it so. On the other hand, if you
say it long enough and loud enough, people will tend to believe it. This
is where Niemic makes his impact and his money. He makes his money from
those who don't know better. The tragic impact is on the Oracle user
community.
I understand your willingness to defend Niemic, being an author
yourself. I have not read your work, but I hope it provides good
content. As an author, you probably understand how bad content does not
make an author right. Nor does not having written a book make me or
others wrong.
I have no intention of writing a book on Oracle performance tuning. Too
many titles out there claim the same space, and I would rather devote my
energies to the experience of actual performance analysis, design and
tuning. To find a good book on the subject, look the the 9i Oracle
Performance Method book. Or read Anjo Kolk's YAPP Wait Events paper. Or
read the writing of Cary Millsap or Craig Shallahamer. They are right,
Niemic is wrong.
So, I suggest the man is an idiot. Harsh words, but true enough and I
stand by them.
- ricky