This discussion is archived
10 Replies Latest reply: Aug 11, 2009 11:17 AM by mbobak RSS

db file scatter read

user3266490 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    680087 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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
    680087 Pro
    Currently Being Moderated
    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
    CharlesHooper Expert
    Currently Being Moderated
    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
    680087 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points