Oracle Study之--Oracle等待事件（4）

Oracle Study之--Oracle等待事件（4）

 Db file scattered read
這個(gè)等待事件在實(shí)際生產(chǎn)庫(kù)中經(jīng)?？梢钥吹?，這是一個(gè)用戶操作引起的等待事件，當(dāng)用戶發(fā)出每次I/O需要讀取多個(gè)數(shù)據(jù)塊這樣的SQL 操作時(shí)，會(huì)產(chǎn)生這個(gè)等待事件，最常見(jiàn)的兩種情況是全表掃描（FTS： Full Table Scan）和索引快速掃描（IFFS： index fast full scan）。
這個(gè)名稱中的scattered( 分散)，可能會(huì)導(dǎo)致很多人認(rèn)為它是以scattered 的方式來(lái)讀取數(shù)據(jù)塊的，其實(shí)恰恰相反，當(dāng)發(fā)生這種等待事件時(shí)，SQL的操作都是順序地讀取數(shù)據(jù)塊的，比如FTS或者IFFS方式（如果忽略需要讀取的數(shù)據(jù)塊已經(jīng)存在內(nèi)存中的情況）。
這里的scattered指的是讀取的數(shù)據(jù)塊在內(nèi)存中的存放方式，他們被讀取到內(nèi)存中后，是以分散的方式存在在內(nèi)存中，而不是連續(xù)的。
這個(gè)等待事件有三個(gè)參數(shù)：
File#： 要讀取的數(shù)據(jù)塊所在數(shù)據(jù)文件的文件號(hào)。
Block#： 要讀取的起始數(shù)據(jù)塊號(hào)。
Blocks： 需要讀取的數(shù)據(jù)塊數(shù)目。

案例分析：

12:04:54 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
12:04:59   2  where upper(event) like 'DB FILE%';
EVENT                                                            TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read                                                 5069          .02
db file scattered read                                                   930          .03
db file single write                                                      27          .36
db file parallel write                                                    15        14.24
db file parallel read                                                     34          .64
Elapsed: 00:00:00.12

12:06:53 SCOTT@ prod>select * from t1;

12:05:04 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
  2* where upper(event) like 'DB FILE%'
EVENT                                                            TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read                                                 5166          .02
db file scattered read                                                   966          .03
db file single write                                                      27          .36
db file parallel write                                                    16        13.69
db file parallel read                                                     34          .64
Elapsed: 00:00:00.02

oracle在執(zhí)行FTS時(shí)也進(jìn)行Single Block I/O。這時(shí)即便是FTS也會(huì)發(fā)生db file sequential read等待。FTS上使用Single Block I/O或讀取比MBRC值小的塊數(shù)的情況如下：
（1）達(dá)到區(qū)的界線時(shí)：如一個(gè)區(qū)有9個(gè)塊，一次Multi Block I/O讀取8個(gè)塊，則一次以Multi Block I/O讀取之后的剩余一個(gè)塊通過(guò)Single Block I/O讀取，如果剩下的塊有兩個(gè)，就會(huì)執(zhí)行Multi Block I/O，而且只讀取兩個(gè)塊。
（2）掃描過(guò)程中讀取被緩存的塊時(shí)：如讀取8個(gè)塊時(shí)，其中第三個(gè)塊被緩存，oracle將前兩個(gè)塊通過(guò)Multi Block I/O讀取，對(duì)于第三個(gè)塊執(zhí)行一次Logical I/O，剩下的5個(gè)塊通過(guò)Multi Block I/O讀取。這種情況經(jīng)常發(fā)生時(shí)，因引發(fā)多次的I/O，可能成為FTS速度下降的原因。

（3）存在行鏈接時(shí)：在執(zhí)行FTS的過(guò)程中，如果發(fā)現(xiàn)了行鏈接，oracle為了讀取剩下的行引起的附加I/O，此時(shí)執(zhí)行Single Block I/O。

14:16:34 SYS@ prod>show parameter mult
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     19
parallel_adaptive_multi_user         boolean     TRUE

14:17:28 SYS@ prod>col segment_name for a20
14:18:08 SYS@ prod>select OWNER,SEGMENT_NAME ,SEGMENT_TYPE,EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents
14:18:47   2   where segment_name='T1' AND owner='SCOTT';
OWNER                          SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID   BLOCK_ID     BLOCKS
------------------------------ -------------------- ------------------ ---------- ---------- ----------
SCOTT                          T1                   TABLE                       0        168          8
SCOTT                          T1                   TABLE                       1        184          8
SCOTT                          T1                   TABLE                       2        192          8
SCOTT                          T1                   TABLE                       3        200          8
SCOTT                          T1                   TABLE                       4        208          8
SCOTT                          T1                   TABLE                       5        216          8
SCOTT                          T1                   TABLE                       6        224          8
SCOTT                          T1                   TABLE                       7        232          8
SCOTT                          T1                   TABLE                       8        240          8
SCOTT                          T1                   TABLE                       9        248          8
SCOTT                          T1                   TABLE                      10        256          8
SCOTT                          T1                   TABLE                      11        264          8
SCOTT                          T1                   TABLE                      12        272          8
SCOTT                          T1                   TABLE                      13        280          8
SCOTT                          T1                   TABLE                      14        288          8
SCOTT                          T1                   TABLE                      15        296          8
SCOTT                          T1                   TABLE                      16        384        128
OWNER                          SEGMENT_NAME         SEGMENT_TYPE        EXTENT_ID   BLOCK_ID     BLOCKS
------------------------------ -------------------- ------------------ ---------- ---------- ----------
SCOTT                          T1                   TABLE                      17        512        128
SCOTT                          T1                   TABLE                      18        640        128
SCOTT                          T1                   TABLE                      19        768        128
SCOTT                          T1                   TABLE                      20        896        128
SCOTT                          T1                   TABLE                      21       1024        128
22 rows selected.
Elapsed: 00:00:00.78

Db file sequential read
這個(gè)等待事件在實(shí)際生產(chǎn)庫(kù)也很常見(jiàn)，當(dāng)Oracle 需要每次I/O只讀取單個(gè)數(shù)據(jù)塊這樣的操作時(shí)，會(huì)產(chǎn)生這個(gè)等待事件。最常見(jiàn)的情況有索引的訪問(wèn)（除IFFS外的方式），回滾操作，以ROWID的方式訪問(wèn)表中的數(shù)據(jù)，重建控制文件，對(duì)文件頭做DUMP等。
這里的sequential也并非指的是Oracle 按順序的方式來(lái)訪問(wèn)數(shù)據(jù)，和db file scattered read一樣，它指的是讀取的數(shù)據(jù)塊在內(nèi)存中是以連續(xù)的方式存放的。
這個(gè)等待事件有三個(gè)參數(shù)：
File#： 要讀取的數(shù)據(jù)塊鎖在數(shù)據(jù)文件的文件號(hào)。
Block#： 要讀取的起始數(shù)據(jù)塊號(hào)。
Blocks： 要讀取的數(shù)據(jù)塊數(shù)目（這里應(yīng)該等于1）。

案例分析：

14:28:55 SYS@ prod>alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.28

14:29:08 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
14:29:41   2  where upper(event) like 'DB FILE%';
EVENT                                                            TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read                                                13991          .04
db file scattered read                                                  1637          .03
db file single write                                                      36          .35
db file parallel write                                                   946         2.98
db file parallel read                                                     46          .48
Elapsed: 00:00:00.03

14:26:46 SCOTT@ prod>create index t1_ind on t1(id);
Index created.
14:28:30 SCOTT@ prod>select * from t1 where id=1000
14:28:48   2  ;
        ID
----------
      1000
      1000
      1000
Elapsed: 00:00:00.05

14:29:46 SYS@ prod>select event,TOTAL_WAITS,AVERAGE_WAIT from v$system_event
  2* where upper(event) like 'DB FILE%'
EVENT                                                            TOTAL_WAITS AVERAGE_WAIT
---------------------------------------------------------------- ----------- ------------
db file sequential read                                                13994          .04
db file scattered read                                                  1637          .03
db file single write                                                      36          .35
db file parallel write                                                   946         2.98
db file parallel read                                                     46          .48
Elapsed: 00:00:00.03

14:29:58 SYS@ prod>

數(shù)據(jù)文件關(guān)于Multi Block I/O和Single Block I/O的活動(dòng)信息：

14:38:22 SYS@ prod>select f.file#,
  2         f.name,
  3         s.phyrds,
  4         s.phyblkrd,
  5         s.readtim,
  6         s.singleblkrds,
  7         s.singleblkrdtim,
  8         (s.phyblkrd - s.singleblkrds) as multiblkrd,
  9         (s.readtim - s.singleblkrdtim) as multiblkrdtim,
 10         round(s.singleblkrdtim /
 11               decode(s.singleblkrds, 0, 1, s.singleblkrds),
 12               3) as singleblk_avgtim,
 13         round((s.readtim - s.singleblkrdtim) /
 14               nullif((s.phyblkrd - s.singleblkrds), 0),
 15               3) as multiblk_avgtim
 16    from v$filestat s, v$datafile f
 17*  where s.file# = f.file#
     FILE# NAME                                                   PHYRDS   PHYBLKRD    READTIM SINGLEBLKRDS SINGLEBLKRDTIM MULTIBLKRD MULTIBLKRDTIM SINGLEBLK_AVGTIM MULTIBLK_AVGTIM
---------- -------------------------------------------------- ---------- ---------- ---------- ------------ -------------- ---------- ------------- ---------------- ---------------
         1 /u01/app/oracle/oradata/prod/system01.dbf               16977      68027        419        12896            373      55131              46             .029            .001
         2 /u01/app/oracle/oradata/prod/sysaux01.dbf                2041       3089        142         1894            134       1195               8             .071            .007
         3 /u01/app/oracle/oradata/prod/undotbs1.dbf                  11         11          4           11              4          0               0             .364
         4 /u01/app/oracle/oradata/prod/users01.dbf                  591       3355          8          359              7       2996               1             .019               0
         5 /u01/app/oracle/oradata/prod/example01.dbf                 10         14          0            9              0          5               0                0               0
         6 /u01/app/oracle/oradata/prod/tbs1.dbf                       4          4          0            4              0          0               0                0
         7 /u01/app/oracle/oradata/prod/undotbs2.dbf                1815       1818         50         1812             48          6               2             .026            .333
         8 /u01/app/oracle/oradata/prod/perftbs01.dbf                  4          4          0            4              0          0               0                0
         9 /u01/app/oracle/oradata/prod/tbs2.dbf                       4          4          0            4              0          0               0                0
9 rows selected.

select f.file#,  
       f.name,  
       s.phyrds,  
       s.phyblkrd,  
       s.readtim, --所有的讀取工作信息  
       s.singleblkrds,  
       s.singleblkrdtim, --Single Block I/O  
       (s.phyblkrd - s.singleblkrds) as multiblkrd, --Multi Block I/O次數(shù)  
       (s.readtim - s.singleblkrdtim) as multiblkrdtim, --Multi Block I/O時(shí)間  
       round(s.singleblkrdtim /  
             decode(s.singleblkrds, 0, 1, s.singleblkrds),  
             3) as singleblk_avgtim, --Single Block I/O 平均等待時(shí)間（cs）  
       round((s.readtim - s.singleblkrdtim) /  
             nullif((s.phyblkrd - s.singleblkrds), 0),  
             3) as multiblk_avgtim --Multi Block I/O 平均等待時(shí)間（cs）  
  from v$filestat s, v$datafile f  
 where s.file# = f.file#;