基于Oracle的SQL优化典型案例分

《基于Oracle的SQL优化典型案例分》由会员上传分享，免费在线阅读，更多相关内容在学术论文-天天文库。

1、基于Oracle的SQL优化典型案例分析崔华（dbsnake）@dbsnake关于我•中航信工程师•OracleACE•ACOUG成员Oracle里SQL优化的方法论•Oracle里的SQL优化实际上是基于对CBO和执行计划的深刻理解•Oracle里的SQL优化不能脱离实际的业务•Oracle里SQL优化需要适时使用绑定变量优化器模式对计算成本带来巨大影响的实例•CBO认为全表扫描一个700多万数据量的大表的成本值仅为2每次登陆的平均等待时间95.67秒初步分析•上述SQL好的和不好的执行计划所对应

2、的成本值的过于接近就是导致上述坐席登陆慢的问题多次不间断出现的原因。事情没有这么简单•SQL优化最有技术含量的部分不在于你通过种种手段（比如重新收集统计信息等）调整了目标SQL的执行计划、缩短了其执行时间、解决了该SQL的性能问题，而是在于你要知道CBO为什么在一开始会选错执行计划，你要知道CBO选错执行计划的根本原因深入分析•导致CBO评估出对一个实际数据量为730多万且统计信息准确的大表S_EVT_ACT执行全表扫描操作后的成本值仅为2的原因是因为参数OPTIMIZER_MODE的值在sessi

3、on级别被修改成了FIRST_ROWS_10，这同时也是导致上述坐席登陆慢的问题多次不间断出现的根本原因。解决方法•修改各个session中对于参数OPTIMIZER_MODE的设置，将其值修改为默认值ALL_ROWS•如果不能在session级修改参数OPTIMIZER_MODE的值，我们还可以使用SQLProfile。在上述18个表关联SQL中加入Hint（即/*+index(T18S_EVT_ACT_P1)*/），并用加入Hint后改写SQL的执行计划替换原SQL的执行计划查询转换的综合应用实

4、例（逻辑读从200万降到6）•某系统某个模块响应速度缓慢，客户方DBA已经从AWR报告的TOPSQL中定位和确认了导致上述模块响应速度缓慢的SQL。现需要对该SQL进行分析和调优，以提高响应速度，减轻系统压力。初步分析•上述SQL包含了IN，而IN之后的括号内是一个包含视图的子查询（即selectgrppolnofromv_bc_lcpolwherepolno=‘9022000000000388’），它不是一个常量的集合，所以Oracle这里不能对该SQL做“IN-ListIterator”和“IN

5、-ListExpansion/ORExpansion”；•上述SQL中的视图V_BC_LCGRPPOL和V_BC_LCPOL均包含了集合运算符UNIONALL，所以Oracle这里也不能对该SQL做视图合并；•于是Oracle现在就只剩下了两条路可走：要么对该SQL走FILTER类型的执行计划（即“IN-ListFilter”），要么对该SQL做子查询展开。初步分析•Oracle这里选择的是走FILTER类型的执行计划，既然是选择走FILTER类型的执行计划，同时上述SQL的where条件中除了“g

6、rppolnoin(selectgrppolnofromv_bc_lcpolwherepolno='9022000000000388')”之外再没有其他的限制条件，那么Oracle必然就会先执行“select*fromv_bc_lcgrppol”，这当然会全表扫描视图V_BC_LCGRPPOL的基表LCGRPPOL和LBGRPPOL深入分析•对于不拆开子查询但是会把它转换为一个内嵌视图（InlineView）的子查询展开，只有当经过子查询展开后的等价改写SQL的成本值小于原SQL的成本值时，Orac

7、le才会对目标SQL执行子查询展开。所以这里CBO为什么没有选择走子查询展开的原因要么是因为经过子查询展开后的等价改写SQL的成本值大于原SQL的成本值，要么是因为CBO的Bug。事情没有这么简单•在上述SQL中加入UNNESTHint后虽然可以强制让Oracle做子查询展开，但此时子查询展开后并不一定会走我们想要的嵌套循环连接，而且即使走嵌套循环连接，驱动结果集也不一定就是我们想要的根据主键PK_LCPOL和PK_LBPOL去分别访问表LCPOL和表LBPOL后再做UNIONALL操作后得到的结果

8、集。事情没有这么简单•为了能更精细的控制上述SQL的执行计划，这里我们选择了直接使用子查询展开后等价改写的形式，这样一旦走不出我们想要的执行计划，我们还可以使用额外的Hint（诸如ORDERD、USE_NL等）来继续对其执行计划做调整。解决方法解决方法（续）•至此我们就圆满的解决了上述问题，从这个例子的解决过程我们可以看出，虽然最后的解决方法很简单，但这其实完全倚赖于我们对Oracle如何处理SQL语句中的IN、子查询展开、视图合并和连接谓词推入的深刻理解总结•兵无常