(RE): Calculating LIOs

  • From: ryan.gaffuri@xxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 13 Aug 2004 16:02:40 +0000

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@xxxxxxxxxxxxx
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

Other related posts: