| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: now what??
In addition to Cary's excellent advice, here's a bit of specific =
knowledge
that may help you. When you encounter buffer busy waits, there are a =
few
things to consider. What type of block is seeing the waits? If it's a
segment header, then looking to increase free lists is probably a good
idea. If it's data blocks (table or index), then what's the P3 value?
If it's 130, that means that the buffer is busy cause data is being read
into it (due to one of the db file sequential/scattered reads). These
types of buffer busy waits are secondary to heavy physical I/O that's
colliding on blocks. (This can easily be seen/simulated by doing
concurrent full table scans on "large" tables, for sufficiently large
values of "large", or even due to concurrent fast full index scans or
grossly inefficient index full or range scans.) So, if you see P3
set to 130 for data blocks, you want to focus on your inefficient SQL
that's causing db file sequential/scattered reads. When you get those
under control, that entire class of buffer busy waits ought to =
disappear.
There are other cases of buffer busy waits, but in my experience=20
they're less common, and I won't write more on them unless you reply =
back
that you're seeing things that don't fit either the segment header case=20
or the data block w/ P3 set to 130 case.
You definitely want to follow Cary's advice as to the overall approach, but the above maybe helpful in diagnosing and dealing w/ buffer busy waits as you encounter them.
Hope that helps,
-Mark
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Cary Millsap
Sent: Tuesday, August 24, 2004 10:45 AM
To: oracle-l_at_freelists.org
Subject: RE: now what??
Robyn,
I'm sorry you lost the battle, but you don't need to lose the war...
Take it one user action at a time, beginning with the ones that are the =
=3D
most
important to the business. It is possible that there's a handful of
inefficient SQL statements out there that are dominantly responsible for =
=3D
the
queueing at your I/O subsystem. If that's the case, and if you can find =
=3D
and
fix them, then you can make the money invested into the CPU upgrade =3D
begin to
pay off.
The 10046 traces can give you the information you need to determine =3D
where
your problem root causes are and how much response time you can save by
addressing them. You're probably going to need to learn to read 10053 =
=3D
traces
to motor more quickly through the SQL optimization work you're going to =
=3D
be
doing.
Good luck, and <ad>let us know if we can help you; we've been =3D there</ad>.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 9/14 San Francisco, 10/5 Charlotte, 10/26
Toronto
- SQL Optimization 101: 8/16 Minneapolis, 9/20 Hartford, 10/18 New =3D
Orleans
- Hotsos Symposium 2005: March 6-10 Dallas
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =3D
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Robyn
Sent: Tuesday, August 24, 2004 9:33 AM
To: oracle-l_at_freelists.org
Subject: now what??
Gurus,
I am in the midst of living the examples given in several of your books. I spoke to a few of you at the Oracle-l dinner at Hotsos about this situation and the hypothesis has again proven true - adding more cpu to a system without a cpu bottleneck can actually slow it down.
For the last six months or so, I've been running 10046 traces on many
of our problem processes, and all of the waits have been related to
the sequential and scattered file reads, both in the number of waits
and in the durations of the wait time. I've been able to redistribute
the waits by working with the optimizer, the statistics and the =
sql.=3D20
As a result, our nightly batch runtimes have been reduced by about
30%. However, others were convinced that the problem was really lack
of cpu, new boxes were acquired and things have now slowed to a crawl
for key user processes. We now have buffer busy waits in addition to
the read waits, plus the duration of the various read waits appears to
be longer in some cases since the hardware upgrade.
Now that the 2 seconds of 'I was right' enjoyment has faded, I need to put together a plan to fix it. Much of the sql should be rewritten and these file systems are swiss cheese - they've been adding bits of space to dictionary managed tablespaces for years. The databases are very large, 500 and 800 gb's or so. The equipment is not bad: 8 cpu HP-UX boxes with emc storage. The db's are 9.2.0.3 and will soon be patched to 9.2.0.5. (test db has already been patched and several key queries perform better with the patch)
The unix admins want me to break the files into smaller pieces because the drive queue waits are really long. I've been arguing for LMT's with uniform extents and ASSM since I got here anyway. Can rebuilding the storage objects reduce the durations of the sequential and scattered read waits, or should I focus my efforts elsewhere first? I have increased freelists on the objects with buffer busy waits, and the number of BBWs has been reduced, but they are about 1/4 the duration of the read waits so the improvement is minimal.
Advice and comments appreciated ...
Robyn
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |