Re: Buffer Busy wait event

From: Mladen Gogala <gogala.mladen_at_bogus.email.com>
Date: Wed, 10 Jun 2009 11:08:29 +0000 (UTC)
Message-ID: <h0o47d$9f1$2_at_solani.org>

Na Wed, 10 Jun 2009 01:09:09 -0700, shweta.kaparwan napisao:

> 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
>
> a) Buffer busy wait on segment ( table or index) appears only when there
> is update on that segment or it can appear during 'select' ?
>
> b) 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

Buffer busy waits happens when a session wants to pin a buffer in an exclusive mode, but the wanted buffer is already pinned by another session. The term "to pin" means to obtain the buffer pin latch:

SQL> select latch#,level#,hash
  2 from v$latch
  3 where name = 'buffer pin latch';

    LATCH# LEVEL# HASH
---------- ---------- ----------

       452 3 3925519355

The most frequent situations in which 2 processes compete for the same latch happens when two processes are modifying the same block in the table and thus both need to pin it in the exclusive state or when 2 processes are reading the same table using 'db file [scattered|sequential] read' mechanism. Bsically, if 2 processes want to load the same block, one will have to wait. There is no queue, it's not a lock, it's a latch. Read Tanel's blog about "reliable waits", it's exceptionally well written.

The first 2 arguments to the "buffer busy wait" event are file# and block#. Some kind of script that executes once every 10 seconds and collects file# and block# id's would probably give you a good idea of what is going on and what object is being in the center of attention of the active sessions.

-- 
http://mgogala.freehostia.com

Received on Wed Jun 10 2009 - 06:08:29 CDT