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 -> Re: SQL Trace Output - Query vs Disk - Are they mutually exclisive?

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

From: Ganesh Raja <ganesh.raja_at_nospam.logicacmg.com>
Date: Wed, 10 Mar 2004 13:51:32 +0800
Message-ID: <newscache$zxicuh$l6j$1@animal.logica.co.uk>


It Actually Satisfied the Query using one Block but Since u havea very Low Arraysize u are getting 4 Consistent Reads.

Just Increase your Arraysize and repeat the example.

HTH

-- 
Cheers,
Ganesh Raja


"Toby Brown" <toby_brown_at_optusnet.com.au> wrote in message
news:2a84f50c.0403090544.7092e09d_at_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 - 23:51:32 CST

Original text of this message

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