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).
Received on Fri Apr 19 2002 - 11:59:22 CDT
