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: '[EMAIL PROTECTED]' > 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
