批量修改数据后应收集统计信息
一次帮朋友诊断一个问题，最终发现导致问题的主要原因是统计信息不准确。下面是详细诊断过程。
问题分析
出现问题的数据库中错误地加载了几百万条记录。发现加载错误后，尝试通过DELETE语句删除这些数据，但是这时发现了问题，这些数据的查询变得异常缓慢，更不要说DELETE操作了。
对数据库进行检查后发现，CBO给出了一个非常低效的执行计划：将两个BTREE索引转化为BITMAP索引，然后执行BITMAP AND操作，再将得到的BITMAP索引转化为BTREE索引，最后根据索引的ROWID对表进行扫描。
首先需要了解错误加载的数据量，但是CBO给出的这个执行计划在短时间内根本无法得到结果，于是通过提示INDEX_JOIN将执行计划改为索引连接方式，最终得到了COUNT(*)的结果，发现错误加载的数据超过了400万。对于这种数据量而言，通过索引扫描获得本身就是个灾难，更不要说还要进行BITMAP和BTREE之间的两次转换了。
导致执行计划出错的主要原因就是统计信息不准确，不过检查表和索引的上次分析时间，发现是昨天夜里才分析过的。进一步询问后发现，错误的数据加载发生在今天，而查询性能下降是从错误的数据导入后开始的。
之后找到了导致问题的原因：在导入大量数据后没有重新收集统计信息，CBO根据加载数据前的统计信息进行判断，因此得到了这种“高效”的执行计划。事实也确实如此，因为在加载数据前满足这个查询的记录是0条，而加载数据后满足这个查询的结果超过了400万条。导致问题产生的并不是CBO的算法，而是加载数据后没有同步更新统计信息。
测试案例
上面的问题分析起来比较抽象，下面通过一个例子来模拟再现这个问题：
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执行计划。如果没有进行更新，则NAME列等于‘T’这个条件的选择性很高，CBO会选择NAME列上的索引扫描，而不会选择两个索引进行BITMAP 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
根据现在的统计信息，CBO认为采用BITMAP INDEX AND的代价最小。
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
2 SELECT 100000 + ROWNUM,
虽然加载了大量数据，使得原本高效的执行计划变得效率很差，但是由于统计信息没有更新，CBO选择的执行计划也不会发生改变，因为CBO并不了解数据发生的变化。
如果加载数据后对表进行重新分析，则不会导致这种情况的出现：
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不再选择索引扫描，而是直接选择全表扫描。由于这个例子本身的数据量比较小，因此索引扫描和全表扫描两个执行计划执行时间差别不大。但是对于一个几千万的大表和几百万的加载数据而言，二者的执行效率就有天壤之别了。
这个问题其实经常会出现，尤其在数据库升级或大量数据上线情况下更容易出现。当大批的新数据加载到系统中时，所有人都认为工作结束了，但是第二天系统正式上线，当压力刚一上来，整个系统都崩溃了。导致这些问题的一个重要原因就是没有把统计信息的收集作为数据升级的一部分。