You Asked
Tom:
I was reading this Oracle magazine
http://www.oracle.com/technetwork/issue-archive/2011/11-jul/o40asktom-402616.html
<quote>
A full index scan reads the index a block at a time, from start to finish, It uses single-block, not multiblock, I/O for this operation
A fast full index scan reads the entire index, unsorted, as it exists on disk, We use multiblock I/O and read all the leaf, branch, and root blocks
</quote>
create table t
nologging
as
select *
from all_objects;
create index t_ind on t(owner,object_type,object_name) nologging;
begin
dbms_stats.gather_table_stats(
ownname =>user,
tabname =>'T',
estimate_percent=>dbms_stats.auto_sample_size,
cascade=>true);
end;
/
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> exec show_space(user,'T_IND','INDEX');
l_total_blocks**************** 448
l_total_bytes***************** yes
l_unused_blocks*************** 19
l_unused_bytes**************** 155648
l_last_used_extent_file_id**** 22
l_last_used_extent_block_id*** 4873
l_last_used_block************* 13
l_unformatted_blocks********** 0
l_unformatted_bytes*********** 0
l_fs1_blocks****************** 0
l_fs1_bytes******************* 0
l_fs2_blocks****************** 1
l_fs2_bytes******************* 8192
l_fs3_blocks****************** 0
l_fs3_bytes******************* 0
l_fs4_blocks****************** 0
l_fs4_bytes******************* 0
l_full_blocks***************** 414
l_full_bytes****************** 3391488
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> set autotrace traceonly explain statistics;
rajesh@10GR2>
rajesh@10GR2> select owner,object_type,object_name
2 from t
3 order by owner,object_type,object_name;
58779 rows selected.
Elapsed: 00:00:02.48
Execution Plan
----------------------------------------------------------
Plan hash value: 607953271
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58779 | 2296K| 417 (1)| 00:00:06 |
| 1 | INDEX FULL SCAN | T_IND | 58779 | 2296K| 417 (1)| 00:00:06 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
803 consistent gets
0 physical reads
0 redo size
1563107 bytes sent via SQL*Net to client
2984 bytes received via SQL*Net from client
393 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58779 rows processed
rajesh@10GR2>
rajesh@10GR2> select owner,object_type,object_name
2 from t;
58779 rows selected.
Elapsed: 00:00:02.31
Execution Plan
----------------------------------------------------------
Plan hash value: 3351170763
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 58779 | 2296K| 94 (3)| 00:00:02 |
| 1 | INDEX FAST FULL SCAN| T_IND | 58779 | 2296K| 94 (3)| 00:00:02 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
810 consistent gets
1 physical reads
0 redo size
1566056 bytes sent via SQL*Net to client
2984 bytes received via SQL*Net from client
393 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
58779 rows processed
rajesh@10GR2>
rajesh@10GR2> set autotrace off
rajesh@10GR2>
Questions:
1) Size of index is 429 Blocks (448-19), INDEX FULL SCAN - is doing a single-block IO, if so why the above query needs 803 blocks (from buffer) to answer and why *NOT* 429 blocks?
2) How can i measure the total IO's used by a query? Say in INDEX FULL SCAN we are doing a single-block IO but Autotrace statistics shows that i am reading 803 blocks from buffer to answer this query. How do i know how many IO's is really spent in reading 803 blocks from buffer?
and we said...
1)
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514
it has to do with your arraysize and how many times Oracle has to revisit a block. If you use an arraysize of 2 - you would see about 58779/2 consistent gets. If you use an arraysize of 1000 - you would see about 58779/1000 consistent gets. That above link has examples of this phenomena.
2) You did already? The second query used 810 logical IO's to perform its job. It included one physical IO using multi-block IO (we know it was multi-block IO because we did an index fast full scan)
Consistent gets are always block gets - single block gets. When we are talking "single block IO" and "multiblock IO" - we are talking about a physical IO phenomena. In this case - your index was almost entirely in cache and we didn't really do any physical IO to speak of.
