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