3 Replies Latest reply: Nov 18, 2013 12:45 AM by Nikolay Savvinov RSS

    Strange block prefetch patterns in 11g

    rfradinho

      Hi.

       

      I've detected that Oracle 11g is able to prefect blocks when using table access by index rowid. This is great for datawarehouses (DWH), but when/how Oracle uses block level prefetching is not clear.

      This problem really affects performance on 11g datawareshouses as the results can be very different.

       

      So far, where's what I've found (these results where collected for empty buffer caches):

      • Oracle is able to prefetch index and table blocks when doing table access by index rowid even if there's no nested loop join batching
      • After a tablespace being created, nested loops on table/index in the tablespace are done using single block reads (db file sequential read). This only goes away after the DB is bounced. Neither gathering statistics nor flushing buffer_cache/shared_pool solves the problem.
      • After the DB is bounced, when doing nested loops access by rowid, the first block of each extent is read using single block reads and the remaining of the extent is prefetched using 'db file scattered read' up to the end of the extend or DBFMRB (assuming the data is packed together sequentially in the extents)
      • Next time the data is requested from that extent, all data from table/index is read using db file scattered reads,  there are no longer single block reads for the extent header block.
      • Forcing full scans on index and tables is enough for Oracle to cache (somewhere) the extent header information. Flushing the cache/shared_pool will not invalidate that information and prefetches will read all extent wihout single block reads for the header block.

       

      Where's how I've reproduced the problem, on Oracle 11.2.0.1.0, Windows 7 64 bits with local disks (no ASM), 8K block size, .

      Creating a table+index in a 128K tablespace (each segment will have 16 blocks). The data in the table is sorted by ID, so filtering by ID will return the least number of block possible and all blocks are consecutive:

      CREATE TABLESPACE TS_128K DATAFILE  'd:\ORADATA\TESTE\TS_128K_002.DBF' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED LOGGING ONLINE PERMANENT

      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128k

      SEGMENT SPACE MANAGEMENT MANUAL ;

       

      create table T tablespace TS_128K nologging as

      select mod(rownum, 1e2) id, lpad('x', 1, 'x') x

      from dual connect by level <= 1e6

      order by id;

       

       

      create index IDX_T on T(id) tablespace TS_128K nologging compress;

       

       

      exec dbms_stats.gather_table_stats(user, 'T', estimate_percent=>null,method_opt=>'for all columns size 1')

       

       

      Read the data from table using the index:

       

      alter session set db_file_multiblock_read_count = 128 ;

       

      alter system flush buffer_cache;

      alter system flush shared_pool;

       


      select count(length(x)) from T where id between 10 and 16 ;

       

      Execution Plan

      --------------------------------------------------------------------------------------

      | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

      --------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT             |       |     1 |     5 |   251   (1)| 00:00:04 |

      |   1 |  SORT AGGREGATE              |       |     1 |     5 |            |          |

      |   2 |   TABLE ACCESS BY INDEX ROWID| T     | 80606 |   393K|   251   (1)| 00:00:04 |

      |*  3 |    INDEX RANGE SCAN          | IDX_T | 80606 |       |   127   (1)| 00:00:02 |

      --------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):

         3 - access("ID">=10 AND "ID"<=16)

       

      This is what I get when until I bounce the DB (all reads are db file sequential reads)

       

      WAIT #11: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=74261 tim=96925045642

      WAIT #11: nam='db file sequential read' ela= 6208 file#=7 block#=1665 blocks=1 obj#=74470 tim=96925051918

      WAIT #11: nam='db file sequential read' ela= 4851 file#=7 block#=2171 blocks=1 obj#=74470 tim=96925056835

      WAIT #11: nam='db file sequential read' ela= 5796 file#=7 block#=1819 blocks=1 obj#=74470 tim=96925062678

      WAIT #11: nam='db file sequential read' ela= 7091 file#=7 block#=280 blocks=1 obj#=74469 tim=96925069857

      WAIT #11: nam='db file sequential read' ela= 282 file#=7 block#=281 blocks=1 obj#=74469 tim=96925070204

      WAIT #11: nam='db file sequential read' ela= 270 file#=7 block#=1820 blocks=1 obj#=74470 tim=96925070601

      WAIT #11: nam='db file sequential read' ela= 218 file#=7 block#=282 blocks=1 obj#=74469 tim=96925070971

      [...]

       

      After I bounce the database, then it starts to read the first block using single reads and the remaining blocks on the extent using multiblock reads:

      EXEC #5:c=0,e=15,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1883549804,tim=98054723327

      WAIT #5: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=98054723367

      WAIT #5: nam='Disk file operations I/O' ela= 72 FileOperation=2 fileno=7 filetype=2 obj#=74474 tim=98054723486

      WAIT #5: nam='db file sequential read' ela= 12960 file#=7 block#=1665 blocks=1 obj#=74474 tim=98054736474

      WAIT #5: nam='db file sequential read' ela= 21410 file#=7 block#=2171 blocks=1 obj#=74474 tim=98054757983

      WAIT #5: nam='db file sequential read' ela= 22377 file#=7 block#=1819 blocks=1 obj#=74474 tim=98054780424

      WAIT #5: nam='db file sequential read' ela= 15349 file#=7 block#=280 blocks=1 obj#=74473 tim=98054795897

      WAIT #5: nam='db file scattered read' ela= 13822 file#=7 block#=281 blocks=7 obj#=74473 tim=98054809899

      WAIT #5: nam='db file scattered read' ela= 488 file#=7 block#=1820 blocks=4 obj#=74474 tim=98054857456

      WAIT #5: nam='db file sequential read' ela= 34925 file#=7 block#=1824 blocks=1 obj#=74474 tim=98054894002

      WAIT #5: nam='db file scattered read' ela= 171842 file#=7 block#=1825 blocks=15 obj#=74474 tim=98055066371

      WAIT #5: nam='db file sequential read' ela= 261 file#=7 block#=288 blocks=1 obj#=74473 tim=98055067476

      WAIT #5: nam='db file scattered read' ela= 1328 file#=7 block#=289 blocks=15 obj#=74473 tim=98055069298

      WAIT #5: nam='db file sequential read' ela= 24779 file#=7 block#=1840 blocks=1 obj#=74474 tim=98055098838

      WAIT #5: nam='db file scattered read' ela= 1257 file#=7 block#=1841 blocks=15 obj#=74474 tim=98055100636

      WAIT #5: nam='db file sequential read' ela= 24738 file#=7 block#=304 blocks=1 obj#=74473 tim=98055126180

      WAIT #5: nam='db file scattered read' ela= 15299 file#=7 block#=305 blocks=15 obj#=74473 tim=98055142016

      WAIT #5: nam='db file sequential read' ela= 34570 file#=7 block#=1856 blocks=1 obj#=74474 tim=98055181298

      WAIT #5: nam='db file scattered read' ela= 1309 file#=7 block#=1857 blocks=15 obj#=74474 tim=98055183144

      WAIT #5: nam='db file sequential read' ela= 13036 file#=7 block#=320 blocks=1 obj#=74473 tim=98055197077

      [...]

       

      If I flush the caches and ask for the same data , now the full extent is read using multiblock reads (if I change the filter on ID to read from extents that where never visited before, I'll get that single block read on header + remaining extent multiblock reads)

       

      WAIT #6: nam='SQL*Net message to client' ela= 4 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=98237022295

      WAIT #6: nam='db file sequential read' ela= 12608 file#=7 block#=1665 blocks=1 obj#=74474 tim=98237034964

      WAIT #6: nam='db file sequential read' ela= 4829 file#=7 block#=2171 blocks=1 obj#=74474 tim=98237039883

      WAIT #6: nam='db file scattered read' ela= 7116 file#=7 block#=1808 blocks=16 obj#=74474 tim=98237047101

      WAIT #6: nam='db file scattered read' ela= 7359 file#=7 block#=272 blocks=16 obj#=74473 tim=98237054586

      WAIT #6: nam='db file scattered read' ela= 1359 file#=7 block#=1824 blocks=16 obj#=74474 tim=98237057773

      WAIT #6: nam='db file scattered read' ela= 1358 file#=7 block#=288 blocks=16 obj#=74473 tim=98237060271

      WAIT #6: nam='db file scattered read' ela= 11341 file#=7 block#=1840 blocks=16 obj#=74474 tim=98237076799

      [...]

       

       

      Can anyone explain the rules for block prefetching/batching ? This is happening even without the query plan using nested loop batch joins as you can see in the plan above.

      Thanks in advance.