꿈꾸는 개발자, DBA 커뮤니티 구루비

튜닝 전

이번 예제는 OR조건이 사용될 경우 조건절 컬럼이 인덱스가 있음에도 불구하고 FULL SCAN 하는 것을 인덱스를 사용하도록 유도하는 예제이다.

아래의 내용에서 보는 것처럼 EMP 테이블에 EMPNO와 HIREDATE 컬럼으로 구성된 인덱스가 있음에도 불구하고, FULL SCAN을 하여 A-Time에 8.84초가 걸린것을 확인할 수 있다.

 
ALTER SESSION SET STATISTICS_LEVEL = ALL ;
SET SERVEROUTPUT OFF;
SET LINESIZE 2000;

VAR B1 NUMBER;
VAR B2 VARCHAR2(100);
VAR B3 VARCHAR2(100);

EXEC :B1 := 10;
EXEC :B2 := '20090401';
EXEC :B3 := '20090402';

SELECT *
FROM   EMP E
WHERE  (E.DEPTNO = :B1  
        OR 
        E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                   AND     TO_DATE(:B3, 'YYYYMMDD')  
       );

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |   2021K|     29 |00:00:08.84 |   44242 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("E"."DEPTNO"=:B1 OR ("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD')
              AND "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))))

1차 튜닝, UNION ALL 분리

이를 튜닝하는 1차 방법은 각 조건절이 독립적으로 사용될 수 있도록 SQL을 UNION ALL을 사용하여 분리하는 것이다.

OR 조건의 특징은 'A OR B'일 경우 A가 True이면 B를 수행하지 않고 A가 False일 경우 B를 수행한다. 그러므로 UNION ALL로 분리할 때 상단은 True가 되게 하고 하단은 False가 되게 구성하면 된다.

여기서는 DEPTNO 조건을 True와 False로 나누면 되며 UNION ALL 상단에 'DEPTNO = :B1', 하단에 'DEPTNO :B1'를 위치시켜 분리를 하였다.

 
SElECT *
FROM   EMP E
WHERE  E.DEPTNO = :B1   
UNION ALL
SElECT *
FROM   EMP E
WHERE  E.DEPTNO  :B1
AND    E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                  AND     TO_DATE(:B3, 'YYYYMMDD') 
;


-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                    |        |      1 |        |     29 |00:00:06.14 |   44248 |  43957 |
|*  2 |   TABLE ACCESS FULL           | EMP    |      1 |   1669K|      9 |00:00:06.14 |   44242 |  43957 |
|*  3 |   FILTER                      |        |      1 |        |     20 |00:00:00.01 |       6 |      0 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  20865 |     20 |00:00:00.01 |       6 |      0 |
|*  5 |     INDEX RANGE SCAN          | EMP_N2 |      1 |  45067 |     20 |00:00:00.01 |       4 |      0 |
-----------------------------------------------------------------------------------------------------------
                                                                                                           
Predicate Information (identified by operation id):                                                        
---------------------------------------------------                                                        
                                                                                                           
   2 - filter("E"."DEPTNO"=:B1)                                                                            
   3 - filter(TO_DATE(:B2,'YYYYMMDD')<=TO_DATE(:B3,'YYYYMMDD'))                                            
   4 - filter("E"."DEPTNO"<>:B1)                                                                           
   5 - access("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))

1차 튜닝, UNION ALL 분리 - 힌트 사용

위의 결과에서 보듯이 결과는 29건으로 동일하며 수행시간은 8.84초에서 6.14초로 줄어들었다.

하지만 E.HIREDATE 조건절을 사용한 UNINO ALL 하단은 EMP_N2 인덱스를 사용하여 0.01초만에 추출되었는데, E.DEPTNO 조건절을 사용한 UNION ALL 상단은 여전히 FULL SCAN을 하고 있다.

그러므로 이 부분을 해소하기 위해 힌트를 사용해 보도록 하자.

 
SElECT /*+ INDEX(E EMP_N1) */
       *
FROM   EMP E
WHERE  E.DEPTNO = :B1   
UNION ALL
SElECT *
FROM   EMP E
WHERE  E.DEPTNO  :B1
AND    E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                  AND     TO_DATE(:B3, 'YYYYMMDD') 
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   1 |  UNION-ALL                    |        |      1 |        |     29 |00:00:00.02 |      12 |      5 |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMP    |      1 |   1669K|      9 |00:00:00.02 |       6 |      3 |
|*  3 |    INDEX RANGE SCAN           | EMP_N1 |      1 |   1669K|      9 |00:00:00.02 |       4 |      2 |
|*  4 |   FILTER                      |        |      1 |        |     20 |00:00:00.01 |       6 |      2 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  20865 |     20 |00:00:00.01 |       6 |      2 |
|*  6 |     INDEX RANGE SCAN          | EMP_N2 |      1 |  45067 |     20 |00:00:00.01 |       4 |      0 |
-----------------------------------------------------------------------------------------------------------
                                                                                                           
Predicate Information (identified by operation id):                                                        
---------------------------------------------------                                                        
                                                                                                           
   3 - access("E"."DEPTNO"=:B1)                                                                            
   4 - filter(TO_DATE(:B2,'YYYYMMDD')<=TO_DATE(:B3,'YYYYMMDD'))                                            
   5 - filter("E"."DEPTNO"<>:B1)                                                                           
   6 - access("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))

위의 결과에서 보듯이 UNION ALL 상단에 힌트를 사용한 결과 0.02초로 줄어들었고 전체 시간 또한 감소된 걸 확인할 수 있다.

이처럼 OR 조건을 사용하여 인덱스가 있음에도 불구하고 FULL SCAN을 하고 있는 SQL들이 있다면 우선 OR 조건으로 분리할 수 있는지 검토하는 것이 좋다.

2차 튜닝, USE_CONCAT 힌트 사용

이번에는 SQL을 분리하지 않고 힌트만을 사용하여 각각 조건에 맞는 인덱스가 사용되도록 힌트를 사용하는 예제이다.

1차 튜닝에서는 OR조건을 기준으로 UNION을 사용하여 2개의 SQL 분리하였지만, 이번에는 USE_CONCAT이라는 힌트를 사용하여 실행계획이 2개로 분리하도록 유도하였다.

 
SElECT /*+ USE_CONCAT */  
       *
FROM   EMP E
WHERE  (E.DEPTNO = :B1          
        OR 
        E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                   AND     TO_DATE(:B3, 'YYYYMMDD') 
       );

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  CONCATENATION               |        |      1 |        |     29 |00:00:01.96 |   44250 |      2 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  25065 |     20 |00:00:00.03 |       9 |      2 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |  45525 |     20 |00:00:00.03 |       5 |      2 |
|*  4 |   TABLE ACCESS FULL          | EMP    |      1 |   2500K|      9 |00:00:07.93 |   44241 |      0 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------                                                       
   3 - access("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))
   4 - filter(("E"."DEPTNO"=:B1 AND (LNNVL("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD')) OR
              LNNVL("E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD')))))

위에서 보는 것처럼 USE_CONCAT 힌트를 사용하면 실행계획에서 CONCATENATION이라는 구문을 볼 수 있으며, Predicate Information절에서 어떤 조건들이 매칭되는지를 확인할 수 있다.

Predicate Information절을 기준으로 보았을 때 Id 2~3번절은 HIREDATE 조건에 의한 절이라는 것을 알 수 있고 Id 4번절은 DEPTNO 조건에 의한 것이라는 것을 알 수 있다.

그런데, 1차 튜닝때와 마찬가지로 옵티마이저가 Id 1번절을 FULL SCAN 하고 있어 이 부분도 인덱스를 사용하도록 유도하고자 한다.

그런데 인덱스를 사용하도록 유도하려면 1차 튜닝때 처럼 테이블이름 또는 테이블의 Alias처럼 인식할 수 있는 Pointer가 있어야 하는데 여기서는 그런 Pointer를 찾을 수가 없다. 이럴 경우 어떻게 인덱스를 유도할 수 있을까?

방법은 바로 강좌란 '1.2.4. OUTLINE Format'에서 공부한 내용으로, 옵티마이저가 Internal하게 구성하고 있는 Query Block Name을 이용하여 Pointer를 얻는 방법이다.

2차 튜닝, USE_CONCAT 힌트 사용 - Query Block Name Hint 확인

Query Block Name이란 옵티마이저가 SQL을 Parsing할 때 각각의 SQL을 구간별로 따로 인식하여 이름을 부여하고 이를 통해 실행계획을 만드는데, 이 때 각각의 구간을 Query Block Name에서 확인할 수 있다.

Query Block Name은 QB_NAME이라는 힌트를 통해 임의로 부여를 할 수 있으며, 부여하지 않을 경우 옵티마이저가 'SEL$1'과 같은 Internal한 이름을 부여한다.

 
SElECT /*+ USE_CONCAT */  
       *
FROM   EMP E
WHERE  (E.DEPTNO = :B1          
        OR 
        E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                   AND     TO_DATE(:B3, 'YYYYMMDD') 
       );

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  CONCATENATION               |        |      1 |        |     29 |00:00:05.30 |   44250 |   4174 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  25065 |     20 |00:00:00.01 |       9 |      0 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |  45525 |     20 |00:00:00.01 |       5 |      0 |
|*  4 |   TABLE ACCESS FULL          | EMP    |      1 |   2500K|      9 |00:00:05.30 |   44241 |   4174 |
----------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / E@SEL$1
   3 - SEL$1_1 / E@SEL$1
   4 - SEL$1_2 / E@SEL$1_2
   
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
      OPT_PARAM('_b_tree_bitmap_plans' 'false')
      OPT_PARAM('_fast_full_scan_enabled' 'false')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$1_1")
      USE_CONCAT(@"SEL$1" 8)
      OUTLINE_LEAF(@"SEL$1_2")
      OUTLINE(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1_1" "E"@"SEL$1" ("EMP"."HIREDATE"))
      FULL(@"SEL$1_2" "E"@"SEL$1_2")
      END_OUTLINE_DATA
  */

위의 내용에서 'Query Block Name / Object Alias'를 보면 실행계획에서 Id-1 ~ 4의 내용에 대해 Query Block Name과 Alias 이름이 어떻게 부여되었는지 알 수 있다.

즉 이 SQL은 Id-1번절에서 'SEL$1' 이름으로 하나의 집합 구간에 대해 'Query Block Name'을 주었고, 'USE_CONCAT' 힌트를 통해 2개의 실행계획으로 분리되면서 HIREDATE 조건절은 'SEL$1_1' 이름이, 'DEPTNO' 조건절은 'SEL$1_2' 이름이 부여된 걸 알 수 있다 .

그리고 'Query Block Name' 이외에 각 Object(테이블 또는 View)를 식별할 수 있는 Alias 이름을 'Object Alias'에서 확인할 수 있는데, HIREDATE 조건으로 액세스하는 EMP 테이블 Alias는 'E@SEL$1'으로, DEPTNO 조건으로 액세스하는 EMP 테이블 Alias는 'E@SEL$1_2'인 것을 알 수 있다.

이처럼 힌트를 사용하기 위한 Pointer를 찾을 수 없을 때 XPLAN의 Outline 정보를 이용하면 Pointer를 찾을 수 있으며 이를 통해 힌트를 사용할 수 있다.

2차 튜닝, USE_CONCAT 힌트 사용 - Query Block Name Hint 사용

Outline을 통해 힌트를 사용하는 방법은 다음과 같다.

 
1. Object Alias가 있는 경우
    - /*+ INDEX(Alias 인덱스명) */
    - /*+ INDEX(E EMP_N1) */

2. Object Alias가 없는 경우
    - /*+ INDEX(Query_Block_Name Query_Alias 인덱스명) */
    - /*+ INDEX(SEL$1_2 E@SEL$1_2 EMP_N1) */

Outline에서 얻은 Pointer는 반드시 Query_Alias만 사용하면 안되고 Query_Block_Name도 같이 사용해야 함.

여기서 우리가 원하는 것은 DEPTNO 조건에 의해 FULL SCAN하는 것을 인덱스를 사용하도록 유도하기 위해 힌트를 사용하는 것이며, 이를 위해 Pointer 값도 얻었기 때문에 이를 이용하여 힌트를 사용해보자.

 
SElECT /*+ USE_CONCAT 
           INDEX(@SEL$1_2 E@SEL$1_2 EMP_N1) */
       *
FROM   EMP E
WHERE  (E.DEPTNO = :B1         
        OR 
        E.HIREDATE BETWEEN TO_DATE(:B2, 'YYYYMMDD')  
                   AND     TO_DATE(:B3, 'YYYYMMDD') 
       );

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST'));

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------------
|   1 |  CONCATENATION               |        |      1 |        |     29 |00:00:00.01 |      13 |      8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |  24979 |     20 |00:00:00.01 |       9 |      5 |
|*  3 |    INDEX RANGE SCAN          | EMP_N2 |      1 |  44962 |     20 |00:00:00.01 |       5 |      3 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| EMP    |      1 |   2491K|      9 |00:00:00.01 |       4 |      3 |
|*  5 |    INDEX RANGE SCAN          | EMP_N1 |      1 |   2497K|      9 |00:00:00.01 |       3 |      3 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD') AND "E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))
   4 - filter((LNNVL("E"."HIREDATE">=TO_DATE(:B2,'YYYYMMDD')) OR
              LNNVL("E"."HIREDATE"<=TO_DATE(:B3,'YYYYMMDD'))))
   5 - access("E"."DEPTNO"=:B1)

드디어 Outline Pointer를 통해 인덱스를 사용하도록 유도하였다.

이처럼 Alias Pointer가 없을 때에도 Outline을 이용하여 힌트를 사용할 수 있으므로 하나의 튜닝방법론으로 숙지하면 좋을 것으로 보인다.

다만 이 방법론을 범용적으로 사용하는 것은 지양해야 한다. 그 이유는 Internal하게 부여되는 Query_Block_Name이나 Query_Alias가 Object 또는 SQL이 변경됨에 따라 값들도 변경될 수 있으므로 기존 힌트가 무용지물이 될 수 있다. 그러므로 최후의 보루로 튜닝방법이 없을 때 사용하는 것이 좋으며 가능하다면 QB_NAME 힌트를 사용하여 Query_Alias 값을 직접 부여하여 사용하는 것이 좋다.