SQL Trace Output - Query vs Disk - Are they mutually exclisive?

From: Toby Brown <toby_brown_at_optusnet.com.au>
Date: 9 Mar 2004 05:44:19 -0800
Message-ID: <2a84f50c.0403090544.7092e09d@posting.google.com>

Hi,

perhaps a dumb question but I've tried to make sense of it using Oracle docs and other white papers and I can't work it out. So at the expense of looking silly I've decided to ask the forum and get this clear in my head rather than avoid it and trust others blindly (even if they are right).

Question:
When the buffer cache is empty and I do a select on a table, shouldn't the "disk" read statistic from the trace file have as its value, the number of blocks that were read to satisfy the query? Now if that's true then, will the "query" or consistent gets statistic in the sql trace file have 0 in it or will it also have a value greater than 0, if so then why?

When I ran sql tracing it showed that there were 5 blocks retrieved to satisfy the query and only one of them was disk and 4 were query. I would've thought that all blocks would come from disk. No? What is the relationship (if any) between the "disk" and "query" statistics of sql trace?

My scenario below:
I'm running Oracle 9.2.0.2.0 EE on Red Hat Linux 8 (thanks for you help):

I'm trying to understand Oracle SQL Tracing. I login as sys, bounce the instance to ensure that the buffer cache is empty. I start sql tracing for the session using dbms_support package. I issue the command "select * from scott.emp". I stop the sql tracing for the session. BTW, I've ensured that I'm the only one logged into the database throughout this whole exercise.

I run TKPROF on the trace file and get the following output for my select statemnt:

select *
from
 scott.emp

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.00       0.04          1          1          0  
        0
Execute      1      0.00       0.00          0          0          0  
        0
Fetch        2      0.00       0.00          1          4          0  
       14

------- ------ -------- ---------- ---------- ---------- ----------

---

total        4      0.00       0.04          2          5          0  
       14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: SYS

Rows Row Source Operation

-------  ---------------------------------------------------
     14  TABLE ACCESS FULL EMP (cr=4 r=1 w=0 time=387 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total

Waited

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

  ---

  db file sequential read 2 0.01 0.01 SQL*Net message to client 2 0.00 0.00 SQL*Net message from client 2 12.78 12.78

  ---

Received on Tue Mar 09 2004 - 07:44:19 CST