Re: Buffer Busy wait event
From: <shweta.kaparwan_at_googlemail.com>
Date: Wed, 10 Jun 2009 03:58:38 -0700 (PDT)
Message-ID: <ff46a342-5b15-4bfc-aa80-7e857a085ddf_at_f19g2000yqh.googlegroups.com>
On Jun 10, 10:26 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <shweta.kapar..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Date: Wed, 10 Jun 2009 03:58:38 -0700 (PDT)
Message-ID: <ff46a342-5b15-4bfc-aa80-7e857a085ddf_at_f19g2000yqh.googlegroups.com>
On Jun 10, 10:26 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <shweta.kapar..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
many thanks Jonathan.
we suspect the mentioned query for buffer busy wait events.
further,
we have below execution plan for the query mentioned earlier.
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=FIRST_ROWS 71 K 14819 SORT ORDER BY 71 K 6 M14819
INLIST ITERATOR
TABLE ACCESS BY INDEX ROWID XX.TABLE_X 71 K 6 M 13341 INDEX RANGE SCAN XX.IND_TABLE_X_CODE 71 K268
also:
select count(*) from table_x
1983498
select count ( distinct (code)) from table_x
110
Question/s:
- Your comment on indexing scheme opted for the mentioned query?
- Full scan on TABLE_X would be a better choice? or any other recommendation?
- There are direct path read/write wait events seen sometime due to this huge sort in query. we have :
SQL> show parameter sort_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ sort_area_retained_size integer 1048576 sort_area_size integer 524288SQL> show parameter work
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ workarea_size_policy string manualSQL> show parameter pga_
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 209715200SQL> do you recommend here increasing sort_area_size or setting workarea_size_policy to AUTO?
d) If workarea_size_policy is set to AUTO, then i think PGA memory may go beyond the target 209715200 and it may eat up all the memory?
Regards. Received on Wed Jun 10 2009 - 05:58:38 CDT