| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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!
 |
 |