Oracle性能优化-SQL优化(案例四)

Oracle 性能优化 -SQL 优化 ( 案例四 )

环境：

DB:Oracle 11.2.0.1.0

问题：

ERP 薪资发放节点计算时间耗时 较长，需要15 分钟左右；

问题原因：

有两个SQL 执行特别慢

第一个查询WA_CACU_DATA 的 SQL ，在 plsql 中执行特别快，返回 0 条，怀疑某些堆表被当成临时表使用，导致执行计划有问题，手动删除和锁定这些表的统计信息后查询 SQL 速度有明显提高；

第二个更新WA_CACU_DATA 的 SQL ，第一次执行快，第二次执行慢，执行计划不稳定，禁用基数反馈 (_optimizer_use_feedback) 后速度正常；

解决过程：

问题重现时，查看主要慢在两个SQL ，一个 select wa_cacu_data ... ，另一个 update  ...;

一 耗时长的查询SQL 如下

执行计划如下：

解决方案：

在plsql 中执行特别快，返回 0 条，怀疑某些堆表被当成临时表使用，导致执行计划有问题，手动删除和锁定这些表的统计信息后查询 SQL 速度有明显提高；

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ tbm_period ’ );

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ org_adminorg ’ );

SQL> exec dbms_stats.delete_table_stats( ‘ cjc ’ , ’ org_hrorg ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ tbm_period ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ org_adminorg ’ );

SQL> exec dbms_stats.lock_table_stats( ‘ cjc ’ , ’ org_hrorg ’ );

二：耗时长的update 语句

抓取完整sql 单独执行时，发现第一次执行很快，第二次执行特别慢，并且第一次和第二次生成的执行计划不一样，第二次执行计划带有“ cardinality feedback used for this statement ”，怀疑和 oracle 11g 基数反馈特性有关，导致执行计划不稳定， SQL 执行效率低。

解决方案：

session 级别禁用基数反馈后，多次手动执行 SQL ，速度稳定变快了。

alter session set "_optimizer_use_feedback"=false;

临时解决办法可以考虑系统级别禁用基数反馈，或研发更改代码，在sql 级别增加 hint 禁用基数反馈。

alter system set "_optimizer_use_feedback"=false;

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！