Extremely High Buffer Busy Waits. Please Help !!!

[anuragmin]

Hi,
I ran the following the following results after running the query on v$waitstat and v$system_event tables.

I know something is wrong but do not know the approach to fix this.

I tried a few books also but none of them explain clearly how to fix the buffer busy waits problems clearly enough.

Please Help.

Thanks
Anurag

select * from v$waitstat where class ='data block';

CLASS COUNT TIME
------------------ --------- ---------
data block 3962775 0

select substr(event,1,25)"Event",TOTAL_WAITS, TOTAL_TIMEOUTS,TIME_WAITED ,
2 AVERAGE_WAIT from v$system_event where event='buffer busy waits';

Event TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
------------------------- ----------- -------------- ----------- ------------
buffer busy waits 3962830 73 0 0

[Halo]

Temp solution:Bounce the db, run query again.

[gpsingh]

1) Does it come during any particular query or is for whole database since startup.

2) Either case , query v$latch , do u see any waits on
cache hash chains.

3) If yes , query v$filestat, find out which of the file is haveing max i/o.

4) Query v$latch_children and find out which cache hash chain is having max of misses .

5) Find out from the addr of this latch and match it to x$bh hladdr and find out the block which is on this latch.

6) Find out to which object this block belongs from dba_Segment and

a) Either tune that query or increase the freelists of that object or find out if pctfree and pctused is tuned for that(those) objects.

Take Care
GP