| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: (RE): Calculating LIOs
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?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
ryan.gaffuri_at_comcast.net
Sent: Friday, August 13, 2004 12:03 PM
To: oracle-l_at_freelists.org
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 =
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_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 - 11:05:59 CDT
 |
 |