[转帖]Oracle提高SQL执行效率的三种方法

郑全

Oracle提高SQL执行效率的三种方法 (修改了一下)

　

　　要提高SQL在Oracle里的执行效率，优化应用的业务逻辑是最主要的，从技术层面来讲Oracle也提供了一些方法，今天主要讲3种：

　　Oracle提供了多种方法用于减少花在剖析OracleSQL表达式上的时间，在执行带有大量执行计划的复杂查询时剖析过程会拖累系统的性能。现在我们来简要地看看这些方法中的几种。

　　1、使用ordered提示

　　Oracle必须花费大量的时间来剖析多表的合并，用以确定表合并的最佳顺序。如果SQL表达式涉及七个乃至更多的表合并，那么有时就会需要超过30分钟的时间来剖析，因为Oracle必须评估表合并所有可能的顺序。八个表就会有40,000多种顺序。Ordered这个提示(hint)和其他的提示一起使用能够产生合适的合并顺序。

　　Ordered这个提示会要求列在SQL表达式FROM字句里的表按照指定的顺序进行合并，FROM字句里的第一个表会指定驱动表格(drivingtable)。驱动表格应该是返回最小行数的表格。使用ordered提示会跳过非常耗时和耗资源的剖析操作，并加快Oracle SQL的执行。

　　Listing A如下：

　　以下是引用片段：

　　Listing A

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

       from emp e,bonus b

　　where e.ename = b.ename ;

　　ListingA里是一个复杂查询的例子，这个查询被强制进行一个嵌套循环，从而与对emp表格进行的并行查询合并。要注意，我已经使用ordered提示来引导Oracle去按照FROM子句所列出的顺序来评估表格。

　　2、使用ordered_predicates

　　ordered_predicates提示在查询的WHERE子句里指定的，并被用来指定布尔判断(Booleanpredicate)被评估的顺序。在没有ordered_predicates的情况下，Oracle会使用下面这些步骤来评估SQL判断的顺序：

 

　　子查询的评估先于外层WHERE子句里的Boolean条件。

　　所有没有内置函数或者子查询的布尔条件都按照其在WHERE子句里相反的顺序进行评估，即最后一条判断最先被评估。

　　每个判断都带有内置函数的布尔判断都依据其预计的评估值按递增排列。

 

　　你可以使用ordered_predicates提示来强制取代这些缺省的评估规则，那么你WHERE子句里的项目就会按照其在查询里出现的顺序被评估。在查询的WHERE子句里使用了PL/SQL函数的情况下，通常会使用ordered_predicates提示。如果你知道限制最多的判断并且希望Oracle最先评估这些判断的时候，在这种情况下，它也是非常有用的。用法提示：你不能使用ordered_predicates提示来保存对索引键进行判断评估的顺序。

      create table t1(v1,n1,n2) as select to_char(mod(rownum,20)),rownum,mod(rownum,20) from all_objects where rownum<=3000;

 

      SQL> execute dbms_stats.gather_tabLE_stats('HR','T1');

     

SQL> select
  2         v1,n2,n1
  3    from t1
  4   where v1=1
  5     and n2=18
  6    and   n1=998
  7  ;

no rows selected

 

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    10 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    10 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N1"=998 AND "N2"=18 AND TO_NUMBER("V1")=1)

 

SQL> select /*+ ordered_predicates */
  2         v1,n2,n1
  3    from t1
  4   where v1=1
  5     and n2=18
  6    and   n1=998
  7  ;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    48 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    48 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("V1")=1 AND "N2"=18 AND "N1"=998)

 

　　3、限制表格合并评估的数量

　　提高SQL剖析性能的最后一种方法是强制取代Oracle的一个参数，这个参数控制着在评估一个查询的时候，基于消耗的优化器所评估的可能合并数量。

　　_optimizer_search_limit这个参数会指定表格合并组合的最大数量，后者将会在Oracle试图确定合并多表格最佳方式的时候被评估。这个参数有助于防止优化器花更多的时间来评估可能的合并顺序，而不是把时间花在寻找最佳合并顺序上。_optimizer_search_limit还控制着用于调用starjoin提示的阙值，当查询里的表格数量低于_optimizer_search_limit(其缺省的值是5)的时候，star提示就会被光顾。

　　以上只是一些Oracle DBA用来优化Oracle数据库应用程序SQL查询的性能的一些小技巧。