Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Enqueue and DB File Sequential Read Waits Problem

RE: Enqueue and DB File Sequential Read Waits Problem

From: Diego Cutrone <dcutrone_at_afip.gov.ar>
Date: Fri, 19 Apr 2002 13:14:57 -0800
Message-ID: <F001.00449EA7.20020419131457@fatcity.com>


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

> 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).
Received on Fri Apr 19 2002 - 16:14:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US