Re: Enqueue and DB File Sequential Read Waits Problem

Diego,

Let me say that v$system_event (and I assume itīs valid for v$session_event too) does not report
the "enqueue" average wait time correctly. (unless until version 8.1.6, I don't know what version are you using)

I know and I know why that is ;-) And by the way, it is correct .......

Anjo.

Diego Cutrone wrote:

> Hi Jonathan/Anjo/Sam:
>
> Let me say that v$system_event (and I assume itīs valid for v$session_event
> too) does not report
> the "enqueue" average wait time correctly. (unless until version 8.1.6, I
> don't know what version are you using)
> To calculate the precise number of enqueue waits you have to use v$sysstat
> "enqueue waits" (*) statistic or
> to do TOTAL_WAITS-TOTAL_TIMEOUTS (*). it's the same.
> So, to get the correct average you should divide TIME_WAITED/(*).
>
> In order to resolve the enqueue waits I'd take Jonathan advice.
> First identify the class of enqueue wait you're facing.
> You can do that running a query against v$session_wait (like Jonathan said)
> or by tracing the
> session with event 10046 and interpreting the output.
>
> Is this the only transaction running against these tables in the moment
> you're executing this procedure?
>
> If this were true, then I think it'd not be possible to see enqueue waits of
> type TX. right?
> So it must be another type of enqueue problem.
>
> Find out which type of enqueue problem you have.
> select * from x$ksqst where ksqstwat != 0
>
> HTH
> Greetings
> Diego Cutrone
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Friday, April 19, 2002 10:59 AM
>
> > Jonathan,
> > Thanks for your response. The programmer stopped the process last night
> > because it was taking too long ... so there was nothing in x$ksqst when I
> > looked this morning. However, thanks for the tips on how to troubleshoot
> > this further.
> > > Can I confirm that your report is describing a job where your session
> runs
> > for six hours, and reports 1 hour of enqueues, and 4 > hours of db file
> > sequential reads, in that six hours ?
> > Yes, that is what v$session_event showed me. And I confirmed the start
> time
> > with the programmer.
> > Sam.
> > ***********************************
> > Waits Problem Date: Fri, 19 Apr 2002 13:14:19 +0100
> > Regarding the Enqueues: One follow-up is to connect as SYS and look at
> > x$ksqst where ksqstwat != 0. This tells you two things (after the event).
> > First, which type of enqueue has suffered waits, and secondly whether the
> > waits in v$session_wait are represent a few very long waits or lots of
> > shorter waits. (v$session_wait increments every 3 seconds, x$ksqst just
> once
> > at the end) However, since your wait time is a large fraction of your
> > elapsed time, you will probably be able to get some extra information
> simply
> > by running a query against v$session_wait where name = 'enqueue' a couple
> of
> > dozen times in quick succession. For the same reason, if you look at
> v$lock
> > where request != 0 at regular intervals, you will be able to see the type
> > and id of the wait. If you can identify the enqueue type, request mode,
> and
> > id values, then you can track down causes more easily. Regarding the db
> file
> > sequential read: Can I confirm that your report is describing a job where
> > your session runs for six hours, and reports 1 hour of enqueues, and 4
> hours
> > of db file sequential reads, in that six hours ? If that is the case, then
> > even though the reads are very fast reads (presumably helped by a large
> > file-system buffer) then I suspect your SQL (or the strategy surrounding
> > your use of SQL) needs to be examined. You MAY be doing an extreme amount
> of
> > logical I/O to require 7M single block reads for one (effective) hour of
> CPU
> > usage. For example, it is possible that the code has been 'tuned' to
> > eliminate tablescans and increase hit ratios, with the detrimental
> > side-effect that the logical I/O has gone up dramatically, and increased
> the
> > actual cost of physical I/O. It is possible that the code has been written
> > to loop through cursors and 'emulate' joining by executing several simple
> > statements per row of the main cursor. In the short term, you may get an
> > improvement in performance by shifting memory away from the file system
> > buffer and into the Oracle buffer. The logical I/O won't drop, but the
> > number of waits for filesystem interaction may.
> >
> >
> >
> > > -----Original Message-----
> > > From: Sam Bootsma
> > > Sent: April 18, 2002 5:33 PM
> > > To: 'ORACLE-L_at_fatcity.com'
> > > Subject: Enqueue and DB File Sequential Read Waits Problem
> > >
> > > We have a batch job taking a long time to process. Querying the
> > > v$session_wait view, I discovered there have been over 1200 enqueue
> waits,
> > > with an average wait of just over 307 and a max wait of 308. The total
> > > time waited is 370081 (just over one hour if these figures are in
> > > centiseconds). The batch job has been running about 6 hours.
> > >
> > > I also see that db file sequential read has waited almost 4 hours. This
> > > sounds like 4 hours waiting on index access requests. What could cause
> > > these long waits?
> > >
> > > I am not sure how to troubleshoot this problem further (Reading the
> > > Performance 101 Book is on My To Do List). This is Oracle 81630 on
> > > Windows NT server.
> > >
> > > Any suggestions or advice on how to troubleshoot this further is much
> > > appreciated. Here is my query and the output:
> > >
> > > SQL> l
> > > 1 select substr(event, 1,30), total_waits, time_waited, average_wait,
> > > max_wait
> > > 2 from v$session_event
> > > 3* where sid=18
> > > SQL> /
> > >
> > > SUBSTR(EVENT,1,30) TOTAL_WAITS TIME_WAITED
> > > AVERAGE_WAIT MAX_WAIT
> > > ------------------------------ ----------------------
> > > -------------------- -----------------------
> > > ---------------------
> > > latch free 5 0
> > > 0 0
> > > enqueue 1204 370081
> > > 307.376246 308
> > > buffer busy waits 45 0
> > > 0 0
> > > log file switch completion 2 27 13.5
> > > 17
> > > log file sync 4 0
> > > 0 0
> > > db file sequential read 7269278 1375000
> > > .18915221 29
> > > direct path read 4 10
> > > 2.5 10
> > > direct path write 1098 112
> > > .102003643 2
> > > file open 4 0
> > > 0 0
> > > SQL*Net message to client 555 0 0
> > > 0
> > > SQL*Net more data to client 11 1
> > > .090909091 1
> > > SQL*Net message from client 555 3256 5.86666667
> > > 1038
> > >
> > > Thanks again for any pointers and suggestions.
> > >
> > >
> > > Sam Bootsma, OCP
> > > Technical Support Analyst
> > >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Sam Bootsma
> > INET: SamB_at_cpas.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Diego Cutrone
> INET: dcutrone_at_afip.gov.ar
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).