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

沙海

3185
文章

2
评论

2021年4月12日01:26:40杂谈 Java评论20字数 5440阅读18分8秒阅读模式

摘要

速读摘要

速读摘要文章源自JAVA秀-https://www.javaxiu.com/10993.html

近期在客户现场屡次遇到由于统计信息过旧，导致执行计划选错引发的数据库性能问题，今天做个总结。就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，有大量结果集需要扫描，就会爆发SQL性能问题。不收集统计信息，此时统计列统计信息过旧，HIGH_VALUE依然是原来的值78179。查看结果集发现dictionary值为1，这明显是一个错误的执行计划，由于统计信息过旧，已经低于谓词条件区间(谓词过界)导致CBO低估了查询成本。文章源自JAVA秀-https://www.javaxiu.com/10993.html

原文约 1743 字 | 图片 11 张 | 建议阅读 4 分钟 | 评价反馈文章源自JAVA秀-https://www.javaxiu.com/10993.html

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

原创任艳杰数据和云文章源自JAVA秀-https://www.javaxiu.com/10993.html

点击上方"蓝字"文章源自JAVA秀-https://www.javaxiu.com/10993.html

关注我们，享更多干货！文章源自JAVA秀-https://www.javaxiu.com/10993.html

近期在客户现场屡次遇到由于统计信息过旧，导致执行计划选错引发的数据库性能问题，今天做个总结。文章源自JAVA秀-https://www.javaxiu.com/10993.html

谓词越界常见发生在 where 谓词是时间字段的情况，总的来说统计信息记录的是一个过旧的时间，而 SQL 传入的时间是一个最新的时间范围（往往是 <time time1<c<time2）。由于统计信息不全，按照 CBO 计算出来的结果集就很小，在多表关联的情况下，CBO 就会选择认为的最优的关联方式，而实际执行时发现不是那么回事，有大量结果集需要扫描，就会爆发 SQL 性能问题。文章源自JAVA秀-https://www.javaxiu.com/10993.html

谓词越界就是 select 的谓词的条件不在统计信息 low_value 和 high_value 之间，在实际选择结果集要大于 CBO 记录的结果集数量，即实际的 selectivity 偏大，这种情况下 CBO 评估出来的 selectivity 会出现严重的偏差，导致 CBO 选错执行计划。文章源自JAVA秀-https://www.javaxiu.com/10993.html

测试验证

下面做一组测试，从执行计划 cost 看谓词越界的发生过程，先插入部分数据：文章源自JAVA秀-https://www.javaxiu.com/10993.html

DECLAREi INT;BEGINi := 78179;WHILE(i < 100000)LOOPi := i + 1;INSERT INTO test_obj(object_id) VALUES(i);COMMIT;END LOOP;END;/

查看此时的 num_rows：文章源自JAVA秀-https://www.javaxiu.com/10993.html

TEST@PROD1> select count(*) from test_obj;  COUNT(*)----------     94283TEST@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。文章源自JAVA秀-https://www.javaxiu.com/10993.html

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     NumberLOW_VALUE                      HIGH_VALUE                           Values      Nulls------------------------------ ------------------------------ ------------ ----------C103                           C3085250                             72,462（原值）  0

查询结果返回 2081 行结果集。文章源自JAVA秀-https://www.javaxiu.com/10993.html

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_adjustnull_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 低估了查询成本。文章源自JAVA秀-https://www.javaxiu.com/10993.html

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

重新收集统计信息再次查看执行计划。文章源自JAVA秀-https://www.javaxiu.com/10993.html

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     NumberLOW_VALUE            HIGH_VALUE                 Values      Nulls-------------------- -------------------- ------------ ----------C103                 C30B                       94,283          0

此时统计信息 HIGH_VALUE 已经和最初计算的值相等，Typ=2 Len=2: c3,b。再次查看执行计划，此时 CBO 已经能够产生了正确的执行计划了。文章源自JAVA秀-https://www.javaxiu.com/10993.html

执行计划为：文章源自JAVA秀-https://www.javaxiu.com/10993.html

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% 以上才会进行统计信息收集，大表不常收集统计信息就容易爆发谓词越界。文章源自JAVA秀-https://www.javaxiu.com/10993.html