【sql调优之执行计划】使用hint(一)Hints for Optimization Approaches and Goals

版本:10.2.0.4

Hints for Optimization Approaches and Goals

可以通过使用hint来改变或者指定优化器对执行计划的选择，分类来看这些hint的使用规则和一些例子。

Hints for optimization approaches and goals

一些优化目标的hint比如：

All_rows,first_rows(n),rule

10g默认的优化模式是all_rows，也就是使用choose的时候优化器选择的优化器模式。除了rule之外，其他都是CBO的优化模式。

看看hint对执行计划影响的例子：

SQL> set linesize 1000

SQL> set autot traceonly explain;

SQL> select /*+ all_rows */* from scott.emp a,scott.dept b

  2  where a.deptno = a.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2034389985

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    52 |  3016 |     8   (0)| 00:00:01 |

|   1 |  MERGE JOIN CARTESIAN|      |    52 |  3016 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |

|   3 |   BUFFER SORT        |      |    13 |   494 |     5   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL | EMP  |    13 |   494 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("A"."DEPTNO" IS NOT NULL)

 

SQL> select /*+ first_rows(2) */* from scott.emp a,scott.dept b

  2  where a.deptno = a.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4192419542

 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     2 |   116 |     4   (0)| 00:00:01 |

|   1 |  NESTED LOOPS      |      |     2 |   116 |     4   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| DEPT |     2 |    40 |     2   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |     2 |    76 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("A"."DEPTNO" IS NOT NULL)

 

SQL> select /*+ first_rows(100) */* from scott.emp a,scott.dept b

  2  where a.deptno = a.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2034389985

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    52 |  3016 |     8   (0)| 00:00:01 |

|   1 |  MERGE JOIN CARTESIAN|      |    52 |  3016 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |

|   3 |   BUFFER SORT        |      |    13 |   494 |     5   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL | EMP  |    13 |   494 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("A"."DEPTNO" IS NOT NULL)

 

SQL>

或者使用rule的hint，即RBO：

SQL> select /*+ rule */* from scott.emp a,scott.dept b

  2  where a.deptno = a.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 4192419542

 

-----------------------------------

| Id  | Operation          | Name |

-----------------------------------

|   0 | SELECT STATEMENT   |      |

|   1 |  NESTED LOOPS      |      |

|   2 |   TABLE ACCESS FULL| DEPT |

|*  3 |   TABLE ACCESS FULL| EMP  |

-----------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - filter("A"."DEPTNO"="A"."DEPTNO")

 

Note

-----

   - rule based optimizer used (consider using cbo)

 

SQL>

也可以使用choose，让优化器自动选择，这也是默认的情况

SQL> select /*+ choose */* from scott.emp a,scott.dept b

  2  where a.deptno = a.deptno;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2034389985

 

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    52 |  3016 |     8   (0)| 00:00:01 |

|   1 |  MERGE JOIN CARTESIAN|      |    52 |  3016 |     8   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    80 |     3   (0)| 00:00:01 |

|   3 |   BUFFER SORT        |      |    13 |   494 |     5   (0)| 00:00:01 |

|*  4 |    TABLE ACCESS FULL | EMP  |    13 |   494 |     1   (0)| 00:00:01 |

-----------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - filter("A"."DEPTNO" IS NOT NULL)

 

SQL>

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-671829/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-671829/