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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Cary Millsap Sent: Tuesday, August 24, 2004 10:45 AM To: oracle-l@xxxxxxxxxxxxx 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 - Visit www.hotsos.com for schedule details... -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx =3D [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Robyn Sent: Tuesday, August 24, 2004 9:33 AM To: oracle-l@xxxxxxxxxxxxx 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 ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------