iis服务器助手广告广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >Oracle 学习之性能优化（九）使用hint
  • 784

0
分享到

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

2024-04-02 19:04:59 784人浏览 薄情痞子
摘要

        基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行

        基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了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} 
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...


Hints for Optimization Approaches and Goals

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

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

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

 语句块选择基于规则的优化方法.

实例：

sql> select  * 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  * 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


全表扫描

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  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 |
--------------------------------------------------------------------------

SQL> SELECT ROWID,EMPNO FROM EMP;

ROWID			EMPNO
------------------ ----------
AAASZHAAEAAAACXAAA	 7369
AAASZHAAEAAAACXAAB	 7499
AAASZHAAEAAAACXAAC	 7521
AAASZHAAEAAAACXAAD	 7566
AAASZHAAEAAAACXAAE	 7654
AAASZHAAEAAAACXAAF	 7698
AAASZHAAEAAAACXAAG	 7782
AAASZHAAEAAAACXAAH	 7788
AAASZHAAEAAAACXAai	 7839
AAASZHAAEAAAACXAAJ	 7844
AAASZHAAEAAAACXAAK	 7876

ROWID			EMPNO
------------------ ----------
AAASZHAAEAAAACXAAL	 7900
AAASZHAAEAAAACXAAM	 7902
AAASZHAAEAAAACXAAN	 7934

14 rows selected.

从上面的结果集中选取一个rowid，不加hint

SQL> 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 |
---------------------------------------------------------------------------------------

使用hint

SQL> SELECT  * 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_NAME一定要大写

SQL> select  * 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 |
--------------------------------------------------------------------------------------

 表明对表选择索引升序的扫描方法. 建立索引时如果没有指定desc，那么INDEX_ASC和INDEX 提示表示相同意义。

SQL> select  * 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 |
--------------------------------------------------------------------------------------

 表明对表选择索引降序的扫描方法.

SQL> select  * 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降序排列的。


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<'1981/06/09 00:00:00';

     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"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
       filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))

使用hint后

SQL> SELECT  * FROM EMP WHERE SAL<1500 AND HIREDATE<'1981/06/09 00:00:00';

     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"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
       filter("HIREDATE"<TO_DATE(' 1981-06-09 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
   7 - access("SAL"<1500)
       filter("SAL"<1500)

 

当谓词中引用的列都有索引的时候，可以通过指定采用索引关联的方式，来访问数据.选择列只能是索引中的列。

SQL>  create index idx_emp_ename on emp(ename);

Index created.
SQL> select  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 |
---------------------------------------------------------------------------------------

对指定的表执行快速全索引扫描,而不是全表扫描的办法

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  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 |
--------------------------------------------------------------------------------

不使用索引

SQL> select  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 |
--------------------------------------------------------------------------

index最少两个，最多不超过5个。

这个和INDEX_JOIN有点类似，但是INDEX_JOIN只能指定两个索引

SQL> CREATE INDEX IDX_EMP_JOB ON EMP(JOB);

Index created.

SQL> select  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

将WHERE 子句中的or或者in 查询转换成UNION ALL查询

SQL> SELECT  *  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  |       |       |    |      |
-----------------------------------------------------------------------------------------------

与USE_CONCAT正好相反，就是阻止优化器将条件中带or或者in查询转换成UNION ALL

SQL> 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  * 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 |
--------------------------------------------------------------------------

使用物化视图重写sql

不使用物化视图重写sql

对视图查询进行合并。

看如下例子：

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 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

与MERGE操作正好相反。


Hints for Join Orders

 根据表在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  * 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

使用循环嵌套进行连接,并把指定的第一个表作为驱动表.

SQL> select  * 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 |
---------------------------------------------------------------------------


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  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 |
---------------------------------------------------------------------------------------

 将指定的表与其他表通过哈希连接方式连接起来.

SQL> select  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 |
---------------------------------------------------------------------------

此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 * 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 |      |
-----------------------------------------------------------------------------------------------

 将指定的表作为连接次序中的首表. 

SQL> select * 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 |
---------------------------------------------------------------------------

, , and 将not in 改写成反连接。 AJ = anti-join

SQL> select * from emp where empno not in (select  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 |
---------------------------------------------------------------------------

, , and 将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 |
------------------------------------------------------------------------------------------------

添加hint

SQL> select * from dept where exists (select * 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

 

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

select  count(*)  from emp t;

 

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

select  count(*)  from emp t;

以直接加载的方式将数据加载入库

insert into t  select * from t;

设置sql执行时动态采用的级别，这个级别为0~10

select  * from t where id > 1234

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

select  last_name from employees


您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: Oracle 学习之性能优化（九）使用hint

本文链接: https://www.lsjlt.com/news/45482.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • Oracle 学习之性能优化（七）join的实现方式
      本文讨论一下join技术背后的机制。我们知道常用的表连接有如下几种笛卡尔连接内连接左外连接右外连接全连接这些sql的写法，想必大家都很清楚了，那么这些连接的数据访问是如何实现的呢？nested...
    99+
    2024-04-02
  • golang函数性能优化与机器学习
    针对机器学习任务对 go 函数性能优化技巧：使用并发 goroutine 实现并行执行，提升性能。注意内存管理，避免逃逸分配和使用指针操作原始数据，优化内存使用。实战案例中，并行化机器学...
    99+
    2024-04-26
    机器学习 golang 函数性能优化 git 垃圾回收器
  • Oracle 性能优化 之 游标及 SQL
    一、游标 我们要先说一下游标这个概念。       从 Oracle 数据库管理员...
    99+
    2024-04-02
  • Go语言中数组如何优化性能？学习笔记带你走进优化之路！
    数组是计算机科学中最基本的数据结构之一。在Go语言中，数组是一个固定长度、由相同类型元素组成的序列。在一些高性能的场景中，如图形处理、游戏开发、大规模数据处理等，数组的性能优化显得尤为重要。本篇文章将介绍Go语言中数组的性能优化方法，带你...
    99+
    2023-10-20
    数组 学习笔记 http
  • C++技术中的机器学习：使用C++如何优化机器学习模型的性能？
    使用c++++优化机器学习模型的性能：优化数据结构：使用高效的容器，避免不必要的数据结构。优化算法：使用并行编程、gpu加速等策略并行化代码。优化内存管理：使用智能指针、内存池优化内存分...
    99+
    2024-05-12
    机器学习 c++
  • PHP学习笔记：性能优化与缓存技术
    一、引言在开发和运维PHP应用程序时，性能优化是一个重要的考虑因素。随着用户量的增加，应用程序的负载可能会迅速增大，这会导致响应时间延长，甚至导致服务器崩溃。为了提供更好的用户体验和稳定的系统性能，我们需要采取一些性能优化的策略和技术。本文...
    99+
    2023-10-21
    性能优化 缓存技术 PHP
  • Oracle性能优化使用度量和预警
    度量、预警及基线 对性能进行监视时，需要使用一些统计信息之外的特定信息。为了确定特定统计信息是否重要，则需要了解经过一段特定时间之后，该统计信息的变化量。为了具有前瞻性，则需要在存在特定条件时（例如系统...
    99+
    2024-04-02
  • Python 数组 path 学习笔记：如何优化数组性能？
    Python 中的数组是一种非常常见的数据结构，它可以用来存储一系列的元素，并且支持随机访问和修改。在实际编程中，我们经常需要对数组进行一些操作，如查找、插入、删除等，而这些操作的效率往往会对程序的性能产生很大的影响。因此，如何优化数组性...
    99+
    2023-08-19
    数组 path 学习笔记
  • 怎么优化使用NVARCHAR2列的Oracle查询性能
    在使用NVARCHAR2列的Oracle查询性能时，可以考虑以下优化方法： 使用合适的字符集：确保NVARCHAR2列使用合适的...
    99+
    2024-04-02
  • 如何使用 IDE 优化 Python 学习笔记？
    Python 是一种高级编程语言，广受欢迎，因为它易于学习，容易上手，同时也具有强大的功能。对于初学者来说，学习 Python 的过程中，一个好的 IDE 是非常重要的。使用 IDE 可以优化学习笔记，提高学习效率。在本文中，我们将介绍如何...
    99+
    2023-10-27
    学习笔记 ide 教程
  • Webpack性能优化之DLL有什么用
    这篇文章主要介绍了Webpack性能优化之DLL有什么用，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。前言在用 Webpack 打包的时候，...
    99+
    2024-04-02
  • MySQL使用索引优化性能
    目录1.索引问题2.索引的存储分类3.如何使用索引3.1使用索引3.2存在索引但不使用索引4.查看索引使用情况5.两个简单实用的优化方法5.1定期分析表和检查表5.2定期优化表1.索...
    99+
    2024-04-02
  • PHP 应用程序性能优化之数据库优化指南
    PHP 应用程序性能优化之数据库优化指南 前言 数据库是 PHP 应用程序的重要组成部分，它的性能直接影响应用程序的整体性能。优化数据库可以显著提高应用程序的速度和响应能力。本文将提供...
    99+
    2024-05-01
    php 数据库优化 mysql redis
  • PHP数组存储学习笔记：如何优化数组的性能？
    在PHP中，数组是一个非常重要的数据类型。它可以存储一组相关的数据，并且可以通过索引访问每个数据项。但是，在处理大型数组时，可能会遇到性能问题。本文将介绍如何优化PHP数组的性能，以便更高效地处理大型数据集。 一、避免多维数组 PHP中的...
    99+
    2023-08-05
    存储 学习笔记 数组
  • SQLSERVER使用表分区优化性能
    目录1.简介2.表分区2.1分区范围2.2分区键2.3索引分区3.创建表分区3.1创建文件组3.2指定文件组存放路径3.3创建分区函数3.4创建分区方案3.5创建分区表3.6创建分区...
    99+
    2024-04-02
  • tsc性能优化ProjectReferences使用详解
    目录什么是 Project References示例项目结构不使用 Project References 带来的问题tsconfig.json 的 references 配置项tsc...
    99+
    2022-11-16
    tsc性能Project Reference Project Reference
  • vue3 学习笔记之axios的使用变化总结
    目录一、axio 得基本使用 二、如何解决跨域问题？ 三、封装 四、全局引用 axios 使用 axios 之前，需要先安装好。 yarn add axios npm ins...
    99+
    2024-04-02
  • 大数据与PHP学习笔记：如何优化Apache服务器性能？
    随着大数据时代的到来，数据的处理和管理变得越来越重要。而PHP作为一种在Web开发中广泛使用的编程语言，与Apache服务器配合使用可以实现高效的数据处理和管理。本文将介绍如何优化Apache服务器性能，提高数据处理和管理的效率。 一、A...
    99+
    2023-11-02
    学习笔记 大数据 apache
  • 如何使用 PHP 优化网站性能
    通过优化数据库查询、压缩页面和资源、启用页面缓存、优化 php 代码和加载资源，可以有效提升 php 网站性能。例如，一家电子商务网站采用页面缓存、数据库索引优化和 gzip 压缩，网站...
    99+
    2024-05-02
    php 网站性能 css redis
  • 使用泛型优化golang代码性能
    通过使用泛型，可以在不编写特定类型函数的情况下对各种数据类型进行操作，从而减少代码重复。泛型通过消除对类型检查和转换的开销来提高性能，因为编译器可以生成针对任何类型高效工作的单个通用函数...
    99+
    2024-05-03
    java php golang
软考高级职称资格查询
iis服务器助手广告
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作