Re: Freelists and Freelist Groups

"Emmanuel" <mars_at_tacks.com> wrote in message news:dlkr0h$mt4$1_at_s1.news.oleane.net...
>
> EVENT
> TOTAL_WAITS
> ---------------------------------------------------------------- -----------
> TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAITx
> -------------- ----------- -------------
> db file sequential read
> 35620543
> 0 17278123 .485060629
>
> db file scattered read
> 12259065
> 0 2801865 .228554543
>
>
> buffer busy waits
> 640026
> 0 359692 .561995919
>
>
> enqueue
> 938
> 650 202598 215.989339
>
>
> direct path read
> 281251
> 0 91242 .324414847
>
> Now considering that the instance has been started 4 days ago, the number
> of "db file sequential/scattered read" waits seems enormous (even if the
> average wait's not so huge). The v$waitstat view confirms that there's
> somewhat contentions :
>
>
> Could someone give me a hand on this ?
>
> Thanks
>
> Emmanuel

The time lost on buffer busy waits is a small fraction of the time lost on physical I/O; moreover, one of the causes of buffer busy waits is excessive I/O. Consequently, you should be addressing the I/O problem first, as a reduction in I/O may result in the elimination of the buffer busy waits.

Check v$sql for any queries where disk_reads is high, especially if it is also a significant fraction of the buffer_gets, and see if you can find ways to make them more efficient.

-- 
Regards

Jonathan Lewis

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

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005