Re: Missing CPU time from 10046

mccmx_at_hotmail.com wrote:
> Oracle 10.2.0.2 W2K3 SE
>
> When tracing a batch process (sqr.exe) using 10046 level 8, the trace
> file breaks down as follows:
>
> 80% SQL Net Message From Client (i.e. waiting for sqr.exe to execute)
> 15% DB CPU Time
> 5% DB I/O Read Time
>
> However when I monitor this process at the OS level, the oracle.exe
> eats 100% of a single CPU while the batch process (sqr.exe) hovers
> around 3 or 4% for the duration of the job.
>
> So my question is, what operations does oracle.exe perform that aren't
> accounted for in the 10046 trace file (or v$sesstat)...?
>
> e.g. character set conversion etc..
>
> The DB cpu figures are not matching the OS CPU figures....!
>
> Any one have any idea why..?

If you read Cary Millsap's book "Optimizing Oracle Performance" there is a good discussion in chapters 5/6/7 of what may be involved here. ( See especially the discussions of uninstrumented code and walking the clock ).

The first question that I would have is what tool did you use to interpret the trace data? Was it tkprof? Or the oracle OEM gui kicking off something?

Maybe there is a flaw in that tool and how it interpreted the raw trace data.

Cary Millsap's company has their own resource profiler and you might want to contact hotsos for a free trial to see how well that tool processes the trace file and if it produces a profiler that more closely matches what you have observed. There's also an excellent open source clone orarsp that also does an excellent job of digesting 10046 files ( it's a clone of the hotsos resource profiler ).

Finally, maybe there are some bugs in that version of oracle? You may very well want to submit your question and the trace file in a service request to oracle support. Received on Fri Sep 29 2006 - 11:25:09 CDT