【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/，如需轉載，請註明出處，否則將追究法律責任。