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 -

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 M

14819

    INLIST ITERATOR

      TABLE ACCESS BY INDEX ROWID	      XX.TABLE_X	   71 K	6 M	13341
        INDEX RANGE SCAN	              XX.IND_TABLE_X_CODE  71 K

268

also:
select count(*) from table_x
1983498
select count ( distinct (code)) from table_x 110

Question/s:

1. Your comment on indexing scheme opted for the mentioned query?
2. Full scan on TABLE_X would be a better choice? or any other recommendation?
3. 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     524288

SQL> show parameter work

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
workarea_size_policy                 string      manual

SQL> show parameter pga_

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
pga_aggregate_target                 big integer 209715200

SQL> 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