Re: SQL> select class,count from v$waitstat; show data block contention

James could be right and Connor could be right. See the problem is that the buffer busy waits event doesn't tell what is causing the problem. The bbw comes from either someone changing the block and another wants to access the block or from multiple processes trying to read the same block (one will do the read and the others will wait on the bbw event). You can distinguish between them by looking at v$session_wait and look at the p3 column (id for bbw). Depends on the version of oracle that you are using.

From my experience, waits on data blocks means either that you are missing freelists (many processes are inserting in to the same object) or the buffer cache is too small too keep your current working set in and you have to read them from disk all the time.

Anjo.

Connor McDonald wrote:

> James Williams wrote:
> >
> > I am experiencing some I/O degredation as the below data block numbers
> > keep going up.
> >
> > Thinking about recreating the tables and increasing the freelists and
> > inittrans. Is this my best course of action?
> >
> > SQL> select class,count from v$waitstat;
> >
> > CLASS COUNT
> > ------------------ ----------
> > data block 86357
> > sort block 0
> > save undo block 0
> > segment header 0
> > save undo header 0
> > free list 0
> > extent map 0
> > bitmap block 0
> > bitmap index block 0
> > unused 0
> > system undo header 0
> >
> > CLASS COUNT
> > ------------------ ----------
> > system undo block 0
> > undo header 0
> > undo block 5
> >
> > 14 rows selected.
> >
> > SQL>
>
> Probably not...The most common cause of 'data block' is hot queries, ie
> churning through blocks heavily.
>
> v$session_wait and v$sqlarea may be useful to try catch such queries.
>
> hth
> connor
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the statue"
Received on Tue Mar 13 2001 - 03:47:43 CST