본문 바로가기
DBMS/SQL튜닝

oracle hint 적용 안되는 경우 확인 및 해결 법

by 드바 2023. 7. 7.
뷰 머징과 같은 쿼리변환(Query Transformation)으로 힌트가 적용되지 않는 경우 확인 및 해결 법

 
SQL 수행

-- 테스트 뷰 생성
CREATE VIEW HR.DP_V AS 
SELECT DP.*
FROM(SELECT *
FROM HR.DEPARTMENTS
UNION ALL
SELECT *
FROM HR.DEPARTMENTS_TMP
) DP, EMPLOYEES E, LOCATIONS L
WHERE DP.DEPARTMENT_ID = E.DEPARTMENT_ID
AND DP.LOCATION_ID = L.LOCATION_ID 
;

-- SQL
SELECT E.* FROM HR.EMPLOYEES E, HR.DP_V D
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME LIKE 'Shi%'
;

---------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name 	     | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |		     |	    1 |   4050 |00:00:00.01 |	  550 |
|   1 |  NESTED LOOPS				 |		     |	    1 |   4050 |00:00:00.01 |	  550 |
|   2 |   NESTED LOOPS				 |		     |	    1 |     90 |00:00:00.01 |	  190 |
|   3 |    NESTED LOOPS 			 |		     |	    1 |      2 |00:00:00.01 |	    8 |
|   4 |     VIEW				 |		     |	    1 |      2 |00:00:00.01 |	    6 |
|   5 |      UNION-ALL				 |		     |	    1 |      2 |00:00:00.01 |	    6 |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS	     |	    1 |      1 |00:00:00.01 |	    3 |
|*  7 |        INDEX RANGE SCAN 		 | DEPARTMENTS_IX01  |	    1 |      1 |00:00:00.01 |	    2 |
|*  8 |       TABLE ACCESS FULL 		 | DEPARTMENTS_TMP   |	    1 |      1 |00:00:00.01 |	    3 |
|*  9 |     INDEX UNIQUE SCAN			 | LOC_ID_PK	     |	    2 |      2 |00:00:00.01 |	    2 |
|  10 |    TABLE ACCESS BY INDEX ROWID BATCHED	 | EMPLOYEES	     |	    2 |     90 |00:00:00.01 |	  182 |
|* 11 |     INDEX RANGE SCAN			 | EMP_DEPARTMENT_IX |	    2 |     90 |00:00:00.01 |	   92 |
|* 12 |   INDEX RANGE SCAN			 | EMP_DEPARTMENT_IX |	   90 |   4050 |00:00:00.01 |	  360 |
---------------------------------------------------------------------------------------------------------------

 
조인 순서를 e -> d 로 변경해보자

SELECT /*+ leading(e d) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME LIKE 'Shi%'
;

-- 힌트가 적용되지 않았다
---------------------------------------------------------------------------------------------------------------
| Id  | Operation				 | Name 	     | Starts | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			 |		     |	    1 |   4050 |00:00:00.01 |	  550 |
|   1 |  NESTED LOOPS				 |		     |	    1 |   4050 |00:00:00.01 |	  550 |
|   2 |   NESTED LOOPS				 |		     |	    1 |     90 |00:00:00.01 |	  190 |
|   3 |    NESTED LOOPS 			 |		     |	    1 |      2 |00:00:00.01 |	    8 |
|   4 |     VIEW				 |		     |	    1 |      2 |00:00:00.01 |	    6 |
|   5 |      UNION-ALL				 |		     |	    1 |      2 |00:00:00.01 |	    6 |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS	     |	    1 |      1 |00:00:00.01 |	    3 |
|*  7 |        INDEX RANGE SCAN 		 | DEPARTMENTS_IX01  |	    1 |      1 |00:00:00.01 |	    2 |
|*  8 |       TABLE ACCESS FULL 		 | DEPARTMENTS_TMP   |	    1 |      1 |00:00:00.01 |	    3 |
|*  9 |     INDEX UNIQUE SCAN			 | LOC_ID_PK	     |	    2 |      2 |00:00:00.01 |	    2 |
|  10 |    TABLE ACCESS BY INDEX ROWID BATCHED	 | EMPLOYEES	     |	    2 |     90 |00:00:00.01 |	  182 |
|* 11 |     INDEX RANGE SCAN			 | EMP_DEPARTMENT_IX |	    2 |     90 |00:00:00.01 |	   92 |
|* 12 |   INDEX RANGE SCAN			 | EMP_DEPARTMENT_IX |	   90 |   4050 |00:00:00.01 |	  360 |
---------------------------------------------------------------------------------------------------------------

-- 원인은 Outline Data MERGE(@"SEL$2" >"SEL$1") 부분에서 확인 가능(쿼리 변환 발생)
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$F5BB74E1
   4 - SET$1	    / DP@SEL$2
   5 - SET$1
   6 - SEL$3	    / DEPARTMENTS@SEL$3
   7 - SEL$3	    / DEPARTMENTS@SEL$3
   8 - SEL$4	    / DEPARTMENTS_TMP@SEL$4
   9 - SEL$F5BB74E1 / L@SEL$2
  10 - SEL$F5BB74E1 / E@SEL$1
  11 - SEL$F5BB74E1 / E@SEL$1
  12 - SEL$F5BB74E1 / E@SEL$2

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('19.1.0')
      DB_VERSION('19.1.0')
      OPT_PARAM('_px_adaptive_dist_method' 'off')
      OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
      OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$3")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SET$1")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      MERGE(@"SEL$2" >"SEL$1")           <-- 뷰 머징이 발생 하였다
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$2")
      NO_ACCESS(@"SEL$F5BB74E1" "DP"@"SEL$2")
      INDEX(@"SEL$F5BB74E1" "L"@"SEL$2" ("LOCATIONS"."LOCATION_ID"))
      INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEES"."DEPARTMENT_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1" "E"@"SEL$1")
      INDEX(@"SEL$F5BB74E1" "E"@"SEL$2" ("EMPLOYEES"."DEPARTMENT_ID"))
      LEADING(@"SEL$F5BB74E1" "DP"@"SEL$2" "L"@"SEL$2" "E"@"SEL$1" "E"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "L"@"SEL$2")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$1")
      USE_NL(@"SEL$F5BB74E1" "E"@"SEL$2")
      FULL(@"SEL$4" "DEPARTMENTS_TMP"@"SEL$4")
      INDEX_RS_ASC(@"SEL$3" "DEPARTMENTS"@"SEL$3" ("DEPARTMENTS"."DEPARTMENT_NAME"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "DEPARTMENTS"@"SEL$3")
      END_OUTLINE_DATA
  */
반응형


no_merge 힌트로 뷰머징이 발생하지 않도록 변경

SELECT /*+ no_merge(d) leading(e d) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME LIKE 'Shi%'
;

-- 조인 순서 변경 힌트가 잘 적용되었다
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				  | Name	      | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			  |		      |      1 |   4050 |00:00:00.01 |	   106 |       |       |	  |
|*  1 |  HASH JOIN				  |		      |      1 |   4050 |00:00:00.01 |	   106 |   932K|   932K| 1232K (0)|
|   2 |   TABLE ACCESS FULL			  | EMPLOYEES	      |      1 |    107 |00:00:00.01 |	     6 |       |       |	  |
|   3 |   VIEW					  | DP_V	      |      1 |     90 |00:00:00.01 |	   100 |       |       |	  |
|   4 |    NESTED LOOPS 			  |		      |      1 |     90 |00:00:00.01 |	   100 |       |       |	  |
|   5 |     NESTED LOOPS			  |		      |      1 |      2 |00:00:00.01 |	     8 |       |       |	  |
|   6 |      VIEW				  |		      |      1 |      2 |00:00:00.01 |	     6 |       |       |	  |
|   7 |       UNION-ALL 			  |		      |      1 |      2 |00:00:00.01 |	     6 |       |       |	  |
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS       |      1 |      1 |00:00:00.01 |	     3 |       |       |	  |
|*  9 | 	INDEX RANGE SCAN		  | DEPARTMENTS_IX01  |      1 |      1 |00:00:00.01 |	     2 |       |       |	  |
|* 10 |        TABLE ACCESS FULL		  | DEPARTMENTS_TMP   |      1 |      1 |00:00:00.01 |	     3 |       |       |	  |
|* 11 |      INDEX UNIQUE SCAN			  | LOC_ID_PK	      |      2 |      2 |00:00:00.01 |	     2 |       |       |	  |
|* 12 |     INDEX RANGE SCAN			  | EMP_DEPARTMENT_IX |      2 |     90 |00:00:00.01 |	    92 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------

 
DP_V 뷰 내부의 조인 순서를 변경해 보자(dp->l->e) -> (l->dp->e)

SELECT /*+ no_merge(d) leading(e d) leading(d.l d.dp d.e) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME LIKE 'Shi%'
;

-- (d.dp)와 같은 글로벌 힌트를 사용해서 뷰 내부 오브젝트에 힌트를 적용할 수 있다
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation				  | Name	      | Starts | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT			  |		      |      1 |   4050 |00:00:00.01 |	   105 |       |       |	  |
|*  1 |  HASH JOIN				  |		      |      1 |   4050 |00:00:00.01 |	   105 |   932K|   932K| 1208K (0)|
|   2 |   TABLE ACCESS FULL			  | EMPLOYEES	      |      1 |    107 |00:00:00.01 |	     6 |       |       |	  |
|   3 |   VIEW					  | DP_V	      |      1 |     90 |00:00:00.01 |	    99 |       |       |	  |
|   4 |    NESTED LOOPS 			  |		      |      1 |     90 |00:00:00.01 |	    99 |       |       |	  |
|*  5 |     HASH JOIN				  |		      |      1 |      2 |00:00:00.01 |	     7 |  2546K|  2546K| 1485K (0)|
|   6 |      INDEX FULL SCAN			  | LOC_ID_PK	      |      1 |     23 |00:00:00.01 |	     1 |       |       |	  |
|   7 |      VIEW				  |		      |      1 |      2 |00:00:00.01 |	     6 |       |       |	  |
|   8 |       UNION-ALL 			  |		      |      1 |      2 |00:00:00.01 |	     6 |       |       |	  |
|   9 |        TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS       |      1 |      1 |00:00:00.01 |	     3 |       |       |	  |
|* 10 | 	INDEX RANGE SCAN		  | DEPARTMENTS_IX01  |      1 |      1 |00:00:00.01 |	     2 |       |       |	  |
|* 11 |        TABLE ACCESS FULL		  | DEPARTMENTS_TMP   |      1 |      1 |00:00:00.01 |	     3 |       |       |	  |
|* 12 |     INDEX RANGE SCAN			  | EMP_DEPARTMENT_IX |      2 |     90 |00:00:00.01 |	    92 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------

 
뷰(dp_v) 내부 인라인뷰(dp)의 DEPARTMENTS 테이블에 힌트를 적용해보자(index -> table full scan)

SELECT /*+ no_merge(d) leading(e d) leading(d.l d.dp d.e) full(d.dp.DEPARTMENTS) */ E.* FROM HR.EMPLOYEES E, HR.DP_V D
WHERE 1=1
AND E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.DEPARTMENT_NAME LIKE 'Shi%'
;

-- INDEX RANGE SCAN -> TABLE ACCESS FULL 변경됨
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation		| Name		    | Starts | A-Rows |   A-Time   | Buffers |	OMem |	1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	|		    |	   1 |	 4050 |00:00:00.01 |	 109 |	     |	     |		|
|*  1 |  HASH JOIN		|		    |	   1 |	 4050 |00:00:00.01 |	 109 |	 932K|	 932K| 1232K (0)|
|   2 |   TABLE ACCESS FULL	| EMPLOYEES	    |	   1 |	  107 |00:00:00.01 |	   6 |	     |	     |		|
|   3 |   VIEW			| DP_V		    |	   1 |	   90 |00:00:00.01 |	 103 |	     |	     |		|
|   4 |    NESTED LOOPS 	|		    |	   1 |	   90 |00:00:00.01 |	 103 |	     |	     |		|
|*  5 |     HASH JOIN		|		    |	   1 |	    2 |00:00:00.01 |	  11 |	2546K|	2546K| 1689K (0)|
|   6 |      INDEX FULL SCAN	| LOC_ID_PK	    |	   1 |	   23 |00:00:00.01 |	   1 |	     |	     |		|
|   7 |      VIEW		|		    |	   1 |	    2 |00:00:00.01 |	  10 |	     |	     |		|
|   8 |       UNION-ALL 	|		    |	   1 |	    2 |00:00:00.01 |	  10 |	     |	     |		|
|*  9 |        TABLE ACCESS FULL| DEPARTMENTS	    |	   1 |	    1 |00:00:00.01 |	   7 |	     |	     |		|
|* 10 |        TABLE ACCESS FULL| DEPARTMENTS_TMP   |	   1 |	    1 |00:00:00.01 |	   3 |	     |	     |		|
|* 11 |     INDEX RANGE SCAN	| EMP_DEPARTMENT_IX |	   2 |	   90 |00:00:00.01 |	  92 |	     |	     |		|
-------------------------------------------------------------------------------------------------------------------------

'DBMS > SQL튜닝' 카테고리의 다른 글

rollup, grouping sets, cube, grouping 함수  (0) 2023.07.30
oracle ABS 함수, 절대값을 구하는 함수  (0) 2023.07.18
oracle hint 위치별 적용여부 테스트  (0) 2023.07.04
ORDER BY 컬럼명이 가리키는 컬럼은?  (0) 2023.07.03
펑션 위치별 실행 횟 수  (0) 2023.07.02

관련글

댓글

티스토리툴바