분석함수의 실행계획 - 2부

빠른 이해를 위해서 이전 글을 먼저 보고 이 글을 읽기 바란다. 이 글의 1부에서 WINDOW SORT, WINDOW SORT PUSHED RANK, WINDOW NOSORT, WINDOW NOSORT STOPKEY OPERATION들을 정리해 보았다. 이번에는 나머지 네 가지 Operation에 관해 알아볼 것이다. 이번 시간을 마치면 분석함수의 실행계획은 모두 정리된다.

 

WINDOW (SORT)  

WINDOW (SORT PUSHED RANK)

WINDOW (NOSORT)

WINDOW (NOSORT STOPKEY)   <-- 저번 시간에는 여기까지

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT <-- 이번 시간에는 여기까지

 

5. WINDOW BUFFER

WINDOW BUFFER는 기본적으로 Ranking Family를 사용하지 않는 경우에 발생한다. 또한 인덱스가 적절하거나, 혹은 OVER절에 ORDER BY구문이 없는 경우에 발생한다. where절의 filter 조건이 있다면, 전체 건을 Sort 하지 않지만 최종 결과 건(Window의 범위)에 대해서는 sort가 필요하다.

 

SELECT PROD_ID,

       SUM(AMOUNT_SOLD) OVER() AS SUM_AVG

  FROM SALES_T

WHERE PROMO_ID = 33;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  WINDOW BUFFER     |         |   2074 |00:00:00.04 |    4440 |59392  (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.03 |    4440 |          |

---------------------------------------------------------------------------------

 

위의 결과를 보면 2074건에 대하여 Sort가 발생하였다. 하지만 아래는 다르다.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       SUM(AMOUNT_SOLD) OVER(ORDER BY NULL) AS RN

  FROM SALES_T ;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |    918K|00:00:03.69 |    4440 |          |

|   1 |  WINDOW BUFFER     |         |    918K|00:00:03.69 |    4440 |   35M (0)|

|   2 |   TABLE ACCESS FULL| SALES_T |    918K|00:00:00.96 |    4440 |          |

---------------------------------------------------------------------------------

 

WHERE 절이 없으므로 WINDOW의 범위는 전체이다. 전체 건(약 92만건)을 SORT해야 하므로 PGA를 35M나 사용하였다. OVER절에 ORDER BY가 없지만, 전체 건을 SORT 했으므로 WINDOW BUFFER는 WINDOW SORT Operation이 발생한 것과 마찬가지 이다.

 

이제 인덱스를 사용하면서 OVER절에 ORDER BY가 있는 경우를 살펴보자.

 

SELECT /*+ INDEX(S PK_SALES_T) */

         SUM(AMOUNT_SOLD) OVER(ORDER BY S.CUST_ID, S.CHANNEL_ID, S.TIME_ID ) AS "누적금액",

        S.*

  FROM SALES_T S

 WHERE S.PROD_ID = 22

 ORDER BY S.CUST_ID, S.CHANNEL_ID, S.TIME_ID ;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |   3441 |00:00:00.06 |    2911 |          |

|   1 |  WINDOW BUFFER               |            |   3441 |00:00:00.06 |    2911 |  206K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |   3441 |00:00:00.05 |    2911 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |   3441 |00:00:00.04 |      17 |          |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("S"."PROD_ID"=22)

 

인덱스 (PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID)컬럼과 OVER절의 ORDER BY구문의 컬럼순서가 동일하므로 WINDOW SORT 가 발생하지 않고 WINDOW BUFFER 가 발생하였다. SQL의 마지막에 위치한 ORDER BY 구문은 인덱스 컬럼과 일치하므로 별도의 SORT가 필요하지 않다.

 

6. WINDOW BUFFER PUSHED RANK

OPERATION에 RANK가 있으므로 Ranking Family를 사용한 것이다. 이 OPERATION을 볼 수 있으면 좋겠지만 10g에서 사라졌다. Oracle 9i 까지는 적절한 인덱스가 있는 경우에 Ranking Family를 사용하고, 뷰/인라인 뷰 외부에서 분석함수를 Filter로 사용한다면 WINDOW BUFFER PUSHED RANK가 발생하였다. 하지만 10g부터는 더 효율적인 WINDOW NOSORT(10g R1) 혹은 WINDOW NOSORT STOPKEY(10g R2)로 대체되었다. 참고로 야함나르 독자님에 의하면 버전 9.2.0.7에서도 WINDOW NOSORT가 발생한다고 한다. (관련링크)

 

9i에서 직접 테스트 해보면 좋겠지만 환경이 그렇지 못하므로 Julian Dyke의 테스트 내용을 인용한다.

 

This example was developed using Oracle 9.2.0.1 on Windows 2000

 

This example requires the following table definition

 

    CREATE TABLE t1 (c1 NUMBER NOT NULL,c2 NUMBER);

 

    CREATE INDEX i1 ON t1 (c1);

The table must be analysed

 

    ANALYZE TABLE t1 COMPUTE STATISTICS;

The statement

 

    SELECT c2,r1

FROM ( SELECT c2,RANK () OVER (ORDER BY c1) AS r1

               FROM t1 )

    WHERE r1 < 10;

 

9i의 실행계획

0     SELECT STATEMENT Optimizer=CHOOSE

1   0   VIEW

2   1     WINDOW (BUFFER PUSHED RANK)

3   2       TABLE ACCESS (BY INDEX ROWID) OF 'T1'

4   3         INDEX (FULL SCAN) OF 'I1' (NON-UNIQUE)

 

테스트의 출처 http://www.juliandyke.com/Optimisation/Operations/WindowBufferPushedRank.html

 

 

10g R2의 실행계획

---------------------------------------------------------------------------

| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |      |     1 |    26 |     1   (0)|

|*  1 |  VIEW                         |      |     1 |    26 |     1   (0)|

|*  2 |   WINDOW NOSORT STOPKEY       |      |     1 |    26 |     1   (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| T1   |     1 |    26 |     1   (0)|

|   4 |     INDEX FULL SCAN           | I1   |     1 |       |     1   (0)|

---------------------------------------------------------------------------

 

10g R2에서는 WINDOW BUFFER PUSHED RANK 대신에 WINDOW NOSORT STOPKEY 가 발생하므로 SORT가 전혀 발생하지 않으며 STOPKEY가 발생하여 필요한 부분만 SCAN된다. 따라서 TOP SQL이나 페이징 처리시 상당한 성능개선 효과를 볼 수 있다.

 

7. WINDOW CHILD PUSHED RANK

Ranking Family를 사용하고 인라인 뷰 외부에서 분석함수를 Filter로 사용한다면 실행계획에 ~PUSHED RANK 혹은 ~STOPKEY가 발생한다고 하였다. WINDOW CHILD PUSHED RANK도 마찬가지이다. 단 하나의 차이점은 Parallel Query를 사용했다는 것이다.

 

SELECT *

  FROM (SELECT /*+ FULL(T) PARALLEL(T 4) */  PROMO_ID,

               RANK() OVER(ORDER BY PROD_ID) AS RN

          FROM SALES_T T)

WHERE RN = 1

 

--------------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |          |   6002 |00:00:01.84 |       5 |          |

|*  1 |  VIEW                        |          |   6002 |00:00:01.84 |       5 |          |

|*  2 |   WINDOW SORT PUSHED RANK    |          |   6003 |00:00:01.83 |       5 |  142K (0)|

|   3 |    PX COORDINATOR            |          |   6006 |00:00:01.81 |       5 |          |

|   4 |     PX SEND QC (RANDOM)      | :TQ10000 |      0 |00:00:00.01 |       0 |          |

|*  5 |      WINDOW CHILD PUSHED RANK|          |      0 |00:00:00.01 |       0 |96256  (0)|

|   6 |       PX BLOCK ITERATOR      |          |      0 |00:00:00.01 |       0 |          |

|*  7 |        TABLE ACCESS FULL     | SALES_T  |      0 |00:00:00.01 |       0 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"=1)

   2 - filter(RANK() OVER ( ORDER BY "PROD_ID")<=1)

   5 - filter(RANK() OVER ( ORDER BY "PROD_ID")<=1)

   7 - access(:Z>=:Z AND :Z<=:Z)

 

ID 5번에 WINDOW CHILD PUSHED RANK가 발생했음에도 ID 2번에 WINDOW SORT PUSHED RANK가 발생한 이유는 QUERY COORDINATOR가 개별 PARALLEL SLAVE들을 취합해야 하기 때문이다.

 

8. WINDOW IN SQL MODEL SORT

MODEL절에서 분석함수를 사용하는 경우에 발생한다.

 

SELECT PROD_ID,a1,a2

  FROM (SELECT PROD_ID,SUM(AMOUNT_SOLD) a1

          FROM SALES_T

         GROUP BY PROD_ID )

       MODEL DIMENSION BY (PROD_ID)

       MEASURES (a1,0 a2)

       RULES ( a2[any] = SUM (a1) OVER () ) ;  --> 분석함수 사용

 

------------------------------------------------------------------------------------------

| Id  | Operation                   | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |         |     72 |00:00:01.91 |    4440 |          |

|   1 |  SQL MODEL ORDERED          |         |     72 |00:00:01.91 |    4440 |  405K (0)|

|   2 |   HASH GROUP BY             |         |     72 |00:00:01.91 |    4440 | 2516K (0)|

|   3 |    TABLE ACCESS FULL        | SALES_T |    918K|00:00:00.93 |    4440 |          |

|   4 |   WINDOW (IN SQL MODEL) SORT|         |     72 |00:00:00.01 |       0 | 2048  (0)|

------------------------------------------------------------------------------------------

 

위와 아래의 SQL은 같은 결과를 출력한다. 그리고 PGA 사용량도 비슷하다.

 

SELECT PROD_ID,SUM(AMOUNT_SOLD) a1, sum(SUM(AMOUNT_SOLD)) OVER() as a2

  FROM SALES_T

 GROUP BY PROD_ID ;

 

----------------------------------------------------------------------------------

| Id  | Operation           | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |         |     72 |00:00:01.91 |    4440 |          |

|   1 |  WINDOW BUFFER      |         |     72 |00:00:01.91 |    4440 | 2048  (0)|

|   2 |   HASH GROUP BY     |         |     72 |00:00:01.91 |    4440 | 2515K (0)|

|   3 |    TABLE ACCESS FULL| SALES_T |    918K|00:00:00.93 |    4440 |          |

----------------------------------------------------------------------------------

 

단순히 전체 합계를 출력하는 것이라면 MODEL절 보다는 분석함수를 사용하는 것이 조금 유리하다. SQL MODEL ORDERED OPERATION이 없기 때문이다. 참고로 MODEL절이 무엇인지 궁금한 사람은 SQL 포기하지마라 , Model 절에 대하여 라는 두 가지 글을 보기 바란다.

 

결론

 

WINDOW (SORT) :

-FTS를 사용하거나 인덱스가 적절하지 않은 경우 발생 

WINDOW (SORT PUSHED RANK) :

-위와 같으나 Ranking Family를 사용해야 하며, 뷰/인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

WINDOW (NOSORT) :

-인덱스가 적절한 경우나, OVER절에 ORDER BY가 없는 Ranking Family를 사용한 경우 발생

WINDOW (NOSORT STOPKEY) :

-위와 같으나 Ranking Family를 사용해야 하며, 뷰/인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

-10g R2 신기능

WINDOW (BUFFER) :

    - Ranking Family를 사용하지 않고 ORDER BY가 없는 경우이거나 적절한 인덱스를 사용하는 경우 발생

WINDOW (BUFFER PUSHED RANK) :

- Ranking Family를 사용하고, 뷰/인라인뷰 외부에서 분석함수를 filter로 사용해야 하며, 8i~9i 에서만 발생됨.

- 비효율을 개선하여 10g R1에서는 WINDOW (NOSORT)가 발생하며, 10g R2에서는 WINDOW (NOSORT STOPKEY)가  발생됨

WINDOW (CHILD PUSHED RANK) :

   - Ranking Family를 사용하고, 뷰/인라인뷰 외부에서 분석함수를 filter로 사용해야 하며, Parallel Query를 사용하는 경우 발생

WINDOW (IN SQL MODEL) SORT

   - Model 절에서 분석함수로 Rule을 정하는 경우 발생

 

심화학습이 필요한 이유

저번 글과 이번 글에서 분석함수를 사용할 때 나올 수 있는 실행계획을 정리해 보았다. 이제 분석함수의 실행계획을 보면 어떤 환경에서 실행되었는지, 어떤 비효율이 있는지 감을 잡을 수 있을 것이다. 다음 글에서는 위의 결론을 이용하여 좀더 고차원적인 심화과정에 도전할 것이다. 다음 글의 목적은 실전에서 분석함수를 사용할 때, 비효율을 잡아내는 능력을 향상시키고, 실제로 성능개선을 해보는 것이다.