Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> CPU time missing from 10046 trace of cached table
Looks like 10046 traces don't correctly report the CPU usage of full
scans of cached tables.
I put together a simple cached test table:
CREATE TABLE test
cache
as select * from dba_segments ;
inserted a bunch more data to fill up the table
ran a query, got a 10046 trace, used tkprof, and got this output where it is all CPU but the CPU falls way short of the elapsed time:
SELECT owner,
SUM (bytes)
FROM test
GROUP BY owner
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 8 8.89 13.20 0 43970 0 93
total 10 8.89 13.20 0 43971 0 93
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 270
Rows Row Source Operation
------- --------------------------------------------------- 93 SORT GROUP BY (cr=43970 pr=0 pw=0 time=13204096 us)5392512 TABLE ACCESS FULL TEST (cr=43970 pr=0 pw=0 time=57 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait TotalWaited
Notice that there are no disk reads so all the data is cached in memory, but the time spent on the CPU is significantly less than the total time. The waits are small.
I guess just keep this in mind if you are doing traces on cached tables.
p.s. I tried to post this before so if somehow it shows up twice I'm sorry. I'm not sure why I can't find my previous post... Received on Thu Jul 19 2007 - 13:56:59 CDT