Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> (RE): Calculating LIOs

(RE): Calculating LIOs

From: <ryan.gaffuri_at_comcast.net>
Date: Fri, 13 Aug 2004 16:02:40 +0000
Message-Id: <081320041602.2043.411CE620000663EF000007FB2200762194079D9A00000E09D2020E979D@comcast.net>


Excellent post mark. However, I have the following 1 table query that does the following
select col1, col2
from table
where primary_key = <value>
The table has
56 rows
1 MB size. 128 blocks(so 8192 blocksize) My 10046 trace shows a full table scan with 63 logical IOs. Roughly half the number of blocks. Anyone know why? Looks like I am reading two blocks per LIO. I figure my number of blocks and bytes in the table by doing the following select blocks,bytes
from dba_segments
where segment_name = <table>
-------------- Original message --------------

> Ryan,
>
> First, your comment "the table was in memory, so no additional LIOs for =
> hitting the storage." Um, LIO =3D logical I/O. It's the work Oracle =
> does to get data from the buffer cache into your server process's =
> memory. If the block you're after is already in the buffer cache, then =
> it will not cause a PIO, if not, it will cause a PIO. However, the =
> number of LIOs should not change based on the contents of the buffer =
> cache. The number of LIOs is all about the access path you're using to =
> get to the data. If you have a table that's exactly 1MB (1,048,576 =
> bytes), and the block size is 8k (8.192 bytes) then, to do a full table =
> scan will take 128 LIOs. This is invariant on the contents of the =
> buffer cache, the db_file_multiblock_read_count, etc. Those factors can =
> and will affect the number of PIOs, but not the number of LIOs.
>
> To go a bit more into estimating how many LIOs a query should take, you =
> need to know a particular statement's execution plan, and need some =
> insight into what Oracle is actually doing, given various access paths. =
> For example, a simple example may be 'select * from table_a where =
> pk_column =3D 123;' This is almost certainly going to be an INDEX =
> UNIQUE SCAN followed by a TABLE ACCESS BY ROWID. Well, how big is the =
> index? How many levels? Remember that regardless of the value =
> specified in the pk_column predicate, the number of levels from the =
>



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Fri Aug 13 2004 - 10:58:32 CDT

Original text of this message

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