数据库性能问题总结--屡次发生的Oracle谓词越界

数据库性能问题总结--屡次发生的***谓词越界

近期在客户现场屡次遇到由于统计信息过旧，导致执行计划选错引发的数据库性能问题，今天做个总结。

谓词越界常见发生在 where 谓词是时间字段的情况，总的来说统计信息记录的是一个过旧的时间，而 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

谓词越界主要发生在大表，按照 *** 统计信息收集机制，表的数据变化量达到 10% 以上才会进行统计信息收集，大表不常收集统计信息就容易爆发谓词越界。

预防方式

可对关键表实行按谓词查询条件分区，即按天或者按月分区可规避此问题发生。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。