Re: Buffer Busy wait event

<shweta.kaparwan_at_googlemail.com> wrote in message news: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
>
> 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
>

See all the waits for db file scattered reads.

If two sessions run a large tablescan at the same time, there will be moments when one session is reading blocks into the cache just as the other session decides to read the same blocks. In this case the second session will go into "buffer busy waits" until the read is complete.

In 10g, buffer busy waits due to this type of collision have had their event renamed to "read by other session".

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html