如何用智能优化器提高Oracle的性能

核心提示： 在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序，为了尽快创建最小的解决方案集，如何用智能优化器提高Oracle的性能(3)，这里所遵循的规则是将表结合起来，通常优先使用限制最严格的WHERE子句来连接表，你应该知道在SQL语句第一次进入库缓存时可能存在重大的启动延迟，

在Oracle SQL中解决此问题的最好的方法是手工指定表的连接顺序。为了尽快创建最小的解决方案集，这里所遵循的规则是将表结合起来，通常优先使用限制最严格的WHERE子句来连接表。

下面的代码是一个查询执行计划的例子，该例子在emp表的关联查询上强制执行了嵌套的循环连接。注意，我已经使用了ordered提示来直接最优化表的评估顺序，最终它们表现在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子句中的第一个表指定了驱动表。ordered提示通常被用来与其它的提示联合起来来保证采用正确的顺序连接多个表。它的用途更多的是在扭转连接表数在四个以上的数据仓库的查询方面。

另外一个例子，下面的查询使用ordered提示按照指定的顺序来连接表：emp、dept、sal，最后是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提示来手工指定表的连接顺序，从而显著地减少优化和执行新查询所需的时间。