CPU underreported on cached tables

From: <bdurrettccci_at_yahoo.com>
Date: Wed, 18 Jul 2007 11:53:43 -0700
Message-ID: <1184784823.462552.199740@g12g2000prg.googlegroups.com>

Looks like CPU is underreported in 10046 traces of queries against cached tables. I have a table test created thus:

CREATE TABLE test
cache
as select * from dba_segments ;

I add a bunch more rows to fill it up and this is what I get from a tkprof of a 10046 trace:

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

• Waited ----------

  ---

  SQL*Net message to client 8 0.00 0.00 SQL*Net message from client 8 0.18 0.69

  ---

Notice that the waits are negligible and there are no disk reads. It is all in memory. But the CPU is only 8.89 out of the total 13.20 seconds.

>From what I've seen from another tool I have the missing time is
really all CPU.

• Bobby

p.s. I'm on 10.1.0.4 on HP-UX RAC. Received on Wed Jul 18 2007 - 13:53:43 CDT