RE: Process consuming a lot of CPU, UTL_FILE and WAIT EVENTS relationship
- From: John Kanagaraj <john.kanagaraj@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 14:25:16 -0700
Diego,
>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 'http://www.needhim.org' for Grace and Mercy that is freely
available!
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
