| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> 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..?
>
> Matt
I agree with hpuxrac regarding Cary Millsap's book - if you want to analyze 10046 trace files, Cary's book is probably one of the best reference books for doing so.
Performance views to take a look at while running the sqr.exe batch program:
V$OSSTAT, V$SYSTEM_EVENT (wait events at the system level), V$SESSION_EVENT (wait events at the session level), V$WAITSTAT, V$SYSSTAT (system level), and V$SESSION_WAIT (instantaneous currentwait events). Look at the delta of the values in those views.
Ethereal (Wireshark) can be used on the client that is running the sqr.exe batch program to see if the batch program is trying to access some sort of a translation library on a remote machine before each SQL statement is processed. Such behavior might explain the 80% client side wait events.
It is hard to say why the DB CPU figures are different than you are seeing at the OS level. The trace file parser that you used may have stripped recursive CPU usage time from the report - this would be generated by things that happen automatically in the database such as triggers firing, space management calls, etc. that may be a result of user initiated SQL.
If it were me trying to determine what is happening in the system, I would use a combination of a packet capture program (Ethereal), manually review a 10046 trace file line by line (or write a program to do it automatically, or hire it out to someone else), and take initial snapshots of the performance views and compare those initial values with the values following the process completion. This three prong approach can be used to help find issues at the network layer, server hardware or configuration issues, just plain bad programming practices, and the exact ordering of wait event sources and the wait event duration.
Is this sqr.exe batch program by chance a reporting program for an equipment maintenance package?
Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.
Received on Fri Sep 29 2006 - 21:46:42 CDT
 |
 |