RE: buffer busy waits

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Mon, 1 Aug 2005 14:21:40 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C450236138D@NT15.oneneck.corp>

Thanks Terry, great article and that is a handy little script you provided in your article to check wait events over the last 30 seconds - simple, yet powerful.

Yes, the users are complaining - the system "slows to a crawl" occasionally and that's when I see the huge numbers of bbws and db file seq. reads. Unfortunately, this is on 32 bit windows so I'm limited to 3GB SGA split between two instances, unless I use indirect data buffers to access > 3GB cache, but I don't have any experience with that and had read about some negative experiences with it so I'm a bit hesitant to go there.

Anyone out there using use_indirect_data_buffers and >3GB RAM with Windows? Any success/horror stories?

My main focus for now is reducing the I/O required by tuning the SQL/schema. One thing I noticed is they have pctfree set to 40 on this huge table so I'm going to see if I can change pctfree to 5 and cram 35% more data into each block.

Thanks again,
Brandon

-----Original Message-----
From: Terry Sutton [mailto:terrysutton_at_usa.net]

. . .

I described a situation similar to this in a paper a while back (http://www.dbspecialists.com/presentations/wait_events2.html)

. . .

Original Message -----

I have a 9.2.0.6 database with the following top 5 timed events from statspack over a period of 396 minutes:

Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     %
Total
Event                                               Waits    Time (s) Ela

Time

-------------------------------------------- ------------ ----------- ------
--
buffer busy waits                               8,911,867      77,134
37.62
db file sequential read                         3,991,319      59,535
29.03
CPU time                                                       32,274

15.74

. . .

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Aug 01 2005 - 16:22:18 CDT