-
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
-
Temp solution:Bounce the db, run query again.
-
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
