RE: Enqueue and DB File Sequential Read Waits Problem

Hi Anjo,

> Q1: Are you running
> multiple jobs at the same time ? Could be true because you are running out
of
> CPU ....

How can you tell we are running out of CPU??? We are a development shop and have about 30 instances running on a single database server. There was just one major job running on this particular instance during the afternoon (although there may have been 3 or 4 additional connections).

I will talk to the programmers about possible improvements to the SQL statements ...

Thanks for the suggestions!

Sam.

p.s. If I have convincing evidence that the CPU on this box is overtaxed, it may help me to get another database server to spread instances onto.

 Date: Thu, 18 Apr 2002 23:22:17 +0200
 Subject: Re: Enqueue and DB File Sequential Read Waits Problem

Sam,

The enqueue problem looks like an TX enqueue problem. Given the fact that the
average wait is 307 and max wait is 308, I can see that you are also running out of CPU on your box. The normal max_wait
should be 300 and the average wait should be less than 300. So the question now is why is there an TX enqueue problem. Q1: Are you running
multiple jobs at the same time ? Could be true because you are running out of
CPU ....
The different jobs could interfere with each other. Given the fact that only 4
log file sync's have been done, could indicate some job control updates take place.

The problem with db file sequential read is not the average speed (2 msec is fast), but there are peaks (max_wait of 290 msec) and the real problem is 7.2
million read operations.
The question you need to ask your self: Do I need to do that many I/O operations ? Could I tune the SQL to reduce the number of I/O or utilize my cache better ?

Anjo.

> -----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).