Re: Data Access Time

In article <3523BDD1.167EB0E7_at_usl.edu>,
  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 are running the query in SQL*PLUS, use 'Set Timing On'. This will give you the elapsed time the query took (on some platforms, it will give more.) You can also use 'Set Time On' for less precise information. (I always have both set on in my 'login.sql'.)

As for which block is queried, if an index is used, you will read one block for each level of the btree and then one datablock, if all the information is in the sga buffer. If the data is not in the SGA, the ORACLE will read 5 db blocks for each level to chase the btree level chain, then 5 db data blocks to get the data row.

If an index is not used, then the number of blocks depends on whether the table is in the SGA or not. If not, the answer is all of them for the table.

If you really need to know the specific block read, also get the rowid of the data. It contains the block number as part of the structure.

(Obviously, I do not consider memory scans the same as disk reads.)

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Apr 03 1998 - 00:00:00 CST