oracle nl_sj,Oracle 学习之性能优化（九）使用hint

最新推荐文章于 2023-11-14 16:54:50 发布
最新推荐文章于 2023-11-14 16:54:50 发布
阅读量214 收藏
点赞数
文章标签： oracle nl_sj

基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从 而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle 中，是通过为语句添加 Hints(提示)来实现干预优化器优化的目的。

不建议在代码中使用hint，在代码使用hint使得CBO无法根据实际的数据状态选择正确的执行计划。毕竟 数据是不断变化的， 10g以后的CBO也越来越完善，大多数情况下我们该让Oracle自行决定采用什么执行计划。Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现：Hints for Optimization Approaches and Goals

Hints for Access Paths

Hints for Query Transformations

Hints for Join Orders

Hints for Join Operations

Hints for Parallel Execution

Additional Hints

实现提示的语法：{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

Hints for Optimization Approaches and Goals

/*+ ALL_ROWS*/ 语句块选择基于成本的优化方法,并获得最佳吞吐量,使资源消耗最小化.

/*+ FIRST_ROWS(n)*/ 语句块选择基于成本的优化方法,并获得最佳响应时间,使资源消耗最小化.

/*+ CHOOSE*/ 语句块依赖统计信息来决定选择CBO还是RBO

/*+ RULE*/ 语句块选择基于规则的优化方法.

实例：SQL> select /*+ALL_ROWS*/ * from emp,dept where emp.deptno=dept.deptno;

Execution Plan

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

Plan hash value: 844388907

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

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

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

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6(17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6(17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4(25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

filter("EMP"."DEPTNO"="DEPT"."DEPTNO")SQL>  select /*+ FIRST_ROWS(1)*/ * from emp,dept where emp.deptno=dept.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 3625962092

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

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

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

|   0 | SELECT STATEMENT     |       |     1 |    58 |     3 (0)| 00:00:01 |

|   1 |  NESTED LOOPS     |       |       |       |    |       |

|   2 |   NESTED LOOPS     |       |     1 |    58 |     3 (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL     | EMP     |     1 |    38 |     2 (0)| 00:00:01 |

|*  4 |    INDEX UNIQUE SCAN     | PK_DEPT |     1 |       |     0 (0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1 (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Hints for Access Paths

/*+ FULL(TABLE)*/ 全表扫描SQL> select empno from emp;

14 rows selected.

Execution Plan

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

Plan hash value: 179099197

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

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

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

|   0 | SELECT STATEMENT |  |1 |4 |1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | PK_EMP |1 |4 |1   (0)| 00:00:01 |

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

加hint后SQL> select /*+ FULL(emp)*/ ename from emp;

14 rows selected.

Execution Plan

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

Plan hash value: 3956160932

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

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

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

|   0 | SELECT STATEMENT  | |     1 |     6 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS FULL| EMP  |     1 |     6 |     2   (0)| 00:00:01 |

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

/*+ROWID(TABLE)*/SQL> SELECT ROWID,EMPNO FROM EMP;

ROWIDEMPNO

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

AAASZHAAEAAAACXAAA 7369

AAASZHAAEAAAACXAAB 7499

AAASZHAAEAAAACXAAC 7521

AAASZHAAEAAAACXAAD 7566

AAASZHAAEAAAACXAAE 7654

AAASZHAAEAAAACXAAF 7698

AAASZHAAEAAAACXAAG 7782

AAASZHAAEAAAACXAAH 7788

AAASZHAAEAAAACXAAI 7839

AAASZHAAEAAAACXAAJ 7844

AAASZHAAEAAAACXAAK 7876

ROWIDEMPNO

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

AAASZHAAEAAAACXAAL 7900

AAASZHAAEAAAACXAAM 7902

AAASZHAAEAAAACXAAN 7934

14 rows selected.

从上面的结果集中选取一个rowid，不加hintSQL> SELECT * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654);

Execution Plan

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

Plan hash value: 2355049923

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

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

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

|   0 | SELECT STATEMENT     |      |     1 |    38 |     2(0)| 00:00:01 |

|   1 |  INLIST ITERATOR     |      |       |       |    |      |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     2(0)| 00:00:01 |

|*  3 |    INDEX UNIQUE SCAN     | PK_EMP |     1 |       |     1(0)| 00:00:01 |

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

使用hintSQL> SELECT /*+rowid(EMP)*/ * FROM EMP WHERE ROWID>='AAASZHAAEAAAACXAAA' AND EMPNO IN(7521,7654);

Execution Plan

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

Plan hash value: 2267975152

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

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

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

|   0 | SELECT STATEMENT    |   | 1 |38 | 3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY ROWID RANGE| EMP  | 1 |38 | 3   (0)| 00:00:01 |

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

/*+ INDEX(TABLE INDEX_NAME) */ 对表选择索引的扫描方法. INDEX_NAME一定要大写SQL> select /*+INDEX(emp PK_EMP)*/ * from emp;

14 rows selected.

Execution Plan

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

Plan hash value: 4170700152

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

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

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

|   0 | SELECT STATEMENT    |     |   1 |  38 |   2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   2   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN    | PK_EMP |  14 |     |   1   (0)| 00:00:01 |

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

/*+ INDEX_ASC(TABLE INDEX_NAME)*/ 表明对表选择索引升序的扫描方法. 建立索引时如果没有指定desc，那么INDEX_ASC和INDEX 提示表示相同意义。SQL> select /*+INDEX_ASC(emp PK_EMP)*/ * from emp;

14 rows selected.

Execution Plan

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

Plan hash value: 4170700152

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

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

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

|   0 | SELECT STATEMENT    |     |   1 |  38 |   2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   2   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN    | PK_EMP |  14 |     |   1   (0)| 00:00:01 |

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

/*+ INDEX_DESC(TABLE INDEX_NAME)*/ 表明对表选择索引降序的扫描方法.SQL> select /*+INDEX_DESC(emp PK_EMP)*/ * from emp;

EMPNO ENAME  JOB     MGR HIREDATE    SAL       COMM     DEPTNO

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

7934 MILLER  CLERK     7782 1982/01/23 00:00:00   1300    10

7902 FORD   ANALYST    7566 1981/12/03 00:00:00   3000    20

7900 JAMES  CLERK     7698 1981/12/03 00:00:00    950    30

7876 ADAMS  CLERK     7788 1987/05/23 00:00:00   1100    20

7844 TURNER  SALESMAN    7698 1981/09/08 00:00:00   1500  0   30

7839 KING   PRESIDENT 1981/11/17 00:00:00   5000    10

7788 SCOTT  ANALYST    7566 1987/04/19 00:00:00   3000    20

7782 CLARK  MANAGER    7839 1981/06/09 00:00:00   2450    10

7698 BLAKE  MANAGER    7839 1981/05/01 00:00:00   2850    30

7654 MARTIN  SALESMAN    7698 1981/09/28 00:00:00   1250       1400   30

7566 JONES  MANAGER    7839 1981/04/02 00:00:00   2975    20

7521 WARD   SALESMAN    7698 1981/02/22 00:00:00   1250        500   30

7499 ALLEN  SALESMAN    7698 1981/02/20 00:00:00   1600        300   30

7369 SMITH  CLERK     7902 1980/12/17 00:00:00    800    20

14 rows selected.

Execution Plan

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

Plan hash value: 3088625055

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

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

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

|   0 | SELECT STATEMENT    |     |   1 |  38 |   2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |   1 |  38 |   2   (0)| 00:00:01 |

|   2 |   INDEX FULL SCAN DESCENDING| PK_EMP |  14 |     |   1   (0)| 00:00:01 |

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

上面的查询结果是按照empno降序排列的。

/*+INDEX_COMBINE(TABLE INDEX1 INDEX2 ...)*/SQL> create bitmap index bidx_emp_sal on emp(sal);

Index created.

SQL> create bitmap index bidx_emp_hiredate on emp(hiredate);

Index created.SQL> SELECT * FROM EMP WHERE SAL<1500 AND HIREDATE

EMPNO ENAME  JOB     MGR HIREDATE    SAL       COMM     DEPTNO

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

7369 SMITH  CLERK     7902 1980/12/17 00:00:00    800    20

7521 WARD   SALESMAN    7698 1981/02/22 00:00:00   1250        500   30

Execution Plan

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

Plan hash value: 1384570463

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

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

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

|   0 | SELECT STATEMENT     | |     1 |    38 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID | EMP |     1 |    38 |     2   (0)| 00:00:01 |

|   2 |   BITMAP CONVERSION TO ROWIDS| | | |      |  |

|*  3 |    BITMAP INDEX RANGE SCAN   | BIDX_EMP_HIREDATE | | |      |  |

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

Predicate Information (identified by operation id):

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

1 - filter("SAL"<1500)

3 - access("HIREDATE"

filter("HIREDATE"

使用hint后SQL> SELECT /*+INDEX_COMBINE( EMP BIDX_EMP_HIREDATE BIDX_EMP_SAL)*/ * FROM EMP WHERE SAL<1500 AND HIREDATE

EMPNO ENAME  JOB     MGR HIREDATE    SAL       COMM     DEPTNO

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

7369 SMITH  CLERK     7902 1980/12/17 00:00:00    800    20

7521 WARD   SALESMAN    7698 1981/02/22 00:00:00   1250        500   30

Execution Plan

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

Plan hash value: 1332639593

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

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

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

|   0 | SELECT STATEMENT     | |     1 |    38 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | EMP |     1 |    38 |     2   (0)| 00:00:01 |

|   2 |   BITMAP CONVERSION TO ROWIDS| | | |      |  |

|   3 |    BITMAP AND     | | | |      |  |

|   4 |     BITMAP MERGE     | | | |      |  |

|*  5 |      BITMAP INDEX RANGE SCAN | BIDX_EMP_HIREDATE | | |      |  |

|   6 |     BITMAP MERGE     | | | |      |  |

|*  7 |      BITMAP INDEX RANGE SCAN | BIDX_EMP_SAL | | |      |  |

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

Predicate Information (identified by operation id):

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

5 - access("HIREDATE"

filter("HIREDATE"

7 - access("SAL"<1500)

filter("SAL"<1500)

/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */

当谓词中引用的列都有索引的时候，可以通过指定采用索引关联的方式，来访问数据.选择列只能是索引中的列。SQL>  create index idx_emp_ename on emp(ename);

Index created.

SQL> select /*+ INDEX_JOIN( emp PK_EMP IDX_EMP_ENAME)*/ empno,ename from emp where ename='KING' and empno=7839;

EMPNO ENAME

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

7839 KING

Execution Plan

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

Plan hash value: 70197466

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

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

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

|   0 | SELECT STATEMENT   |      |     1 |    10 |     2  (50)| 00:00:01 |

|*  1 |  VIEW   | index$_join$_001 |     1 |    10 |     2  (50)| 00:00:01 |

|*  2 |   HASH JOIN   |      |       |       |    |      |

|*  3 |    INDEX RANGE SCAN| PK_EMP      |     1 |    10 |     0(0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN| IDX_EMP_ENAME    |     1 |    10 |     1(0)| 00:00:01 |

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

/*+ INDEX_FFS(TABLE INDEX_NAME) */对指定的表执行快速全索引扫描,而不是全表扫描的办法SQL> select empno from emp;

Execution Plan

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

Plan hash value: 179099197

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

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

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

|   0 | SELECT STATEMENT |  |1 |4 |1   (0)| 00:00:01 |

|   1 |  INDEX FULL SCAN | PK_EMP |1 |4 |1   (0)| 00:00:01 |

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

我们加上hint后SQL> select /*+INDEX_FFS(emp PK_EMP)*/ empno from emp order by empno;

Execution Plan

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

Plan hash value: 3618959410

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

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

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

|   0 | SELECT STATEMENT      |        |    14 |    56 |     3(34)| 00:00:01 |

|   1 |  SORT ORDER BY      |        |    14 |    56 |     3(34)| 00:00:01 |

|   2 |   INDEX FAST FULL SCAN| PK_EMP |    14 |    56 |     2 (0)| 00:00:01 |

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

/*+NO_INDEX(TABLE INDEX_NAME)*/ 不使用索引SQL> select /*+NO_INDEX(emp PK_EMP)*/ empno from emp;

Execution Plan

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

Plan hash value: 3956160932

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

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

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

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

|   1 |  TABLE ACCESS FULL| EMP  |     1 |     4 |     2   (0)| 00:00:01 |

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

/*+AND_EQUAL(TABLE INDEX1 INDEX2 ...)*/ index最少两个，最多不超过5个。

这个和INDEX_JOIN有点类似，但是INDEX_JOIN只能指定两个索引SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB);

Index created.

SQL> select /*+ AND_EQUAL(emp IDX_EMP_JOB IDX_EMP_ENAME)*/ empno,ename from emp where ENAME='KING' and JOB='SALESMAN';

no rows selected

Execution Plan

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

Plan hash value: 1954919191

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

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

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

|   0 | SELECT STATEMENT    |    |  1 | 18 |  2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |  1 | 18 |  2   (0)| 00:00:01 |

|   2 |   AND-EQUAL    |    |    |    | |    |

|*  3 |    INDEX RANGE SCAN    | IDX_EMP_ENAME |  1 |    |  1   (0)| 00:00:01 |

|*  4 |    INDEX RANGE SCAN    | IDX_EMP_JOB   |  3 |    |  1   (0)| 00:00:01 |

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

Hints for Query Transformations

/*+USE_CONCAT*/ 将WHERE 子句中的or或者in 查询转换成UNION ALL查询SQL> SELECT /*+USE_CONCAT*/ *  from emp where ename='KING' OR SAL>5000;

Execution Plan

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

Plan hash value: 1292243969

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

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

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

|   0 | SELECT STATEMENT      |       |     1 |    38 |     4(0)| 00:00:01 |

|   1 |  CONCATENATION      |       |       |       |    |      |

|   2 |   TABLE ACCESS BY INDEX ROWID | EMP      |     1 |    38 |     2(0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN      | IDX_EMP_ENAME |     1 |       |     1(0)| 00:00:01 |

|*  4 |   TABLE ACCESS BY INDEX ROWID | EMP      |     1 |    38 |     2(0)| 00:00:01 |

|   5 |    BITMAP CONVERSION TO ROWIDS|       |       |       |    |      |

|*  6 |     BITMAP INDEX RANGE SCAN   | BIDX_EMP_SAL  |       |       |    |      |

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

/*+NO_EXPAND*/ 与USE_CONCAT正好相反，就是阻止优化器将条件中带or或者in查询转换成UNION ALLSQL> select * from emp where empno=7840 or ename='SCOTT';

Execution Plan

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

Plan hash value: 2037299637

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

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

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

|   0 | SELECT STATEMENT     |     |   1 |  38 |   3   (0)| 00:00:01 |

|   1 |  CONCATENATION     |     |     |     |  |     |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP     |   1 |  38 |   2   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN     | IDX_EMP_ENAME |   1 |     |   1   (0)| 00:00:01 |

|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP     |   1 |  38 |   1   (0)| 00:00:01 |

|*  5 |    INDEX UNIQUE SCAN     | PK_EMP     |   1 |     |   0   (0)| 00:00:01 |

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

加hint后SQL> select /*+NO_EXPAND*/ * from emp where empno=7840 or ename='SCOTT';

Execution Plan

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

Plan hash value: 3956160932

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

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

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

|   0 | SELECT STATEMENT  | |     2 |    76 |     3   (0)| 00:00:01 |

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

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

/*+REWRITE(mview)*/ 使用物化视图重写sql

/*+NO_REWRITE*/ 不使用物化视图重写sql

/*+MERGE*/ 对视图查询进行合并。

看如下例子：SQL> SELECT e1.ename, e1.sal, v.avg_sal

FROM emp e1,

(SELECT deptno, avg(sal) avg_sal

FROM emp e2

GROUP BY deptno) v

WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal;

Execution Plan

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

Plan hash value: 269884559

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

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

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

|   0 | SELECT STATEMENT     |    |  1 | 29 |  8  (25)| 00:00:01 |

|*  1 |  HASH JOIN     |    |  1 | 29 |  8  (25)| 00:00:01 |

|   2 |   VIEW     |    |  3 | 48 |  4  (25)| 00:00:01 |

|   3 |    HASH GROUP BY     |    |  3 | 21 |  4  (25)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| EMP  | 14 | 98 |  3   (0)| 00:00:01 |

|   5 |   TABLE ACCESS FULL  | EMP  | 14 |182 |  3   (0)| 00:00:01 |

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

先把v的结果集算出来，再和e1进行join运算。

如果使用hint呢。SQL> SELECT /*+merge(v)*/e1.ename, e1.sal, v.avg_sal

FROM emp e1,

(SELECT deptno, avg(sal) avg_sal

FROM emp e2

GROUP BY deptno) v

WHERE e1.deptno = v.deptno AND e1.sal > v.avg_sal;

Execution Plan

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

Plan hash value: 2435006919

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

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

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

|   0 | SELECT STATEMENT     |    |  4 |128 |  8  (25)| 00:00:01 |

|*  1 |  FILTER      |    |    |    | |    |

|   2 |   HASH GROUP BY      |    |  4 |128 |  8  (25)| 00:00:01 |

|*  3 |    HASH JOIN     |    | 65 |  2080 |  7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| EMP  | 14 |350 |  3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| EMP  | 14 | 98 |  3   (0)| 00:00:01 |

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

先将两表进行关联，再进行group by

/*NO_MERGE(VIEW)*/ 与MERGE操作正好相反。

Hints for Join Orders

/*+ORDERED*/ 根据表在FROM子句中的顺序,依次对其连接.SQL>  select * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 844388907

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

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

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

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6(17)| 00:00:01 |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6(17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4(25)| 00:00:01 |

|   5 |    TABLE ACCESS FULL     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |

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

虽然emp表写在前面，但是优化器并没有先处理emp表。

添加hint后SQL> select /*+ORDERED*/ * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 1123238657

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

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

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

|   0 | SELECT STATEMENT   |  |    14 |   812 |7  (15)| 00:00:01 |

|*  1 |  HASH JOIN   |  |    14 |   812 |7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |3   (0)| 00:00:01 |

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

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

Hints for Join Operations

/*+USE_NL(TABLE1 TABLE2)*/ 使用循环嵌套进行连接,并把指定的第一个表作为驱动表.SQL> select /*+USE_NL(d e)*/ * from emp e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 4192419542

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

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

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

|   0 | SELECT STATEMENT   |  |    14 |   812 |    10   (0)| 00:00:01 |

|   1 |  NESTED LOOPS   |  |    14 |   812 |    10   (0)| 00:00:01 |

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

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

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

/*+USE_MERGE(table1 table2)*/SQL> alter session set optimizer_mode=first_rows_1;

Session altered.

SQL> select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.

Execution Plan

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

Plan hash value: 3355052392

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

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

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

|   0 | SELECT STATEMENT     |      |     1 |    20 |     3(0)| 00:00:01 |

|   1 |  NESTED LOOPS     |      |       |       |    |      |

|   2 |   NESTED LOOPS     |      |     1 |    20 |     3(0)| 00:00:01 |

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

|*  4 |    INDEX UNIQUE SCAN     | PK_EMP |     1 |       |     0(0)| 00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1(0)| 00:00:01 |

---------------------------------------------------------------------------------------SQL> select /*+USE_MERGE(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.

Execution Plan

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

Plan hash value: 992080948

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

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

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

|   0 | SELECT STATEMENT     |      |     1 |    30 |     6  (17)| 00:00:01 |

|   1 |  MERGE JOIN     |      |     1 |    30 |     6  (17)| 00:00:01 |

|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   140 |     2(0)| 00:00:01 |

|   3 |    INDEX FULL SCAN     | PK_EMP |    14 |       |     1(0)| 00:00:01 |

|*  4 |   SORT JOIN     |      |    13 |   130 |     4  (25)| 00:00:01 |

|*  5 |    TABLE ACCESS FULL     | EMP    |    13 |   130 |     3(0)| 00:00:01 |

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

/*+USE_HASH(table1 table2)*/ 将指定的表与其他表通过哈希连接方式连接起来.SQL> select /*+USE_HASH(a b)*/ a.ename,b.ename from emp a,emp b where a.mgr=b.empno;

13 rows selected.

Execution Plan

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

Plan hash value: 3638257876

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

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

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

|   0 | SELECT STATEMENT   |  |1 |    20 |7  (15)| 00:00:01 |

|*  1 |  HASH JOIN   |  |1 |    20 |7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |3   (0)| 00:00:01 |

|*  3 |   TABLE ACCESS FULL| EMP  |7 |    70 |3   (0)| 00:00:01 |

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

/*+DRIVING_SITE(TABLE)*/ 此hint在使用dblink时有用。我们看如下例子SQL> conn / as sysdba

Connected.

SQL> grant create database link to scott;

Grant succeeded.

SQL> conn scott/tiger

Connected.

SQL> create shared database link "db1"

authenticated by SCOTT

identified by "tiger"

using '192.168.199.216:1521/11GDG1';

进行如下查询SQL> select * from emp@db1 e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 2705760024

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

| Id  | Operation     | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Inst|IN-OUT|

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

|   0 | SELECT STATEMENT     |       |    14 |   812 |     6(17)| 00:00:01 ||      |

|   1 |  MERGE JOIN     |       |    14 |   812 |     6(17)| 00:00:01 ||      |

|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2 (0)| 00:00:01 ||      |

|   3 |    INDEX FULL SCAN     | PK_DEPT |     4 |       |     1 (0)| 00:00:01 ||      |

|*  4 |   SORT JOIN     |       |    14 |   532 |     4(25)| 00:00:01 ||      |

|   5 |    REMOTE     | EMP     |    14 |   532 |     3 (0)| 00:00:01 |    DB1 | R->S |

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

Oracle是将db1上的emp的数据传到本地，然后排序合并。如果emp的数据量非常大时，这样无疑是非常耗时的。如果我们可以将dept传给远端，在远端执行，结果返回到本地，那么执行的速度会比较快。SQL> select /*+DRIVING_SITE(e)*/* from emp@db1 e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 2412741621

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

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|

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

|   0 | SELECT STATEMENT REMOTE|      |    14 |   812 |     7  (15)| 00:00:01 |        |      |

|*  1 |  HASH JOIN       |      |    14 |   812 |     7  (15)| 00:00:01 |        |      |

|   2 |   REMOTE       | DEPT |     4 |    80 |     3(0)| 00:00:01 |      ! | R->S |

|   3 |   TABLE ACCESS FULL    | EMP  |    14 |   532 |     3(0)| 00:00:01 |  DGTST |      |

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

/*+LEADING(TABLE)*/ 将指定的表作为连接次序中的首表.SQL> select /*+LEADING(e)*/* from emp e,dept d where e.deptno=d.deptno;

14 rows selected.

Execution Plan

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

Plan hash value: 1123238657

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

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

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

|   0 | SELECT STATEMENT   |  |    14 |   812 |7  (15)| 00:00:01 |

|*  1 |  HASH JOIN   |  |    14 |   812 |7  (15)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |3   (0)| 00:00:01 |

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

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

/*+HASH_AJ*/ , /*+MERGE_AJ*/, and /*+NL_AJ*/ 将not in 改写成反连接。 AJ = anti-joinSQL> select * from emp where empno not in (select /*+NL_AJ*/ mgr from emp where mgr is not null) ;

8 rows selected.

Execution Plan

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

Plan hash value: 3509159946

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

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

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

|   0 | SELECT STATEMENT   |  |8 |   336 |    24   (0)| 00:00:01 |

|   1 |  NESTED LOOPS ANTI |  |8 |   336 |    24   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |3   (0)| 00:00:01 |

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

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

/*+HASH_SJ*/, /*+MERGE_SJ*/, and /*+NL_SJ*/ 将exists子句改写成半连接  SJ = semi-join

(一对多，只要有一个record 就 join成功)SQL> select * from dept where exists (select * from emp where deptno=dept.deptno and sal<1000);

Execution Plan

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

Plan hash value: 1946750470

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

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

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

|   0 | SELECT STATEMENT|       |     1 |    27 |     4(25)| 00:00:01 |

|   1 |  NESTED LOOPS|       |       |       |    |       |

|   2 |   NESTED LOOPS|       |     1 |    27 |     4(25)| 00:00:01 |

|   3 |    SORT UNIQUE|       |     1 |     7 |     2 (0)| 00:00:01 |

|   4 |     TABLE ACCESS BY INDEX ROWID | EMP       |     1 |     7 |     2 (0)| 00:00:01 |

|   5 |      BITMAP CONVERSION TO ROWIDS|       |       |       |    |       |

|*  6 |       BITMAP INDEX RANGE SCAN| BIDX_EMP_SAL |       |       |    |       |

|*  7 |    INDEX UNIQUE SCAN| PK_DEPT      |     1 |       |     0 (0)| 00:00:01 |

|   8 |   TABLE ACCESS BY INDEX ROWID| DEPT       |     1 |    20 |     1 (0)| 00:00:01 |

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

添加hintSQL> select * from dept where exists (select /*+HASH_SJ*/* from emp where deptno=dept.deptno and sal<1000);

Execution Plan

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

Plan hash value: 944460660

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

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

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

|   0 | SELECT STATEMENT      |      |   2 |  54 |   6  (17)| 00:00:01 |

|*  1 |  HASH JOIN SEMI       |      |   2 |  54 |   6  (17)| 00:00:01 |

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

|   3 |   TABLE ACCESS BY INDEX ROWID | EMP     |   1 |   7 |   2   (0)| 00:00:01 |

|   4 |    BITMAP CONVERSION TO ROWIDS|      |     |     |  |     |

|*  5 |     BITMAP INDEX RANGE SCAN   | BIDX_EMP_SAL |     |     |  |     |

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

其他常用的hint

/*+ parallel(table_name n) */

在sql中指定执行的并行度，这个值将会覆盖自身的并行度

select /*+ parallel(t 4) */ count(*)  from emp t;

/*+ no_parallel(table_name) */

在sql中指定执行的不使用并行

select /*+ no_parallel(t) */ count(*)  from emp t;

/*+ append */以直接加载的方式将数据加载入库

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

设置sql执行时动态采用的级别，这个级别为0~10select /*+ dynamic_sampling(t 4) */ * from t where id > 1234

/*+ cache(table_name) */

进行全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性

select /*+ full(employees) cache(employees) */ last_name from employees

稗官无印
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
"或"的扩张，use_concat以及no_expand提示使用
@DBA_白老大
02-26 2447
"或"的扩张，use_concat以及no_expand提示使用
use_concat，NO_EXPAND提示
cotchte0421的博客
12-30 136
USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.最后合并所有查询块的结果，返回结果集给用户。 但是从9.2.0后，这个提示似乎不是怎么有效了。 select /*+ use_concat opti...
小议USE_CONCAT提示 (转）
congtudi4111的博客
07-07 188
原文：http://yangtingkun.itpub.net/post/468/500859 前两天写了篇文章描述在一个查询中使用两个以上索引的几种情况，有人在我的BLOG里面回复提到了USE_CONCAT提示，这里就...
oracle执行计划remote,ORACLE执行计划(7)---采用TRUNC(SYSDATE)-1方法 无法确定分区
weixin_34315264的博客
04-14 918
在看分区的执行计划当中 说明字段有各种不同的分区操作其中有两个PARTITION RANGE ITERATORPARTITION RANGE singleITERATOR的成本高于SINGLE 好像ITERATOR是跨分区,当没有跨多个分区似的还有个PARTITION RANGE ITERATOR ALL 这就是访问所有分区.不管怎么样,不太喜欢PARTITION RANGE ITERATORse...
oracle use 代表什么意思,使用USE_CONCAT提示
weixin_42610671的博客
04-03 940
使用USE_CONCAT提示USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块.最后合并所有查询块的结果，返回结果集给用户。当使用多个in-lists查询时，Oracle可能选择把单个查询扩展为多个查询块。使用USE_CONCAT提示示例:1.使用scott用户及标准表进行测试$ sqlplus scott/tigerSQL*Plus: Release 9.2.0.4.0 ...
mongodb之使用explain和hint性能分析和优化
02-26
当你第一眼看到explain和hint的时候，第一个反应就是mysql中所谓的这两个关键词，确实可以看出，这个就是在mysql中借鉴过来的，既然是借鉴过来的，我想大家都知道这两个关键字的用处，话不多说，速速观看~~~1....
Oracle中Driving_site Hint的用法
12-14
对于使用DBLINK远程访问数据库的SQL，ORACLE可以有两种选择： 第一：在Remote数据库执行该段SQL； 第二：在Local数据库执行该段SQL； 所以优化策略和思路： 策略：远程访问，网络传输占很大部分，优化原则，减少网络...
实战Oracle SQL调优 hint特性
最新发布
03-05
实战Oracle SQL调优 hint特性
oracle_hint教程汇总
11-08
oracle_hint教程汇总，详细分析各种oracle的hint的用途及用法
oracle执行计划remote,执行计划中的REMOTE是不是很耗时？有没有变通方法？
weixin_34901693的博客
04-14 654
[PHP]SELECT STATEMENT, GOAL = RULEFILTERNESTED LOOPS OUTERMERGE JOIN OUTERNESTED LOOPSNESTED LOOPS OUTERMERGE JOINNESTED LOOPSMERGE JOINSORT JOINREMOTESORT JOINREMOTETABLE ACCESS BY INDEX ROWID ...
在dblink对象上使用全局并行是无效，应使用普通形式
N
03-05 2763
情况1：普通的未加/*+ DRIVING_SITE() */HINT,耗时最长40秒； select t2.status,t1.object_type from t2,aiki.t1@dblk_spcom_to_crm t1 where t2.object_id=t1.object_id and t2.object_id=123456; Execution Plan --------------
DBLINK引起的SQL性能问题
风中清淡
11-30 6277
DBLINK引起的SQL性能问题
Oracle insert 导致hint DRIVING_SITE失效解决方案
关注系统性能调优
08-24 2453
业务是将远程的表的数据同步到本地。 数据库是Oracle 11.2.0.4.0 INSERT INTO GG_ISSUE       (ISSUE_ID,        CONTRACT_ID,        ISSUE_DATE,        PROJECT_ID)       SELECT /*+DRIVING_SITE(ISSUE)*/  /*+DRIVING_SITE(PR
use_concat导致not in时临时表不动态采样进而导致的性能问题
zhaosj1726的专栏
01-21 1134
从这个案例,你可以看到无论是限制也好,还是说bug也好,我们并不是总能使用动态采样这个特性的,而且动态采样在硬分析时是有代价的,它会执行带有/* OPT_DYN_SAMP */这样注释的sql语句来执行动态采样数据动作的,而且如果它硬分析时动态采样到的刚好是非典型值的话,可能会生成糟糕的执行计划的.所以随意的使用动态采样,其实也是一种不负责任的行为,对于临时表,还是在插入代表性数据之后手工收集统计信息的好,对于新建的普通表,在加载完数据后,立即手工收集统计信息为好.
Oracle之Hint使用总结
热门推荐
旺旺的博客
06-30 2万+
导读： 一、关于hint 1、引入hint的原因； 2、不能过分依赖hint； 3、hint的弊端； 4、hint和注释的关系； 5、hint的功能。 二、hint的具体用法 1、hint的语法； 2、hint的作用域； 3、hint数据字典。 三、hint的分类 1、和优化器相关的； 2、和访问路径相关的； 3、和查询转换相关的； 4、和表连接顺序相关的； 5、和表操作相关的； 6、和并行相关的； 7、其他相关的。...
【性能优化】 之 HINTS 相关作业
好记忆也需烂博客
11-01 824
做完 优化课程 中HINTS 相关作业后， 感觉这几个作业还是蛮有特点，也能说明一些问题， 就在提交作业的同时一并发布了 ************************************************************************************************** 1.写一条SQL，使它通过全表扫描方式的效率优于索引访问，分别给出
Oracle所有执行计划关键词 + 所有hint
m0_47243617的博客
11-14 1056
执行计划里面的关键词大全和hint。
Oracle之三大连接的使用限制，nl、hash、merge
qq_33879355的博客
10-25 2448
的萨达萨达多撒大所
Oracle之三大连接讲解
qq_33879355的博客
10-26 618
1.nl连接 /*  结论： NL连接中，驱动表放回的数据条数，决定着被驱动表的访问次数 */ /*  结论： NL连接中，驱动表被访问0或者1次，被驱动表被访问0次或者N次，N由驱动表返回的结果集的条数来定） */ 名字解释：E-Rows：预期返回数据数量，A-Rows：实际返回数据数量 --环境构造 --研究Nested Loops Join的表访问次数前准备工作
oracle sql hint use_nl 表连接的顺序应该怎么选择
05-26
Oracle SQL中的USE_NL提示用于指定表连接时使用嵌套循环连接（Nested Loops Join）算法。在使用USE_NL提示时，连接顺序可以根据查询的具体情况来选择。 一般来说，如果连接的结果集较小，而其中一个表的数据可以...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值