Oracle Tuning 강좌
부분범위처리 튜닝 방법 0 5 14,037

by 강정식 부분범위처리 10046 Trace [2010.06.28]


튜닝 전

  아래 SQL문장은 OLTP환경에서 부분범위를 처리하기 위한 예제이다.

  Trace를 확인한 결과 901건이 추출되었으며. 이 중 화면상에 설계된 15건만 부분범위 처리하여 보여주고자 한다.

 
SELECT D.DNAME,
       EMP_V.DEPTNO,
       EMP_V.HIREDATE,
       EMP_V.MIN_JOB       
FROM   (SELECT E.DEPTNO,
               TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
               MIN(E.JOB) MIN_JOB
        FROM   EMP E
        GROUP BY E.DEPTNO,
                 TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
       DEPT D
WHERE  EMP_V.DEPTNO = D.DEPTNO
AND    D.DNAME      = :B1;

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.001          0          0          0          0
Execute      1    0.000        0.004          0          0          0          0
Fetch       92   43.910       45.473       6989      44244          0        901
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total       94   43.910       45.477       6989      44244          0        901

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    901   HASH JOIN  (cr=44244 pr=6989 pw=0 time=44110198 us)
      1    TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=126 us)
1000007    VIEW  (cr=44241 pr=6989 pw=0 time=46107766 us)
1000007     HASH GROUP BY (cr=44241 pr=6989 pw=0 time=45107756 us)
10000000      TABLE ACCESS FULL EMP (cr=44241 pr=6989 pw=0 time=10000130 us)
    

1차 개선 - ROWNUM 사용

  1차로 위 SQL문에서 최종 SQL에 ROWNUM을 넣은 뒤 인라인 뷰로 만든 다음 밖에서 화면 설계된 로우수 만큼 추출하는 조건을 주었다. 하지만 이 조건만으로는 부분범위 처리가 되지 않는다.

  부분범위 처리 조건은 실행계획에서 모든 조인은 NL 조인이 되어야 하며, 만약 HASH JOIN이 하나라도 있으면 부분범위처리가 안 된다.

 
SELECT *
FROM   (SELECT D.DNAME,
               EMP_V.DEPTNO,
               EMP_V.HIREDATE,
               EMP_V.MIN_JOB,
               ROWNUM CNT      
        FROM   (SELECT E.DEPTNO,
                       TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
                       MIN(E.JOB) MIN_JOB
                FROM   EMP E
                GROUP BY E.DEPTNO,
                         TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
               DEPT D
        WHERE  EMP_V.DEPTNO = D.DEPTNO
        AND    D.DNAME      = :B1)
WHERE  CNT <= 15;

-- Trace 내용
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.000        0.004          0          0          0          0
Fetch        3   43.170       42.211          0      44244          0         15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5   43.170       42.218          0      44244          0         15

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   VIEW  (cr=44244 pr=0 pw=0 time=40881287 us)  -- ROWNUM 적용
    901    COUNT  (cr=44244 pr=0 pw=0 time=40883966 us)
-- HASH JOIN이 적용되어 부분범위 처리를 하지 못하고 EMP 테이블을 모두 액세스함    
    901     HASH JOIN  (cr=44244 pr=0 pw=0 time=40883056 us)
      1      TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=131 us)
1000007      VIEW  (cr=44241 pr=0 pw=0 time=41880601 us)
1000007       HASH GROUP BY (cr=44241 pr=0 pw=0 time=41880596 us)
10000000       TABLE ACCESS FULL EMP (cr=44241 pr=0 pw=0 time=114 us)  

-- XPLAN 내용
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   706K|    40M|       | 40769   (4)| 00:08:10 |
|*  1 |  VIEW                  |      |   706K|    40M|       | 40769   (4)| 00:08:10 |
|   2 |   COUNT                |      |       |       |       |            |          |
|*  3 |    HASH JOIN           |      |   706K|    15M|       | 40769   (4)| 00:08:10 |
|*  4 |     TABLE ACCESS FULL  | DEPT |     1 |    11 |       |     3   (0)| 00:00:01 |
|   5 |     VIEW               |      |  3534K|    40M|       | 40731   (4)| 00:08:09 |
|   6 |      HASH GROUP BY     |      |  3534K|    43M|   311M| 40731   (4)| 00:08:09 |
|   7 |       TABLE ACCESS FULL| EMP  |    10M|   124M|       | 12240   (3)| 00:02:27 |
---------------------------------------------------------------------------------------
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - filter("CNT"<=15)                                                               
   3 - access("EMP_V"."DEPTNO"="D"."DEPTNO")                                           
   4 - filter("D"."DNAME"=:B1) 

-- 위의 내용을 보면 HASH JOIN 부분인 Id 3번에 조인키인 3 - access("EMP_V"."DEPTNO"="D"."DEPTNO") 
-- 내용이 있는것을 확인할 수 있으며, 이를 통해 조인키가 EMP_V 인라인 뷰 안으로 Merge가 
-- 되지 못하였다는 것을 알 수 있으므로 가급적 NL 조인이 되도록 만들어야 한다.
    

2차 개선 - NL 조인 유도하기 위해 힌트 사용

  1차 개선에서 HASH JOIN 때문에 조건이 EMP_V 인라인 뷰 안으로 Merge가 되지 않았으므로 USE_NL 힌트를 사용해 NL 조인이 되도록 유도한다.

 
SELECT *
FROM   (SELECT /*+ LEADING(D) USE_NL(D EMP_V) PUSH_PRED(EMP_V) INDEX(EMP_V.E EMP_N1) */
               D.DNAME,
               EMP_V.DEPTNO,
               EMP_V.HIREDATE,
               EMP_V.MIN_JOB,
               ROWNUM CNT      
        FROM   (SELECT E.DEPTNO,
                       TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
                       MIN(E.JOB) MIN_JOB
                FROM   EMP E
                GROUP BY E.DEPTNO,
                         TO_CHAR(E.HIREDATE, 'YYYYMMDD')) EMP_V,
               DEPT D
        WHERE  EMP_V.DEPTNO = D.DEPTNO
        AND    D.DNAME      = :B1  
        )
WHERE  CNT <= 15;

-- Trace 내용
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.002          0          0          0          0
Execute      1    0.000        0.004          0          0          0          0
Fetch        3   32.600       31.880          0      44245          0         15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5   32.600       31.886          0      44245          0         15

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   VIEW  (cr=44245 pr=0 pw=0 time=30636573 us)
    901    COUNT  (cr=44245 pr=0 pw=0 time=30639256 us)
    901     NESTED LOOPS  (cr=44245 pr=0 pw=0 time=30638349 us)
      1      TABLE ACCESS FULL DEPT (cr=4 pr=0 pw=0 time=204 us)
    901      VIEW  (cr=44241 pr=0 pw=0 time=30637328 us)
1000007       SORT GROUP BY (cr=44241 pr=0 pw=0 time=31636297 us)
10000000        TABLE ACCESS FULL EMP (cr=44241 pr=0 pw=0 time=113 us)


-- 위에서 NESTED LOOPS이 되었음에도 불구하고 EMP_V 인라인 뷰 안의 EMP 테이블을 
-- 여전히 TABLE ACCESS FULL을 하고 있으며, 부분범위처리 또한 되지 않았다.
-- 그 이유를 확인하기 위해 아래에서 XPLAN의 Predicate Information 내용을 살펴보자.

-- XPLAN 내용
---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   706K|    40M|       | 40734   (4)| 00:08:09 |
|*  1 |  VIEW                  |      |   706K|    40M|       | 40734   (4)| 00:08:09 |
|   2 |   COUNT                |      |       |       |       |            |          |
|   3 |    NESTED LOOPS        |      |   706K|    15M|       | 40734   (4)| 00:08:09 |
|*  4 |     TABLE ACCESS FULL  | DEPT |     1 |    11 |       |     3   (0)| 00:00:01 |
|*  5 |     VIEW               |      |   706K|  8284K|       | 40731   (4)| 00:08:09 |
|   6 |      SORT GROUP BY     |      |  3534K|    43M|   311M| 40731   (4)| 00:08:09 |
|   7 |       TABLE ACCESS FULL| EMP  |    10M|   124M|       | 12240   (3)| 00:02:27 |
---------------------------------------------------------------------------------------
                                                                                       
Predicate Information (identified by operation id):                                    
---------------------------------------------------                                    
                                                                                       
   1 - filter("CNT"<=15)                                                               
   4 - filter("D"."DNAME"=:B1)                                                         
   5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") 


-- Id 5번을 보면 HASH에서 NL로 변경되었음에도 불구하고 5 - filter("EMP_V"."DEPTNO"="D"."DEPTNO") 
-- 내용처럼 조인키가 Merge가 되지 않았다. 왜 NL 조인으로 변경하였음에도 불구하고 조인키가 
-- Merge가 되지 않았을까? 그 이유는 바로 EMP_V 인라인뷰에 그룹함수가 있기 때문이다.
-- 일반적으로 View Merging이 되기 위해서는 아래의 제약조건이 없어야 하는데 이를 일부 살펴보면 
-- 다음과 같다.

① 집합 연산자를 사용할 경우(union, intersect, minus)
② connect by 절을 사용할 경우
③ rownum을 사용할 경우
④ 그룹함수를 사용할 경우(avg, count, max, min, sum)
⑤ 분석함수를 사용할 경우(row_number, rank, ...)

-- 이 중 여기서의 문제는 4번째 Case인 그룹함수를 EMP_V에서 사용했기 때문에 Merge가 일어나지 못하고 
-- 있는데, 일반적으로 위의 구문을 사용할경우 옵티마이저는 가급적 해당 집합을 먼저 구성한 다음 
-- 조인을 해야 데이터 정합성을 해치지 않기 때문에 View Merging을 가급적 시도하지 않는다.

-- 하지만 이런 그룹함수들도 분석함수를 사용할 경우 Complex View Merging을 통해 옵티마이저가 
-- Merging을 시키도록 할 수 있다. 
    

3차 개선 - 그룹함수를 분석함수를 사용하여 변경

  여기서는 그룹함수의 MIN을 분석함수의 ROW_NUMBER를 통해 변경하는 방법을 살펴보려고 한다. 변경내용에만 집중하기 위해 EMP_V 인라인 뷰 내용만 가지고 설명을 하고자 한다.

 
-- 1. EMP_V 인라인 뷰 그룹함수 버전
SELECT E.DEPTNO,
       TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
       MIN(E.JOB) MIN_JOB
FROM   EMP E
GROUP  BY E.DEPTNO,
          TO_CHAR(E.HIREDATE, 'YYYYMMDD');

-- 위의 내용을 보면 DEPTNO, HIREDATE 2개의 컬럼을 Grouping 지은 후, 
-- 그 중 JOB 컬럼 값 중 최소값(MIN)을 가져오는 내용이다.
-- 이를 분석함수로 대체할 경우 어떻게 하면 될까? 바로 ROW_NUMBER() 함수를 
-- 사용하여 JOB 컬럼값을 Asc로 ORDER BY 시킨 후 첫 번째 값만 가져오도록 변경해주면 된다.

-- 2. EMP_V 인라인 뷰 분석함수 버전
SELECT INLINE_EMP.DEPTNO,
       INLINE_EMP.HIREDATE,
       INLINE_EMP.MIN_JOB
FROM   (SELECT E.DEPTNO,
               TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
               E.JOB MIN_JOB,
               ROW_NUMBER() OVER(PARTITION BY E.DEPTNO, TO_CHAR(E.HIREDATE, 'YYYYMMDD') ORDER BY E.JOB) GUBN
        FROM   EMP E) INLINE_EMP
WHERE  INLINE_EMP.GUBN = 1;


-- 즉, 위의 내용처럼 PARTITION BY를 통해 2개의 컬럼을 Grouping 시킨 후 ORDER BY 한 결과를 
-- 밖에서 1 값만 취득하면 그룹함수의 MIN()과 동일한 효과를 나타낼 수 있으며,
-- 이를 통해 EMP_V 인라인 뷰 안으로 조인키인 DEPTNO 값이 Merge 되도록 할 수 있다.  
    

4차 개선 - EMP_V 인라인 뷰의 그룹함수를 분석함수로 대체

  이제 위에서 EMP_V 인라인 뷰의 그룹함수를 분석함수로 대체하여 다시 테스트를 해보자.

 
SELECT *
FROM   (SELECT /*+ LEADING(D) 
                   USE_NL(D EMP_V) 
                   PUSH_PRED(EMP_V) 
                   INDEX(EMP_V.INLINE_EMP.E EMP_N1) */
               D.DNAME,
               EMP_V.DEPTNO,
               EMP_V.HIREDATE,
               EMP_V.MIN_JOB               
        FROM   (SELECT INLINE_EMP.DEPTNO,
                       INLINE_EMP.HIREDATE,
                       INLINE_EMP.MIN_JOB
                FROM   (SELECT E.DEPTNO,
                               TO_CHAR(E.HIREDATE, 'YYYYMMDD') HIREDATE,
                               E.JOB MIN_JOB,
                               ROW_NUMBER() OVER(PARTITION BY E.DEPTNO, 
                                                              TO_CHAR(E.HIREDATE, 'YYYYMMDD') 
                                                 ORDER BY E.JOB) GUBN
                        FROM   EMP E) INLINE_EMP
                WHERE  INLINE_EMP.GUBN = 1) EMP_V,
               DEPT D
        WHERE  EMP_V.DEPTNO = D.DEPTNO
        AND    D.DNAME      = :B1)
WHERE  ROWNUM <= 15;    

-- Trace 내용
Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.003          0          0          0          0
Execute      1    0.000        0.010          0          0          0          0
Fetch        3    0.060        0.055         18         59          0         15
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        5    0.060        0.068         18         59          0         15

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: APPS (ID=44)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
     15   COUNT STOPKEY (cr=59 pr=18 pw=0 time=53933 us)
     15    NESTED LOOPS  (cr=59 pr=18 pw=0 time=53924 us)
      1     TABLE ACCESS FULL DEPT (cr=3 pr=0 pw=0 time=85 us)
-- View Merging이 되어 PUSHED PREDICATE 실행계획이 나타남      
     15     VIEW PUSHED PREDICATE  (cr=56 pr=18 pw=0 time=53834 us)
    132      WINDOW SORT PUSHED RANK (cr=56 pr=18 pw=0 time=54082 us)
   9000       TABLE ACCESS BY INDEX ROWID EMP (cr=56 pr=18 pw=0 time=18068 us)
   9000        INDEX RANGE SCAN EMP_N1 (cr=21 pr=18 pw=0 time=9053 us)(Object ID 6720703)   


-- 이제 다시 Trace 내용을 살펴보면 좀 전까지 View로 막혔던 내용이 'VIEW PUSHED PREDICATE'라는 
-- 내용으로 변경되었으며, EMP_V 인라인 뷰 안의 EMP 테이블이 FULL SCAN이 아닌 
-- INDEX RANGE SCAN으로 변경되었음을 알 수 있다.
-- 또한, 우리가 원하는 부분범위처리가 되어 EMP 테이블을 천만건 액세스하는 것이 아닌 9,000건만 
-- 액세스하여 수행속도를 기존 45초에서 0.068초로 감소된것을 확인할 수 있다.

-- 우리가 원하는 실행계획을 통해 부분범위처리가 되었긴 하지만, 
-- 실제 조건이 잘 들어갔는지 확인하기 위해 XPLAN을 다시 살펴보자.

-- XPLAN 내용
--------------------------------------------------------------------------------------------------      
| Id  | Operation                       | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |      
--------------------------------------------------------------------------------------------------      
|   0 | SELECT STATEMENT                |        |    15 |   765 |       |     4   (0)| 00:00:01 |      
|*  1 |  COUNT STOPKEY                  |        |       |       |       |            |          |      
|   2 |   NESTED LOOPS                  |        |  1111K|    54M|       |     4   (0)| 00:00:01 |      
|*  3 |    TABLE ACCESS FULL            | DEPT   |     1 |    14 |       |     3   (0)| 00:00:01 |      
|*  4 |    VIEW PUSHED PREDICATE        |        |     3 |   111 |       |     1   (0)| 00:00:01 |      
|*  5 |     WINDOW SORT PUSHED RANK     |        |  2001K|    24M|    91M| 26239   (2)| 00:05:15 |      
|   6 |      TABLE ACCESS BY INDEX ROWID| EMP    |  2001K|    24M|       | 16592   (1)| 00:03:20 |      
|*  7 |       INDEX RANGE SCAN          | EMP_N1 |  2018K|       |       |  3986   (2)| 00:00:48 |      
--------------------------------------------------------------------------------------------------      

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=15)                                                                               
   3 - filter("D"."DNAME"=:B1)                                                                          
   4 - filter("INLINE_EMP"."GUBN"=1)                                                                    
   5 - filter(ROW_NUMBER() OVER ( PARTITION BY                                                          
              "E"."DEPTNO",TO_CHAR(INTERNAL_FUNCTION("E"."HIREDATE"),'YYYYMMDD') 
               ORDER BY "E"."JOB")<=1)
   7 - access("E"."DEPTNO"="D"."DEPTNO") -- 조인 조건이 뷰 안으로 침투가 된 것이 확인됨

-- 위의 내용에서 보듯이 Id 4번에 조인키가 위치해 있지 않고 Id 7번에 조인키가 위치해 
-- 있는것으로 보아 Merge가 잘 되었음을 확인할 수 있다.
-- 이처럼, 튜닝을 진행할 때는 우리가 원하는 방향을 설정한 후 분할&정복 방식으로 진행하는 
-- 것이 좋으며, 실행계획 유도가 안 될 경우 그 이유가 무엇인지 XPLAN 정보를 통해 보정할 
-- 경우 원하는 성능향상을 얻을 수 있을것이다.
    

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

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

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

by Ejql [2010.07.16 09:39:59]
이런 실전적인 부분이 조금 필요했습니다. 감사합니다.

by 강정식 [2010.07.19 15:54:33]
도움이 되셨다니 다행입니다.

감사합니다.

by 진진 [2010.07.28 18:11:26]
늘상 닥치는 문제인데...이렇게 푸는거였군요
감사합니다

by 손님 [2012.10.28 16:59:50]

안녕하세요..
실행계획을 위의  << -- XPLAN 내용 >> 포맷으로 보려면 어떻게 해야하나요?
저는 위처럼 실행시간이 안나옵니다. ㅠ

감사합니다.


by 참된신자 [2014.08.13 15:51:41]

감사합니다 :)

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