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

From: <Riyaj_Shamsudeen_at_i2.com>
Date: Thu, 17 May 2001 10:10:16 -0700
Message-ID: <F001.0030674E.20010517101127@fatcity.com>

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

"Shevtsov, Eduard" <EShevtsov_at_flagship.ru> Sent by: root_at_fatcity.com
05/17/01 03:40 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: index FS vs index FFS - what's the difference ?

Hi List,

I've made two corresponding dumps and ... I must admit I have much less understanding than I had before.
Please look at them.

PARSING IN CURSOR #1 len=68 dep=0 uid=32 oct=3 lid=32 tim=219058914 hv=168660475 ad='aa69f79c'
select /*+ INDEX(documents pk_documents) */ count(*) from documents
END OF STMT
PARSE #1:c=12,e=29,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219058914

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219058914
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108810 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=109485 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=108811 p3=1
WAIT #1: nam='db file scattered read' ela= 1 p1=22 p2=108812 p3=11

[skip]

WAIT #1: nam='db file parallel read' ela= 1 p1=1 p2=11 p3=11 WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11

[skip]

WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=109472 p3=11
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109483 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109486 p3=1
WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11

=====================

PARSING IN CURSOR #1 len=72 dep=0 uid=32 oct=3 lid=32 tim=219108177 hv=3669603672 ad='aa6a4f84'
select /*+ INDEX_FFS(documents pk_documents) */ count(*)
from documents
END OF STMT
PARSE #1:c=11,e=13,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219108177

EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219108177
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108809 p3=1
WAIT #1: nam='db file scattered read' ela= 2 p1=22 p2=108810 p3=8
WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=108818 p3=8

[skip, all next lines are the same]

My questions are

Why I get 'db file scattered read' waits for index full scan ?
Why p3 for this event is 11 even though my db_file_multiblock_read_count = 8 ?
What does mean 'db file parallel read' event?

Anjo Kolk's paper doesn't describe it (guess the wait is Oracle8-specific). Oracle 8i Reference says:

<blockquote> This happens during recovery. Database blocks that need to be changed as
part of recovery are read in parallel from the database.</blockquote>

But it seems not my case.

4. Finally, what is the difference between the two index paths in that case ?

I'm on 8.1.7.0 Solaris, file-based system, LMT

Thanks in advance,
Ed

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shevtsov, Eduard
  INET: EShevtsov_at_flagship.ru

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu May 17 2001 - 12:10:16 CDT