使用SQL tuning advisor(STA)自动优化SQL

使用SQL tuning advisor(STA)自动优化SQL

Oracle 10g之后的优化器支持两种模式，一个是normal模式，一个是tuning模式。在大多数情况下，优化器处于normal模式。基于CBO的normal模式只考虑很小部分的执行计划集合用于选择哪个执行计划，因为它需要在尽可能短的时间，通常是几秒或毫秒级来对当前的SQL语句进行解析并生成执行计划。因此并不能保证SQL语句每次都是使用最佳的执行计划。而tuning模式则将高负载的SQL语句直接扔给优化器，优化器来自动对其进行详细的分析，调试并给出建议，这就是Oracle 提供的Automatic Tuning Optimizer，即自动调整优化器。Oracle 自动调整优化器通过SQL调优建议器(SQL tuning advisor)来体现。 

1、SQL tuning的基本步骤
a、鉴别需要调整的高负载SQL或者Top SQL
b、寻找可改进的执行计划
c、实施能够改进的执行计划以提高SQL效率

2、如何tuning SQL
a、检查是否为优化器设置了合理的参数(optimizer_mode,optimizer_index_caching,optimizer_index_cost_adj,以及相关cache size)
b、检查SQL语句所涉及的对象是否存在过时的统计信息或者倾斜列是否缺少直方图等
c、通过添加提示来引导SQL语句使用正确的访问路径，以及连接方式等
d、重构等价的SQL语句以使得SQL更高效(如最小化基表及中间结果集，避免列运算，列上的函数，null值，不等运算使得索引失效)
e、添加合理的索引或物化视图以及移除冗余索引,分散I/O等

3、Automatic Tuning Optimizer 做什么?
a、分析统计信息
优化器执行计划产生期间记录当前SQL语句涉及对象的统计信息的类型以及哪些被使用或哪些是需要的
当统计信息记录完成后自动调整优化器会比对与查询相关的这些对象的统计信息是否可用或过时或非均衡列缺少直方图等
针对上述的操作之后得到哪些对象没有统计信息以及哪些对象缺少统计信息以及额外的统计信息用于生成report
b、分析访问路径
优化器会分析当前SQL所使用的访问路径是否合理，也就是分析基于表的访问方式，如全表扫描，索引扫描等
自动调整优化器会基于谓词尝试假设性的推断来创建合理的索引，也就是建议通过添加或修改相应的索引来提高性能
c、SQL结构分析
优化器会建议对于一些具有较大影响的SQL语句作结构性调整及转换(基于内部规则)，如未嵌套的子查询，重写物化视图，视图合并等
基于语法以及语义结构的分析与调整，如谓词列上的运算，UNION与UNION ALL的使用，NOT IN, NOT EXIST之间替换等
对中间结果集以及连接方式等实现一些预估的分析
d、SQL profiling
SQL profiling 内置于优化器，就是一个剖析工具，基于上述得到的信息对当前的SQL进行剖析，以检查出导致性能糟糕的故障点
所有上述分析得到的结果以及辅助信息最后以sql profile的形式表现出来，供用户来判断是否接受
当用户接受这些profile，下次处于normal模式时，相同的sql语句会使用这个profile
可以对profile进行启用，停用，以及修改，因此即使表发生较大的变化，profile依旧能使得SQL受益

4、Automatic Tuning Optimizer与SQL tuning advisor结构图