一个执行计划异常变更的案例 – 外传之SQL Profile(下)

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之SQL Profile(下)

之前的几篇文章:
《一个执行计划异常变更的案例 - 前传》
《一个执行计划异常变更的案例 - 外传之绑定变量窥探》
《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》
《一个执行计划异常变更的案例 - 外传之rolling invalidation》
《一个执行计划异常变更的案例 - 外传之聚簇因子(Clustering Factor)》
《一个执行计划异常变更的案例 - 外传之查询执行计划的几种方法》
《一个执行计划异常变更的案例 - 外传之AWR》
《一个执行计划异常变更的案例 - 外传之ASH》
《一个执行计划异常变更的案例 - 外传之SQL AWR》
《一个执行计划异常变更的案例 - 外传之直方图》
《一个执行计划异常变更的案例 - 外传之SQL Profile(上)》

上篇文章介绍了Automatic类型的SQL Profile,这种类型的SQL Profile隐患就是未锁定执行计划,只是对统计信息进行了一些修正,一旦表统计信息出现了一些波动,就可能出现错误的修正。

为了解决这种问题就可以尝试Manual类型的SQL Profile,我们来看下他是如何不变更原文的情况下,调整执行计划,并做到可以稳定执行计划的目的。

为了创建Manual类型的SQL Profile,我们需要使用MOS(All About the SQLT Diagnostic Tool (文档 ID 215187.1))中可下载的一个脚本coe_xfr_sql_profile.sql。

我们依旧采用上篇文章中使用的测试表t1和t2,数据量、索引和统计信息收集均相同。使用如下SQL执行计划不是最优的,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

通过上篇文章的分析,我们知道这才是最优的执行计划,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

首先查询这两条SQL对应的sql_id,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

查询这两个sql_id对应的plan_hash_value,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

执行coe_xfr_sql_profile.sql脚本,输入参数为上面第一次执行的SQL语句(即需要优化的)对应的sql_id和plan_hash_value,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

输出结果中含有一个脚本,命名格式就是“coe_xfr_sql_profile_(sql_id)_(plan_hash_value).sql,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

打开脚本可以看见其注释,说明他可以创建一个自定义的SQL Profile,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

接着我们对使用正确执行计划的SQL执行脚本,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

同样生成了一个脚本,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

我们用正确的执行计划对应的脚本中HINT部分,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

替换错误执行计划对应的脚本中HINT部分,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

同时将下面这个参数force_match的默认值FALSE改为TRUE,意思是针对不同文本值的SQL,可以重用此SQL Profile,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

然后执行此脚本,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

此时就创建了一个Manual类型的SQL Profile。

我们看下效果,重新执行SQL,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

可以看出执行计划已经是最优的,而且Note部分说明已经使用了SQL Profile。

此时我们再次将t1表优化器认知的数量改为500万,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

Automatic类型的SQL Profile此时就会由于缩放错误,再次选择错误的执行计划,我们看下这种Manual类型的SQL Profile,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

看出仍旧使用的正确执行计划,证明了这种类型的SQL Profile是可以锁定正确执行计划。

上面我们将force_match参数值设为了TRUE,看下有什么作用,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

我们将%ABC%换为了%ZZZ%,仍旧采用了正确的执行计划。

当然,如果SQL语句变了,意味着上述手工创建的SQL Profile就不能用了,除非再次创建对应的SQL Profile,
一个执行计划异常变更的案例 - 外传之SQL Profile(下)

总结:
和上篇文章介绍的Automatic类型的SQL Profile相比,Manual类型的SQL Profile的创建过程要复杂一些,但其可以不改SQL的前提下,调整执行计划,最重要的是他能稳定执行计划,不会因为统计信息波动等问题,导致选择错误的执行计划,对于一些短期内不能改应用调整SQL的场景,我们可以选择合适的SQL Profile类型进行执行计划的调整操作。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:) 一个执行计划异常变更的案例 - 外传之SQL Profile(下)

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一个执行计划异常变更的案例 – 外传之SQL Profile(下)


 上一篇
一个执行计划异常变更的案例 – 外传之SQL Profile(上) 一个执行计划异常变更的案例 – 外传之SQL Profile(上)
之前的几篇文章:《一个执行计划异常变更的案例 - 前传》《一个执行计划异常变更的案例 - 外传之绑定变量窥探》《一个执行计划异常变更的案例 - 外传之查看绑定变量值的几种方法》《一个执行计划异常变更的案例 - 外传之rolling inva
下一篇 
select count(*)、count(1)、count(主键列)和count(包含空值的列)有何区别? select count(*)、count(1)、count(主键列)和count(包含空值的列)有何区别?
下班路上看见网上有人问一个问题: oracle 10g以后count(*)和count(非空列)性能方面有什么区别? 乍一看,确实有些含糊,Oracle中往往小问题蕴含着大智慧,如何破云见日? 最直接的方法,我想就是通过10