Re: high data block waits in v$waitstat 8.1.7

Increasing freelists generally helps only with inserts into tables, it won't necessarily help with index blocks, updates, or deletes.

Whilst 1,000 per day does not look like a good number, you do need to compare it
with the actual amount of work done, and check the time lost.

In v$system_event, it the total time waited on "buffer busy wait" significant compared to waits like, file reads, enqueues, latches ?

Look at v$sysstat - is the number of consistent gets huge compared to the number of waits ? (say 1,000,000 times the size).

In Oracle 9.2, there is a view called v$segstat that can be cued to record wait activity per segment - so a quick report on this dynamic view would tell you which segments were
suffering most buffer busy waits.

In 8.1 there is probably no better way that watching - however if you check v$session_event and v$session, you may notice that a particular program suffers most of the waits, and then switch on 10046 at level 8 for one session running that program. This will catch all the waits in a trace file.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            July / Sept
        Australia      July / August
        Malaysia        September
        USA (MI)        November
http://www.jlcomp.demon.co.uk/seminar.html

sg wrote in message <3D12C3AC.3080500_at_europe.com>...


>Hii

>

>Does anyone know how to diagnostic data block waits in 8i? Currently we

>see around 1000 data block waits increase per day in a 100 users Oracle

>iAS (forms, reports and discoverer) OLTP application/system. I have

>recently increase the freelists of all tables to 4 but it seems it has

>no effect YET since freelists are added to new blocks only.

>

>I know we have to monitor v$session_wait wand watch for p1, p2 and p3

>columns but is there another way or I have to sit there and do select *

>from v$session_wait all the time :?

>

>Does this improve in 9i or we have to use same technique?

>