本文转载自微信公众号「数据和云」，作者任艳杰。转载本文请联系数据和云公众号。
近期在客户现场屡次遇到由于统计信息过旧，导致执行计划选错引发的数据库性能问题，今天做个总结。
谓词越界常见发生在 where 谓词是时间字段的情况，总的来说统计信息记录的是一个过旧的时间，而 SQL 传入的时间是一个最新的时间范围(往往是 <time time1<c<time2）由于统计信息不全，按照 CBO 计算出来的结果集就很小，在多表关联的情况下，CBO 就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，有大量结果集需要扫描，就会爆发 SQL 性能问题。
谓词越界就是 select 的谓词的条件不在统计信息 low_value 和 high_value 之间，在实际选择结果集要大于 CBO 记录的结果集数量，即实际的 selectivity 偏大，这种情况下 CBO 评估出来的 selectivity 会出现严重的偏差，导致 CBO 选错执行计划。
测试验证
下面做一组测试，从执行计划 cost 看谓词越界的发生过程，先插入部分数据：
- DECLARE
- i INT;
- BEGIN
- i := 78179;
- WHILE(i < 100000)
- LOOP
- i := i + 1;
- INSERT INTO test_obj(object_id) VALUES(i);
- COMMIT;
- END LOOP;
- END;
- /
查看此时的 num_rows：
- TEST@PROD1> select count(*) from test_obj;
- COUNT(*)
- ----------
- 94283
- TEST@PROD1> select max(object_ID),dump(max(object_id),16) from test_obj;
- MAX(OBJECT_ID) DUMP(MAX(OBJECT_ID),16)
- -------------- ----------------------------------------
- 100000 Typ=2 Len=2: c3,b
- TEST@PROD1> select min(object_ID),dump(min(object_id),16) from test_obj;
- MIN(OBJECT_ID ) DUMP(MIN(OBJECT_ID),16)
- ------------------------------ ----------------------------------------
- 2 Typ=2 Len=2: c1,3 --C103
不收集统计信息，此时统计列统计信息过旧，HIGH_VALUE 依然是原来的值 78179。
- TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST';
- Distinct Number
- LOW_VALUE HIGH_VALUE Values Nulls
- ------------------------------ ------------------------------ ------------ ----------
- C103 C3085250 72,462（原值） 0
查询结果返回 2081 行结果集。
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- COUNT(*)
- ----------
- 2801
- 计算公式为：
- selectivity=((VAL2 - VAL1) / (HIGH_VALUE - LOW_VALUE)+2 / NUM_DISTINCT) * null_adjust
- null_adjust=(NUM_ROES - NUM_NULLS) / NUM_ROES
- 计算结果为：
- TEST@PROD1> select round(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283) from dual;
- ROUND(((81000-78200)/(100000-2)+2/94283)*(94283-0)/94283*94283)
- ---------------------------------------------------------------
- 2642
查看结果集发现 dictionary 值为 1，这明显是一个错误的执行计划，由于统计信息过旧，已经低于谓词条件区间(谓词过界)导致 CBO 低估了查询成本。
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2217143630
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 289 (1)| 00:00:04 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | TABLE ACCESS FULL| TEST_OBJ | 1 | 5 | 289 (1)| 00:00:04 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("OBJECT_ID">=78200 AND "OBJECT_ID"<=81000)
- Statistics
- ----------------------------------------------------------
- 1 recursive calls
- 0 db block gets
- 1117 consistent gets
- 0 physical reads
- 0 redo size
- 423 bytes sent via SQL*Net to client
- 419 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
重新收集统计信息再次查看执行计划。
- TEST@PROD1> exec dbms_stats.gather_table_stats('test','test_obj');
- TEST@PROD1> select low_value ,high_value,num_distinct,num_nulls from DBA_TAB_COL_STATISTICS where table_name='TEST_OBJ' and owner='TEST';
- Distinct Number
- LOW_VALUE HIGH_VALUE Values Nulls
- -------------------- -------------------- ------------ ----------
- C103 C30B 94,283 0
此时统计信息 HIGH_VALUE 已经和最初计算的值相等，Typ=2 Len=2: c3,b。再次查看执行计划，此时 CBO 已经能够产生了正确的执行计划了。
执行计划为：
- TEST@PROD1> select count(*) from test_obj where object_id between 78200 and 81000;
- Execution Plan
- ----------------------------------------------------------
- Plan hash value: 2217143630
- -------------------------------------------------------------------------------
- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
- -------------------------------------------------------------------------------
- | 0 | SELECT STATEMENT | | 1 | 5 | 314 (1)| 00:00:04 |
- | 1 | SORT AGGREGATE | | 1 | 5 | | |
- |* 2 | TABLE ACCESS FULL| TEST_OBJ | 2642 | 13210 | 314 (1)| 00:00:04 |
- -------------------------------------------------------------------------------
- Predicate Information (identified by operation id):
- ---------------------------------------------------
- 2 - filter("OBJECT_ID">=78200 AND "OBJECT_ID"<=81000)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 1117 consistent gets
- 0 physical reads
- 0 redo size
- 423 bytes sent via SQL*Net to client
- 419 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
谓词越界主要发生在大表，按照 Oracle 统计信息收集机制，表的数据变化量达到 10% 以上才会进行统计信息收集，大表不常收集统计信息就容易爆发谓词越界。
预防方式
可对关键表实行按谓词查询条件分区，即按天或者按月分区可规避此问题发生。
