RE: buffer busy waits
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "Terry Sutton" <terrysutton@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 1 Aug 2005 14:21:40 -0700
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@xxxxxxx]
. . .
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
