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: Data Access Time

Re: Data Access Time

From: Dave Wotton <Dave.Wotton_at_it.camcnty.no-spam.gov.uk>
Date: 1998/04/03
Message-ID: <6g34qn$puq@dns.camcnty.gov.uk>#1/1

Chen Wei Kian <weikian_at_usl.edu> wrote:
>Does anyone know if it is possible to check whcih block of data does
>a given query accessed to get the result ? What I am trying to do is
>to count how many block of data has to be accessed for a given query
>to obtain the result. The purpose of doing so is to compare the time
>required for 2 queries to obtain the result. Do you know if there is
>any other alternative way that allow me to so ?
>Wei Kian Chen

If you switch on sql_trace ( alter session set sql_trace = true ) and switch on timed_statistics in your init.ora, then analyse your trace files using tkprof, it will tell you the number of physical reads, consistent reads, elapsed time, cpu time etc. for each query.

Remember that the physical I/O counts ( and hence elapsed times ) will vary from execution to execution: The first time you run a query, the blocks won't be in the buffer cahce so there'll be a lot of physical I/O. If you then run the query again immediately after, your physical I/O count will be much lower, as most of the blocks will be cached in the SGA.

If you really want to see block addresses, the pseudo-column ROWID tells you which block the row came from ( well, almost: if the row has migrated, you'll get the address of its home block, not its current block )

HTH, Dave.

-- 

To reply by email, remove the "no-spam" bit from my email address.
Received on Fri Apr 03 1998 - 00:00:00 CST

Original text of this message

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