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

Re the 'scattered read' for full index scan.

This term is counter intuitive at first glance, and is commonly seen on full table scans. It makes sense that it would show up on full index scans as well.

Here's an explanation of 'scattered read' found at http://fncduh.fnal.gov/supportdb/working/logbook/db_file_multiblock_read_count.htm

 The Explain Plan for a given query will reveal if a full table scan will be  performed. The BSTAT/ESTAT report lists statistics for multi-block reads as  "db file scattered read". The term "scattered read" refers to multiple blocks  read into DB block buffers that are "scattered" throughout memory. High  values for db file scattered read is often an indicator of many full table  scans being performed. BSTAT/ESTAT is a useful tool in tuning this and other  init.ora parameters.

Jared

On Thursday 17 May 2001 01:40, Shevtsov, Eduard wrote:
> 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
>
> 1. Why I get 'db file scattered read' waits for index full scan ?
> 2. Why p3 for this event is 11 even though my db_file_multiblock_read_count
> = 8 ?
> 3. 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: Jared Still
  INET: jkstill_at_cybcon.com

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).