Re: Cache buffer chain problem

Are you absolutely sure that it is waiting on this event, rather than reporting this event as the last event waited for ? Do you have timed_statistics on so that you can see the wait_times as it waits. You could try setting event 10046 to level 8 to get a trace file which lists all the waits that actually occur.

One possible explanation for the random change in performance is simply a conflicting job - the simple case is that some other job keeps locking data that this job wants, and this job waits. Another option is that another job keeps updating a lot of the data that this job is using, and this job then has to run up a huge overhead applying rollback to get to a read-consistent state - although 4 seconds to 5 minutes sounds a bit extreme.

Can you insert:

    select * from v$session_wait where sid = ...     select * from v$mystat;

at the beginning and end of the job to see if this gives you a clue ?

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

robertchung0909_at_my-deja.com wrote in message
<8pjjik$di5$1_at_nnrp1.deja.com>...


>

>

>We have a server that is suddenly causing some problem.  A job that

>used to finish in about 3-4 seconds sometimes takes 5 minutes to

>complete.  This problem appears and disappears randomly.  We found when

>the job is taking long, it mostly sits idle waiting on "Cache Buffer

>Chain".  Has anyone seen this behavior?  What should we do to alleviate

>this problem?  We are using Oracle 7.3.4 on RS/6000.  Thank you in

>advance.

>

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.