Buffer Busy wait event

From: <shweta.kaparwan_at_googlemail.com>
Date: Wed, 10 Jun 2009 01:09:09 -0700 (PDT)
Message-ID: <f47d3a04-3564-4dfb-b370-aca2a4364371_at_x6g2000vbg.googlegroups.com>

All,

All,

We noticed in 20 Mins duration statspack report( Oracle 9.2.0.6)

Load Profile

~~~~~~~~~~~~                            Per Second       Per
Transaction
                                   ---------------
---------------
                  Redo size:             11,147.18
16,392.91
              Logical reads:              3,033.52
4,461.06
              Block changes:                 47.99
70.57
             Physical reads:                617.38
907.91
            Physical writes:                 33.78
49.68
                 User calls:                163.97
241.13
                     Parses:                 87.16
128.17
                Hard parses:                  0.56
0.83
                      Sorts:                  3.32
4.88
                     Logons:                  0.33
0.48
                   Executes:                 87.70
128.96
               Transactions:                  0.68

 Buffer Nowait %: 99.99

Top 5 Timed Events

---

% Total

Event                                               Waits    Time (s)

Ela Time

-------------------------------------------- ------------ -----------
--------
db file scattered read                             53,024

262 44.90
CPU time
127 21.72

db file sequential read                            28,017
78    13.42
control file sequential read                       27,594
38     6.56
enqueue                                             2,249
13     2.16

Buffer Busy wait event in statspack

^LTop 5 Buf. Busy Waits per Segment for DB: DB1 Instance: DB01 Snaps: 6403
-> End Segment Buffer Busy Waits Threshold: 100  

Buffer

                                           Subobject  Obj.
Busy
Owner      Tablespace Object Name          Name       Type

Waits %Total

---------- ---------- -------------------- ---------- -----
------------ -------
XX         DATA     TABLE_X                          TABLE
2,925   93.30
XX         INDEX     IND_TABLE_X_CODE                INDEX

207 6.60
.....
.....  

---

#--Table_x has index IND_TABLE_X_CODE on column Code.

We have identified some of the SQLs and tuned them to reduce overall IO.

Questions

1. Buffer busy wait on segment ( table or index) appears only when there is update on that segment or it can appear during 'select' ?
2. Found below SQL.. CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- ---------

   ---

   174,026 3 58,008.7 4.8 13.03 81.31 2843313473

Module: java_q4p_at_<host> (TNS V1-V3)
SELECT col1,code,col3,...col41,col42 FROM TABLE_X WHERE CODE IN ( :P16 , :P17 , :P18 , :P19 )
ORDER BY 41 DESC could this be root cause for buffer busy wait? as there are buffer busy waits on table_x and corresponding index segment on that table i.e. IND_TABLE_X_CODE.

c) In other way to find out the cause of buffer busy wait?

Regards Received on Wed Jun 10 2009 - 03:09:09 CDT