| 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: Calculating LIOs
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 = index root block to any leaf block is always the same. So, if the index = has a BELVEL of, say, 3, then to get from the root to the leaf = containing 123 will be 4 buffer gets. The TABLE ACCESS BY ROWID is one = more buffer get. So, that's a total of 5 buffer gets. =20
In general, with a few notable exceptions, you can often draw some = conclusions regarding a query's efficiency by looking at the total = buffer gets and the number of rows returned. For example, if you've got = a 3 table join, and it's all index driven, and it returns 1 row, then = you can expect how many buffer gets? Well, it depends on the size of = the table and depth of the indexes involved and whether any join = predicates are going to cause a TABLE ACESS BY ROWID. In this case, = it may be safe to say that a good upper bound is around 15 buffer gets. = (We already estimated a single table pk driven access at around 5 buffer =
gets.) It may be slightly less, depending on the indexing scheme and = whether a table access by rowid is required for every table. If it's = significantly more than 15, then you need to look at the access paths =and ask yourself why Oracle needs to do so many LIOs to produce that one = row of output. Are there better access paths available?
Notable exceptions to this type of estimate include any type of = analytical or aggregate functions. By their nature, they could be = looking at a *huge* number of blocks and only produce one row (or a = small number of rows) of output. In that case, it's more difficult to = get a sense for buffer gets/row.
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 10:41 AM
To: oracle-l_at_freelists.org
Subject: Calculating LIOs
Any papers on how to calculate how many LIOs a query should use? For =
example I had a full table scan of a 1 MB table with 8kb blocksizes.=20
The table was in memory, so no additional LIOs for hitting the storage. =
How do I calculate how many LIOs I should get? I actually go 63? Same =
for different types of index reads?=20
Not an absolute necessity. I'd just like to know. Or maybe we can =
discuss it on here?=20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |