批量修改數(shù)據(jù)后應(yīng)收集統(tǒng)計(jì)信息

一次幫朋友診斷一個(gè)問(wèn)題，最終發(fā)現(xiàn)導(dǎo)致問(wèn)題的主要原因是統(tǒng)計(jì)信息不準(zhǔn)確。下面是詳細(xì)診斷過(guò)程。

問(wèn)題分析

出現(xiàn)問(wèn)題的數(shù)據(jù)庫(kù)中錯(cuò)誤地加載了幾百萬(wàn)條記錄。發(fā)現(xiàn)加載錯(cuò)誤后，嘗試通過(guò)DELETE語(yǔ)句刪除這些數(shù)據(jù)，但是這時(shí)發(fā)現(xiàn)了問(wèn)題，這些數(shù)據(jù)的查詢變得異常緩慢，更不要說(shuō)DELETE操作了。

對(duì)數(shù)據(jù)庫(kù)進(jìn)行檢查后發(fā)現(xiàn)，CBO給出了一個(gè)非常低效的執(zhí)行計(jì)劃：將兩個(gè)BTREE索引轉(zhuǎn)化為BITMAP索引，然后執(zhí)行BITMAP AND操作，再將得到的BITMAP索引轉(zhuǎn)化為BTREE索引，最后根據(jù)索引的ROWID對(duì)表進(jìn)行掃描。

首先需要了解錯(cuò)誤加載的數(shù)據(jù)量，但是CBO給出的這個(gè)執(zhí)行計(jì)劃在短時(shí)間內(nèi)根本無(wú)法得到結(jié)果，于是通過(guò)提示INDEX_JOIN將執(zhí)行計(jì)劃改為索引連接方式，最終得到了COUNT(*)的結(jié)果，發(fā)現(xiàn)錯(cuò)誤加載的數(shù)據(jù)超過(guò)了400萬(wàn)。對(duì)于這種數(shù)據(jù)量而言，通過(guò)索引掃描獲得本身就是個(gè)災(zāi)難，更不要說(shuō)還要進(jìn)行BITMAP和BTREE之間的兩次轉(zhuǎn)換了。

導(dǎo)致執(zhí)行計(jì)劃出錯(cuò)的主要原因就是統(tǒng)計(jì)信息不準(zhǔn)確，不過(guò)檢查表和索引的上次分析時(shí)間，發(fā)現(xiàn)是昨天夜里才分析過(guò)的。進(jìn)一步詢問(wèn)后發(fā)現(xiàn)，錯(cuò)誤的數(shù)據(jù)加載發(fā)生在今天，而查詢性能下降是從錯(cuò)誤的數(shù)據(jù)導(dǎo)入后開始的。

之后找到了導(dǎo)致問(wèn)題的原因：在導(dǎo)入大量數(shù)據(jù)后沒(méi)有重新收集統(tǒng)計(jì)信息，CBO根據(jù)加載數(shù)據(jù)前的統(tǒng)計(jì)信息進(jìn)行判斷，因此得到了這種“高效”的執(zhí)行計(jì)劃。事實(shí)也確實(shí)如此，因?yàn)樵诩虞d數(shù)據(jù)前滿足這個(gè)查詢的記錄是0條，而加載數(shù)據(jù)后滿足這個(gè)查詢的結(jié)果超過(guò)了400萬(wàn)條。導(dǎo)致問(wèn)題產(chǎn)生的并不是CBO的算法，而是加載數(shù)據(jù)后沒(méi)有同步更新統(tǒng)計(jì)信息。

測(cè)試案例

上面的問(wèn)題分析起來(lái)比較抽象，下面通過(guò)一個(gè)例子來(lái)模擬再現(xiàn)這個(gè)問(wèn)題：

    SQL＞ CREATE TABLE T (
      2  ID NUMBER,
      3  NAME VARCHAR2(30),
      4  TYPE VARCHAR2(30),
      5  OTHER VARCHAR2(4000));
    表已創(chuàng)建。
    SQL＞ CREATE INDEX IND_T_NAME
      2  ON T(NAME);
    索引已創(chuàng)建。
    SQL＞ CREATE INDEX IND_T_TYPE
      2  ON T(TYPE);
    索引已創(chuàng)建。
    SQL＞ INSERT INTO T
      2  SELECT ROWNUM,
      3  OBJECT_NAME,
      4  OBJECT_TYPE,
      5  LPAD('A', 2000, 'A')
      6  FROM ALL_OBJECTS;
    已創(chuàng)建69406行。
    SQL＞ UPDATE T
      2  SET NAME = 'T'
      3  WHERE TYPE = 'SYNONYM';
    已更新27696行。
    SQL＞ COMMIT;
    提交完成。
    SQL＞ EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
    PL/SQL過(guò)程已成功完成。

首先構(gòu)造了一張大表，其中UPDATE語(yǔ)句是為了目標(biāo)查詢可以采用BITMAP INDEX AND執(zhí)行計(jì)劃。如果沒(méi)有進(jìn)行更新，則NAME列等于‘T’這個(gè)條件的選擇性很高，CBO會(huì)選擇NAME列上的索引掃描，而不會(huì)選擇兩個(gè)索引進(jìn)行BITMAP AND：

    SQL＞ SET AUTOT TRACE
    SQL＞ SELECT ＊
      2  FROM T
      3  WHERE NAME = 'T'
      4  AND TYPE = 'TABLE';
    執(zhí)行計(jì)劃
    ----------------------------------------------------------
    Plan hash value: 4030788717
    -----------------------------------------------------------------------------------------
    | Id| Operation                         | Name       |Rows |Bytes |Cost(%CPU)  | Time     |
    -----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT                  |            |    1 |  2031|    2   (0) | 00:00:01 |
    | 1 |  TABLE ACCESS BY INDEX ROWID     | T          |    1 |  2031|    2   (0) | 00:00:01 |
    | 2 |   BITMAP CONVERSION TO ROWIDS     |            |     |      |            |          |
    | 3 |    BITMAP AND                     |            |     |      |            |          |
    | 4 |    BITMAP CONVERSION FROM ROWIDS |            |     |      |            |          |
    |＊5 |     INDEX RANGE SCAN             |IND_T_NAME  |    2 |      |    1   (0) | 00:00:01 |
    | 6 |    BITMAP CONVERSION FROM ROWIDS |            |     |      |            |          |
    |＊7 |     INDEX RANGE SCAN             |IND_T_TYPE  |    2 |      |    1   (0) | 00:00:01 |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      5- access("NAME"='T')
      7- access("TYPE"='TABLE')
    統(tǒng)計(jì)信息
    ----------------------------------------------------------
            1  recursive calls
            0  db block gets
            49  consistent gets
            0  physical reads
            0  redo size
          2746  bytes sent via SQL＊Net to client
            519  bytes received via SQL＊Net from client
            2  SQL＊Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
            1  rows processed

根據(jù)現(xiàn)在的統(tǒng)計(jì)信息，CBO認(rèn)為采用BITMAP INDEX AND的代價(jià)最小。

    SQL＞ SET AUTOT OFF
    SQL＞ INSERT INTO T
      2  SELECT 100000 + ROWNUM,
      3  'T',
      4  'TABLE',
      5  LPAD('A', 2000, 'A')
      6  FROM ALL_OBJECTS;
    已創(chuàng)建69406行。
    SQL＞ COMMIT;
    提交完成。

這里模擬加載大量數(shù)據(jù)的情況。需要注意，這里加載所有的數(shù)據(jù)都是滿足NAME =‘T’和TYPE = ‘TABLE’的條件，再次執(zhí)行前面的查詢：

    SQL＞ SET AUTOT TRACE
    SQL＞ SET TIMING ON
    SQL＞ SELECT ＊
      2  FROM T
      3  WHERE NAME = 'T'
      4  AND TYPE = 'TABLE';
    已選擇69407行。
    已用時(shí)間:  00: 00: 09.41
    執(zhí)行計(jì)劃
    ----------------------------------------------------------
    Plan hash value: 4030788717
    -----------------------------------------------------------------------------------------
    | Id| Operation                         | Name      |Rows |Bytes |Cost(%CPU) | Time       |
    -----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT                  |           |    1|  2031|    2   (0)| 00:00:01   |
    | 1 |  TABLE ACCESS BY INDEX ROWID     | T         |    1|  2031|    2   (0)| 00:00:01   |
    | 2 |   BITMAP CONVERSION TO ROWIDS     |           |     |      |            |             |
    | 3 |    BITMAP AND                     |           |     |      |            |             |
    | 4 |    BITMAP CONVERSION FROM ROWIDS |           |     |      |            |             |
    |＊5 |     INDEX RANGE SCAN             |IND_T_NAME|    2|      |    1   (0)| 00:00:01   |
    | 6 |    BITMAP CONVERSION FROM ROWIDS |           |     |      |            |             |
    |＊7 |     INDEX RANGE SCAN             |IND_T_TYPE|    2|      |    1   (0)| 00:00:01   |
    -----------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      5- access("NAME"='T')
      7- access("TYPE"='TABLE')
    統(tǒng)計(jì)信息
    ----------------------------------------------------------
              1   recursive calls
              0   db block gets
          14140   consistent gets
              0   physical reads
              0   redo size
      141756898   bytes sent via SQL＊Net to client
          51416   bytes received via SQL＊Net from client
          4629   SQL＊Net roundtrips to/from client
              0   sorts (memory)
              0   sorts (disk)
          69407   rows processed
      2  SELECT 100000 + ROWNUM,

雖然加載了大量數(shù)據(jù)，使得原本高效的執(zhí)行計(jì)劃變得效率很差，但是由于統(tǒng)計(jì)信息沒(méi)有更新，CBO選擇的執(zhí)行計(jì)劃也不會(huì)發(fā)生改變，因?yàn)镃BO并不了解數(shù)據(jù)發(fā)生的變化。

如果加載數(shù)據(jù)后對(duì)表進(jìn)行重新分析，則不會(huì)導(dǎo)致這種情況的出現(xiàn)：

    SQL＞ EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
    PL/SQL過(guò)程已成功完成。
    已用時(shí)間:  00: 00: 02.07
    SQL＞ SELECT ＊
      2  FROM T
      3  WHERE NAME = 'T'
      4  AND TYPE = 'TABLE';
    已選擇69407行。
    已用時(shí)間:  00: 00: 09.15
    執(zhí)行計(jì)劃
    ----------------------------------------------------------
    Plan hash value: 1601196873
    --------------------------------------------------------------------------
    | Id  | Operation          | Name| Rows | Bytes |Cost(%CPU)  | Time       |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |      |47170 |    90M|  7451   (1) | 00:01:45   |
    |＊  1 |  TABLE ACCESS FULL| T    |47170 |    90M|  7451   (1) | 00:01:45   |
    --------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
      1- filter("TYPE"='TABLE' AND "NAME"='T')
    統(tǒng)計(jì)信息
    ----------------------------------------------------------
            1  recursive calls
            0  db block gets
        23818  consistent gets
            0  physical reads
            0  redo size
      1544905  bytes sent via SQL＊Net to client
        51416  bytes received via SQL＊Net from client
          4629  SQL＊Net roundtrips to/from client
            0  sorts (memory)
            0  sorts (disk)
        69407  rows processed

小結(jié)

收集統(tǒng)計(jì)信息后，Oracle不再選擇索引掃描，而是直接選擇全表掃描。由于這個(gè)例子本身的數(shù)據(jù)量比較小，因此索引掃描和全表掃描兩個(gè)執(zhí)行計(jì)劃執(zhí)行時(shí)間差別不大。但是對(duì)于一個(gè)幾千萬(wàn)的大表和幾百萬(wàn)的加載數(shù)據(jù)而言，二者的執(zhí)行效率就有天壤之別了。

這個(gè)問(wèn)題其實(shí)經(jīng)常會(huì)出現(xiàn)，尤其在數(shù)據(jù)庫(kù)升級(jí)或大量數(shù)據(jù)上線情況下更容易出現(xiàn)。當(dāng)大批的新數(shù)據(jù)加載到系統(tǒng)中時(shí)，所有人都認(rèn)為工作結(jié)束了，但是第二天系統(tǒng)正式上線，當(dāng)壓力剛一上來(lái)，整個(gè)系統(tǒng)都崩潰了。導(dǎo)致這些問(wèn)題的一個(gè)重要原因就是沒(méi)有把統(tǒng)計(jì)信息的收集作為數(shù)據(jù)升級(jí)的一部分。