Oracle Tuning 강좌
View안의 서브쿼리 액세스 순서를 핸들링하는 경우 2 3 99,999+

by 강정식 DBMS_XPLAN DBMS_XPLAN.DISPLAY_CURSOR Query Block Name LEADING [2010.08.25]


튜닝 전

  이번 예제 또한 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를 보았는데, 다시한번 강조하지만 가급적 이 방법은 사용을 지양하고 기존 힌트 사용방법으로 하는 것이 좋으며, 빠른 시간내에 대응을 해야할 경우 사용하는 것이 좋다.

- 강좌 URL : http://www.gurubee.net/lecture/2148

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 전창환 [2011.04.20 15:08:49]
안녕하세요 궁금한 점이 있어 문의드립니다. 다소 동떨어진 질문일수 있는데요.
인라인뷰인데 플랜에는 view가 없습니다. 거기에 대한 설명은 없어서요. 저는 이걸 중요하게 생각하는데 다른 예문들을 봐도 이에 대한 설명은 없는거 같습니다.

by 마농 [2011.04.21 10:28:28]
특정 부분에 대한 강좌에서 모든 걸 다 다룰 수는 없겠죠.
질문하신 내용은 Query Transformation(쿼리변형)과 관련이 있습니다.
실제 작성된 쿼리와 실행되는 쿼리는 달라 질 수 있습니다.
실행 전 내부적으로 쿼리가 변형이 일어나는 것입니다.
오라클은 쿼리변형을 통해 복잡한 쿼리를 단순화하려는 시도를 합니다.
서브쿼리나 인라인뷰등을 없애려고 노력하지요.
Query Transformation의 방법엔 View Merging, Subquery Unnesting 등등 여러 기법이 있구요.
위의 경우엔 View Merging 이 일어난 경우겠네요.
Query Transformation 으로 검색해 보세요.

by 참된신자 [2014.08.14 09:18:41]

감사합니다 :)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입