필자는 위의 글들을 이미 게시하였다. 그래서 Access Path와 Join Method는 정리가 되었고, 블로그로 배포되었으므로 그것으로 끝이라고 생각했다. 하지만 어느 개발자와 대화를 해보니 그런 생각이 짧았음을 알게 되었다.
개발자 : 위의 3가지 문서는 잘 보고 있습니다. 하지만 Access Path에 관해서는 큰 구멍이 있습니다.
필자 : 네? 무슨 말인가요?
개발자 : 분석함수는 Access Path를 변경시킵니다. 즉 분석함수를 어떻게 사용하느냐에 따라 실행계획의 Operation이 변경되므로 여타의 함수와는 엄연히 다릅니다. 그러므로 수석님께서 분석함수와 관련된 Access Path를 정리해 주셨으면 합니다.
필자 : 음…. 프로젝트도 있고, 블로그도 관리해야 하고, 책도 써야 하고, 개인적인 공부도 해야 하는데요. 시간이 나질 않습니다. (헉! 말을 하자마자 실수였다는 것을 알아차렸다. 블로그 관리라니……)
개발자 : 지금 하시고 계신 네 가지 이외의 일을 해달라는 게 아닙니다. 분석함수의 실행계획을 정리하셔서 블로그에 올리시면, 그게 블로그 관리 아닌가요?
필자 : …..
이번에는 공짜가 아니다
이렇게 해서 이 글을 쓰게 되었다. 해당개발자는 언제 나에게 맥주 한잔 사기 바란다. 이런 식으로 말려들어서 글을 작성한 적이 한두번이 아니다. ^^
분석함수의 내부
분석함수를 이용하여 답을 잘 내었으므로 그것으로 만족하는 사람들이 있다. 답을 내는 것은 표면적인 것(문법)을 이해한 것이다. 하지만 그것만 알아서는 내부적인 매커니즘을 이해할 수 없다. 즉 분석함수를 사용함에 따라 Access Path(Operation)가 어떻게 변경되는지도 알아야 모두를 아는 것이다. 분석함수의 내부적인 Operation을 모른다면 SQL 작성은 할지 몰라도, 성능을 향상시킬 수는 없다. 오늘의 목표는 분석함수의 내부를 정복함으로써 튜닝을 가능하게 하는 것이다.
분석함수의 실행계획은 아래와 같이 총 8가지로 예상된다. 만약, 아래의 List 이외의 것을 발견하면 이 글을 보는 독자들을 위해 알려주기 바란다. 글이 너무 길어지므로, 이번 시간에는 8개중에 위의 4개를 알아보고자 한다. 어떤 경우에 아래의 4가지 실행계획이 발생하는지 알아보자.
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
환경: Oracle 11.2.0.1
테스트를 위해 테이블과 인덱스를 만든다.
CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;
CREATE INDEX IX_PROD ON SALES_T( PROD_ID );
1. WINDOW SORT
분석함수를 사용하면 기본적으로 SORT가 발생한다. 이때 SORT를 대체할 수 있는 적절한 인덱스가 없다면 WINDOW SORT가 발생한다. 아래의 경우가 대표적이다.
-인덱스와 분석함수 OVER절의 컬럼이 일치하지 않을 때 발생한다.
-분석함수를 사용하고 Full Table Scan을 사용할 때 발생한다.
먼저 인덱스와 OVER 절의 컬럼이 일치하지 않는 경우를 살펴보자.
SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN
FROM SALES_T
WHERE PROD_ID = 30;
-------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29282 |00:00:00.18 | 424 | |
| 1 | WINDOW SORT | | 29282 |00:00:00.18 | 424 | 1558K (0)|
| 2 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.09 | 424 | |
|* 3 | INDEX RANGE SCAN | IX_PROD | 29282 |00:00:00.03 | 60 | |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PROD_ID"=30)
인덱스는 PROD_ID 로 구성되었지만, OVER절은 CUST_ID로 되어 서로 다르므로 실행계획의 Id 2번에 WINDOW SORT가 발생하였다.
이제 분석함수를 사용하면서 FTS(Full Table Scan)이 발생하는 경우를 보자.
SELECT PROD_ID,
SUM(AMOUNT_SOLD) OVER() AS SUM_AVG
FROM SALES_T
WHERE PROMO_ID = 33
ORDER BY CUST_ID;
---------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2074 |00:00:00.04 | 4440 | |
| 1 | WINDOW SORT | | 2074 |00:00:00.04 | 4440 | 102K (0)|
|* 2 | TABLE ACCESS FULL| SALES_T | 2074 |00:00:00.04 | 4440 | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROMO_ID"=33)
WHERE 절이 PROD_ID가 아니라 PROMO_ID이므로 FTS가 발생하였다. 이때 분석함수를 사용하면 WINDOW SORT가 발생된다.
이때 ORDER BY절이 있더라도 별도의 SORT ORDER BY operation이 발생하지 않는다. 분석함수가 SORT를 대신하기 때문이다. 이때 SORT의 일량은 동일하다. 즉 분석함수와 ORDER BY절이 동시에 존재한다면, SORT의 일량은 분석함수가 없고 ORDER BY만 존재하는 SQL과 동일 하다. 아래의 SQL이 이 사실을 증명한다.
SELECT PROD_ID, AMOUNT_SOLD
FROM SALES_T
WHERE PROMO_ID = 33
ORDER BY CUST_ID;
---------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2074 |00:00:00.04 | 4440 | |
| 1 | SORT ORDER BY | | 2074 |00:00:00.04 | 4440 | 102K (0)|
|* 2 | TABLE ACCESS FULL| SALES_T | 2074 |00:00:00.04 | 4440 | |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("PROMO_ID"=33)
위에서 설명된 SQL의 분석함수 + ORDER BY의 SORT 일량과 ORDER BY만 사용한 SORT의 일량은 102K로 동일함을 알 수 있다. 즉 분석함수와 ORDER BY를 같이 사용하던지, 아니면 ORDER BY만 사용하던지 간에 SORT의 부하는 동일하다는 이야기 이다.
2.WINDOW SORT PUSHED RANK
위에서 적당한 인덱스가 없을 때, 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 WINDOW SORT에 대해 좀더 깊이 들어가 보자.
SELECT *
FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN
FROM SALES_T
WHERE PROD_ID = 30 )
WHERE RN <= 10;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |00:00:00.13 | 424 | |
|* 1 | VIEW | | 10 |00:00:00.13 | 424 | |
|* 2 | WINDOW SORT PUSHED RANK | | 11 |00:00:00.13 | 424 | 2048 (0)|
| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.10 | 424 | |
|* 4 | INDEX RANGE SCAN | IX_PROD | 29282 |00:00:00.03 | 60 | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID")<=10)
4 - access("PROD_ID"=30)
이 글에서 처음 소개된 SQL과 같지만, 인라인뷰 외부에서 분석함수 컬럼을 filter 조건으로 사용하였다. 즉 상위 10건만 보자고 한 것이다. 이런 경우 WINDOW SORT PUSHED RANK operation이 발생하며 SORT의 부하는 제한된다. 다시 말해 RN <= 10 조건에 의해 전체를 SORT할 필요가 없이 10건의 배열을 만들고, 그 배열만 SORT 하면 된다. 이 글에서 최초로 설명된 SQL의 SORT 일량과 WINDOW SORT PUSHED RANK의 일량을 비교해보면 1558K 과 2K 로 천지차이이다. 분석함수 중에 순위를 구하는 것(RANK, DENSE_RANK, ROW_NUMBER)을 인라인뷰 외수에서 filter로 사용하면 적절한 인덱스가 없더라도 Sort의 부하는 최소화 된다는 것을 기억하자.
이런 사실은 분석함수를 사용하지 않더라도 동일하게 발생한다. 즉 위에서 실행된 SQL의 SORT량과 아래의 SORT이 동일하다. 아래는 분석함수를 사용하지 않고 ROWNUM을 사용하였다.
SELECT *
FROM ( SELECT /*+ INDEX(T IX_PROD) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD
FROM SALES_T T
WHERE PROD_ID = 30
ORDER BY CUST_ID)
WHERE ROWNUM <= 10;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |00:00:00.13 | 424 | |
|* 1 | COUNT STOPKEY | | 10 |00:00:00.13 | 424 | |
| 2 | VIEW | | 10 |00:00:00.13 | 424 | |
|* 3 | SORT ORDER BY STOPKEY | | 10 |00:00:00.13 | 424 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.09 | 424 | |
|* 5 | INDEX RANGE SCAN | IX_PROD | 29282 |00:00:00.03 | 60 | |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter(ROWNUM<=10)
5 - access("PROD_ID"=30)
WINDOW SORT PUSHED RANK 대신에 SORT ORDER BY STOPKEY operation이 사용되었지만 하는 일과 원리는 동일하므로 SORT량도 동일하다.
3.WINDOW NOSORT
적절한 인덱스가 없을 때 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 동일한 SQL을 실행하되 적절한 인덱스를 만들고 실행해보자.
CREATE INDEX PK_SALES_T ON SALES_T(PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID);
SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN
FROM SALES_T T
WHERE PROD_ID = 30;
----------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 29282 |00:00:00.34 | 28352 | |
| 1 | WINDOW NOSORT | | 29282 |00:00:00.34 | 28352 | |
| 2 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.24 | 28352 | |
|* 3 | INDEX RANGE SCAN | PK_SALES_T | 29282 |00:00:00.07 | 133 | |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("PROD_ID"=30)
WHERE절과 OVER절의 ORDER BY에 만족하는 인덱스를 생성하였더니 SORT가 전혀 발생하지 않는다. 또한 OPERATION도 WINDOW SORT에서 WINDOW NOSORT로 바뀌었다. 적절한 인덱스가 왜 필요한지 알 수 있는 대목이다. 참고로 OVER 절의 ORDER BY 구문은 PROD_ID , CUST_ID 로 바꾸어도 동일한 결과를 얻을 수 있다.
또 다른 경우를 보자. 분석함수를 사용하고, Full Table Scan을 사용하더라도 WINDOW NOSORT가 발생할 수 있다.
SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
ROW_NUMBER() 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:04.81 | 4898 | |
| 1 | WINDOW NOSORT | | 918K|00:00:04.81 | 4898 | |
| 2 | TABLE ACCESS FULL| SALES_T | 918K|00:00:01.99 | 4898 | |
---------------------------------------------------------------------------------
FTS를 사용했지만 분석함수에 ORDER BY절이 없으므로 SORT는 발생하지 않는다. 무작위로 10건을 추출하는 경우에 사용할 수 있다. 물론 ROWNUM을 사용해도 동일한 효과를 낼 것이다.
4.WINDOW NOSORT STOPKEY
인덱스의 컬럼순서와 분석함수 OVER절에 존재하는 ORDER BY 컬럼순서가 동일하며, Ranking Family 함수를 인라인뷰 외부에서 filter 조건으로 사용할 때 발생한다.
SELECT *
FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,
ROW_NUMBER() OVER(ORDER BY PROD_ID) AS RN
FROM SALES_T
WHERE PROD_ID = 30 )
WHERE RN <= 10;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |00:00:00.01 | 6 | |
|* 1 | VIEW | | 10 |00:00:00.01 | 6 | |
|* 2 | WINDOW NOSORT STOPKEY | | 10 |00:00:00.01 | 6 | |
| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 11 |00:00:00.01 | 6 | |
|* 4 | INDEX RANGE SCAN | IX_PROD | 11 |00:00:00.01 | 4 | |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "PROD_ID")<=10)
4 - access("PROD_ID"=30)
WINDOW NOSORT STOPKEY는 부분범위처리에 최적화되어 있다
분석함수를 Rownum처럼 사용하였기 때문에 WINDOW NOSORT STOPKEY가 발생하였다. 1건을 더 읽었지만 비효율은 느낄 수 없는 정도이다. WINDOW NOSORT STOPKEY와 이미 위에서 설명된 WINDOW SORT PUSHED RANK와의 차이는 단순히 SORT의 유무뿐만이 아니다. Block I/O량도 차이가 난다. 즉 WINDOW SORT PUSHED RANK는 전체 건을 읽은 후에 다 버리고 10건만 출력한다. 이에 반해 WINDOW NOSORT STOPKEY는 11건을 읽고 한 건만 버리므로 부분범위처리의 효율이 좋다. 2번과 4번의 Block I/O량을 비교해보기 바란다. 몇 십 배나 차이가 난다..
참고: Ranking Family 분석함수란 RANK, DENSE_RANK, ROW_NUMBER를 말한다.
결론
WINDOW (SORT) :
-FTS를 사용하거나 인덱스가 적절하지 않은 경우 발생
WINDOW (SORT PUSHED RANK) :
-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생
WINDOW (NOSORT) :
-인덱스가 적절한 경우나, OVER절에 ORDER BY가 없는 Ranking Family를 사용한 경우 발생
WINDOW (NOSORT STOPKEY) :
-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생
“실행계획만 보고도 SQL을 어느 정도 짐작 할 수 있다” 라고 주장하는 튜닝의 고수가 있다. 필자는 때에 따라 그럴 수도, 아닐 수도 있다고 생각한다. 하지만 최소한 분석함수에 관해서는 어느 정도 가능하다고 생각한다. 분석함수의 내부를 이해했다면 충분히 그럴 수 있다. 이제 위의 결론을 이용하여, 실행계획만 보고도 어떤 분석함수를 어떤 상황에서 사용한 것인지 짐작 할 수 있겠는가? 대충 짐작 할 수 있다면 성공한 것이다.
다음 글(2 부)에서 소개될 내용
WINDOW (BUFFER)
WINDOW (BUFFER PUSHED RANK)
WINDOW (CHILD PUSHED RANK)
WINDOW (IN SQL MODEL) SORT
'Oracle > Data Access Pattern' 카테고리의 다른 글
| 분석함수의 실행계획 - 심화과정 (12) | 2011.02.14 |
|---|---|
| 분석함수의 실행계획 - 2부 (12) | 2011.02.10 |
| Index_desc 힌트와 rownum = 1 조합은 안전한가? (12) | 2010.11.09 |
| Bloom Filter와 Group By의 관계 (10) | 2010.09.09 |
| Inlist Iterator를 사용하지 말아야 할 때 (13) | 2010.06.23 |
