Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Process consuming a lot of CPU, UTL_FILE and WAIT EVENTS rela tionship

RE: Process consuming a lot of CPU, UTL_FILE and WAIT EVENTS rela tionship

From: John Kanagaraj <>
Date: Wed, 23 Jun 2004 14:25:16 -0700
Message-ID: <>


>My guess is that the session is performing many
>utl_file.get_line as seen before, and that's why it's taking
>so much CPU time.
>But what it's not clear to me is why Oracle does not show that
>in the wait events......

Keep in mind that
(a) ALL possible events have not been instrumented to post status in the Wait Interface. Thus, your process may be executing Oracle kernel code that does not let the Wait interface know what it is waiting for. It will still accumulate service time 'c' and wall-clock time 'e' though. (b) When a process is executing in the CPU, it does NOT post a wait event. Thus, what you see in V$SESSION_WAIT.EVENT is the _last_ instrumented wait that occurred. A combination of STATE, WAIT_TIME and SECONDS_IN_WAIT qualify this wait..
(c) A wait event such as 'latch free' or 'buffer busy wait' is posted _only_ when contention is encountered for resources that are accessed. The absence of such wait events does not necessarily mean that a buffer was not accessed not that CPU was not consumed. A 10046 will not show this in the trace, will this will be accounted for in 'c' and 'e' (and cr, cu as the case may be).

You will _also_ have to take a look at delta changes in V$SESSTAT for that session to see what's going on. A 10046 does NOT provide this information from V$SESSTAT. Thus you may determine that there were many 'session logical reads', etc. which contributed to the CPU time.

In fact IMHO, the above lists what is probably missing in both the Wait Interface as well as a 10046 trace...

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on '' for Grace and Mercy that is freely available!

Received on Wed Jun 23 2004 - 16:22:22 CDT

Original text of this message