批量修改数据后应收集统计信息
今天帮朋友定位了一个问题，导致问题的主要原因是统计信息不准确。
简单描述一下问题，朋友的数据库中错误的导入了几百万的记录，需要通过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不在选择索引扫描，而是选择全表扫描。这个例子的数据量比较小，因此两个执行计划之间的差别不大，对于一个几千万的大表而言，二者的差别就十分惊人了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-663590/ ，如需转载，请注明出处，否则将追究法律责任。
