| 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
Ryan,
D'oh, I should have mentioned the HWM (High Water Mark).
In the case of a FULL TABLE SCAN (or a FAST FULL INDEX SCAN on an = index), Oracle will read up to the HWM. That is, the point at which = data is now or has ever been stored. Blocks above the HWM are known to = be empty, so Oracle does not read them. If you have a table that = contained 100 million rows, and you delete 80 million of them, the HWM = will still be at the point where it was when the table contained 100 = million rows. One can lower the HWM by re-organizing the table. In the = old days, that meant export/import. Today, 'alter table table_a move;' = will do it. Note that if you do so, all indexes will be invalidated = (quite naturally, since the data in the table is all being moved, the = rowids in the indexes will no longer be valid) and you'll need to do an = 'alter index index_name rebuild;' for all the indexes.
Hope that helps,
-Mark
-----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 =3D <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.=20
I figure my number of blocks and bytes in the table by doing the =
following
select blocks,bytes
from dba_segments
where segment_name =3D <table>
-------------- Original message --------------=20
> Ryan,=20
>=20
> First, your comment "the table was in memory, so no additional LIOs =
for =3D=20
> hitting the storage." Um, LIO =3D3D logical I/O. It's the work Oracle =
=3D=20
> does to get data from the buffer cache into your server process's =3D=20
> memory. If the block you're after is already in the buffer cache, then =
=3D=20
> it will not cause a PIO, if not, it will cause a PIO. However, the =3D =
> number of LIOs should not change based on the contents of the buffer =
=3D=20
> cache. The number of LIOs is all about the access path you're using to =
=3D=20
> get to the data. If you have a table that's exactly 1MB (1,048,576 =3D =
> bytes), and the block size is 8k (8.192 bytes) then, to do a full =
table =3D=20
> scan will take 128 LIOs. This is invariant on the contents of the =3D=20
> buffer cache, the db_file_multiblock_read_count, etc. Those factors =
can =3D=20
> and will affect the number of PIOs, but not the number of LIOs.=20
>=20
> To go a bit more into estimating how many LIOs a query should take, =
you =3D=20
> need to know a particular statement's execution plan, and need some =
=3D=20
> insight into what Oracle is actually doing, given various access =
paths. =3D=20
> For example, a simple example may be 'select * from table_a where =3D=20
> pk_column =3D3D 123;' This is almost certainly going to be an INDEX =
=3D=20
> UNIQUE SCAN followed by a TABLE ACCESS BY ROWID. Well, how big is the =
=3D=20
> index? How many levels? Remember that regardless of the value =3D=20
> specified in the pk_column predicate, the number of levels from the =
=3D=20
>=20
-- 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:51:34 CDT
 |
 |