Oracle SQL运行时间
最主要组成部分是花在为执行预备新SQL语句上时间
不过
假如了解了可执行计划产生内在机制你就可以控制Oracle花费在评估表连接顺序时间并在总体上提高查询性能预备为执行提供
SQL语句在
个SQL语句进入Oracle库cache的后、而真正被执行的前将会依次发生如下事件:语法检查——检查该SQL语句
拼写和词序是否正确 语义解析——Oracle根据数据词典(data dictionary)来验证所有
表格(table)和列(column) 已保存纲要检查——Oracle检查词典以确认对应该SQL语句是否已存在已保存
纲要(Stored Outline) 产生执行计划——Oracle根据
种罚值(cost-based)优化算法和数据词典中统计数据来决定如何生成最优执行计划 产生 2进制代码——Oracle在执行计划
基础上生成可执行 2进制代码 旦开始预备执行SQL语句上述过程很快就会执行这是
Oracle可以识别出同样SQL语句并对同样SQL语句重复使用对应可执行代码然而对产生ad hoc SQL系统以及SQL中嵌入文本值(literal value)情况SQL执行计划生成时间就会变得相当长而且以前执行计划也经常不能被再次利用对那些牵涉到许多表格查询Oracle可能要花上很长时间来决定把连接这些表格顺序评估连接表格
顺序生成可执行计划
时间往往是SQL预备过程中最大开销组成部分尤其是在处理有多个表连接查询情况下当Oracle评估表连接顺序时它必须考虑每种可能排序例如当有 6个表格需要连接时Oracle需要考虑720种(6排列数即6×5×4×3×2×1＝720)可能连接排序当需要连接表数量超过10时这个排列问题将变得非常突出:假如需要连接表格有15个那么需要考虑可能查询排列顺序超过万亿种(精确值为1,307,674,368,000)在optimizer_search_limit参数中设置限制
你可以通过optimizer_search_limit参数来控制上述问题
发生该参数用来指定优化器评估表格连接顺序最大数目利用这个参数就可以防止优化器在评估所有可能表格连接顺序中所花费多余时间假如查询中表数量少于或者等于optimizer_search_limit那么优化器检查所有可能表连接方式例如
涉及了 5个表查询共有120种(5！＝5×4×3×2×1＝120)可能连接顺序所以假如参数optimizer_search_limit值设置为5(默认值)那么优化器就会考虑所有这120种可能连接顺序optimizer_search_limit参数还用来控制启动开始连接指示(star join h
)阈值当查询所涉及表格数量少于参数optimizer_search_limit设定值开始连接指示将被设置另
个工具:optimizer_max_permutations参数optimizer_max_permutations
参数用来设定优化器优化范围上界(即最多考虑多少种表格连接顺序)它依靠于参数optimizer_search_limit参数optimizer_max_permutations默认值为8000参数optimizer_search_limit 和optimizer_max_permutations
同用来设置优化器所考虑排列数上限优化器不断产生可能表连接排列直到排列数达到参数optimizer_search_limit或者optimizer_max_permutations为止旦优化器停止产生新可能连接排列它将会从中选择出耗费最小排列用已排序指示来指定
种连接排序你可以设定优化器评估
排列数上限但是对复杂情况下即使答应排列数很大优化器也很可能在远远没有找到个比较合适排列的间就已经停止优化了你不妨回头看看我前面举那个例子(15个需要连接表有超过万亿种排列)假如设定优化器考虑80000种排列那么这仅仅考虑了所有可能性0.000006％优化器极可能没有达到最佳排列在Oracle SQL中解决这个问题
最好思路方法就是手工指定种表格连接顺序这里需要遵循大原则就是表格连接顺序应该使得查询计划尽快得以建立通常在SQL语句中使用WHERE限制子句下面以
个对名为emp表格并行查询为例例子中代码强制查询计划执行个嵌套循环连接(nested loop join)注重我使用了已排序指示来引导优化器来评估WHERE子句中给出表格连接顺序select /*+ ordered use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm.
from
emp e,
bonus b
where
e.ename = b.ename
;
上面
例子要求优化器按照SQL语句中FROM子句指定顺序连接表格FROM子句中第个表格指定为驱动表格(driving table)已排序指示经常和其它指示联合使用以确保多个表格按照适当顺序连接起来在碰到涉及 4个以上表格数据仓库查询时经常也是这样处理下面另给出
个例子在这个例子中我们使用个已排序指示(ordered h)来把表格按照个特定顺序(先是emp然后是dep和sal最后是bonus)连接起来进
步改进执行计划我指定emp表格到dept表格连接使用hash连接sal表格到bonus表格使用嵌套循环连接select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
from
emp,
dept,
sal,
bonus
where . . .
对实际应用
建议在实际应用场合下
减小optimizer_max_permutations参数并使用已保存优化计划或者已保存纲要(这样在查询涉及到许多表格时就可以避免重新解析查询所花费实际)会更有效率旦找到最好表格连接顺序你可以手工指定表格连接顺序(通过已排序指示)并保存纲要这样就永久保存该表格连接顺序当执行
个新查询时你可以首先把optimizer_search_limit设置为该查询所涉及表格数这样优化器将从所有连接顺序中找出最佳那种以后执行该查询时你就可以在WHERE子句中按照最佳连接顺序排列表格名称并设置已保存指示和已保存纲要这样就可以按照最佳顺序连接表格而无需重复评估各种可能排序这样查询速度将会得到显著提高已排序指示
优先级高于optimizer_search_limit和 optimizer_max_permutations参数假如设置了已排序指示那么表格就会按照查询命令中FROM子句给出顺序连接这样这个过程就没有优化器优化表格连接顺序这步骤了作为Oracle
专业人士你应该知道SQL语句进入库cache中有个明显起始延时但是聪明Oracle数据库治理员以及Oracle开发者能改变表格搜索限制参数或者利用已排序指示来手工指定表格连接顺序这样可以极大降低优化以及执行新查询所花费时间 
最新评论