RE: (RE): Calculating LIOs

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 13 Aug 2004 12:09:41 -0400

Where is the HWM.  The odds are the full allocation is not being used to
hold data.
To begin with what kind of table is this: heap, IOT, partitioned, clustered?
Have deletes ever been done in the past on this table so that the used
blocks are sparely populated?

-- Mark D Powell --

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
ryan.gaffuri@xxxxxxxxxxx
Sent: Friday, August 13, 2004 12:03 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: (RE): Calculating LIOs


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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: