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

튜닝 전

이번 예제 또한 Query Block Name을 이용한 튜닝방법으로, 서브쿼리에 있는 테이블을 원하는 액세스 순서에 위치시키는 내용이다.

서브쿼리 또한 위에서 살펴본 내용처럼 Pointer가 없기 때문에 기존에 사용하는 힌트(PUSH_SUBQ)로는 제어가 불가능 하였지만 Query Block Name을 사용할 경우에는 이 또한 가능하다.

이 튜닝방법의 핵심은 서브쿼리가 주요 필터링 조건임에도 불구하고 원하는 액세스 순서에 위치 시킬 수 없어 여러 SQL 변형을 통해야 했지만 단 한번의 글로벌 힌트로 제어가 가능하다는 점이다.

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

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

EXEC :B1 := '00010101';
EXEC :B2 := '00010102';
EXEC :B3 := 10;

SELECT *
FROM   (SELECT E_1.EMPNO,
               E_1.JOB,
               E_1.HIREDATE,
               D.DEPTNO
        FROM   EMP  E_1,
               DEPT D
        WHERE  E_1.DEPTNO = D.DEPTNO
        AND    EXISTS (SELECT 1
                       FROM   EMP E_2
                       WHERE  E_2.DEPTNO = E_1.DEPTNO
                       AND    E_2.HIREDATE BETWEEN TO_DATE(:B1, 'YYYYMMDD')  
                                           AND     TO_DATE(:B2, 'YYYYMMDD') 
                       )
       ) EMP_V
WHERE  EMP_V.DEPTNO = :B3;

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

----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | A-Rows |   A-Time   | Buffers | Reads  |
----------------------------------------------------------------------------------------------------
|*  1 |  FILTER                        |         |      1 |      9 |00:00:08.74 |   44247 |  43940 |
|   2 |   MERGE JOIN SEMI              |         |      1 |      9 |00:00:08.74 |   44247 |  43940 |
|   3 |    NESTED LOOPS                |         |      1 |      9 |00:00:08.72 |   44243 |  43938 |
|*  4 |     INDEX UNIQUE SCAN          | DEPT_U1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|*  5 |     TABLE ACCESS FULL          | EMP     |      1 |      9 |00:00:08.72 |   44242 |  43938 |
|*  6 |    SORT UNIQUE                 |         |      9 |      9 |00:00:00.02 |       4 |      2 |
|*  7 |     TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      9 |00:00:00.02 |       4 |      2 |
|*  8 |      INDEX RANGE SCAN          | EMP_N2  |      1 |     20 |00:00:00.02 |       3 |      2 |
----------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1F949E82
   4 - SEL$1F949E82 / D@SEL$2
   5 - SEL$1F949E82 / E_1@SEL$2
   7 - SEL$1F949E82 / E_2@SEL$3
   8 - SEL$1F949E82 / E_2@SEL$3

Outline Data 
-------------
  /*+
      INDEX(@"SEL$1F949E82" "D"@"SEL$2" ("DEPT"."DEPTNO"))
      FULL(@"SEL$1F949E82" "E_1"@"SEL$2")
      INDEX_RS_ASC(@"SEL$1F949E82" "E_2"@"SEL$3" ("EMP"."HIREDATE"))
      LEADING(@"SEL$1F949E82" "D"@"SEL$2" "E_1"@"SEL$2" "E_2"@"SEL$3")
      USE_NL(@"SEL$1F949E82" "E_1"@"SEL$2")
      USE_MERGE(@"SEL$1F949E82" "E_2"@"SEL$3")
      END_OUTLINE_DATA
  */

위의 예제는 EMP_V 인라인뷰에서 E_1와 D가 조인이 되어 있고, 서브쿼리로 E_2가 E_1과 연결이 되어 있다.

또한 액세스 순서는 Outline Data(LEADING(@"SEL$1F949E82" "D"@"SEL$2" "E_1"@"SEL$2" "E_2"@"SEL$3"))를 통해 D -> E_1 -> E_2 순서로 진행되는 것을 알 수 있다.

우리는 이 액세스 순서를 E_2(서브쿼리)가 마지막에서 2번째로 위치시키고자 하며, E_1은 맨 마지막에 수행되도록 하고자 한다.

1차 튜닝, Query Block Name을 이용하여 액세스 순서 조정

방법은 간단하다. Query Block Name을 이용하여 D -> E_2 -> E_1이 되도록 LEADING 힌트를 사용하면 된다.

 
SELECT /*+ LEADING(@SEL$1F949E82 D@SEL$2 E_2@SEL$3 E_1@SEL$2) */
       *
FROM   (SELECT E_1.EMPNO,
               E_1.JOB,
               E_1.HIREDATE,
               D.DEPTNO
        FROM   EMP  E_1,
               DEPT D
        WHERE  E_1.DEPTNO = D.DEPTNO
        AND    EXISTS (SELECT 1
                       FROM   EMP E_2
                       WHERE  E_2.DEPTNO = E_1.DEPTNO
                       AND    E_2.HIREDATE BETWEEN TO_DATE(:B1, 'YYYYMMDD')  
                                           AND     TO_DATE(:B2, 'YYYYMMDD') 
                       )
       ) EMP_V
WHERE  EMP_V.DEPTNO = :B3;

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

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                         |         |      1 |        |      9 |00:00:06.92 |   44247 |  43959 |
|*  2 |   HASH JOIN                     |         |      1 |   3899M|      9 |00:00:06.92 |   44247 |  43959 |
|   3 |    NESTED LOOPS                 |         |      1 |   6245 |      1 |00:00:00.05 |       5 |      3 |
|*  4 |     INDEX UNIQUE SCAN           | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|   5 |     SORT UNIQUE                 |         |      1 |   6245 |      1 |00:00:00.05 |       4 |      3 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| EMP     |      1 |   6245 |      9 |00:00:00.05 |       4 |      3 |
|*  7 |       INDEX RANGE SCAN          | EMP_N2  |      1 |  44962 |     20 |00:00:00.05 |       3 |      3 |
|*  8 |    TABLE ACCESS FULL            | EMP     |      1 |   2497K|      9 |00:00:06.87 |   44242 |  43956 |
--------------------------------------------------------------------------------------------------------------

위의 결과처럼 액세스 순서가 D -> E_2 -> E_1이 된 것을 확인할 수 있다.

하지만 E_1을 액세스 할 때 'TABLE ACCESS FULL'이 일어나 여전히 Buffers를 44242만큼 읽고 있어 비효율이므로 이 부분을 인덱스를 사용하도록 힌트를 추가해보자.

2차 튜닝, Query Block Name을 이용하여 인덱스 유도

방법은 E_1의 Query Block Name을 이용하여 INDEX 힌트를 사용하면 된다.

 
SELECT /*+ LEADING(@SEL$1F949E82 D@SEL$2 E_2@SEL$3 E_1@SEL$2) USE_NL(@SEL$1F949E82 E_1@SEL$2) */
       *
FROM   (SELECT E_1.EMPNO,
               E_1.JOB,
               E_1.HIREDATE,
               D.DEPTNO
        FROM   EMP  E_1,
               DEPT D
        WHERE  E_1.DEPTNO = D.DEPTNO
        AND    EXISTS (SELECT 1
                       FROM   EMP E_2
                       WHERE  E_2.DEPTNO = E_1.DEPTNO
                       AND    E_2.HIREDATE BETWEEN TO_DATE(:B1, 'YYYYMMDD')  
                                           AND     TO_DATE(:B2, 'YYYYMMDD') 
                       )
       ) EMP_V
WHERE  EMP_V.DEPTNO = :B3;

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

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER                          |         |      1 |        |      9 |00:00:00.01 |      11 |      3 |
|   2 |   TABLE ACCESS BY INDEX ROWID    | EMP     |      1 |    624K|      9 |00:00:00.01 |      11 |      3 |
|   3 |    NESTED LOOPS                  |         |      1 |   3899M|     11 |00:00:00.01 |       9 |      3 |
|   4 |     NESTED LOOPS                 |         |      1 |   6245 |      1 |00:00:00.01 |       5 |      0 |
|*  5 |      INDEX UNIQUE SCAN           | DEPT_U1 |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |
|   6 |      SORT UNIQUE                 |         |      1 |   6245 |      1 |00:00:00.01 |       4 |      0 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| EMP     |      1 |   6245 |      9 |00:00:00.01 |       4 |      0 |
|*  8 |        INDEX RANGE SCAN          | EMP_N2  |      1 |  44962 |     20 |00:00:00.01 |       3 |      0 |
|*  9 |     INDEX RANGE SCAN             | EMP_N1  |      1 |    624K|      9 |00:00:00.01 |       4 |      3 |
---------------------------------------------------------------------------------------------------------------

이제 원하는 실행계획이 완성되었으며, 수행시간은 기존 8.7초에서 0.01초로 줄어들었고 Buffers 또한 44247에서 11로 줄어든것을 알 수 있다.

이렇게 2가지 Outline Date를 이용하여 튜닝하는 Case를 보았는데, 다시한번 강조하지만 가급적 이 방법은 사용을 지양하고 기존 힌트 사용방법으로 하는 것이 좋으며, 빠른 시간내에 대응을 해야할 경우 사용하는 것이 좋다.