Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> CPU time missing from 10046 trace of cached table

CPU time missing from 10046 trace of cached table

From: <bdurrettccci_at_yahoo.com>
Date: Thu, 19 Jul 2007 11:56:59 -0700
Message-ID: <1184871419.578375.213100@m3g2000hsh.googlegroups.com>


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  Total
Waited

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US