网上做家教兼职哪个网站,数字营销推广平台,超变传奇网站,百度六安分公司#xff1a;数据科学、人工智能从业者的在线大学。数据科学(Python/R/Julia)数据分析、机器学习、深度学习作者简介戴秋龙#xff0c;拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化#xff0c;优化经验#xff0c;具备丰富的行业服务背景。对Oracle数据库有…数据科学、人工智能从业者的在线大学。数据科学(Python/R/Julia)数据分析、机器学习、深度学习作者简介戴秋龙拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化优化经验具备丰富的行业服务背景。对Oracle数据库有深刻的理解擅长数据库故障诊断数据库性能调优。背景客户某SQL逻辑读比较高。需要优化。也给出了AWR报告AWR报告中主要几个SQL都是类似的问题。SQL_ID: g4nbv7twn23fw, 成本3000 逻辑读/次 40万次/hSELECT * FROM (SELECT XX.*, ROWNUM AS RN FROM (select count(*) from PARTY_CERT P inner join CUSTOMER C on P.PARTY_ID C.PARTY_ID and C.STATUS_CD 1100 where P.PARTY_ID in (:1 ) and P.STATUS_CD in (:2 ) and P.IS_DEFAULT 1) XX WHERE ROWNUM 1000 ) XXX WHERE RN 0分析查询出绑定变量的值带入SQL发现只有9个逻辑读。与AWR报告不符合可能有读者认为性能问题在ID5笛卡尔积问题但从事后看问题不在这里。此时陷入僵局。但ASH视图中或许能给出线索。通过ash分析更多的性能消耗在执行计划的第9步。也就在C表(CUSTOMER)的回表上。SQL中得出C表用到两个字段 C.PARTY_ID,C.STATUS_CD。PARTY_ID上建有索引回表就是为了访问STATUS_CD字段。因此建议建立索引index C ( PARTY_ID, STATUS_CD ); 这样可以避免回表。针对该SQL的优化建议是建立索引。实施组建立索引后从后期多份AWR报告中该SQL平均330逻辑读/次。思考能否继续优化未优化之前带入绑定变量9逻辑读但AWR报告中平均3000逻辑读。结合起来看是否是数据分布不均衡导致呢排查中发现C表 PARTY_ID字段的选择性 98%结合绑定变量继续排查。如图就是一个值在表中有10万其他值在表中只有1条。当PARTY_ID 15151723602037回表需要回10万次。把该值带入SQL中。逻辑读7770/次。是它把平均逻辑读拉到3000.针对该问题上文已经有相关建议。那能否进一步优化?探讨以下探讨在没有建立新索引的基础上既然数据分布不均衡是否可以通过收集直方图来改善性能答案是否定的。做好测试环境。( 建立测试表CUSTOMER_test。导入全部数据建立相关索引收集直方图 ) 执行SQLSQL效率更差15万逻辑读/次回到SQL中。分析SQLSQL只是需要count(1),统计类型的可以考虑用半连接需要和业务确认是否可以改成半连接。( 此处不讨论业务只讨论这种数据分布情况下如何优化 )因为针对数据分布不均衡半连接效果比较好。改写SQL( 带入数据最多的值 )SELECT * FROM (SELECT XX.*, ROWNUM AS RN FROM (select count(1)from CUST_YC_APP.PARTY_CERT P where P.PARTY_ID in (15151723602037)And P.PARTY_ID in( select C.PARTY_ID from CUSTOMER_test Cwhere C.STATUS_CD 1100 ) and P.STATUS_CD in (1000)and P.IS_DEFAULT 1) XX WHERE ROWNUM 1000) XXX WHERE RN 0;改成in后不添加hints就会走全表1286逻辑读/S添加hint/* nl_sj index(c) */9逻辑读/次SQL无法自动走最佳的执行计划需要绑定hints才走。如何自动用最佳执行计划呢删除直方图。删除直方图后P.PARTY_ID in (15151723602037)的数据量虽然很多但CBO评估该数据量1条直接走了hash join ( 有时候也会结合 C.PARTY_ID P.PARTY_ID评估出 C.PARTY_ID 15151723602037 也是1条直接走笛卡尔积关联类似开头的问题)。而不是最好的执行计划。收集直方图会走索引删除直方图会走hash/笛卡尔积关联.就是得不到半连接似乎陷入了困境。设置数据选择性。帮助CBO评估 P表返回的数据其对精确度要求也不高甚至只要多评估几条让CBO倾向选择走半连接即可。DBMS_STATS.set_column_stats(colname PARTY_ID,distcnt 1645919);1645919 大约数据总量的30%测试SQL看SQL是不是直接选择最好的执行计划。执行计划果然是nested_loop seml 关联并且走索引。就是目前探讨的最好的执行计划。9逻辑读/次总结分析并且优化该SQL有注意的地方有6点笛卡尔积关联并不是性能瓶颈。数据特殊分布数据集中在某个值这个值带来严重的索引再回表。结合数据分布把SQL改成半连接形式成本明显减少。由于特殊分布收集直方图当测试特殊分布的值时候会带来大表全表扫描不收集直方图会带来hash join 不是我们想要得到的 nested_loop seml。设置统计信息既能固定走索引扫描(无论此表中数据情况都是索引扫描效率最高)又能满足最好的关联方式nested_loop seml。最终的实施优化方案采用最简单直接的方案而不是我们文中探究的改SQL设置统计信息等。而且最终效果还不错。
