10 Replies Latest reply: Aug 11, 2009 1:17 PM by mbobak RSS

    db file scatter read

    user3266490
      Hi,

      Db file scatter read means oracle Process_ reads the scatter blocks rather than contiguous block from datafiles  and  put them into buffers
      Is it correct?
        • 1. Re: db file scatter read
          Tubby
          http://www.oracle.com/technology/documentation/database11gr1.html

          A good place to search before asking on the forums.
          • 2. Re: db file scatter read
            615264
            In simple terms scattered reads means the full table scan when the DB blocks are scattered in the SGA buffer cache and the blocks are scattered all over the buffer cache.

            In comparison sequential read means index scan on a table where the index blocks are sequentially read from the buffer cache.

            Hope it helps at a high level.

            Amar
            • 3. Re: db file scatter read
              Pavan DBA
              hope these links through google help you.
              http://www.google.co.in/search?hl=en&q=what+is+db+file+scattered+read&meta=&aq=0&oq=what+is+db+file+sca
              • 4. Re: db file scatter read
                Timur Akhmadeev
                In simple terms scattered reads means the full table scan ...
                Wrong. Scattered/sequential reads has nothing to do with access methods. Both FTS and range scans (starting with 10g) may result in scattered and sequential reads being performed by server process. Here is an example:
                drop table t cascade constraints purge;
                create table t nologging as 
                select mod(rownum, 1e5) id, lpad('x', 100, 'x') x
                  from dual connect by level <= 1e7;
                create index t_indx on t(id) nologging;
                exec dbms_stats.gather_table_stats(user, 't', estimate_percent=>null,method_opt=>'for all columns size 1')
                
                --explain plan for select * from t where id between 1000 and 1100;
                --select * from table(dbms_xplan.display);
                
                --alter system flush buffer_cache;
                alter session set events 'immediate trace name flush_cache';
                
                set arraysize 1000 pagesize 100000
                exec dbms_monitor.session_trace_enable(null,null,waits=>true)
                select /*+ index(t t_indx) */ * from t where id between 1000 and 10000;
                exec dbms_monitor.session_trace_disable(null,null)
                select /*+ index(t t_indx) */ * 
                from
                 t where id between 1000 and 10000
                
                
                call     count       cpu    elapsed       disk      query    current        rows
                ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                Parse        1      0.00       0.00          0          0          0           0
                Execute      1      0.00       0.00          0          0          0           0
                Fetch      902      0.00       4.41      16318     902884          0      900100
                ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                total      904      0.00       4.42      16318     902884          0      900100
                
                Misses in library cache during parse: 1
                Optimizer mode: ALL_ROWS
                Parsing user id: 48  
                
                Elapsed times include waiting on following events:
                  Event waited on                             Times   Max. Wait  Total Waited
                  ----------------------------------------   Waited  ----------  ------------
                  SQL*Net message to client                     902        0.00          0.00
                  db file scattered read                       2053        0.05          1.53
                  SQL*Net message from client                   902        0.26         60.03
                  db file sequential read                      1173        0.01          0.03
                  SQL*Net more data to client                  1800        0.00          0.02
                ********************************************************************************
                The plan for the query wasn't captured in the trace, but it uses the index, here is the plan:
                SQL> select * from table(dbms_xplan.display_cursor('3931941940'));
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------------
                
                HASH_VALUE  3931941940, child number 0
                --------------------------------------
                select /*+ index(t t_indx) */ * from t where id between 1000 and 10000
                
                Plan hash value: 4155885868
                
                --------------------------------------------------------------------------------------
                | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT            |        |       |       | 45136 (100)|          |
                |   1 |  TABLE ACCESS BY INDEX ROWID| T      |   900K|    90M| 45136   (1)| 00:09:02 |
                |*  2 |   INDEX RANGE SCAN          | T_INDX |   900K|       |   101   (1)| 00:00:02 |
                --------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   2 - access("ID">=1000 AND "ID"<=10000)
                As you can see, the query resulted in ~2K of scattered and 1K of sequential reads. Note that scattered reads were issued for both index and table segments:
                PARSING IN CURSOR #26 len=70 dep=0 uid=48 oct=3 lid=48 tim=1220678897390027 hv=3931941940 ad='7cbeac34'
                select /*+ index(t t_indx) */ * from t where id between 1000 and 10000
                END OF STMT
                PARSE #26:c=0,e=7466,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1220678897390020
                EXEC #26:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1220678897399286
                WAIT #26: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=164225 tim=1220678897399330
                WAIT #26: nam='db file scattered read' ela= 67 file#=4 block#=639881 blocks=8 obj#=164225 tim=1220678897399505
                WAIT #26: nam='db file scattered read' ela= 51 file#=4 block#=591705 blocks=8 obj#=164225 tim=1220678897399637
                WAIT #26: nam='db file scattered read' ela= 69 file#=8 block#=82401 blocks=8 obj#=164225 tim=1220678897399777
                WAIT #26: nam='db file scattered read' ela= 61 file#=4 block#=12921 blocks=8 obj#=164224 tim=1220678897399913
                FETCH #26:c=0,e=585,p=32,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1220678897399965
                WAIT #26: nam='SQL*Net message from client' ela= 850 driver id=1413697536 #bytes=1 p3=0 obj#=164224 tim=1220678897400879
                WAIT #26: nam='db file sequential read' ela= 11 file#=4 block#=329788 blocks=1 obj#=164224 tim=1220678897400951
                WAIT #26: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=164224 tim=1220678897400986
                WAIT #26: nam='db file sequential read' ela= 8 file#=4 block#=387031 blocks=1 obj#=164224 tim=1220678897401035
                WAIT #26: nam='db file sequential read' ela= 9 file#=4 block#=393841 blocks=1 obj#=164224 tim=1220678897401087
                WAIT #26: nam='db file scattered read' ela= 38462 file#=4 block#=515385 blocks=8 obj#=164224 tim=1220678897439607
                WAIT #26: nam='db file scattered read' ela= 36644 file#=4 block#=515905 blocks=8 obj#=164224 tim=1220678897476355
                WAIT #26: nam='db file scattered read' ela= 35308 file#=4 block#=518489 blocks=8 obj#=164224 tim=1220678897511736
                WAIT #26: nam='db file scattered read' ela= 77 file#=4 block#=519009 blocks=8 obj#=164224 tim=1220678897511888
                WAIT #26: nam='db file scattered read' ela= 66 file#=4 block#=521593 blocks=8 obj#=164224 tim=1220678897512024
                WAIT #26: nam='db file scattered read' ela= 30257 file#=4 block#=522121 blocks=8 obj#=164224 tim=1220678897542348
                WAIT #26: nam='db file scattered read' ela= 36313 file#=4 block#=524705 blocks=8 obj#=164224 tim=1220678897578740
                WAIT #26: nam='db file scattered read' ela= 64 file#=4 block#=525225 blocks=8 obj#=164224 tim=1220678897578885 
                ...
                SQL> select object_id, object_name from user_objects where object_id in (164224, 164225);
                
                 OBJECT_ID OBJECT_NAME
                ---------- ------------------------------------------------------------------------------
                    164224 T
                    164225 T_INDX
                • 5. Re: db file scatter read
                  user3266490
                  Hi,

                  Thanks for reply.I would like FIRST to clear whether db file sequential read is related to memory(SGA) or disk (data file)
                  Bcz,Server Process read the blocks from DataFiles First and then only Put them into Buffer.Is It Correct?


                  When these events Happened? I mentioned followings please tell which one is correct

                  If the table data blocks stored in many datafiles,but buffer is contiguous buffer. what is happened.

                  If the table data blocks stored in many datafiles,but buffer is not contiguous buffer. what is happened.


                  If the table data blocks stored in a single datafile,but buffer is non contiguous buffer. what is happened.

                  If the table data blocks stored in a single datafile,but buffer is contiguous buffer. what is happened. I think this one is not db file scatter read
                  • 6. Re: db file scatter read
                    Charles Hooper
                    Timur Akhmadeev wrote:
                    In simple terms scattered reads means the full table scan ...
                    Wrong. Scattered/sequential reads has nothing to do with access methods. Both FTS and range scans (starting with 10g) may result in scattered and sequential reads being performed by server process. Here is an example:
                    (snip)
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------------
                    
                    HASH_VALUE  3931941940, child number 0
                    --------------------------------------
                    select /*+ index(t t_indx) */ * from t where id between 1000 and 10000
                    
                    Plan hash value: 4155885868
                    
                    --------------------------------------------------------------------------------------
                    | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                    --------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT            |        |       |       | 45136 (100)|          |
                    |   1 |  TABLE ACCESS BY INDEX ROWID| T      |   900K|    90M| 45136   (1)| 00:09:02 |
                    |*  2 |   INDEX RANGE SCAN          | T_INDX |   900K|       |   101   (1)| 00:00:02 |
                    --------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                    2 - access("ID">=1000 AND "ID"<=10000)
                    [/code]
                    As you can see, the query resulted in ~2K of scattered and 1K of sequential reads. Note that scattered reads were issued for both index and table segments:
                    [code]
                    PARSING IN CURSOR #26 len=70 dep=0 uid=48 oct=3 lid=48 tim=1220678897390027 hv=3931941940 ad='7cbeac34'
                    select /*+ index(t t_indx) */ * from t where id between 1000 and 10000
                    END OF STMT
                    PARSE #26:c=0,e=7466,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1220678897390020
                    EXEC #26:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1220678897399286
                    WAIT #26: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=164225 tim=1220678897399330
                    WAIT #26: nam='db file scattered read' ela= 67 file#=4 block#=639881 blocks=8 obj#=164225 tim=1220678897399505
                    WAIT #26: nam='db file scattered read' ela= 51 file#=4 block#=591705 blocks=8 obj#=164225 tim=1220678897399637
                    WAIT #26: nam='db file scattered read' ela= 69 file#=8 block#=82401 blocks=8 obj#=164225 tim=1220678897399777
                    WAIT #26: nam='db file scattered read' ela= 61 file#=4 block#=12921 blocks=8 obj#=164224 tim=1220678897399913
                    FETCH #26:c=0,e=585,p=32,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=1220678897399965
                    WAIT #26: nam='SQL*Net message from client' ela= 850 driver id=1413697536 #bytes=1 p3=0 obj#=164224 tim=1220678897400879
                    WAIT #26: nam='db file sequential read' ela= 11 file#=4 block#=329788 blocks=1 obj#=164224 tim=1220678897400951
                    WAIT #26: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=164224 tim=1220678897400986
                    WAIT #26: nam='db file sequential read' ela= 8 file#=4 block#=387031 blocks=1 obj#=164224 tim=1220678897401035
                    WAIT #26: nam='db file sequential read' ela= 9 file#=4 block#=393841 blocks=1 obj#=164224 tim=1220678897401087
                    WAIT #26: nam='db file scattered read' ela= 38462 file#=4 block#=515385 blocks=8 obj#=164224 tim=1220678897439607
                    WAIT #26: nam='db file scattered read' ela= 36644 file#=4 block#=515905 blocks=8 obj#=164224 tim=1220678897476355
                    WAIT #26: nam='db file scattered read' ela= 35308 file#=4 block#=518489 blocks=8 obj#=164224 tim=1220678897511736
                    WAIT #26: nam='db file scattered read' ela= 77 file#=4 block#=519009 blocks=8 obj#=164224 tim=1220678897511888
                    WAIT #26: nam='db file scattered read' ela= 66 file#=4 block#=521593 blocks=8 obj#=164224 tim=1220678897512024
                    WAIT #26: nam='db file scattered read' ela= 30257 file#=4 block#=522121 blocks=8 obj#=164224 tim=1220678897542348
                    WAIT #26: nam='db file scattered read' ela= 36313 file#=4 block#=524705 blocks=8 obj#=164224 tim=1220678897578740
                    WAIT #26: nam='db file scattered read' ela= 64 file#=4 block#=525225 blocks=8 obj#=164224 tim=1220678897578885 
                    ...
                    SQL> select object_id, object_name from user_objects where object_id in (164224, 164225);
                    
                    OBJECT_ID OBJECT_NAME
                    ---------- ------------------------------------------------------------------------------
                    164224 T
                    164225 T_INDX
                    Timur,

                    Nice test case. I believe that it is a feature of Oracle *11g* that allows what would have been multiple single block reads for an index range scan to be transformed into multi-block reads. I do not believe that this is available in 10g. However, multi-block reads are possible during a fast full scan of an index.

                    It is good that you provided the output of USER_OBJECTS and a section of the trace file. Notice that the OBJECT_ID for table T is 164224. The multiblock reads are for obj#=164224, rather than 164225. I have a feeling that something odd may have happened in your test run - Oracle is actually performing a full table scan.

                    This is the output that I see with ALLSTATS LAST with your test case:
                    select /*+ index(t t_indx) */ t.* from t where  id between 1000 and 10000
                     
                    Plan hash value: 4155885868
                     
                    ---------------------------------------------------------------------------------------------------------
                    | Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                    ---------------------------------------------------------------------------------------------------------
                    |   1 |  TABLE ACCESS BY INDEX ROWID| T      |      1 |    900K|    900K|00:01:04.85 |     910K|  15831 |
                    |*  2 |   INDEX RANGE SCAN          | T_INDX |      1 |    900K|    900K|00:00:03.27 |   10867 |   1885 |
                    ---------------------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                        2 - access("ID">=1000 AND "ID"<=10000)
                    If I try to force a fast full index scan, Oracle actually switched to a full table scan, which completed about 3 times faster:
                    select /*+ INDEX_FFS(t t_indx) */ t.* from t where  id between 1000 and 10000
                     
                    Plan hash value: 1601196873
                     
                    ---------------------------------------------------------------------------------------------
                    | Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
                    ---------------------------------------------------------------------------------------------
                    |*  1 |  TABLE ACCESS FULL| T    |      1 |    900K|    900K|00:00:18.19 |     162K|    153K|
                    ---------------------------------------------------------------------------------------------
                     
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                       1 - filter(("ID"<=10000 AND "ID">=1000))
                    TKPROF and partial 10046:
                    select /*+ index(t t_indx) */ t.* 
                    from
                     t where  id between 1000 and 10000 
                    
                    
                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch     9002      9.40      66.57      15831     910967          0      900100
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total     9004      9.40      66.57      15831     910967          0      900100
                    
                    Misses in library cache during parse: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 31  
                    
                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                     900100  TABLE ACCESS BY INDEX ROWID T (cr=910967 pr=15831 pw=0 time=64854265 us)
                     900100   INDEX RANGE SCAN T_INDX (cr=10867 pr=1885 pw=0 time=3272138 us)(object id 114076)
                    
                    
                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                    9002        0.00          0.01
                      db file sequential read                     15831        0.32         57.37
                      SQL*Net message from client                  9002       10.65         18.71
                    
                    ----
                    PARSING IN CURSOR #1 len=74 dep=0 uid=31 oct=3 lid=31 tim=2397535788 hv=456534877 ad='9b7f6ac0'
                    select /*+ index(t t_indx) */ t.* from t where  id between 1000 and 10000 
                    END OF STMT
                    PARSE #1:c=0,e=1581,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2397535782
                    EXEC #1:c=0,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2397536306
                    WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2397536406
                    WAIT #1: nam='db file sequential read' ela= 147 file#=4 block#=1309996 blocks=1 obj#=114076 tim=2397536812
                    WAIT #1: nam='db file sequential read' ela= 100 file#=4 block#=395874 blocks=1 obj#=114076 tim=2397537067
                    WAIT #1: nam='db file sequential read' ela= 97 file#=4 block#=395493 blocks=1 obj#=114076 tim=2397537318
                    WAIT #1: nam='db file sequential read' ela= 37026 file#=4 block#=388707 blocks=1 obj#=114074 tim=2397574530
                    WAIT #1: nam='db file sequential read' ela= 21965 file#=4 block#=390245 blocks=1 obj#=114074 tim=2397596843
                    WAIT #1: nam='db file sequential read' ela= 4590 file#=4 block#=391784 blocks=1 obj#=114074 tim=2397601682
                    WAIT #1: nam='db file sequential read' ela= 5664 file#=4 block#=393322 blocks=1 obj#=114074 tim=2397607523
                    WAIT #1: nam='db file sequential read' ela= 1304 file#=4 block#=394861 blocks=1 obj#=114074 tim=2397608989
                    WAIT #1: nam='db file sequential read' ela= 3669 file#=4 block#=429943 blocks=1 obj#=114074 tim=2397612833
                    WAIT #1: nam='db file sequential read' ela= 2750 file#=4 block#=431482 blocks=1 obj#=114074 tim=2397615748
                    WAIT #1: nam='db file sequential read' ela= 4971 file#=4 block#=433020 blocks=1 obj#=114074 tim=2397620886
                    WAIT #1: nam='db file sequential read' ela= 2517 file#=4 block#=434558 blocks=1 obj#=114074 tim=2397623582
                    
                    ----
                    ----
                    
                    select /*+ INDEX_FFS(t t_indx) */ t.* 
                    from
                     t where  id between 1000 and 10000 
                    
                    
                    call     count       cpu    elapsed       disk      query    current        rows
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    Parse        1      0.00       0.00          0          0          0           0
                    Execute      1      0.00       0.00          0          0          0           0
                    Fetch     9002      6.57      19.80     153845     162605          0      900100
                    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
                    total     9004      6.57      19.81     153845     162605          0      900100
                    
                    Misses in library cache during parse: 1
                    Optimizer mode: ALL_ROWS
                    Parsing user id: 31  
                    
                    Rows     Row Source Operation
                    -------  ---------------------------------------------------
                     900100  TABLE ACCESS FULL T (cr=162605 pr=153845 pw=0 time=18193729 us)
                    
                    
                    Elapsed times include waiting on following events:
                      Event waited on                             Times   Max. Wait  Total Waited
                      ----------------------------------------   Waited  ----------  ------------
                      SQL*Net message to client                    9002        0.00          0.00
                      db file sequential read                         1        0.00          0.00
                      db file scattered read                       1222        0.29         15.22
                      SQL*Net message from client                  9002       10.67         18.44
                    
                    PARSING IN CURSOR #9 len=78 dep=0 uid=31 oct=3 lid=31 tim=2657656799 hv=1829037591 ad='9933f8d0'
                    select /*+ INDEX_FFS(t t_indx) */ t.* from t where  id between 1000 and 10000 
                    END OF STMT
                    PARSE #9:c=0,e=1566,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2657656793
                    EXEC #9:c=0,e=62,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2657657520
                    WAIT #9: nam='SQL*Net message to client' ela= 5 driver id=1413697536 #bytes=1 p3=0 obj#=355 tim=2657657643
                    WAIT #9: nam='db file sequential read' ela= 193 file#=4 block#=1306283 blocks=1 obj#=114074 tim=2657657988
                    WAIT #9: nam='db file scattered read' ela= 4564 file#=4 block#=1306284 blocks=5 obj#=114074 tim=2657662844
                    WAIT #9: nam='db file scattered read' ela= 6158 file#=4 block#=1306289 blocks=8 obj#=114074 tim=2657669291
                    WAIT #9: nam='db file scattered read' ela= 495 file#=4 block#=1306298 blocks=7 obj#=114074 tim=2657670080
                    FETCH #9:c=0,e=12666,p=21,cr=20,cu=0,mis=0,r=100,dep=0,og=1,tim=2657670409
                    WAIT #9: nam='SQL*Net message from client' ela= 1632 driver id=1413697536 #bytes=1 p3=0 obj#=114074 tim=2657672229
                    WAIT #9: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=114074 tim=2657672417
                    The above was performed with STATISTICS_LEVEL set to ALL at the session level, with an array fetch size of 100 (not fetched using SQL*Plus).

                    There is always a chance that Oracle will switch to a different execution plan, but with a valid index hint like you supplied, that should not happen.

                    Charles Hooper
                    IT Manager/Oracle DBA
                    K&M Machine-Fabricating, Inc.
                    • 7. Re: db file scatter read
                      Timur Akhmadeev
                      Nice test case. I believe that it is a feature of Oracle 11g that allows what would have been multiple single block reads for an index range scan to be transformed into multi-block reads. I do not believe that this is available in 10g. However, multi-block reads are possible during a fast full scan of an index.
                      Nope, this is
                      SQL> select * from v$version;
                      
                      BANNER
                      ----------------------------------------------------------------
                      Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
                      PL/SQL Release 10.2.0.3.0 - Production
                      CORE    10.2.0.3.0      Production
                      TNS for Linux: Version 10.2.0.3.0 - Production
                      NLSRTL Version 10.2.0.3.0 - Production
                      on 2.4.21-32.0.1.ELsmp with 4 Xeon cores (or threads - I'm not sure).
                      I have a feeling that something odd may have happened in your test run - Oracle is actually performing a full table scan.
                      No, FTS is impossible. You can see access to the t_index with 'db file scattered read' - there are plenty of them. Plus there is only one plan for that query and I've provided it earlier.

                      The strace output shows following calls for multiblock IO:
                      _llseek(9, 4354678784, [4354678784], SEEK_SET) = 0
                      readv(9, [{"\6\242\0\0y\34\10\1\256+l\200u\10\2\0049(\0\0\1\0\0\0\200"..., 8192}, {"\6\242\0\0z\34\10\1\256+l\200u\10\2\4r\22\0\0\1\0\0\0\200"..., 8192}, {"\
                      6\242\0\0{\34\10\1\256+l\200u\10\2\4\'\n\0\0\1\0\0\0\200"..., 8192}, {"\6\242\0\0|\34\10\1\256+l\200u\10\2\4\323\344\0\0\1\0\0"..., 8192}, {"\6\242\0\0}\34\1
                      0\1\256+l\200u\10\2\4\4.\0\0\1\0\0\0\200"..., 8192}, {"\6\242\0\0~\34\10\1\256+l\200u\10\2\4\336\325\0\0\1\0\0"..., 8192}, {"\6\242\0\0\177\34\10\1\256+l\200
                      u\10\2\4\237\342\0\0\1"..., 8192}, {"\6\242\0\0\200\34\10\1\256+l\200u\10\2\4&\220\0\0\1\0\0"..., 8192}], 8) = 65536
                      Oracle uses readv on this machine, reading a continuous piece of a data from a file represented by a file descriptor into a provided set of bufferes. It's probably a way to "fill ahead" buffer cache with data blocks which would be necessary later.
                      If I try to force a fast full index scan, Oracle actually switched to a full table scan, which completed about 3 times faster:
                      It's because you select all data feilds - I don't think it's a good idea to use IFFS in this case, and probably Oracle isn't able to do it, that's why it switched to FTS. Selecting just id is fine:
                      SQL> explain plan for select /*+ INDEX_FFS(t t_indx) */ id from t where  id between 1000 and 10000;
                      
                      Explained.
                      
                      SQL> @xp
                      
                      PLAN_TABLE_OUTPUT
                      ---------------------------------------------------------------------------------------------------
                      
                      Plan hash value: 234994809
                      
                      -------------------------------------------------------------------------------
                      | Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                      -------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT     |        |   900K|  4395K|  4997   (3)| 00:01:00 |
                      |*  1 |  INDEX FAST FULL SCAN| T_INDX |   900K|  4395K|  4997   (3)| 00:01:00 |
                      -------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - filter("ID"<=10000 AND "ID">=1000)
                      • 8. Re: db file scatter read
                        Charles Hooper
                        Timur,

                        I think that I understand what is happening here.

                        You are correct that a db file scattered read wait may occur during an index range scan due to an index pre-fetch operation. It did not happen during my testing with Oracle 10.2.0.2, but I may try again later. I was not considering an index pre-fetch operation, and when your trace file showed multi-block reads for the table object, I overlooked that it also performed multiblock reads of the index, and assumed that Oracle pulled a fast one, switching to a full tablescan for the execution rather than an index range scan. References:
                        http://www.freelists.org/post/oracle-l/RE-Calculating-LIOs,11
                        http://www.freelists.org/post/oracle-l/db-file-parallel-read-for-prefetching,2

                        When I stated "I believe that it is a feature of Oracle 11g that allows what would have been multiple single block reads for an index range scan to be transformed into multi-block reads" I was mentioning the capability of Oracle 11g to transform what would have been multiple single block reads into a multiblock read of non-continguous blocks. This would not show as a db file multiblock read wait, but instead as a db file parallel read wait. References:
                        http://jonathanlewis.wordpress.com/2006/12/15/index-operations/
                        http://download.oracle.com/docs/cd/B28359_01/server.111/b28320/waitevents003.htm

                        Charles Hooper
                        IT Manager/Oracle DBA
                        K&M Machine-Fabricating, Inc.
                        • 9. Re: db file scatter read
                          Timur Akhmadeev
                          Charles, thanks for the links.
                          'db file parallel read' due to index prefetch was implemented at least in Oracle 9.2. 'db file scattered reads' on IRS/IFS/ISS access - in 10g (don't know the release), and I wouldn't call it "index prefetch".
                          Here is what I've got from v$sesstat:
                          SQL> select name,value
                            2    from v$sesstat s, v$statname n
                            3   where s.statistic# = n.statistic#
                            4     and s.sid = userenv('sid')
                            5     and n.name like '%prefetch%'
                            6   order by name;
                          
                          NAME                                                                  VALUE
                          ---------------------------------------------------------------- ----------
                          index crx upgrade (prefetch)                                              0
                          physical reads cache prefetch                                             0
                          physical reads prefetch warmup                                            0
                          prefetch clients - 16k                                                    0
                          prefetch clients - 2k                                                     0
                          prefetch clients - 32k                                                    0
                          prefetch clients - 4k                                                     0
                          prefetch clients - 8k                                                     0
                          prefetch clients - default                                                0
                          prefetch clients - keep                                                   0
                          prefetch clients - recycle                                                0
                          prefetch warmup blocks aged out before use                                0
                          prefetch warmup blocks flushed out before use                             0
                          prefetched blocks aged out before use                                     0
                          table lookup prefetch client count                                        0
                          
                          15 rows selected.
                          
                          SQL>
                          SQL> alter session set events '10053 trace name context forever';
                          
                          Session altered.
                          
                          SQL> set autotrace traceonly
                          SQL> set arraysize 1000 pagesize 100000
                          SP2-0267: pagesize option 100000 out of range (0 through 50000)
                          SQL> select /*+ index(t t_indx) t01 */ * from t where id between 1000 and 10000;
                          
                          900100 rows selected.
                          
                          
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 4155885868
                          
                          --------------------------------------------------------------------------------------
                          | Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                          --------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT            |        |   900K|    90M| 45136   (1)| 00:09:02 |
                          |   1 |  TABLE ACCESS BY INDEX ROWID| T      |   900K|    90M| 45136   (1)| 00:09:02 |
                          |*  2 |   INDEX RANGE SCAN          | T_INDX |   900K|       |   101   (1)| 00:00:02 |
                          --------------------------------------------------------------------------------------
                          
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                          
                             2 - access("ID">=1000 AND "ID"<=10000)
                          
                          
                          Statistics
                          ----------------------------------------------------------
                                    1  recursive calls
                                    0  db block gets
                               902884  consistent gets
                                16497  physical reads
                                    0  redo size
                              4614302  bytes sent via SQL*Net to client
                                10292  bytes received via SQL*Net from client
                                  902  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    0  sorts (disk)
                               900100  rows processed
                          
                          SQL> set autotrace off
                          SQL> alter session set events '10053 trace name context off';
                          
                          Session altered.
                          
                          SQL>
                          SQL> select name,value
                            2    from v$sesstat s, v$statname n
                            3   where s.statistic# = n.statistic#
                            4     and s.sid = userenv('sid')
                            5     and n.name like '%prefetch%'
                            6   order by name;
                          
                          NAME                                                                  VALUE
                          ---------------------------------------------------------------- ----------
                          index crx upgrade (prefetch)                                              0
                          physical reads cache prefetch                                         14329
                          physical reads prefetch warmup                                        14329
                          prefetch clients - 16k                                                    0
                          prefetch clients - 2k                                                     0
                          prefetch clients - 32k                                                    0
                          prefetch clients - 4k                                                     0
                          prefetch clients - 8k                                                     0
                          prefetch clients - default                                                0
                          prefetch clients - keep                                                   0
                          prefetch clients - recycle                                                0
                          prefetch warmup blocks aged out before use                                0
                          prefetch warmup blocks flushed out before use                             0
                          prefetched blocks aged out before use                                     0
                          table lookup prefetch client count                                        0
                          
                          15 rows selected.
                          'physical reads cache prefetch' & 'physical reads prefetch warmup' - that's how this feature is called. Important thing is cache warmup is not something "logical" since it fetches continuous blocks on disk (quite possible they would not be accessed at all in the near future) unlike index prefetch, which determines needed blocks and then issues IO for them in one call.
                          10053 shows nothing about prefetching being used.
                          • 10. Re: db file scatter read
                            mbobak
                            This has obviously gone beyond the OP's question. However, to succinctly answer that question, see this link:
                            http://www.hotsos.com/e-library/abstract.php?id=16

                            Oh, and BTW, 'db file sequential read' and 'db file scattered read', both indicate physical I/O.

                            -Mark