Re: index FS vs index FFS - what's the difference ?

[Shevtsov, Eduard]

Hi Riyaj,   Thanks for your response. Very interesting! I completely agree with you about index fast full scan. But I'm still unclear about index full scan. I've always thought that this operation is serviced with 1-block reads. So it would be possible to go through 2-way list of leaf blocks. And if my memory services me right the docs say the same about 1-block reads for IFS. But dump file shows db scattered reads. I don't think that 11 blocks per read is due passing the extent boundaries The LMTablespace has big extents (50Mb) and all such events have the same p3 = 11 in the trace file. Certainly I should have given you much more info. I had restarted DB (shutdown immediately) before I got  each trace file. I just did it in order to clean db cache and catch 'db file %' waits during the following index scan. So I did clean shutdown. I checked each execution plan. Also it's unlikely that someone modified this index at that time. I may be totally wrong here. Just my thoughts.   Thanks again I took your tips and will go into detail tomorrow.   Ed     Hi Ed         I think, For full index scan, oracle server process reads the root block and branch blocks 1 block at a time. It reads the leaf blocks at a db file multiblock -read count blocks per read call.  For the fast full scan, it reads all the block from the segment header to high water mark at a db_file_multiblock_read count blocks per read call. So, root blocks and branch blocks are read 1 block at a time, hence the db file sequential read. Where as for the fast full index scan you will see sequential read events only for the root block. Also, for the full index scan apparently knows about the extent boundary and if the next group of blocks to be read (in the current extent) is less than the db file multiblock read count , then it tries to read until the end of the current extent. That's why 11 blocks instead of 8 blocks.         For the 'db file parallel read' the segment read are file 1 block 11, which is a system rollback segment extent. If you dump the block, you could see the redo for index leaf operations. I guess, this is due to delayed instance recovery.         All these, I found from dumping blocks and doing research myself. Steve,         Can you please clarify ? Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies   www.i2.com