Note in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Toby Brown" <toby_brown_at_optusnet.com.au> wrote in message
news:2a84f50c.0403090544.7092e09d_at_posting.google.com...
>
> 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?
>
> 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 think there's always a little room for error on very
small tasks, especially after bouncing the database,
but I wouldn't have guessed that your result would
look like this.
> 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
>
You've done two fetches for the data, this accounts
for two of your QUERY counts, which are CR reads
on the FETCH line.
But a CR read may cause a physical read if the block
is not in memory, so your DISK count on the FETCH
line is the physical read of the one EMP block.
The 'extra' two QUERY counts on the FETCH line
are there because Oracle 9.2 always does two
CR reads on the segment header block when doing
a full tablescan (or index fast full scan).
This leaves two questions - when did the physical read
of the segment header block take place: perhaps that
is the DISK count in the PARSE line.
The second question is what is the extra QUERY
count in the PARSE line. I don't know.
But Oracle does have some accounting errors when
converting a trace file into a prof file; so I hope the
explanation of the FETCH line is sufficient.
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 14 TABLE ACCESS FULL EMP (cr=4 r=1 w=0 time=387 us)
>
Received on Mon Mar 15 2004 - 17:40:11 CST
