oracle:优化2----使用hints,加速生成执行计划

利用oracle执行计划机制 提高 查询性能。

这是从快速生成执行计划的角度来优化性能，适用多表查询的情况，使用Optimizer hints可以手动指定多表的连接顺序以及连接方式。

使用hints的缺点就是，必须要管理，检查，控制额外的代码。数据库以及主机环境的变化可能导致hints过时或者可能有负面的影响。因此hints用在测试或开发环境中。

1       执行SQL语句前的准备工作

SQL语句进入oracle的库缓存后，在该语句准备执行之前，将执行下列步骤：

1)     语法检查：检查SQL语句拼写是否正确

2)     语义分析：核实所有的与数据字典不一致的表和列的名字

3)     轮廓存储检查：检查数据字典，以确定该SQL语句的轮廓是否已经存在

4)     生成执行计划：使用基于成本的优化规则和数据字典中的统计表来决定最佳执行计划

5)     建立二进制代码：基于执行计划，oracle生成二进制执行代码

 

一旦为执行准备好了SQL语句，以后的执行将很快发生，因为oracle认为同一个SQL语句，并且重用那些语句的执行。然而，对于生成特殊的SQL语句，或嵌入了文字变量的SQL语句的系统，SQL执行计划的生成时间就很重要。对于连接很多表查询，oracle需要花费大量的时间来检测连接这些表的适当顺序。

 

2       评估表的连接顺序

 

在SQL语句的准备过程中，花费最多的步骤是生成执行计划。当oracle评估表的连接顺序时，它必须考虑的表之间所有可能的连接。如，6个表之间连接有720种可能的连接线路。当一个查询中含有超过10个表的连接时，排列的问题将变得更为显著。

 

3       Optimizer_search_limit参数来设定限制

 

Optimizer_search_limit参数，可以指定被优化器用来评估的最大的连接组合数量。可以防止优化器消耗不定数量的时间来评估所有可能的连接组合。

例如: 有5个表连接的查询将有 120（5的阶乘）中可能的连接组合，因此如果optimizer_search_limit等于5（默认值），则优化器将评估所有的120中可能。Optimizer_search_limit参数也控制着调用带*的连接提示的阈值，当查询表中的表数目比optimizer_search_limit小时，带*的提示将被优先考虑。

 

4       Optimizer_max_permutations

Optimizer_max_permutations定义了优化器所考虑数目组合数上限。且依赖于参数optimizer_search_limit。Optimizer_max_permutations的默认值是80,000.

 

一旦优化器停止评估表的连接组合，它将选择成本最低的组合。

 

5       Optimizer hints—ordered

 

在Oracle解决连接顺序的最好方法是手工指定表的连接顺序。通常优先使用限制最严格的where子句来连接表。

 

 

如：在emp表的关联查询上强制执行了嵌套循环连接，使用ordered 提示直接最优化表的评估顺序，最终它们表现在where子句上。

 

Select /*+ ordered use_nl(bonus) parallel(e,4)*/ e.ename, hiredate, b.comm,

From emp e, bonus b

Where e.name=b.name;

 

这个例子要求优化器按顺序连接再from子句中指定的表。From子句中第一个表时驱动表，ordered 提示通常被用来与其它的提示联合起来保证采用正确的顺序连接多个表。

 

Ordered 表示依据from后面写的表的顺序来做连接。

Leading，也是指定表的连接顺序，但是不是按照from后面的表顺序，而是在leading中定义连接顺序。

Hash join可以通过no_swap_join_inputs/swap_join_inputs 来强制控制build表。

 

如，想实现（T3 hash-join(T1 hash-join T2)） hash-Join T4:

 

Select /*+ ordered

Use_hash(t2)

Use_hash(t3)

Swap_join_inputs(t3)

Use_hash(t4)

No_swap_join_inputs(t4)

*/

* from t1, t2,t3,t4

 

使用leading实现：

Select /*+ leading(t1 t2 t3 t4)

Use_hash(t2)

User_hash(t3)

Swap_join_inputs(t3)

Use_hash(t4)

No_swap_join_inputs(t4)

*/ * from t3,t4,t1,t2    ----使用了leading，from后面的表顺序可以任意写

 

6       优化器提示（optimizer hints）

 

在SQL语句中使用优化器提示可以改变执行计划。

优化器提示可以指导优化器使用特定的方法。

 

6.1      Hint概述

 

Hint是优化器的指令。

当书写一个SQL语句的时候，你应该对相关的数据信息有一些了解，但是优化器却是没有的。Hints使你可以做本该优化器做的决定。

 

在一个测试或开发环境中，hints被用来测试按照一个指定访问路径的性能。比如，你可能知道某个index对某个查询更具有可选择性。在这种情形下，你可以使用hints指导优化器使用一个更好的执行计划。

 

使用hints的缺点就是，必须要管理，检查，控制额外的代码。数据库以及主机环境的变化可能导致hints过时或者可能有负面的影响。因此，hints用于测试，使用其它方式管理SQL执行计划，比如 SQL Tuning advisor 和SQL Plan baselines.

 

Oracle支持60多个hints，它们可能有0个或多个参数。

Hints必须出现在这些关键字之后：

Select，update，insert，merge，delete

比如：

Hints指导优化器使用 查询出employee 表的前10行花销最小的 执行计划：

Select /*+ first_rows(10) */ * from employee;

 

 

6.2      Hints类型

 

6.2.1        单表hints

 

单表hints, 作用在一个指定的表或视图上。Index 、use_nl就是单表hints

注意： use_nl(t1 t2)不是多表hints，只是 use_nl(t1) and use_nl(t2) 的简写。

 

6.2.2        多表hints

 

可以指定一个或多个表或视图。Leading 就是多表hints.

 

6.2.3        查询块（query block）

 

查询块hint作用在单个查询块。比如：STAR_TRANSFORMATION 、 UNNEST

 

6.2.4        语句（statement）

 

对整个SQL 语句应用。比如：ALL_ROWS

 

6.3      Hints 类别

6.3.1        hints for join orders

 

如leading、ordered

 

6.3.2        hints for join operations

 

如：use_NL 、no_use_nl、use_nl_with_index, use_merge, no_user_merge

User_hash, no_use_hash

 

6.3.3        hints for parallel execution

 

parallel , no_parallel, parallel_index ,no_parallel_index

pq_distribute

6.3.4        hints for querytransformations

 

rewrite, merge, unnest, use_concat,no_expand

 

6.3.5        hints for access paths

 

full, cluster, hash, index,index_join,index_ffs

 

6.3.6        hints for optimizationapproaches and goals

 

如ALL_ROWS, FIRST_ROWS

 

 

 
7       参考资料

 

利用/*+Ordered*/提高查询性能

http://blog.csdn.net/u011631923/article/details/14127249

oracle hint中ordered 和leading原理很好的帖子

http://blog.itpub.net/13918611/viewspace-1254731/

了解如何利用ordered,use_nl(),leading(),index()进行调优

http://blog.itpub.net/13129975/viewspace-627390/