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

沙海
沙海
沙海
1017
文章
2
评论
2021年4月12日01:26:40
评论
3 5440字阅读18分8秒
摘要

速读摘要

速读摘要

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

原文约 1743 | 图片 11 | 建议阅读 4 分钟 | 评价反馈

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

原创 任艳杰 数据和云

点击上方"蓝字"

关注我们,享更多干货!

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

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

谓词越界就是 select 的谓词的条件不在统计信息 low_value 和 high_value 之间,在实际选择结果集要大于 CBO 记录的结果集数量,即实际的 selectivity 偏大,这种情况下 CBO 评估出来的 selectivity 会出现严重的偏差,导致 CBO 选错执行计划。

测试验证

下面做一组测试,从执行计划 cost 看谓词越界的发生过程,先插入部分数据:

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

查看此时的 num_rows:

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。

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 行结果集。

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 低估了查询成本。

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     NumberLOW_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% 以上才会进行统计信息收集,大表不常收集统计信息就容易爆发谓词越界。

预防方式

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

关于作者

任艳杰,云和恩墨Oracle技术顾问,长期致力于大型Oracle数据库维护工作,具备扎实的理论基础和丰富的实践经验,擅长数据恢复、性能优化、数据迁移等

墨天轮原文链接:https://www.modb.pro/db/44787(复制到浏览器或者点击“阅读原文”立即查看)

END

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

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

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

长按二维码 热招职位一键投递

长按二维码 热招职位一键投递

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

MySQL/PG/Oracle DBA

数据库专家(售前)、销售总监/经理

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

200+热招职位 覆盖全国40+所城市 

由ACDU(中国DBA联盟)和墨天轮联合出品的全新视频节目「数据三分钟」已发布多期,快速了解数据行业动态,快关注我们的视频号看看吧!↓↓↓

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

点击下图查看更多 ↓

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

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

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

云和恩墨大讲堂 | 一个分享交流的地方

长按,识别二维码,加入万人交流社群

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

请备注:云和恩墨大讲堂

  点个“在看” 

你的喜欢会被看到❤

阅读原文

继续阅读
weinxin
资源分享QQ群
本站是一个IT技术分享社区, 会经常分享资源和教程; 分享的时代, 请别再沉默!
沙海
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: