消耗在准备新的SQL语句的时间是Oracle SQL语句执行时间的最重要的组成部分但是通过理解Oracle内部产生执行计划的机制你能够控制Oracle花费在评估连接顺序的时间数量并且能在大体上提高查询性能
准备执行SQL语句
当SQL语句进入Oracle的库缓存后在该语句准备执行之前将执行下列步骤
) 语法检查检查SQL语句拼写是否正确和词序
) 语义分析核实所有的与数据字典不一致的表和列的名字
) 轮廓存储检查检查数据字典以确定该SQL语句的轮廓是否已经存在
) 生成执行计划使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划
) 建立二进制代码基于执行计划Oracle生成二进制执行代码
一旦为执行准备好了SQL语句以后的执行将很快发生因为Oracle认可同一个SQL语句并且重用那些语句的执行然而对于生成特殊的SQL语句或嵌入了文字变量的SQL语句的系统SQL执行计划的生成时间就很重要了并且前一个执行计划通常不能够被重用对那些连接了很多表的查询Oracle需要花费大量的时间来检测连接这些表的适当顺序
评估表的连接顺序
在SQL语句的准备过程中花费最多的步骤是生成执行计划特别是处理有多个表连接的查询当Oracle评估表的连接顺序时它必须考虑到表之间所有可能的连接例如六个表的之间连接有（的阶乘或 * * * * * = ）种可能的连接线路当一个查询中含有超过个表的连接时排列的问题将变得更为显着对于个表之间的连接需要评估的可能查询排列将超过万亿（准确的数字是）种
使用optimizer_search_limit参数来设定限制
通过使用optimizer_search_limit参数你能够指定被优化器用来评估的最大的连接组合数量使用这个参数我们将能够防止优化器消耗不定数量的时间来评估所有可能的连接组合如果在查询中表的数目小于optimizer_search_limit的值优化器将检查所有可能的连接组合
例如有五个表连接的查询将有（! = * * * * = ）种可能的连接组合因此如果optimizer_search_limit等于（默认值）则优化器将评估所有的种可能optimizer_search_limit参数也控制着调用带星号的连接提示的阀值当查询中的表的数目比optimizer_search_limit小时带星号的提示将被优先考虑
另一个工具参数optimizer_max_permutations
初始化参数optimizer_max_permutations定义了优化器所考虑组合数目的上限且依赖于初始参数optimizer_search_limitoptimizer_max_permutations的默认值是
参数optimizer_search_limit和optimizer_max_permutations一起来确定优化器所考虑的组合数目的上限除非（表或组合数目）超过参数optimizer_search_limit 或者 optimizer_max_permutations设定的值否则优化器将生成所有可能的连接组合一旦优化器停止评估表的连接组合它将选择成本最低的组合
使用ordered提示指定连接顺序
你能够设定优化器所执行的评估数目的上限但是即使采用有很高价值的排列评估我们仍然拥有使优化器可以尽早地放弃复杂的查询的重要机会回想一下含有个连接查询的例子它将有超过万亿种的连接组合如果优化器在评估了个组合后停止那么它才仅仅评估了%的可能组合而且或许还没有为这个巨大的查询找到最佳的连接顺序
在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序为了尽快创建最小的解决方案集这里所遵循的规则是将表结合起来通常优先使用限制最严格的WHERE子句来连接表
下面的代码是一个查询执行计划的例子该例子在emp表的关联查询上强制执行了嵌套的循环连接注意我已经使用了ordered提示来直接最优化表的评估顺序最终它们表现在WHERE子句上
select /*+ ordered use_nl(bonus) parallel(e ) */
eename
hiredate
m
from
emp e
bonus b
where
eename = bename
这个例子要求优化器按顺序连接在SQL语句的FROM子句中指定的表在FROM子句中的第一个表指定了驱动表ordered提示通常被用来与其它的提示联合起来来保证采用正确的顺序连接多个表它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面
另外一个例子下面的查询使用ordered提示按照指定的顺序来连接表empdeptsal最后是bonus我通过指定emp到dept使用哈希连接和sal到bonus使用嵌套循环连接来进一步精炼执行计划
select /*+ ordered use_hash (emp dept) use_nl (sal bonus) */
from
emp
dept
sal
bonus
where
实践建议
实际上更有效率的做法是在产品环境中减小optimizer_max_permutations参数的大小并且总是使用稳定的优化计划或存储轮廓来防止出现耗时的含有大量连接的查询一旦找到最佳的连接顺序您就可以通过增加ordered提示到当前的查询中并保存它的存储轮廓来为这些表手工指定连接顺序从而使其持久化
当你打算使用优化器来稳定计划则可以照下面的方法使执行计划持久化临时将optimizer_search_limit设置为查询中的表的数目从而允许优化器考虑所有可能的连接顺序然后通过重新编排WHERE子句中表的名字并使用ordered提示与存储轮廓一起使变更持久化来调整查询在查询中包含四个以上的表时ordered提示和存储轮廓将排除耗时的评估SQL连接顺序解析的任务从而提高查询的速度
一旦检测到最佳的连接顺序我们就可以使用ordered提示来重载optimizer_search_limit和optimizer_max_permutations参数ordered提示要求表按照它们出现在FROM子句中的顺序进行连接所以优化器没有加入描述
作为一个Oracle专业人员你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟但是聪明的Oracle DBA和开发人员能够改变表的搜索限制参数或者使用ordered提示来手工指定表的连接顺序从而显着地减少优化和执行新查询所需的时间
