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

From: Shevtsov, Eduard <EShevtsov_at_flagship.ru>
Date: Thu, 17 May 2001 01:01:40 -0700
Message-ID: <F001.00305D8C.20010517004051@fatcity.com>

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 - 03:01:40 CDT