批量修改資料後應收集統計資訊

今天幫朋友定位了一個問題，導致問題的主要原因是統計資訊不準確。

簡單描述一下問題，朋友的資料庫中錯誤的匯入了幾百萬的記錄，需要通過DELETE操作刪除這些資料，但是發現即使是查詢都異常緩慢，更不要說刪除了。

等上去檢查後發現，Oracle給出了一個非常低效的執行計劃，將兩個BTREE索引轉化為BITMAP索引，然後進行BITMAP AND操作，再將得到的BITMAP索引轉化為BTREE索引，然後根據索引對錶進行掃描。

通過INDEX_JOIN方式執行COUNT(*)操作，發現滿足查詢條件的記錄超過了400萬。顯然利用這個執行計劃，效率是十分低下的。

導致問題的主要原因就是由於在匯入大量的資料後，沒有重新收集統計資訊，Oracle仍然使用舊的統計資訊，因此認為這種執行計劃會很高效，而實際上匯入的400萬記錄都是滿足兩個索引欄位查詢條件的。

文字描述比較抽象，下面通過一個例子來簡單描述這個問題：

SQL> CREATE TABLE T (
  2  ID NUMBER,
  3  NAME VARCHAR2(30),
  4  TYPE VARCHAR2(30),
  5  OTHER VARCHAR2(4000));

表已建立。

SQL> CREATE INDEX IND_T_NAME
  2  ON T(NAME);

索引已建立。

SQL> CREATE INDEX IND_T_TYPE
  2  ON T(TYPE);

索引已建立。

SQL> INSERT INTO T
  2  SELECT ROWNUM,           
  3  OBJECT_NAME,
  4  OBJECT_TYPE,
  5  LPAD('A', 2000, 'A')
  6  FROM ALL_OBJECTS;

已建立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 過程已成功完成。

構造了一張大表，前面的UPDATE語句是為了一會的查詢採用BITMAP INDEX AND執行計劃，而不是單獨掃描其中一個索引：

SQL> SET AUTOT TRACE
SQL> SELECT *
  2  FROM T
  3  WHERE NAME = 'T'
  4  AND TYPE = 'TABLE';

執行計劃
----------------------------------------------------------
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')

統計資訊
----------------------------------------------------------
          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

可以看到，更加當前的統計資訊，採用BITMAP INDEX AND是很高效的，因為這時只返回1條記錄。

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;

已建立69406行。

SQL> COMMIT;

提交完成。

載入了大量的資料都是滿足NAME = ‘T’和TYPE = ‘TABLE’的條件。

再次執行查詢：

SQL> SET AUTOT TRACE
SQL> SET TIMING ON
SQL> SELECT *
  2  FROM T
  3  WHERE NAME = 'T'
  4  AND TYPE = 'TABLE';

已選擇69407行。

已用時間:  00: 00: 09.41

執行計劃
----------------------------------------------------------
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')

統計資訊
----------------------------------------------------------
          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

由於統計資訊沒有更新，Oracle仍然選擇了轉化BITMAP索引的執行計劃，因為Oracle並不知道資料發生的變化，而如果載入資料後對錶進行分析，則不會導致這種情況的出現：

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 過程已成功完成。

已用時間:  00: 00: 02.07
SQL> SELECT *
  2  FROM T
  3  WHERE NAME = 'T'
  4  AND TYPE = 'TABLE';

已選擇69407行。

已用時間:  00: 00: 09.15

執行計劃
----------------------------------------------------------
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')

統計資訊
----------------------------------------------------------
          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

收集統計資訊後，Oracle不在選擇索引掃描，而是選擇全表掃描。這個例子的資料量比較小，因此兩個執行計劃之間的差別不大，對於一個幾千萬的大表而言，二者的差別就十分驚人了。