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 <FONT
face="ARIAL CYR" size=2>here. Just my thoughts.
Thanks again I took your tips and will go
into detail tomorrow.
Ed
Hi Ed <FONT face=sans-serif
size=2> 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. <FONT
face=sans-serif size=2>Steve,
Can you please clarify ? <FONT
face=sans-serif size=2>ThanksRiyaj "Re-yas" ShamsudeenCertified Oracle
DBAi2 technologies www.i2.com
Received on Thu May 17 2001 - 16:05:17 CDT
