'Oracle/Data Access Pattern' 카테고리의 글 목록

분석함수의 성능개선 그 결과는?

Oracle/Data Access Pattern 2011. 2. 18. 01:04

▶분석함수의 비효율을 찾는 방법
▶분석함수의 성능개선 원리
▶분석함수의 실행계획 3부 - 심화과정의 모범답안

이번 글이 분석함수의 실행계획 시리즈의 마지막이다. 1부와 2부에서 분석함수의 실행계획에 대해서 알아보았다. 이 글(3부)은 새로 작성되었으며, 이전 글( 문제 출제용 )은 삭제하지 않고 그대로 두었음을 밝혀둔다. 나중에라도 문제를 풀어볼 사람은 이 글(답안)을 보지 말고, 이전 글을 보기 바란다. 각각의 답안은 오렌지색으로 표시해 두었으므로 쉽게 채점할 수 있을 것이다.

분석함수의 다섯 가지 원리
많은 수의 개발자, DBA, 튜너들은 분석함수를 만나면 식은 땀을 흘린다. 왜냐하면, 분석함수의 비효율을 어떻게 찾을 수 있는지, 또 그 비효율은 어떻게 제거 할 수 있는지 알 수 없기 때문이다. 기껏해야 분석함수의 over절을 만족하는 적절한 인덱스를 생성하는 정도이다. 어쩌면 이런 결과들은 당연하다고 볼 수 있다. 매뉴얼이나 튜닝 책에 분석함수의 비효율을 발견하는 방법과 개선방법에 대한 언급이 별로 없기 때문이다. 이런 어려움을 겪고 있는 여러 사람들의 요구에 의해서 이 글이 만들어졌다. 분석함수의 비효율을 찾고, 성능을 향상시킬 수 있는 다섯 가지 방법을 공개하니, 많은 사람들이 적용하여 더 이상 식은 땀은 흘리지 않기 바란다.

SQL 두 개에 5가지 비효율이 있다. 이것들을 제거하라
이 글에서 설명되는 두 개의 SQL은 길이가 매우 짧다. 그 중 하나는 비효율을 찾아내기 쉽고, 나머지 하나는 어렵다. 두 개의 SQL에는 총 5가지의 튜닝 포인트가 있다. 각각의 포인트는 20점이며 5가지를 모두 맞추는 경우는 100점이 된다. 채점 시 중요한 점은, 독자들이 직접 튜닝한 SQL이 모범답안과 같은지 비교하는 것이 아니라는 것이다. 그것 보다는 비효율을 몇 개 잡아내었는지, 또한 각각의 비효율을 개선시킬 수 있는 방법은 몇 개나 생각했는지를 채점하는 것이다. 이렇게 채점하면, 내가 아는 것은 무엇이고, 모르는 것은 무엇인지 명확히 드러난다. 즉, 무엇을 더 공부해야 하는지 알 수 있다.

다른 사람들은 몇 점 정도 받았나?
몇몇 지인들에게 퀴즈를 내본 결과 100점은 아무도 없었다. 즉 80점이 평균적인 튜너의 수준이라는 이야기 이다. 개발자의 경우는 60점이면 상위 수준이다. 점수에 대해 오해는 하지 말기 바란다. 이 퀴즈로 받은 점수는 분석함수의 성능과 관련된 것일 뿐, 전반적인 SQL 튜닝능력에 대한 점수가 아니다.

이제 실습을 위한 테이블과 인덱스를 생성하자. 실습환경은 Oracle 10g R2, Oracle 11g R1, Oracle 11g R2로 한정한다.

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD CONSTRAINT PK_SALES_T

PRIMARY KEY (PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID) USING INDEX;

개발자가 작성한 문제의 SQL 1

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE TIME_ID = MAX_TIME;

업무설명 : 상품번호 30인 데이터에 대하여 고객별, 채널 별로 가장 최근의 판매량을 나타내시오.

단 고객별, 채널별로 가장 최근의 데이터는 2건 이상일 수 있음.

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

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

| 0 | SELECT STATEMENT | | 12649 |00:00:00.31 | 28337 | |

|* 1 | VIEW | | 12649 |00:00:00.31 | 28337 | |

| 2 | WINDOW BUFFER | | 29282 |00:00:00.27 | 28337 | 1495K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.17 | 28337 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("TIME_ID"="MAX_TIME")

4 - access("PROD_ID"=30)

WHERE 절에 TIME_ID = MAX_TIME 이 존재하는 이유는 고객별, 채널별로 MAX(TIME_ID)에 해당하는 데이터가 두건 이상일 수 있기 때문이다. 이런 경우는 자주 발생한다. 이해를 돕기 위해 비슷한 예를 들어보자. 부서별로 최대급여를 받는 사람들을 출력하고자 할 때, 1번 부서의 최대급여는 1억 원이라고 하자. 그런데 그 부서에서 1억 원(최대급여)을 받는 사람은 강호동, 유재석 둘이라는 이야기 이다.

위의 SQL은 답이 정확하다. 하지만 성능은 비효율이 있어서 별로 신통치 못하다. 이제 문제를 보자. 위 SQL에 여전히 남아있는 비효율을 개선하는 것이 문제이다.

문제1) 위의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 문제점은 두 가지(분석함수의 관점, 일반적인 SQL튜닝의 관점) 이다. 단 인덱스를 추가로 생성하거나 변경해서는 안 된다.

문제 1의 답안

비효율 1은 Sample 답안이다. 점수에 반영되지 않는다.

문제1의 비효율 1 : SALES_T 테이블에 Random Access가 불필요하게 많이 발생한다. 12649 번만 테이블로 액세스 하면 되지만 실제로는 29282 번 액세스함으로써 비효율이 발생되었다.

문제1의 비효율 1의 해결방법 : PK인덱스에 AMOUNT_SOLD가 포함되어 있지 않으므로 Select 절에서 AMOUNT_SOLD를 빼면 인덱스만 액세스 하게 됨. 이때 Rowid를 추가로 Select 해야 한다. 이후에 이미 인덱스에서 Filter된 Rowid로 테이블을 액세스하면 Random Access는 정확히 12649번 만 시도한다. 아래에 SQL이 있으므로 참조하시오.

문제1의 비효율1이 해결된 SQL과 실행계획 제시:

SELECT /*+ LEADING(S) USE_NL(S1) */

S.PROD_ID, S.CUST_ID, S.TIME_ID, S.CHANNEL_ID, S1.AMOUNT_SOLD

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME,

ROWID AS RID

FROM SALES_T T

WHERE PROD_ID = 30 ) S,

SALES_T S1

WHERE S.TIME_ID = S.MAX_TIME

AND S1.ROWID = S.RID;

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

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

| 0 | SELECT STATEMENT | | 1 | 12649 |00:00:02.72 | 11237 | |

| 1 | NESTED LOOPS | | 1 | 12649 |00:00:02.72 | 11237 | |

|* 2 | VIEW | | 1 | 12649 |00:00:00.18 | 118 | |

| 3 | WINDOW BUFFER | | 1 | 29282 |00:00:00.13 | 118 | 1432K (0)|

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

| 5 | TABLE ACCESS BY USER ROWID| SALES_T | 12649 | 12649 |00:00:02.48 | 11119 | |

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

Predicate Information (identified by operation id):

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

2 - filter("S"."TIME_ID"="S"."MAX_TIME")

4 - access("PROD_ID"=30)

이렇게 해서 Sample 답안을 작성해 보았다. 이 방법은 분석함수의 튜닝방법이 아니라 예외적으로 SQL 튜닝방법론에서 언급되었던 방법을 사용하였다. 물론 여기서 페이징처리를 한다면 추가적인 성능향상이 가능하지만 Sample이므로 여기서 멈추기로 한다. 위의 답안은 Sample 이므로 점수에서 빠진다. 또한 이 부분은 분석함수의 튜닝 방법이 아니다. 하지만 앞으로 풀게 될 문제들은 모두 분석함수와 관련된 문제들이다.

문제1의 비효율 2

비효율 1을 제거하니 Random Access는 최소화 되었다. 하지만 실행계획을 보면 쓸데없이 PGA를 1432K만큼 사용하고 있다. 즉 불필요한 WINDOW BUFFER Operation 때문에 sort가 발생하였다.

문제1의 비효율 2 해결방법

고객별 채널별로 최종일자의 데이터를 구하는 것이므로, Rank를 사용하면 적절한 인덱스가 존재하므로 sort를 하지 않는다. Rank는 Row_number와는 다르게 1등이 두 명 이상일 수 있으므로 업무조건을 만족한다.

문제1의 비효율 2이 해결된 SQL과 실행계획 제시.

SELECT /*+ LEADING(A) USE_NL(B) */ A.*, B.AMOUNT_SOLD

FROM ( SELECT /*+ INDEX_DESC(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

RANK() OVER(PARTITION BY CUST_ID, CHANNEL_ID ORDER BY TIME_ID DESC) RNK,

ROWID AS RID

FROM SALES_T T

WHERE PROD_ID = 30 ) A,

SALES_T B

WHERE A.RNK = 1

AND A.RID = B.ROWID;

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

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

| 0 | SELECT STATEMENT | | 1 | 12649 |00:00:00.32 | 11240 | |

| 1 | NESTED LOOPS | | 1 | 12649 |00:00:00.32 | 11240 | |

|* 2 | VIEW | | 1 | 12649 |00:00:00.20 | 120 | |

|* 3 | WINDOW NOSORT | | 1 | 29282 |00:00:00.16 | 120 | |

|* 4 | INDEX RANGE SCAN DESCENDING| PK_SALES_T | 1 | 29282 |00:00:00.06 | 120 | |

| 5 | TABLE ACCESS BY USER ROWID | SALES_T | 12649 | 12649 |00:00:00.05 | 11120 | |

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

Predicate Information (identified by operation id):

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

2 - filter("A"."RNK"=1)

3 - filter(RANK() OVER ( PARTITION BY "CUST_ID","CHANNEL_ID" ORDER BY INTERNAL_FUNCTION("TIME_ID") DESC )<=1)

4 - access("PROD_ID"=30)

이제 문제2를 풀어보자.

개발자가 작성한 문제의 SQL 2

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER ( PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID NULLS FIRST) AS RN,

SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.43 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.43 | 28337 | |

|* 2 | WINDOW SORT PUSHED RANK | | 1 | 29282 |00:00:00.40 | 28337 | 1999K (0)|

| 3 | WINDOW SORT | | 1 | 29282 |00:00:00.30 | 28337 | 1684K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.18 | 28337 | |

|* 5 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.04 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID")

NULLS FIRST)<=10)

5 - access("PROD_ID"=30)

문제2) 위의 SQL 에서 분석함수의 성능상 문제점을 발견하고 개선하시오. 분석함수와 관련된 성능상 문제점은 4가지 이다. 비효율들을 발견하고 이를 모두 개선해야 한다. 단 인덱스를 추가로 생성하거나 변경하면 안 된다.

튜닝의 결과부터 이야기 하자면, Sort량은 3683 K 에서 0 K 로 줄어들었고 Block I/O량은 28337 에서 15 로 줄어들었다. 성능이 몇 배나 개선되었는지 계산하기도 힘들 정도이다. 결과건수가 많아 질수록 이 차이는 점점 더 벌어질 것이다. 성능이 어떻게 개선된 것일까? 지금부터 비효율을 하나씩 벗겨내고 개선방법을 도출해보자.

문제 2의 답안

문제2의 비효율 1

NULLS FIRST를 사용하면 인덱스를 Ascending으로 사용하면 null 값은 가장 마지막에 출력된다. 즉 null 값이 마치 가장 큰 값인 것처럼 저장 되는 것이다. 하지만 NULLS FIRST를 명시하는 순간 sort가 뒤바뀌어 가장 먼저 출력해야 한다. 즉 인덱스를 사용했지만 NULLS FIRST 때문에 추가적인 sort가 필요하다는 이야기 이다. 이러한 문제는 인덱스를 Descending 하게 사용할 때, NULLS LAST를 명시해도 똑같이 발생한다.

문제2의 비효율 1 해결방법

문제의 SQL을 보면 PARTITION BY나 ORDER BY의 컬럼들은 PK 컬럼들이므로 null 일수 없다. 따라서 NULLS FIRST를 삭제해도 무방하다. 다시 작성한 SQL은 다음과 같다.

문제2의 비효율 1이 해결된 SQL과 실행계획 제시

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.45 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.45 | 28337 | |

|* 2 | WINDOW NOSORT | | 1 | 29282 |00:00:00.42 | 28337 | |

| 3 | WINDOW SORT | | 1 | 29282 |00:00:00.32 | 28337 | 1684K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.17 | 28337 | |

|* 5 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

5 - access("PROD_ID"=30)

ROW_NUMBER에 해당하는 Operation이 WINDOW SORT PUSHED RANK에서 WINDOW NOSORT로 바뀌었다. 그 영향으로 ROW_NUMBER는 Sort가 전혀 발생하지 않는다.

문제2의 비효율 2

비효율 1의 튜닝의 결과를 놓고 보면, ROW_NUMBER와 SUM 분석함수의 Operation이 실행계획에 각각 존재한다. 즉 분석함수가 따로 따로 두 번 실행되는 것이다. 분석함수의 종류는 두 개(row_number와 sum)라 하더라도, 분석함수와 관련된 Operation이 한번만 나와야 한다는 말이다. 또한 분석함수 SUM의 order by절에 PROMO_ID가 존재하여 불필요한 sort가 발생하였다.

문제2의 비효율 2 해결방법

이 비효율을 없애면 마치 분석함수가 하나뿐인 것처럼 만들 수 있다. Partition 절과 Order 절을 일치시키면 된다. 아래의 SQL은 Partition 절과 Order 절을 일치시키기 위해 SUM 분석함수에 PARTITION BY 절을 추가해야 한다. 물론 ROW_NUMBER함수에서 Partition By절을 삭제할 수 있지만, 그와 관련된 이야기는 이후 단계에서 논의 되므로, 여기서는 그냥 Partition By절을 추가하기로 한다. 그리고 WINDOW SORT를 유발하는 PROMO_ID도 삭제해야 한다.

문제2의 비효율 2가 해결된 SQL과 실행계획 제시

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.26 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.26 | 28337 | |

| 2 | WINDOW BUFFER | | 1 | 29282 |00:00:00.23 | 28337 | 1495K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.11 | 28337 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

4 - access("PROD_ID"=30)

만약 해결방법 두 가지(Partition By 추가, PROMO_ID 삭제)중 한가지만 적용했다면 10점으로 처리하면 되므로 채점은 가능하다. 아래의 설명부분과 SQL은 답안이 아니며, 원리를 설명하는 부분이다.

Operation 통합의 원리
분석함수 SUM에 Partition By절을 추가하고, order by절에 PROMO_ID를 삭제하니, 예상대로 분석함수 두 개의 Plan이 하나로 합쳐졌다. 여기서 알 수 있는 점은 두 가지 이다. 첫 번째, Partition By와 Order By를 통일 시키면 Operation이 통합되어 분석함수 하나만 실행시키는 결과를 얻는다. 두 번째, WINDOW NOSORT(ROW_NUMBER) + WINDOW BUFFER(SUM) 가 합쳐져서 WINDOW BUFFER가 되었다는 점이다. 좋지 않은 Operation으로 통합된다는 것을 알 수 있다. 기준은 다음과 같다.

WINDOW NOSORT + WINDOW SORT => WINDOW SORT

WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER

WINDOW BUFFER + WINDOW SORT => WINDOW SORT

PROMO_ID를 삭제해도 되는 이유는 ORDER BY에서 모든 PK 컬럼을 사용하였기 때문이다. 즉 PK 컬럼들로 ORDER BY절에서 모두 SORT 되었으므로, 나머지 컬럼으로 아무리 SORT 해보아야 결과는 동일하다는 것이다. Order By절에서 PROMO_ID를 삭제하자 WINDOW SORT가 WINDOW BUFFER 로 바뀌었다. 이에 따라 PGA사용량이 1747K에서 1495K 약간 줄어들었다.

만약 Partition By만 추가하고 PROMO_ID를 삭제하지 않으면 아래와 같이 sort의 비효율이 발생한다.

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.33 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.33 | 28337 | |

| 2 | WINDOW SORT | | 1 | 29282 |00:00:00.30 | 28337 | 1747K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.17 | 28337 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

4 - access("PROD_ID"=30)

Order By절이 다르지만, 통합이 가능한 이유
PROMO_ID를 추가하자, WINDOW SORT가 발생하여 PGA 사용량이 늘어났다. 분석함수 SUM의 ORDER BY절을 보면 PROMO_ID가 있는데 ROW_NUMBER 분석함수에는 PROMO_ID가 없다. ORDER BY는 완벽히 같지 않아도 Operation이 통합 될 수 있다는 점을 알 수 있다. ORDER BY의 마지막 항목이 달라도 Operation이 통합 될 수 있는 원리는 간단하다. Sort 작업을 A + B + C로 이미 했다면 Sort를 A + B 로 다시 해야 할 필요가 없기 때문이다. 즉 A + B + C로 Sort 하는 분석함수 기준으로 A + B로 Sort 하는 분석함수가 통합된다는 의미이다.

오해하지 말 것

서로 다른 종류의 분석함수를 실행했을 때, Partition By의 컬럼과 Order By의 컬럼을 각각 똑같이 맞추어야 하는 것은 아니다. Partition By나 Order By의 컬럼을 각각 맞추는 것이 아니라, OVER절 전체의 컬럼을 일치시키면 된다. 아래의 SQL이 좋은 예제이다.

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER (PARTITION BY PROD_ID ORDER BY CUST_ID, CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID, CUST_ID ORDER BY CHANNEL_ID,TIME_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.32 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.32 | 28337 | |

| 2 | WINDOW BUFFER | | 1 | 29282 |00:00:00.28 | 28337 | 1495K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.14 | 28337 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.04 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

4 - access("PROD_ID"=30)

위의 SQL을 보면 분석함수 두 개의 Partition By의 컬럼과 Order By 컬럼이 모두 다르다. 하지만 실행계획상의 operation은 하나로 통합되었다. 즉 OVER절을 바라볼 때 Partition By나 Order By를 삭제하고 컬럼들만 남긴다면, operation이 통합이 될지, 아닐지 판단할 수 있다. 예컨대, over절의 컬럼은 두 개의 분석함수 모두 PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID 로 똑같으므로 operation이 통합된다.

중요한 것은 컬럼의 순서이다. 즉 Partition By나 Order By를 삭제하고 순수하게 over절의 컬럼들만 남겼을 때, 컬럼의 순서가 같아야 operation이 통합된다. 컬럼의 순서가 다르면 통합되지 않는다. 위의 분석함수 둘은 over절의 컬럼순서가 PROD_ID + CUST_ID + CHANNEL_ID + TIME_ID로 같음을 알 수 있다. OVER절 전체의 컬럼순서가 같다면, 컬럼의 개수와는 상관없이 통합이 가능하다. 예를 들어, 분석함수를 두 개 사용하고 컬럼순서가 하나는 A+B+C 이고 다른 하나는 A+B 라면, 컬럼순서가 동일하므로 통합이 가능하다. 하지만 A+B+C 와 A+C의 조합은 컬럼순서가 다르므로 통합이 불가능하다.

문제2의 비효율 3

비효율 2의 튜닝결과를 보면 Ranking family를 사용하고, 인라인뷰 외부에서 분석함수를 Filter로 처리했음에도 불구하고 STOPKEY가 작동하지 않는다. 이에 따라 부분범위처리가 되지 못하고 불필요한 데이터를 모두 Scan해야 한다.

문제2의 비효율 3의 해결방법

STOPKEY가 작동되도록 하려면 ROW_NUMBER의 OVER절에서 불필요한 PARTITION BY PROD_ID를 제거하면 된다. PROD_ID는 이미 WHERE 절의 Equal 조건에 의하여 상수화 되었기 때문에 PARTITION BY는 필요 없다.

문제2의 비효율 3이 해결된 SQL과 실행계획 제시

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER (PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.16 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.16 | 28337 | |

|* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 |00:00:00.16 | 28337 | |

| 3 | WINDOW BUFFER | | 1 | 11 |00:00:00.16 | 28337 | 1495K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.11 | 28337 | |

|* 5 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

5 - access("PROD_ID"=30)

아래의 설명부분과 SQL은 답안이 아니며 원리를 설명하는 부분이다.

Ranking family에서 불필요한 Partition By를 삭제하자 WINDOW NOSORT STOPKEY가 발생되었다. 하지만 실행계획을 자세히 보면 비효율이 존재한다. 즉 WINDOW BUFFER 때문에 29282건을 모두 읽은 후에 STOPKEY가 발생되었다. 처음부터 11건만 읽는 방법은 없는 것일까? 그렇다고 위의 SQL에서 분석함수 SUM의 PARTITION BY도 삭제한다면 비효율 2의 실행계획으로 돌아가 버린다. 비효율 2에서 이미 설명했던 원칙(좋지 않은 Operation으로 통합되는 원칙) 때문에 WINDOW NOSORT STOPKEY가 WINDOW BUFFER에 묻혀버리는 것이다.

만약 WINDOW BUFFER(분석함수 SUM)가 없다면 WINDOW NOSORT STOPKEY가 완벽히 동작한다. 아래의 SQL로 증명할 수 있다.

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.01 | 15 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.01 | 15 | |

|* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 |00:00:00.01 | 15 | |

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 11 |00:00:00.01 | 15 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 11 |00:00:00.01 | 4 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

4 - access("PROD_ID"=30)

분석함수 SUM만 제거한다면 STOPKEY가 비효율 없이 작동하여 정확히 11건만 읽었다. 위의 실행통계와 비효율 3이 튜닝된 SQL의 실행통계를 비교해보라. Buffers와 Used-Mem 항목을 본다면 비교할 수 없을 정도이다. 또한 WINDOW STOPKEY의 활용범위는 ROWNUM 보다 넓다. 물론 ROW_NUMBER는 ROWNUM 처럼 사용 할 수도 있다. 하지만, RANK나 DENSE_RANK의 경우로 본다면 추가적인 활용법이 있다. 예를 들어, 학교에서 수학성적으로 전교석차 2등 까지 출력한다고 치자. 그러면 1등과 2등이 각각 두 명 이상일 수 있다. 이런 경우는 ROWNUM으로는 구현할 수 없다. RANK나 DENSE_RANK가 WINDOW NOSORT STOPKEY로 성능을 충족시켜면서, 업무요구사항을 만족시키는 유일한 방법이다.

Sort량 2K vs 1999K의 비밀

비효율 3에서는 Partition By를 삭제하여 Sort가 전혀 발생하지 않는 경우를 보았다. 만약 Sort를 동반하는 경우라면, Partition By를 삭제함에 따라 Sort의 일량에 어떤 영향을 끼칠까? 아래의 SQL을 보자.

SELECT *

FROM (SELECT /*+ INDEX(T PK_SALES_T) */

PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER () OVER (ORDER BY CUST_ID, CHANNEL_ID, TIME_ID NULLS FIRST) AS RN,

SUM (AMOUNT_SOLD) OVER (ORDER BY CUST_ID, CHANNEL_ID, TIME_ID, PROMO_ID) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30)

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.34 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.34 | 28337 | |

|* 2 | WINDOW SORT PUSHED RANK | | 1 | 11 |00:00:00.34 | 28337 | 2048 (0)|

| 3 | WINDOW SORT | | 1 | 29282 |00:00:00.30 | 28337 | 1684K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.18 | 28337 | |

|* 5 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.04 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID") NULLS FIRST)<=10)

5 - access("PROD_ID"=30)

위의 SQL은 개발자가 작성한 원본 SQL에서 ROW_NUMBER 함수의 Partition By절을 삭제하고 실행한 것이다. 이 경우는 over절의 NULLS FIRST 때문에 Sort가 발생되는 경우이다. 이 때에도 Sort가 최소화 되어 PGA 사용량은 2K에 불과하다. 개발자가 작성한 원본 실행통계의 Sort량이 1999K임을 감안하면 Partition By의 존재유무는 성능에 지대한 영향을 끼친다고 할 수 있다. Sort량이 이렇게 큰 차이가 나는 이유는 파티션을 삭제한 효과 + RN <= 10 조건 때문이다. 그 filter 조건에 의해서 10개의 배열만 관리하면 되므로 PGA 사용량은 최소화 된다. WINDOW SORT PUSHED RANK의 이러한 성격은 인덱스를 사용하지 않는 경우에도 동일하게 나타난다. 위의 SQL에서 힌트를 FULL(T)로 바꾸고 실행하여도 PGA 사용량은 동일하다.

위의 SQL 처럼 ROW_NUMBER 함수의 NULLS FIRST 보다 Partition By절을 먼저 삭제한 사람은 이 원리를 답안으로 사용해도 무방하다. 하지만 비효율 3에서 설명되었던, Partition By 삭제에 의한 NOSORT STOPKEY 유도 원리는 WINDOW SORT PUSHED RANK와 성격이 다르므로 반드시 알아두어야 한다.

문제2의 비효율 4

진퇴양란, 포기할 것인가?

비효율 3의 튜닝결과를 보면 STOPKEY를 발생시키려고 ROW_NUMBER의 Partition By를 제거하니 Operation이 두 개로 분리되어 다시 비효율이 발생하였다. 그렇다고 분석함수 SUM을 삭제하자니 업무요구사항이 만족되지 않는다. 어떻게든 STOPKEY를 발생시켜서 29282건을 모두 읽는 비효율을 제거해야 한다.

분석함수 SUM을 삭제하는 것은 답이 아니다

보통 이런 경우(진퇴양란)에서는 둘 중에 한가지를 선택한다. 튜닝을 포기하거나 장애물을 제거한다. 열정이 있는 개발자들은 장애물을 제거할 것이다. 분석함수 SUM을 삭제하고, 조회화면에서 프로그램 스크립트를 작성하여 분석함수 SUM의 로직을 대신한다. 물론 이렇게 하는 것은 목적을 달성하는 것이므로, 포기하는 것보다는 만족스럽다. 하지만 나는 포기하지도 말고, 프로그래밍 언어의 도움도 받지 말라고 주장한다. SQL이 아닌 다른 언어의 도움으로 비효율을 해결한 것은 이 문제의 정답이 아니며, SQL만으로 해결할 수 있다.

문제2의 비효율 4 해결방법

일부 개발자는 분석함수 SUM을 제거하고 프로그래밍 언어의 도움을 받아 해결하였다. 하지만 우리는 분석함수 SUM을 물리적으로 삭제하는 것이 아니라 실행계획상에서만 삭제하고자 한다. 방법은 SUM/MIN/MAX/AVG 등의 분석함수에 WINDOW의 범위를 명시적으로 작성하면 된다. 물론 범위를 명시적으로 작성해도 답은 동일하다.

문제2의 비효율 4가 해결된 SQL과 실행계획 제시

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ) AS RN,

SUM(AMOUNT_SOLD) OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.01 | 15 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.01 | 15 | |

|* 2 | WINDOW NOSORT STOPKEY | | 1 | 10 |00:00:00.01 | 15 | |

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 11 |00:00:00.01 | 15 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 11 |00:00:00.01 | 4 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID","CHANNEL_ID","TIME_ID")<=10)

4 - access("PROD_ID"=30)

분석함수 SUM을 제거한 것은 아니지만, WINDOW의 범위를 주어 동일한 효과를 얻었다. 정확히 11건만 읽었으므로, 깔끔하게 모든 문제가 정리되었다. Window의 범위를 명시적으로 작성하는 것이 얼마나 중요한지 알 수 있다.

여기까지가 답안이다. 이제 어떻게 된 것인지 알아보기 위해 분석함수 ROW_NUMBER를 삭제해 보자.

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

SUM(AMOUNT_SOLD) OVER (ORDER BY CUST_ID,CHANNEL_ID,TIME_ID

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 ) ;

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

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

| 0 | SELECT STATEMENT | | 1 | 29282 |00:00:00.36 | 28340 | |

| 1 | VIEW | | 1 | 29282 |00:00:00.36 | 28340 | |

| 2 | WINDOW NOSORT | | 1 | 29282 |00:00:00.30 | 28340 | |

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.20 | 28340 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 121 | |

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

Predicate Information (identified by operation id):

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

4 - access("PROD_ID"=30)

그룹분석함수와 Ranking Family를 같이 사용해도 STOPKEY가 발생한다

그룹분석함수임에도 SORT가 전혀 발생하지 않았다. 즉 SUM/MIN/MAX/AVG 등의 분석함수에 WINDOW의 범위를 지정해주면 WINDOW BUFFER가 WINDOW NOSORT로 바뀐다는 이야기 이다. 이것은 의미 있는 발견이다. 왜냐하면 이때 까지는 SUM/MIN/MAX/AVG 등의 분석함수 때문에 적절한 인덱스를 사용하였지만 WINDOW BUFFER가 발생하여 STOPKEY를 발생시키지 못했다. 그런데 이제 WINDOW NOSORT가 가능해짐으로 Ranking Family의 STOPKEY가 정상적으로 작동하기 때문이다.

이제 적절한 인덱스가 있고, SUM등의 그룹분석함수를 사용한다면, 다음과 같은 결론을 내릴 수 있다.

“SUM등의 그룹분석함수를 사용한다면, WINDOW의 범위를 지정해주어야 한다. 그렇게 한다면 Sort가 전혀 발생하지 않을 뿐만 아니라, Ranking Family 분석함수를 rownum 처럼 사용할 때, STOPKEY를 발생시켜 성능이 개선된다.”

분석함수를 만날 때의 자세
2번 문제의 원본 SQL는 매우 짧은 코드이지만, 비효율은 4개나 된다. 튜닝을 완료했다고 자신하는 순간이라도, 마지막 남은 분석함수의 비효율 하나 때문에 SQL의 성능향상은 도루묵이 될 수 있다. 이런 사실은 SQL을 튜닝 할 때, 분석함수를 만나면 안테나를 세워야 하는 이유가 된다.

미래지향

다섯 가지 비효율에 대해 80점 혹은 그 이상을 받은 사람은 분석함수에 관한 튜닝은 상당한 수준에 있다고 생각한다. 반대로, 80점이 안 된다고 실망하지 말자. 왜냐하면 퀴즈에서 실패했더라도, 실무튜닝에서 100점을 받으면 그만이다. 물론 이렇게 되려면, SQL과 실행계획을 보고 비효율을 읽어내는 능력이 필요하다.

마지막으로 다섯 가지의 원리로 이 글 전부를 정리 해보자. 다섯 가지의 원리는 대부분 아래의 구조로 되어있다.

1) 소제목
2) ~ 를 하라.
3) 그렇게 하면 ~ 효과가 있다.

분석함수의 성능향상 원리

1. min/max 분석함수를 Ranking family로 대체하는 원리

적절한 인덱스를 사용하면서, 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking family를 사용하라. 그렇게 하면 WINDOW BUFFER를 WINDOW NOSORT로 바꾸어 sort를 방지한다.

2. sort의 기준을 유지하는 원리
적절한 인덱스가 존재하는 경우, 분석함수의 ORDER BY절에 NULL FIRST나 LAST를 삭제할 수 있는지 검토하라. 그렇게 하면 인덱스 사용에 의한 Sort 기준이 유지되므로, 추가적인 sort를 방지한다. 주의사항은 NULL FIRST나 LAST를 사용한다고 해서 항상 Sort의 기준이 바뀌는 것은 아니며, 아래의 경우만 해당한다.

ex1) Index ASC로 사용 + 분석함수의 ORDER BY절에 NULL FIRST 는 추가적인 sort발생

ex2) Index DESC로 사용 + 분석함수의 ORDER BY절에 NULL LAST 는 추가적인 sort발생

3. 여러 개의 분석함수를 하나의 Operation으로 통합하는 원리

적절한 인덱스가 존재하고, 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By와 Order By절을 일치시켜라. 그렇게 하면 여러 개의 OPERATION이 통합된다. 다시 말해, 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축시킨다. 이때 ORDER BY는 완전히 같지 않아도 Operation은 통합될 수 있다. 단 아래와 같이 좋지 않은 Operation 을 기준으로 통합된다.

ex1) WINDOW NOSORT + WINDOW SORT => WINDOW SORT

ex2) WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER

ex3) WINDOW BUFFER + WINDOW SORT => WINDOW SORT

ORDER BY가 다를 때, Operation이 통합될 수 있는 조건이 있다. 그 세부내용은 본문을 참조하라.

4. Ranking Family를 이용한 TOP SQL에서 Sort 최소화의 원리

Ranking Family 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용했을 때, 불필요한 Partition By절을 삭제하라. 그렇게 하면 두 가지 경우에 성능이 향상된다. 주의사항은 Partition By절 전체를 제거해야 한다는 것이다. 만약 Partition By절에 컬럼이 하나라도 있으면 Sort가 대량으로 발생된다. 각각의 파티션 값마다 sort를 해야 하므로 어쩔 수 없는 일이다.

첫 번째, 적절한 인덱스가 없어서 FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST나 LAST등의 원인으로 sort가 발생될 때 이다. 이럴 경우 Partition By을 제거하면 필요한 개수 + 1 만큼만 sort가 발생하므로 성능이 향상된다. 하지만 Rownum과는 달리 STOPKEY가 발생되지 않으므로 비효율은 존재한다. 이 때 발생되는 Operation은 WINDOW SORT PUSHED RANK 이다.

두 번째, 적절한 인덱스가 있어서 sort가 발생되지 않는 경우이다. 이 경우는 첫 번째의 경우와는 달리 sort가 전혀 발생하지 않으며, Rownum 처럼 STOPKEY를 발생시켜 부분범위 처리의 효과를 얻는다. 다시 말해, 분석함수를 Rownum 처럼 사용할 수 있다. 이 때 발생되는 Operation은 WINDOW NOSORT STOPKEY 이다. 이 기능은 10g R2에서 추가 되었다.

WINDOW NOSORT STOPKEY는 주의사항이 있다. 그룹분석함수의 OVER절과 Ranking Family의 OVER절의 컬럼순서가 다르면 Operation이 하나로 통합되지 못한다. 이 경우는 성능이 저하된다. 왜냐하면 WINDOW SORT 혹은 WINDOW BUFFER 가 먼저 실행되고 그 후에 WINDOW NOSORT STOPKEY가 발생하기 때문에 Block I/O나 sort량을 감소시키지 못한다. 이런 비효율 때문에 분석함수 여러 개를 하나의 Operation으로 통합하는 원리(원리 3번)의 역할이 중요한 것이다. 이와 관련된 예제는 비효율3이 해결된 SQL과 실행계획을 참조하기 바란다.

5. 그룹분석함수의 NO-SORT 원리

이 원리는 장점이 두 가지 이다. Sort를 방지하고, STOPKEY를 작동시킨다.

적절한 인덱스가 존재하고, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정하라. 그렇게 하면 WINDOW BUFFER Operation을 WINDOW NOSORT로 바꾸어 불필요한 sort를 방지한다. 물론 여기서 이야기 하는 그룹분석함수는 현재 row 까지의 누적집계(Cumulative total) 이다.

더욱 좋은 것은, Ranking family를 Rownum 처럼 사용하고 있다면, STOPKEY를 사용할 수 있게 한다. 즉 Group 분석함수를 사용하면, WINDOW BUFFER가 발생하고, sort가 발생되는데, 이 때문에 STOPKEY를 작동 시킬 수 없다. 그런데 WINDOW의 범위를 지정하여 WINDOW NOSORT로 바꾸면 sort가 발생하지 않기 때문에 STOPKEY가 작동한다. WINDOW의 범위를 지정하는 방법은 아래와 같다. 범위를 생략해도 결과는 같지만, 성능이 저하된다.

ex) SUM(AMOUNT_SOLD) OVER(ORDER BY CUST_ID,CHANNEL_ID,TIME_ID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

참고: Ranking family를 사용하여 WINDOW STOPKEY가 발생하는 경우는 SORT가 발생하지 않는 경우(NOSORT)뿐이다. 그룹분석함수에 의해서 추가적인 SORT를 해야 한다면, 전체범위로 처리됨으로 STOPKEY가 발생하지 않는다.

위의 다섯 가지 원리가 적용되지 않은 것이 각각의 비효율이다. 즉 위의 다섯 가지 원리를 비효율의 해결방법으로 작성한 사람은 100점이다.

자동튜닝기능이 아쉬워

이 글의 결론을 보고 많은 이들이 두 가지를 아쉬워할 것이다. 예컨대, 분석함수 SUM에 WINDOW의 범위를 생략했더라도 자동으로 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 추가해 준다면 얼마나 좋을까? 불필요한 Partition By절을 수동으로 삭제해야 하는 것 또한 마찬가지이다. 아직까지 옵티마이저는 사람을 따라올 수 없다. 사람의 손길을 여전히 필요로 한다. 따라서 전문 개발자라면 Partition By나 Order By 혹은 Window의 범위지정 등의 코딩을 할 때 내부적으로 어떤 일이 발생하는지 알아야 한다. 그냥 코딩을 하는 사람과 그것이 성능상 어떤 의미를 지니는지 알고 개발하는 사람과는 차이가 크다.

이상으로 분석함수의 내부에 대해 3회에 걸쳐 알아보았다. 1부/2부의 내용과 위의 결론 다섯 가지를 완벽히 이해한다면, 분석함수를 사용한 SQL과 실행계획, 그리고 인덱스 구조만 보고도 순간적으로 비효율을 잡아낼 수 있다. 분석함수의 비효율을 만나면 번개가 치듯이 생각이 번쩍 들것이다. 실행계획을 보고 비효율의 냄새를 맡을 줄 아는 능력은 중요하다.

마지막으로, 분석함수의 실행계획에 관한 글을 의뢰한 개발자에게 감사를 표한다. 아무래도 내가 그 개발자에게 맥주를 사는 것이 더 좋을 것 같다. 왜냐하면 그 사람 덕분에 좋은 것(Window의 범위 지정의 효과)을 발견할 수 있었기 때문이다. 두서 없는 긴 글을 읽어주신 독자 여러분들께 감사 드린다.

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

이번 글은 여기까지 이다. 아래의 글은 이전에 올렸던 글의 정답보다 더 좋은 것이 발견되었음을 알리는 것이다.

분석함수의 실행계획을 정리하면서 Group(SUM/MIN/MAX/AVG) 분석함수에 WINDOW의 범위를 명시적으로 작성하는 것이 얼마나 중요한지 필자도 깨닫게 되었다. 예컨대, Pagination과 분석함수의 위험한 조합 글에서 도출된 정답보다 더 좋은 것은 WINDOW의 범위를 명시하는 것이다. 따라서 분석함수를 밖으로 빼는 것보다, 아래와 같이 window의 범위를 지정해 주는 것이 더 유리하다.

SELECT *

FROM (SELECT s.*, ROWNUM rnum

FROM (SELECT /*+ INDEX(S PK_SALES_T) */

s.prod_id, s.cust_id, s.channel_id, s.time_id, amount_sold,

sum (amount_sold) OVER (PARTITION BY s.cust_id ORDER BY s.channel_id, s.time_id

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS sum_amt

FROM sales_t s

WHERE s.prod_id = :v_prod_id --> 30 대입

ORDER BY s.cust_id, s.channel_id, s.time_id

) s

WHERE ROWNUM <= :v_max_row --> 20 대입

)

WHERE rnum >= :v_min_row ; --> 1 대입

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

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

| 0 | SELECT STATEMENT | | 1 | 20 |00:00:00.01 | 24 | |

|* 1 | VIEW | | 1 | 20 |00:00:00.01 | 24 | |

|* 2 | COUNT STOPKEY | | 1 | 20 |00:00:00.01 | 24 | |

| 3 | VIEW | | 1 | 20 |00:00:00.01 | 24 | |

| 4 | WINDOW NOSORT | | 1 | 20 |00:00:00.01 | 24 | |

| 5 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 20 |00:00:00.01 | 24 | |

|* 6 | INDEX RANGE SCAN | PK_SALES_T | 1 | 20 |00:00:00.01 | 4 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RNUM">=:V_MIN_ROW)

2 - filter(ROWNUM<=:V_MAX_ROW)

6 - access("S"."PROD_ID"=:V_PROD_ID)

20건에 대해서 조차 SORT가 전혀 발생하지 않는다. 따라서 Pagination과 분석함수의 위험한 조합 이라는 글은 수정되어야만 한다. 내가 작성한 글을 내가 뒤집었다. 내 것이라도 비판적으로 바라보아야 한다. 최종이론이란 없는 것이므로

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

PS: 몇분이 구두로 혹은 메일로 괴상한 질문을 해서 답글을 남긴다.

Question: 어떻게 이런 책과 글을 쓰며, 주제는 어떻게 잡으며, 연구는 어떻게 하는가?

--> 이상한 질문이다. 내가 용빼는 재주라도 있나?

Answer:

필자의 머리가 좋아서 글을 쓸 수 있는 게 아니다. 오히려 그 반대이다. 똑똑한 다른 사람이라면, 내가 연구랍시고 소비한 시간의 절반만 투자해도 이 글을 썼을 것이다. 나는 다른 사람과 달리 영민하지 못하여 하찮은 문제에도 많은 시간을 소모하며, 실패하면 다시 조금씩 앞으로 전진 할 수 밖에 없다. 그렇다고 다른 재주가 있느냐 하면 그렇지 않다. 방향감각이 둔하여 연구가 막다른 길로 빠진 적이 여러 번일 뿐만 아니라 남들이 가지고 있는 창의성, 순발력, 영어실력, 문장력, 준수한 외모를 나는 가지지 못했고, 심지어 노래방에서는 박자를 못 맞추는 절대음감까지 그 어느 것도 잘하는 게 없다. 아무리 생각해도 세상은 불공평 한 듯 하다.

내가 할 수 있는 일은 나의 유일한 단점이자 장점을 살리는 수뿐이다. 실수를 하더라도, 거북이 근성으로 시간이 나는 대로 조금씩 연구를 하여 이런 글을 쓰는 수 밖에 없다. 글의 주제와 관련된 것도 특별한 것은 없다. 소주한잔 마시면서 어떤 글을 쓸지 동료들과 소통하거나, 독자들이 필요한 글을 나에게 요청한다. 내가 직접 글감을 고른 적은 5번중의 한번 정도이다. 글의 주제가 정해지면 무작정 글을 쓴다. 글을 쓰다가 모르는 것이 있으면(모르는 게 많다) 그에 맞춰 test(연구)를 한다. 궁금증이 해결되면 다시 글을 쓴다. 이것이 끝이다.

이 글도 위에서 설명한 절차와 똑같이 진행되었다. 단언하건대, 분석함수의 성능에 대해 이 문서만큼 깊이 다룬 책이나 매뉴얼은 지금까지 없다. 이런 일이 가능한 이유는 내가 아둔하고, 모르는 게 많은 거북이라서 그런 것이 아닐까?

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 실행계획 - 심화과정 (12)	2011.02.14
분석함수의 실행계획 - 2부 (12)	2011.02.10
분석함수의 실행계획 - 1부 (12)	2011.02.07
Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Bloom Filter와 Group By의 관계 (10)	2010.09.09

Posted by extremedb

,

분석함수의 실행계획 - 심화과정

Oracle/Data Access Pattern 2011. 2. 14. 17:18

필자가 제안한 SQL 튜닝방법론을 읽어 보았다면 다음을 기억할 것이다.

“SQL 튜닝방법론은 온라인 SQL 중 90% 만 해결할 수 있으며, 나머지 10%는 그때 그때 마다 다르게(On the fly 모드) 처리된다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치 이거나 실험과 연구의 결과로 알아내는 것들이다.”

이번 글은 방법론에서 다룰 수 없는, 나머지 10%에 해당하는 경험치와 연구의 결과이다.

분석함수의 나머지 10%

오늘은 SQL 튜닝방법론에서 언급한 나머지 10%를 만났을 때 어떻게 해야 하는지 알아보자. 이번 글이 분석함수의 실행계획 시리즈의 마지막이다. 1부와 2부에서 분석함수의 실행계획에 대해서 알아보았다. 하지만 두 개의 글로도 분석함수와 관련된 성능이슈를 모두 알 수는 없다. 왜냐하면 분석함수와 관련된 세밀한 튜닝포인트가 더 있기 때문이다. 이번에 다루게 될 실전 튜닝은 단순히 적절한 인덱스를 생성하여 비효율을 없애는 것이 아니다. 적절한 인덱스를 생성하는 것은 1부와 2부만 이해해도 충분하며, 이 글에서는 1부와 2부에서 언급하지 않은 것들을 설명하게 된다. 따라서 이 글에서 제시된 문제를 해결하지 못하면 분석함수를 튜닝 할 수 없는 경우를 많이 만날 것이다.

퀴즈는 SQL 두 개이며, 5가지 비효율을 개선해야 한다

이번에는 실전에서 발생할 수 있는 비효율에 대해서 두 가지 관점으로 논의한다. 분석함수를 사용할 때, 실행계획을 보고 비효율을 잡아내는 능력을 향상시키고, 실제로 SQL튜닝을 해보는 것이다. 반드시 문제를 직접 풀어보기 바란다. 당신이 튜너의 입장이 되어, 성능이 좋지 않은 SQL을 두 개 받았다고 가정하고 튜닝문제를 풀어보기 바란다. 두 가지 SQL 중 하나는 쉽고 나머지 하나는 어렵다. 두 개의 SQL에는 5가지의 튜닝 포인트가 있다. 각각의 포인트는 20점이며 5가지를 모두 맞추는 경우는 100점이 된다. 채점 시 중요한 점은 SQL을 맞추는 것이 아니라 비효율을 몇 개 잡아내었는지, 또한 각각의 비효율을 개선시킬 수 있는 방법은 몇 개나 생각했는지를 채점하는 것이다.

이제 실습을 위한 테이블과 인덱스를 생성하자.

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

ALTER TABLE SALES_T ADD CONSTRAINT PK_SALES_T

PRIMARY KEY (PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID) USING INDEX;

개발자가 작성한 문제의 SQL 1

SELECT *

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE TIME_ID = MAX_TIME;

업무설명 : 상품번호 30인 데이터에 대하여 고객별, 채널 별로 가장 최근의 판매량을 나타내시오.

단 고객별, 채널별로 가장 최근의 데이터는 2건 이상일 수 있음.

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

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

| 0 | SELECT STATEMENT | | 12649 |00:00:00.31 | 28337 | |

|* 1 | VIEW | | 12649 |00:00:00.31 | 28337 | |

| 2 | WINDOW BUFFER | | 29282 |00:00:00.27 | 28337 | 1495K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| SALES_T | 29282 |00:00:00.17 | 28337 | |

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 29282 |00:00:00.03 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("TIME_ID"="MAX_TIME")

4 - access("PROD_ID"=30)

WHERE 절에 TIME_ID = MAX_TIME 이 존재하는 이유는 고객별, 채널별로 MAX(TIME_ID)에 해당하는 데이터가 두건 이상일 수 있기 때문이다. 이런 경우는 자주 발생한다. 이해를 돕기 위해 비슷한 예를 들어보자. 부서별로 최대급여를 받는 사람들을 출력하고자 할 때, 1번 부서의 최대급여는 1억 원이라고 하자. 그런데 그 부서에서 1억 원(최대급여)을 받는 사람은 강호동, 유재석 둘이라는 이야기 이다.

위의 SQL은 답이 정확하다. 하지만 성능은 비효율이 있어서 별로 신통치 못하다. 이제 문제를 보자. 위 SQL의 비효율을 개선하는 것이 문제이다.

문제1) 위의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 문제점은 두 가지(분석함수의 관점, 일반적인 SQL튜닝의 관점) 이다. 단 인덱스를 추가로 생성하거나 변경해서는 안 된다.

아래처럼 정답을 제시하면 된다.

문제1의 비효율 1 : 구체적인 설명

문제1의 비효율 1의 해결방법 : 구체적인 설명

문제1의 비효율 1이 해결된 SQL과 실행계획 제시

문제1의 비효율 2 : 구체적인 설명

문제1의 비효율 2의 해결방법 : 구체적인 설명

문제1의 비효율 2이 해결된 SQL과 실행계획 제시

이제 실제로 답안을 작성해보자.(답안 Sample)

문제1의 비효율 1 : SALES_T 테이블에 Random Access가 불필요하게 많이 발생한다. 12649 번만 테이블로 액세스 하면 되지만 실제로는 29282 번 액세스함으로써 비효율이 발생되었다.

문제1의 비효율 1의 해결방법 : PK인덱스에 AMOUNT_SOLD가 포함되어 있지 않으므로 Select 절에서 AMOUNT_SOLD를 빼면 인덱스만 액세스 하게 됨. 이때 Rowid를 추가로 Select 해야 한다. 이후에 이미 인덱스에서 Filter된 Rowid로 테이블을 액세스하면 Random Access는 정확히 12649번 만 시도한다. 아래에 SQL이 있으므로 참조하시오.

문제1의 비효율1이 해결된 SQL과 실행계획 제시:

SELECT /*+ LEADING(S) USE_NL(S1) */

S.PROD_ID, S.CUST_ID, S.TIME_ID, S.CHANNEL_ID, S1.AMOUNT_SOLD

FROM ( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,

MAX(TIME_ID) OVER( PARTITION BY CUST_ID ,CHANNEL_ID ) AS MAX_TIME,

ROWID AS RID

FROM SALES_T T

WHERE PROD_ID = 30 ) S,

SALES_T S1

WHERE S.TIME_ID = S.MAX_TIME

AND S1.ROWID = S.RID;

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

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

| 0 | SELECT STATEMENT | | 1 | 12649 |00:00:02.72 | 11237 | |

| 1 | NESTED LOOPS | | 1 | 12649 |00:00:02.72 | 11237 | |

|* 2 | VIEW | | 1 | 12649 |00:00:00.18 | 118 | |

| 3 | WINDOW BUFFER | | 1 | 29282 |00:00:00.13 | 118 | 1432K (0)|

|* 4 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.03 | 118 | |

| 5 | TABLE ACCESS BY USER ROWID| SALES_T | 12649 | 12649 |00:00:02.48 | 11119 | |

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

Predicate Information (identified by operation id):

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

2 - filter("S"."TIME_ID"="S"."MAX_TIME")

4 - access("PROD_ID"=30)

이렇게 해서 Sample 답안을 작성해 보았다. 이 방법은 분석함수의 튜닝방법이 아니라 예외적으로 SQL 튜닝방법론에서 언급되었던 방법을 사용하였다. 물론 여기서 페이징처리를 한다면 추가적인 성능향상이 가능하지만 Sample이므로 여기서 멈추기로 한다. 위의 답안은 Sample 이므로 점수에서 빠진다. 또한 이 부분은 분석함수의 튜닝 방법이 아니다. 하지만 앞으로 풀게 될 문제들은 모두 분석함수와 관련된 문제들이다.

이제 시작해보자.

문제1에서 분석함수와 관련된 비효율이 하나 있다. 이와 관련된 비효율, 해결방법, 그리고 해결된 SQL과 실행계획을 제시해야 한다.

문제1의 비효율 2 : 구체적인 설명

문제1의 비효율 2 해결방법 : 구체적인 설명

문제1의 비효율 2가 해결된 SQL과 실행계획 제시

이제 좀더 어려운 문제를 풀어보자. 아래의 문제를 100% 맞춘 사람은 아무도 없었다.

문제2) 아래의 SQL 에서 성능상 문제점을 발견하고 개선하시오. 성능상 문제점은 4가지 이다. 4가지의 비효율을 발견하고 이를 모두 개선해야 한다. 단 인덱스를 추가로 생성하거나 변경하면 안 된다.

SELECT *

FROM

( SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

ROW_NUMBER() OVER ( PARTITION BY PROD_ID ORDER BY CUST_ID,CHANNEL_ID,TIME_ID NULLS FIRST) AS RN,

SUM(AMOUNT_SOLD) OVER ( ORDER BY CUST_ID,CHANNEL_ID,TIME_ID,PROMO_ID ) AS SUM_AMT

FROM SALES_T T

WHERE PROD_ID = 30 )

WHERE RN <= 10 ;

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

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

| 0 | SELECT STATEMENT | | 1 | 10 |00:00:00.43 | 28337 | |

|* 1 | VIEW | | 1 | 10 |00:00:00.43 | 28337 | |

|* 2 | WINDOW SORT PUSHED RANK | | 1 | 29282 |00:00:00.40 | 28337 | 1999K (0)|

| 3 | WINDOW SORT | | 1 | 29282 |00:00:00.30 | 28337 | 1684K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| SALES_T | 1 | 29282 |00:00:00.18 | 28337 | |

|* 5 | INDEX RANGE SCAN | PK_SALES_T | 1 | 29282 |00:00:00.04 | 118 | |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=10)

2 - filter(ROW_NUMBER() OVER ( PARTITION BY "PROD_ID" ORDER BY "CUST_ID","CHANNEL_ID",INTERNAL_FUNCTION("TIME_ID")

NULLS FIRST)<=10)

5 - access("PROD_ID"=30)

아래처럼 정답을 제시하면 된다.

문제2의 비효율 1 : 구체적인 설명

문제2의 비효율 1의 해결방법 : 구체적인 설명

문제2의 비효율 1이 해결된 SQL과 실행계획 제시

문제2의 비효율 2 : 구체적인 설명

문제2의 비효율 2의 해결방법 : 구체적인 설명

문제2의 비효율 2가 해결된 SQL과 실행계획 제시

문제2의 비효율 3 : 구체적인 설명

문제2의 비효율 3의 해결방법 : 구체적인 설명

문제2의 비효율 3이 해결된 SQL과 실행계획 제시

문제2의 비효율 4 : 구체적인 설명

문제2의 비효율 4의 해결방법 : 구체적인 설명

문제2의 비효율 4가 해결된 SQL과 실행계획 제시

문제의 구성
이렇게 하여 총 다섯 문제가 출제되었다. 문제 1에는 비효율이 하나이고 문제 2에는 비효율이 넷이다. 다섯 가지 비효율 모두 분석함수와 관련된 것들이다. 실습환경은 Oracle 10g R2, Oracle 11g R1, Oracle 11g R2로 한정한다. 답안은 2011년 2월 18일 공개될 예정이다. 답안을 바로 공개하지 않는 이유는 튜닝실습을 유도하기 위함이다. SQL 두개를 분석하는데 이틀이면 넉넉한 시간이므로 충분히 생각하고 답을 작성하기 바란다. 자신이 튜너가 되었다고 가정하고 비효율을 모두 찾아보길 바란다.

다른 사람들은 몇 점 정도 받았나?

몇몇 지인들에게 퀴즈를 내본 결과 100점은 아무도 없었다. 즉 80점이 평균적인 튜너의 수준이라는 이야기 이다. 개발자의 경우는 60점을 받더라도 실망해서는 안 된다. 개발자가 60점이면 괜찮은 수준이다. 점수에 대해 오해는 하지 말기 바란다. 이 퀴즈로 받은 점수는 분석함수와 관련된 것일 뿐, 전반적인 SQL 튜닝능력에 대한 점수가 아니다. 또한 100 점 이상을 받을 수도 있다. 필자가 알고 있는 튜닝포인트는 5가지뿐이다. 여러분들이 6~7가지 이상의 비효율과 튜닝 포인트를 잡아내어 필자를 능가하는 점수를 받는 것도 가능하다.

행운을 빈다.

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 성능개선 그 결과는? (14)	2011.02.18
분석함수의 실행계획 - 2부 (12)	2011.02.10
분석함수의 실행계획 - 1부 (12)	2011.02.07
Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Bloom Filter와 Group By의 관계 (10)	2010.09.09

Posted by extremedb

,

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

Oracle/Data Access Pattern 2011. 2. 10. 11:23

빠른 이해를 위해서 이전 글을 먼저 보고 이 글을 읽기 바란다. 이 글의 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;

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

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

| 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 ;

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

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

| 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 ;

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

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

| 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의 실행계획

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

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

| 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

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

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

| 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 () ) ; --> 분석함수 사용

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

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

| 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 ;

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

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

| 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을 정하는 경우 발생

심화학습이 필요한 이유

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

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 성능개선 그 결과는? (14)	2011.02.18
분석함수의 실행계획 - 심화과정 (12)	2011.02.14
분석함수의 실행계획 - 1부 (12)	2011.02.07
Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Bloom Filter와 Group By의 관계 (10)	2010.09.09

Posted by extremedb

,

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

Oracle/Data Access Pattern 2011. 2. 7. 07:00

Oracle Data Access Pattern

Partition Access Pattern

Oracle 조인 방법 25가지

필자는 위의 글들을 이미 게시하였다. 그래서 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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;

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

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

| 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 ;

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

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

| 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;

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

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

| 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

Posted by extremedb

,

Index_desc 힌트와 rownum = 1 조합은 안전한가?

Oracle/Data Access Pattern 2010. 11. 9. 00:01

부제 : min/max값을 안전하고 빠르게 구하는 방법

최종일자, 최종순번을 구하기 위한 전통적인 방법은 index desc 힌트와 rownum = 1 조합이었다. 하지만 이것은 대단히 위험한 방법이다. 왜냐하면 튜닝을 하기 전에 값이 맞아야 하며, 성능튜닝은 그 이후의 문제이기 때문이다. 위의 방법은 인덱스의 구성컬럼이 변경 혹은 삭제되거나, 인덱스명이 바뀌면 max 값을 구하지 못한다. 즉 성능을 향상시키기 위해 값이 틀릴 수 있는 가능성을 열어놓은 것이다. 이런 방법은 어떤 이유로도 받아들여져서는 안 된다. 나 또한 예전에 이런 방법을 사용했지만 이는 필자의 명백한 잘못이었다.

올바른 값을 얻어야 하고 성능도 충족해야 하므로 오라클은 first_row(min/max) operation을 내놓았다. 따라서 우리는 index_desc + rownum 대신에 first_row(min/max)을 사용해야 한다. 그런데 항상 first_row(min/max)를 사용해야 할까? first_row(min/max)가 비효율적인 경우는 index_desc + rownum 조합을 생각해 볼 수 있다. 하지만 인덱스가 변경 및 삭제될 때 성능이 느려질지언정 답이 틀리면 안 된다. 만약 max가 아닌 잘못된 값으로 update 되었다고 상상해보라. 큰일이다. 원복시키기도 어렵다. update 가 여러번 되었을 수 있기 때문이다.

환경 :Oracle11g R2

CREATE INDEX ix_cust_channel_time ON SALES (CUST_ID, CHANNEL_ID, TIME_ID) ;

CREATE INDEX ix_cust_time_channel ON SALES (CUST_ID, TIME_ID, CHANNEL_ID) ;
CREATE INDEX ix_time_cust_channel ON SALES (TIME_ID, CUST_ID, CHANNEL_ID) ;

인덱스와 where 조건이 완벽할 때

SELECT /*+ gather_plan_statistics INDEX(s ix_cust_channel_time) */

MAX (time_id)

FROM sales s

WHERE cust_id = :v_cust --30777 대입

AND channel_id = 2;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 3 |

| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 |

| 2 | FIRST ROW | | 1 | 1 |00:00:00.01 | 3 |

|* 3 | INDEX RANGE SCAN (MIN/MAX)| IX_CUST_CHANNEL_TIME | 1 | 1 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

3 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

인덱스가 CUST_ID + CHANNEL_ID + TIME_ID로 되어 있는 경우는 문제가 없다. first_row(min/max) operation을 사용할 수 있고 비효율이 없기 때문에 값이 틀려질 수 있는 index_desc + rownum을 사용해선 안 된다.

where 조건에 인덱스의 중간 컬럼이 빠졌을 때

SELECT /*+ gather_plan_statistics INDEX(S IX_CUST_TIME_CHANNEL) */

MAX (time_id)

FROM sales s

WHERE cust_id = :v_cust --30777 대입

AND channel_id = 2;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 3 |

| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 3 |

| 2 | FIRST ROW | | 1 | 1 |00:00:00.01 | 3 |

|* 3 | INDEX RANGE SCAN (MIN/MAX)| IX_CUST_TIME_CHANNEL | 1 | 1 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

3 - access("CUST_ID"=:V_CUST)

filter("CHANNEL_ID"=2)

인덱스가 CUST_ID + TIME_ID + CHANNEL_ID 로 되어 있는 경우를 보자. 인덱스의 중간컬럼이 where절에 빠져있지만 CUST_ID의 선택도가 워낙 좋으므로 문제가 되지 않는다. first_row(min/max) operation을 그대로 사용하면 된다.

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우

SELECT /*+ gather_plan_statistics INDEX(S IX_TIME_CUST_CHANNEL) */

MAX (time_id)

FROM sales s

WHERE cust_id = :v_cust --30777 대입

AND channel_id = 2;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 755 |

| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 755 |

| 2 | FIRST ROW | | 1 | 1 |00:00:00.02 | 755 |

|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.02 | 755 |

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

Predicate Information (identified by operation id):

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

3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

인덱스가 TIME_ID + CUST_ID + CHANNEL_ID로 구성되어 있을때 인덱스의 선두 컬럼이 where 조건에서 빠졌다. 그로 인해 Index full scan이 발생하여 쓸모 없는 752블록을 Scan하였다. 즉 인덱스를 끝부분부터 계속 scan하다가 운 좋게 755 블록을 scan해보니 cust_id = 30777 와 channel_id = 2을 만족하는 값을 모두 처리한 것이다. 운이 나쁘면 인덱스를 모조리 읽어야 할 수도 있다.

서브쿼리나 인라인뷰를 이용하여 집합을 추가하자는 의견에 대해

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우는 강제로 집합을 추가하자는 의견이 있다. 아래의 SQL이 그것이다.

SELECT TIME_ID

FROM ( SELECT /*+ LEADING(C) INDEX_DESC(S IX_TIME_CUST_CHANNEL) */ S.time_id

FROM sales S,

(SELECT TRUNC(SYSDATE) - LEVEL + 1 AS time_id

FROM DUAL

CONNECT BY LEVEL <= 7300 ) C

WHERE S.cust_id = :v_cust --30777

AND S.channel_id = 2

AND S.time_id = C.time_id )

WHERE ROWNUM = 1;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 512 |

|* 1 | COUNT STOPKEY | | 1 | 1 |00:00:00.02 | 512 |

| 2 | NESTED LOOPS | | 1 | 1 |00:00:00.02 | 512 |

| 3 | VIEW | | 1 | 3484 |00:00:00.02 | 0 |

| 4 | CONNECT BY WITHOUT FILTERING| | 1 | 3484 |00:00:00.01 | 0 |

| 5 | FAST DUAL | | 1 | 1 |00:00:00.01 | 0 |

|* 6 | INDEX RANGE SCAN DESCENDING | IX_TIME_CUST_CHANNEL | 3484 | 1 |00:00:00.01 | 512 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM=1)

6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("C"."TIME_ID") AND "S"."CUST_ID"=:V_CUST AND

"S"."CHANNEL_ID"=2)

이렇게 하니 Scan한 블럭수가 1/3 정도 줄어들었다. 하지만 불필요한 조인이 3484번이나 발생하였다. 이것이 최적은 아니다. 또한 명시적으로 max값을 보장하게 작성된 SQL도 아니다.

Index_ss 힌트를 사용했다. 하지만……

위의 예에서 보듯이 인덱스의 선두 컬럼이 조건절에 없을때 이빨이 빠진 집합을 추가하는 것과 first_row(min/max)를 사용하는 것은 둘다 비효율적이다. 그러므로 index_desc + rownum을 사용하되 값이 바뀌지 않도록 해야 한다. 그런데 인덱스의 첫 번째 컬럼이 조건 절에서 빠졌으므로 index_ss_desc + rownum을 사용해야 한다. 이것이 가능할까? SQL을 바꾸지 않으면 불가능하다. 아래의 SQL을 보자.

SELECT /*+ gather_plan_statistics INDEX_SS(s ix_time_cust_channel) */

MAX (time_id)

FROM sales s

WHERE cust_id = :v_cust --30777

AND channel_id = 2;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 755 |

| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 755 |

| 2 | FIRST ROW | | 1 | 1 |00:00:00.02 | 755 |

|* 3 | INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.02 | 755 |

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

Predicate Information (identified by operation id):

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

3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

Min/Max와 Index Skip Scan을 동시에 사용할 수 없다

min 혹은 max 함수를 사용했을 때 Oracle9i 버전과는 달리 10g와 11g에서는 index skip scan을 사용할 수 없다. 힌트를 추가해도 마찬가지이다. 아래의 10053 trace를 보자.

***************************************

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for SALES[S]

ColGroup (#1, Index) IX_TIME_CUST_CHANNEL

Col#: 2 3 4 CorStregth: 185.95

ColGroup Usage:: PredCnt: 2 Matches Full: Partial:

Table: SALES Alias: S

Card: Original: 918843.000000 Rounded: 33 Computed: 32.54 Non Adjusted: 32.54

kkofmx: index filter:"S"."CUST_ID"=:B1

kkofmx: index filter:"S"."CHANNEL_ID"=2

Access Path: index (Min/Max)

Index: IX_TIME_CUST_CHANNEL

resc_io: 3.00 resc_cpu: 21564

ix_sel: 1.000000 ix_sel_with_filters: 0.000035

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 50.00

***** End Logdef Adjustment ******

***** Logdef predicate Adjustment ******

Final IO cst 0.00 , CPU cst 50.01

***** End Logdef Adjustment ******

Cost: 5.28 Resp: 5.28 Degree: 1

Best:: AccessPath: IndexRange

Index: IX_TIME_CUST_CHANNEL

Cost: 5.28 Degree: 1 Resp: 5.28 Card: 1.00 Bytes: 0

***************************************

힌트를 사용했지만 Index Skip Scan은 고려조차 되지 않는다. 위의 Trace를 보면 "first row(Min/Max)가 가능하다면 Index Skip Scan을 고려하지 않는 로직이 10g와 11g의 옵티마이져에 존재한다” 라고 추론할 수 있다. 인덱스와 where 절이 일치하지 않는 상태라 하더라도 비효율적인 index full scan (Min/Max)에 만족할 수는 없다. 바로 이럴 때 index_desc 와 rownum 조합을 답이 틀려질 수 없도록 사용하면 된다.

아래처럼 max 함수를 제거하면 Index Skip Scan을 사용할 수는 있다.

SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */

time_id

FROM sales s

WHERE cust_id = :v_cust --30777

AND channel_id = 2

AND ROWNUM = 1;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 264 |

|* 1 | COUNT STOPKEY | | 1 | 1 |00:00:00.01 | 264 |

|* 2 | INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.01 | 264 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM=1)

2 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

Max를 없애면 index skip scan을 사용할 수 있다. 하지만.....
블록수가 755에서 264로 1/3으로 줄어들었다. 하지만 인덱스가 수정 및 삭제되면 답이 틀릴 수 있으므로 위험하긴 마찬가지 이다. 따라서 다음의 SQL처럼 사용해야 한다.

안정적이고 성능을 고려한 SQL

SELECT MAX(time_id)

FROM ( SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */ time_id

FROM sales S

WHERE cust_id = :v_cust --30777

AND channel_id = 2

ORDER BY time_id DESC)

WHERE ROWNUM = 1;

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

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

| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.01 | 264 |

| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.01 | 264 |

|* 2 | COUNT STOPKEY | | 1 | 1 |00:00:00.01 | 264 |

| 3 | VIEW | | 1 | 1 |00:00:00.01 | 264 |

|* 4 | INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL | 1 | 1 |00:00:00.01 | 264 |

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM=1)

4 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

ORDER BY를 사용했지만 인덱스의 영향으로 SORT를 하지 않으므로 성능저하도 없다. 또한 인라인뷰 내에서 ORDER BY를 사용하고 외부에서 ROWNUM을 사용했기 때문에 인덱스가 수정 및 삭제되더라도 성능이 느려질 뿐 값이 틀려질 수는 없다. 위의 SQL에서 마지막에 max 함수를 사용한 이유는 where조건에 만족하는 건수가 없더라도 null을 출력해야하기 때문이다. 앞으로 index_desc + rownum 조합을 사용할 것이라면 위의 방법을 사용하길 바란다.

결론

1. Index_desc + rownum을 사용하지 말고 first_row(min/max)를 사용하라

2. 1번이 비효율적인 경우에만 index_desc(혹은 index_ss_desc) + order by를 사용하고 뷰로 감싸라.
그리고 뷰 외부에서 rownum을 사용하라.

3. first_row(min/max)를 사용할 수 있는 환경에서는 index skip scan을 사용할 수 없다. 꼭 사용하려면 집계 함수를 제거하라.

이렇게 하면 성능과 안정성을 동시에 고려할 수 있다.

글의 배경
이런 이야기를 하는 이유는 비판적 사고의 필요성 때문이다. 개발자에게 인라인뷰와 Order by가 없는 Index_desc + Rownum의 위험성을 설명해주었더니 나에게 책을 가져온다. 나는 본적이 없지만 아주 좋은 SQL 튜닝 책이라고 한다. 그것도 어려운 영문 책이다. 열심히 공부하는 사람임에 틀림없다. 하지만 개발자의 한마디 때문에 그사람의 인상이 바뀌어 버렸다. 그 한마디는 “이 책에 Index_desc + Rownum을 사용하라고 되어있습니다.” 였다. 그것이 얼마나 위험한 것인지 여러번 증명하고 설득해 보았으나 맘을 바꾸기는 불가능 하였다. 이래서는 곤란하다. 책을 성경이나 불경처럼 여기고, 저자를 종교의 교주로 여겨서는 안 된다. 이론은 반론이 증명되면 폐기될 수 있다. 하지만 가치관이 개입된 믿음이나 신념은 좀처럼 바뀌지 않는다. 어떠한 증거를 내놓아도 그렇다. 신념은 종교생활에 사용했으면 한다.

책의 내용 중에 잘못된 것이 있으니 나쁜 책이라고 말하는게 아니다. 필자의 서적을 포함해서 모든 책의 내용은 틀릴 수 있다. 정작 나쁜 것은 책이나 저자가 종교화될 때이다. 그런 무 비판적 종교는 이공계 사람에게 치명적이다. 비판적 사고는 엔지니어와 과학자의 버팀목이자 과학기술을 발전시키는 핵심이기 때문이다. 널리 알려진 과학 논쟁인 쿤과 포퍼의 대결에서도 이러한 언급은 드러난다. 두명 모두 비판적 사고는 반드시 필요하다고 하였다. 다만 시기의 문제일 뿐이다.

답답한 마음에 글을 올려보았다. 앞으로 이런 글을 다시 쓰고 싶지 않다.

"신앙은 믿음으로 이루져야한다. 하지만 과학은 비판과 증명으로 이루어져야 한다."

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 실행계획 - 2부 (12)	2011.02.10
분석함수의 실행계획 - 1부 (12)	2011.02.07
Bloom Filter와 Group By의 관계 (10)	2010.09.09
Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12

Posted by extremedb

,

Bloom Filter와 Group By의 관계

Oracle/Data Access Pattern 2010. 9. 9. 00:30

이전에 Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter)과 Partition Access Pattern 이라는 글에서 Bloom Filter의 개념을 설명한적 있다. 이전 글들 때문인지 모르겠으나 많은 사람들이 Parallel Query를 사용하거나 Partition을 엑세스 할때 Bloom Filter로 후행 테이블의 건수를 줄여 조인 건수를 최소화하는 것으로만 생각한다. 맞는 말이지만 그것이 전부가 아니다.
그래서 이번에는 Parallel과 Partition에 상관없이 Bloom Filter가 발생하는 경우를 살펴보고자 한다. 이 글을 통하여 풀고자 하는 오해는 Bloom Filter가 Join 최적화를 위한 후행 테이블의 Filter 알고리즘일 뿐만 아니라 Group By를 최적화하는 도구이기도 하다는 것이다.

실행환경: Oracle11gR2, Windows 32bit

Bloom Filter를 사용하지 않는 경우

먼저 Bloom Filter가 발생하지 않게 힌트를 주고 실행한다. 뒤에서 Bloom Filter를 적용한 경우와 성능을 비교하기 위함이다.

SELECT /*+ LEADING(c) NO_MERGE(S) NO_PX_JOIN_FILTER(S) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:06.58 | 5075 | |

|* 1 | HASH JOIN | | 23 |00:00:06.58 | 5075 | 1194K (0)|

| 2 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 3 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 4 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 5 | VIEW | | 7059 |00:00:06.56 | 4927 | |

| 6 | SORT GROUP BY | | 7059 |00:00:06.54 | 4927 | 9496K (0)|

| 7 | PARTITION RANGE ALL | | 918K|00:00:02.80 | 4927 | |

| 8 | TABLE ACCESS FULL | SALES | 918K|00:00:00.95 | 4927 | |

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

Predicate Information (identified by operation id):

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

1 - access("S"."CUST_ID"="C"."CUST_ID")

4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

Id 기준으로 8번에서 Buffers 항목을 보면 전체건(4927 블록)을 Scan 하였다. 그리고 A-Rows 항목을 보면 Sales 테이블에 대해 약 92만건(918K)을 읽었다. 이제 Id 6번을 보자. 전체 건수인 92만건에 대하여 Sort Group By를 적용하는데 부하가 집중되는 것을 알 수 있다. 시간상으로도 Group By를 하는데 3.7초 정도 걸렸으며 PGA를 9496K나 사용하였다. 즉 대부분의 시간을 Sort Group By Operation 에서 소비한 것이다.

이제 위의 SQL에 Bloom Filter를 적용해 보자. Sales 테이블에 파티션이 적용되어 있으나 파티션과 상관없이 Bloom Filter가 적용된다.

SELECT /*+ LEADING(c) NO_MERGE(S) PX_JOIN_FILTER(S) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:00.15 | 5075 | |

|* 1 | HASH JOIN | | 23 |00:00:00.15 | 5075 | 1197K (0)|

| 2 | JOIN FILTER CREATE | :BF0000 | 151 |00:00:00.01 | 148 | |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 6 | VIEW | | 55 |00:00:00.14 | 4927 | |

| 7 | SORT GROUP BY | | 55 |00:00:00.14 | 4927 |88064 (0)|

| 8 | JOIN FILTER USE | :BF0000 | 7979 |00:00:00.12 | 4927 | |

| 9 | PARTITION RANGE ALL | | 7979 |00:00:00.10 | 4927 | |

|* 10 | TABLE ACCESS FULL | SALES | 7979 |00:00:00.09 | 4927 | |

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

Predicate Information (identified by operation id):

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

1 - access("S"."CUST_ID"="C"."CUST_ID")

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

10 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."CUST_ID"))

Bloom Filter를 사용해보니

위의 실행계획에서 Id 기준으로 8번을 보면 Name 항목에 Bloom Filter가 사용되었다. Bloom Filter의 위력이 얼마나 대단한지 살펴보자. 먼저 Sales 테이블을 Full Table Scan 하였으므로 Buffers는 4927로 Bloom Filter를 사용하지 않는 경우와 똑같다. 하지만 Bloom Filter가 적용되어 92만건이 아닌 7979건(A-Rows 참조)만 살아남았다. 이처럼 Bloom Filter는 Hash Join시 Probe(후행) 집합에서 조인에 참여하는 건수를 줄임으로써 Join 시간을 단축시킨다. Bloom Filter의 효과는 이것이 끝이 아니다. 건수가 줄어듦으로 해서 Sort Group By 작업 또한 92만 건이 아니라 7979건만 하면 된다. Group By에 의한 PGA 사용량을 Bloom Filter가 적용된 실행계획과 비교해보면 100배 이상 차이가 나는 이유도 Bloom Filter의 효과 때문이다.

제약사항

이번에 test한 케이스는 Parallel Query도 아니며 Partition Pruning과도 관련이 없다. 하지만 항상 발생하지는 않는다. 이유는 세 가지 제약사항이 있기 때문이다.

첫 번째, Hash Join을 사용해야 한다. Sort Merge Join이나 Nested Loop Join에서는 발생하지 않는다.
두 번째, Build Input(Driving) 집합에 Filter 조건이 존재해야 한다. 위의 SQL에서는 cust_year_of_birth = 1987가 Filter 조건으로 사용되었다. Filter가 필요한 이유는 선행집합의 Filter조건을 후행집합에서 Bloom Filter로 사용해야 하기 때문이다.
세 번째, Probe(후행) 집합에서 Group By를 사용해야 한다. 위의 SQL에서도 cust_id로 Group By를 하고 있다. 물론 후행집합에 Group By가 적용되려면 뷰나 인라인뷰가 필요하다.

만약 Bloom Filter가 사라져 전체 건이 조인에 참여한다면?

상상하기 싫은 경우지만 Probe(후행) 집합에 Bloom Filter가 사라지는 경우를 살펴보자. 이 경우는 Sales 테이블 전체건수(약 92만건)가 모두 Hash Join에 참여하게 되므로 성능이 저하될 것이다. 아래의 SQL이 그것인데 위의 SQL에서 NO_MERGE(S) 힌트와 PX_JOIN_FILTER(S)만 뺀 것이다.

SELECT /*+ LEADING(c) */

c.cust_id, c.cust_first_name, c.cust_last_name,

s.prod_cnt, s.channel_cnt, s.tot_amt

FROM customers c,

(SELECT s.cust_id,

COUNT (DISTINCT s.prod_id) AS prod_cnt,

COUNT (DISTINCT s.channel_id) AS channel_cnt,

SUM (s.amount_sold) AS tot_amt

FROM sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

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

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

| 0 | SELECT STATEMENT | | 23 |00:00:05.39 | 5075 | |

| 1 | SORT GROUP BY | | 23 |00:00:05.39 | 5075 |75776 (0)|

|* 2 | HASH JOIN | | 3230 |00:00:05.37 | 5075 | 1185K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 151 |00:00:00.01 | 148 | |

| 4 | BITMAP CONVERSION TO ROWIDS| | 151 |00:00:00.01 | 2 | |

|* 5 | BITMAP INDEX SINGLE VALUE | CUSTOMERS_YOB_BIX | 1 |00:00:00.01 | 2 | |

| 6 | PARTITION RANGE ALL | | 918K|00:00:02.70 | 4927 | |

| 7 | TABLE ACCESS FULL | SALES | 918K|00:00:00.94 | 4927 | |

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

Predicate Information (identified by operation id):

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

2 - access("S"."CUST_ID"="C"."CUST_ID")

5 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

악성 쿼리변환

힌트를 제거하자 View Merging(뷰 해체)이 발생하여 인라인뷰가 제거되었다. (View Merging이 발생하지 않는 독자는 MERGE(S) 힌트를 추가하기 바란다) 뷰가 없어짐에 따라 후행집합에서 Group By가 없어지고 조인이 끝난 후에 Group By가 발생한다. 후행집합의 Group By가 사라졌으므로 Bloom Filter가 적용되지 않는다. 따라서 Sales 테이블의 전체건 (약 92만건)이 조인에 참여하게 된다. Bloom Filter가 적용된 경우는 단 55건만 조인에 참여하므로 이 차이는 어마 어마한 것이다. 그 결과 전체 수행시간중에서 Hash Join에서만 절반의 시간을 소모하였다. 즉 잘못된 쿼리변환이 발생하여 Bloom Filter를 죽여버린 것이다. View Merging이 발생할 때 Bloom Filter를 적용할 수 없게되어 비효율이 발생되는지 주의깊게 관찰해야 한다.

결론

이번 Test 케이스에서 Bloom Filter의 특징을 두 가지로 압축할 수 있다. Group By 작업량을 최소화 시켜주고 Hash Join 건수를 줄여준다. 이 두 가지 효과가 맞물려 Bloom Filter를 적용한 SQL이 0.15초 만에 끝날 수 있는 것이다. 후행 테이블에서 Bloom Filter로 걸러지는 건수가 많을 때 두 가지 작업(Group By, Hash Join) 모두 최대의 효율을 발휘한다. 바꿔 말하면 Bloom Filter로 제거되는 건수가 미미 하다면 사용해선 안된다.

CVM(Complex View Merging)이 발생하면 여지없이 Bloom Filter가 사라진다. CVM 때문에 성능이 저하된다면 NO_MERGE 힌트를 사용하여 뷰를 유지시켜야 한다. Bloom Filter가 사라지는 경우는 이 경우 뿐만 아니다. 11gR2에서 새로 적용된 Cardinality Feedback 때문에 Bloom Filter가 사라지는 경우가 보고되고 있다. 마지막(세번째) SQL을 최초로 실행시켰을 때와 두번째로 실행시켰을 때 DBMS_XPLAN.DISPLAY_CURSOR의 실행계획이 달라진다면 Cardinality Feedback이 Bloom Filter를 제거시킨것이다. Shared Pool을 Flush하고 두번 연달아 테스트 해보기 바란다. 이런 현상들 때문에 옵티마이져에 새로운 기능이 추가될 때마다 긴장을 늦출 수 없다. 버전이 올라갈수록 튜닝하기가 쉬워지는것인가? 아니면 그 반대인가?

'Oracle > Data Access Pattern' 카테고리의 다른 글

분석함수의 실행계획 - 1부 (12)	2011.02.07
Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
Partition Access Pattern (13)	2009.12.24

Posted by extremedb

,

Inlist Iterator를 사용하지 말아야 할 때

Oracle/Data Access Pattern 2010. 6. 23. 16:25

2010.06.25 오타를 수정 했습니다. 관련 댓글을 참조바랍니다.

결합 인덱스인 경우 뒤쪽 컬럼의 조건을 살리기 위해서 선두 컬럼에 BETWEEN 이나 LIKE 조건 대신에 IN을 사용해야 한다는 주장이 있다. 다시 말하면 column1 + column2 로 결합 인덱스가 생성되어 있고 column1 between 1 and 3 and column2 between 1 and 3 처럼 사용한다면 column2의 조건은 filter로 처리되어 인덱스의 효과를 보지 못하므로 column1 in (1,2,3) and column2 between 1 and 3 처럼 사용해야 한다는 주장이다. 이것은 항상 옳은 주장 일까?

예외 없는 규칙은 없다
위의 주장대로 하면 뒤쪽 컬럼까지 인덱스를 사용할 수 있다. 하지만 정작 문제가 되는것은 그렇게 하면 항상 성능이 빨라진다고 믿고 있는 사람들이다. 위의 주장은 일반적으로 통용되는 말이지만 오히려 성능이 불리해 질 수 있다. 먼저 IN 조건과 Range 조건(Between 이나 Like 조건)의 특징을 비교하기 위하여 가장 간단한 것(Single Column Index)부터 이야기 해보자.

column1에 인덱스가 있다고 가정하고 column1의 데이터가 1부터 100까지 정수만 존재한다고 했을때 column1 in (1,2) 처럼 사용해야 하는가 아니면 column1 between 1 and 2 처럼 사용해야 하는가? 결론부터 말하자면 column1 in (1,2) 조건은 정답이 아니다. 왜 그런지 아래의 SQL을 보자.

테스트 환경 Oracle 11.1.0.6

우선 sales 테이블에 인덱스를 만들자.

create index idx01 on sales (CUST_ID);

create index idx02 on sales (CUST_ID, TIME_ID);

이제 SQL을 실행 해보자.

SELECT /*+ gather_plan_statistics INDEX( a idx01) */

count(*)

FROM sales a

WHERE cust_id BETWEEN 33 AND 44 ;

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

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 7 |

|* 2 | INDEX RANGE SCAN| IDX01 | 1 | 423 | 1432 |00:00:00.01 | 7 |

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

Predicate Information (identified by operation id):

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

2 - access("CUST_ID">=33 AND "CUST_ID"<=44)

위의 결과는 BETWEEN을 사용하였으므로 INDEX RANGE SCAN이 나왔으며 7개의 블럭을 scan 하였다. 별로 특별한 것이 없는 Plan이다. 이제 BETWEEN 대신에 IN 조건을 사용해보자.

SELECT /*+ gather_plan_statistics INDEX(A idx01) */

COUNT (*)

FROM sales a

WHERE cust_id IN (33, 34, 36, 37, 38, 40, 41, 42, 44) ;

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

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 24 |

| 2 | INLIST ITERATOR | | 1 | | 1432 |00:00:00.01 | 24 |

|* 3 | INDEX RANGE SCAN| IDX01 | 9 | 1171 | 1432 |00:00:00.01 | 24 |

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

Predicate Information (identified by operation id):

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

3 - access(("CUST_ID"=33 OR "CUST_ID"=34 OR "CUST_ID"=36 OR "CUST_ID"=37

OR "CUST_ID"=38 OR "CUST_ID"=40 OR "CUST_ID"=41 OR "CUST_ID"=42 OR

"CUST_ID"=44))

참고로 위의 IN 조건에서 35, 39, 43 은 데이터가 존재하지 않으므로 제외하였다.

IN 을 사용하였더니 BETWEEN 조건에 비해 3배 이상의 블럭을 Scan 하였다.

3배 차이 어디서 나타나는가?

Starts 항목(시도횟수)에 주목하기 바란다. BETWEEN을 사용한 Plan은 INDEX RANGE SCAN이 단 한번만 시도되었지만 IN을 사용한 Plan은 INLIST ITERATOR(반복처리) 때문에 INDEX RANGE SCAN이 9번 시도되었다. 즉 IN-LIST의 개수인 9번 만큼 RANGE SCAN을 반복한 것이다. 쓸모 없이 인덱스의 ROOT 노드와 중간 노드를 9번이나 Scan 하였으므로 비효율이 있는 것은 당연한 것이다.

결합인덱스를 사용할 때

이제 결합 인덱스인 경우 뒤쪽 컬럼의 조건을 살리기 위해서 선두 컬럼에 IN을 사용하면 오히려 성능이 불리해 지는 경우를 살펴보자.

SELECT /*+ gather_plan_statistics INDEX(A idx02) */

COUNT (*)

FROM tlo.sales a

WHERE cust_id IN (33, 34, 36, 37, 38, 40, 41, 42, 44)

AND time_id between to_date('20000101', 'YYYYMMDD') and to_date('20000131', 'YYYYMMDD');

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

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 21 |

| 2 | INLIST ITERATOR | | 1 | | 12 |00:00:00.01 | 21 |

|* 3 | INDEX RANGE SCAN| IDX02 | 9 | 95 | 12 |00:00:00.01 | 21 |

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

Predicate Information (identified by operation id):

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

3 - access((("CUST_ID"=33 OR "CUST_ID"=34 OR "CUST_ID"=36 OR "CUST_ID"=37

OR "CUST_ID"=38 OR "CUST_ID"=40 OR "CUST_ID"=41 OR "CUST_ID"=42 OR

"CUST_ID"=44)) AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd

hh24:mi:ss') AND "TIME_ID"<=TO_DATE(' 2000-01-31 00:00:00', 'syyyy-mm-dd

hh24:mi:ss'))

인덱스 뒤쪽 컬럼인 time_id를 access 조건으로 만들기 위해 cust_id 에 IN 조건을 사용하였으며 21 블럭을 SCAN 하였다. 이제 cust_id에 between 조건을 사용해보자.

SELECT /*+ gather_plan_statistics INDEX( a idx02) */

count(*)

FROM tlo.sales a

WHERE cust_id BETWEEN 33 AND 44

AND time_id between to_date('20000101', 'YYYYMMDD') and to_date('20000131', 'YYYYMMDD');

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

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

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 8 |

|* 2 | INDEX RANGE SCAN| IDX02 | 1 | 7 | 12 |00:00:00.01 | 8 |

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

Predicate Information (identified by operation id):

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

2 - access("CUST_ID">=33 AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00',

'syyyy-mm-dd hh24:mi:ss') AND "CUST_ID"<=44 AND "TIME_ID"<=TO_DATE('

2000-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

filter(("TIME_ID"<=TO_DATE(' 2000-01-31 00:00:00', 'syyyy-mm-dd

hh24:mi:ss') AND "TIME_ID">=TO_DATE(' 2000-01-01 00:00:00', 'syyyy-mm-dd

hh24:mi:ss')))

오히려 BETWEEN 조건이 성능상 우월하다

위의 Plan을 보면 cust_id에 between 조건을 사용하였으므로 time_id 조건을 사용하지 못하고 Filter로 빠졌다. 하지만 읽은 블럭수는 8개에 불과하다. 즉 IN 조건을 사용하여 인덱스의 뒤쪽 컬럼까지 엑세스 조건으로 만들었지만 성능은 오히려 저하되었다. INLIST ITERATOR(반복처리)의 부하 때문이다. 이 글에서 말하고자 하는것이 단순히 "INLIST 의 개수가 많아지면 부하가 증가된다" 라는 규칙을 이야기 하는것이 아니다. IN 조건과 BETWEEN 조건의 갈림길에서 BETWEEN 을 사용해야 유리한 경우가 있는데 그것이 어떤 경우인가? 혹은 기준은 무엇인가? 를 알아보는 것이다. 기준이 무엇일까? 결론을 보기전에 생각해보라.

결론

IN 조건과 Range 조건(Between 이나 Like 조건)을 구분하는 기준은 연속선 조건이다. 예를 들면 1부터 4까지 연속적으로 붙어있는 데이터를 엑세스 할 때는 Between 조건을 사용해야 한다. 왜냐하면 IN 조건은 LIST의 개수만큼 반복처리(LOOP) 되므로 쓸모 없는 부하가 증가하기 때문이다. 또한 특정 ITERATOR에서 조건에 만족하는 데이터가 없더라도 인덱스의 ROOT 노드와 중간 노드를 SCAN하는 비효율을 막을 수 없다. 하지만 Range 조건은 그러한 반복처리와 비효율이 없다.

결합인덱스의 뒤쪽 컬럼을의 조건을 처리주관조건으로 만들기 위해 선두 컬럼을 IN 조건으로 사용하고 싶을 때에도 연속선 기준을 검증용으로 적용시켜야 한다. 다시 말하면 선두 컬럼이 연속선 조건이라면 결합인덱스를 사용할 때에도 IN 조건과 Range 조건 중에 유리한 것을 선택해야 한다는 뜻이다. 초당 수백 번 이상 실행되는 중요한 SQL이고 0.001 초를 다투는 상황이라면 부하의 차이는 클 것이다.

연속적이지 않는 데이터를 엑세스 할 때는 BETWEEN 조건을 사용할 수 없으므로 당연히 IN 조건을 사용해야 한다. 이 글은 엑세스 하고자 하는 데이터가 연속선으로 되었을 때 IN 조건 보다는 BETWEEN 이나 LIKE 조건이 유리함을 나타낸 것이다.

PS

100% 연속된 조건일 때만 BETWEEN 조건을 써야 하는지 질문이 들어왔다. 그렇지 않다. 중간에 몇 개의 이빨이 빠진다고 한들 대세에 지장이 없으면 상관이 없다. 아래처럼 처리하기 바란다.

select *

from tab

where col1 between 1 and 5

and col1 <> 3 ; -- 연속선이 아닐 때의 처리

오늘은 기분좋은 날 입니다.^^

'Oracle > Data Access Pattern' 카테고리의 다른 글

Index_desc 힌트와 rownum = 1 조합은 안전한가? (12)	2010.11.09
Bloom Filter와 Group By의 관계 (10)	2010.09.09
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
Partition Access Pattern (13)	2009.12.24
모든 상식을 의심하라 (16)	2009.11.05

Posted by extremedb

,

오렌지나 TOAD에서 Predicate Information을 참조하는 방법

Oracle/Data Access Pattern 2010. 1. 12. 00:01

지인으로부터 아래와 같은 질문을 받았다.

질문 : Predicate Information을 참조하려면 DBMS_XPLAN 패키지를 사용해야만 합니까?

저희 개발자들은 DBMS_XPLAN 패키지를 사용할 권한이 없습니다.
따라서 오렌지나 TOAD에서 간단히 볼 수 있는 방법이 필요합니다. 가능 합니까?

답변 : 볼 수 있습니다. 단 PLAN_TABLE을 볼수 있는 권한은 있어야 합니다.

요청: 그건 있습니다. 방법을 블로그에 올려주시면 나머지 사람들도 볼수 있겠네요. 올려주시죠.

이렇게 해서 이글을 작성 하게 되었다. 이런 질문을 받았다는 것은 2가지 의미로 해석할 수 있다. 첫번째, 의외로 오렌지나 TOAD의 기능을 모르는 사람이 많이 있을 수 있다는 의미다. 두번째, 튜닝에 필요한 권한이 개발자에게 없다는 안타까움 이다. 이 정책은 매우 아쉬운 선택이며 앞으로 개선되기를 기대해본다. 하지만 수정이 필요한 법이나 악법도 법이므로 수정되기 전까진 따라야 한다.

Predicate Information과 관련된 가장 흔한 오류는 10046 이벤트 + tkprof를 사용하면 Predicate Information을 볼수 있다고 착각 하는 것이다. 절대 볼수 없다.

Predicate Information이 뭐지?
Predicate Information이란 인덱스 scan 시의 컬럼 액세스 정보, 조인정보, filter 정보를 각 Opreation 단위로 나타낸 것이다. 아래의 예제를 보자.

explain plan for

SELECT /*+ LEADING(e) USE_NL(d) */

e.employee_id, e.first_name, e.last_name, e.email, e.salary

FROM employee e, department d

WHERE e.department_id = d.department_id

AND e.job_id = 'SH_CLERK';

select * from table(dbms_xplan.display);

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

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

| 0 | SELECT STATEMENT | | 20 | 860 | 3 (0)|

|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 20 | 860 | 3 (0)|

|* 2 | INDEX RANGE SCAN | EMP_JOB_IX | 20 | | 1 (0)|

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

Predicate Information (identified by operation id):

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

1 - filter("E"."DEPARTMENT_ID" IS NOT NULL) --> FILER 정보가 출력됨

2 - access("E"."JOB_ID"='SH_CLERK') --> INDEX SCAN 정보 혹은 JOIN 정보가 출력됨

FILTER와 ACCESS 정보는 중요하다

위에서 출력된 Predicate Information을 보면 FILTER 와 INDEX SCAN 정보를 정확히 볼 수 있다. 특히 인덱스가 여러 개의 컬럼으로 구성된 경우 몇 번째 컬럼까지 액세스 되었는지 보려면 Predicate Information이 필수적인 것이다. 예를 들어 인덱스가 COL1 + COL2 + COL3로 되어 있는데 Predicate Information에서 INDEX를 SCAN에 사용된 컬럼이 하나뿐이고 COL2와 COL3는 테이블의 FILTER로 풀린다면 성능에 문제가 될 수 있다. 따라서 Predicate Information을 확인 하는 것은 매우 중요한 것이다.

문제는 이처럼 중요한 정보를 DBMS_XPLAN 패키지를 사용하지 않고 'TOAD나 오렌지에서 어떻게 볼수 있냐' 이다.

지금부터 따라 해보기 바란다.

1. TOAD에서 Predicate Information 보기

먼저 토드 화면에서 EXPLAIN PLAN을 실행한다. EXPLAIN PLAN은 구급차 아이콘을 누르면 된다.

그러면 위와 같은 화면이 출력될 것이다. 위의 화면에서는 Predicate Information가 없다. 지금부터 Predicate Information을 추가해보자.

먼저 TOAD 화면의 하단(Explain Plan) 탭에서 오른쪽 버튼을 클릭한다. 연이어 Adjust Content를 선택한다.

그러면 위와 같은 화면이 뜨는데 여기서 Access Predicates와 Filter Predicates의 Visible 항목을 체크하고 OK를 클릭한다.

위와 같이 TOAD에서 Access Predicates와 Filter Predicates가 깔끔하게 출력되었다.

2. 오렌지에서 Predicate Information 보기

오렌지를 사용한다면 PLAN TOOL에서 Show Plan버튼을 클릭하거나 Function 키 F5를 누르고 하단의 세번째 탭을 클릭하면 아래의 그림처럼 Predicate Information을 볼 수 있다.

확인하는 습관이 필요해
이로써 어디서든 무엇을 사용하든 버튼 클릭 만으로 Predicate Information을 볼 수 있게 되었다. 이제부터 Predicate Information을 애용하기 바란다. 특히 Index Scan 시에 몇번째 컬럼까지 이용하였는지 확인하는 습관이 필요하다.

PS :
필자는 TOAD나 오렌지의 제조사나 판매사와는 상관없는 사람이다. 단지 가끔 이용할 뿐...

'Oracle > Data Access Pattern' 카테고리의 다른 글

Bloom Filter와 Group By의 관계 (10)	2010.09.09
Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
Partition Access Pattern (13)	2009.12.24
모든 상식을 의심하라 (16)	2009.11.05
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29

Posted by extremedb

,

Partition Access Pattern

Oracle/Data Access Pattern 2009. 12. 24. 17:25

지난 2009년 10월달에 Oracle Data Access Pattern을 정복하라 라는 글을 통하여 데이터의 접근방법에 대하여 알아보았다. 오늘은 파티션 데이터의 접근방법에 대하여 알아보자. 필자가 이글을 올리는 이유는 실행계획에 Partition Access Pattern 이 나오지만 해석을 못하는 사람이 많이 있기 때문이다. 오늘 이글을 읽고 이해한다면 파티션에 어떻게 접근하는지 접근하는 방법은 어떤것인지 모두 알수 있다.

기본적인 Partition의 종류는 3가지이다.

1.RANGE
2.LIST
3.HASH

하지만 위의 3가지를 엑세스 패턴으로 나누고자 한다면 매우 종류가 많아진다.

Partition RANGE Access Pattern

1.PARTITION RANGE SINGLE

2.2.PARTITION RANGE ITERATOR

3.3.PARTITION RANGE INLIST

4.4.PARTITION RANGE ALL

5.5.PARTITION RANGE EMPTY

6.6.PARTITION RANGE OR

7.7.PARTITION RANGE SUBQUERY

8.8.PARTITION RANGE JOIN-FILTER

9.9.PARTITION RANGE MULTI-COLUMN

Partition LIST Access Pattern

1.
1.PARTITION LIST SINGLE

2.2.PARTITION LIST ITERATOR

3.3.PARTITION LIST INLIST

4.4.PARTITION LIST ALL

5.5.PARTITION LIST EMPTY

6.6.PARTITION LIST OR

7.7.PARTITION LIST SUBQUERY

8.8.PARTITION LIST JOIN-FILTER

Partition HASH Access Pattern

1.
1.PARTITION HASH SINGLE

2.2.PARTITION HASH ITERATOR

3.3.PARTITION HASH INLIST

4.4.PARTITION HASH ALL

5.5.PARTITION HASH SUBQUERY

6.6.PARTITION HASH JOIN-FILTER

총 23가지이다.
이모든 것을 언제 다 배운단 말인가?
이럴때 필자가 정리한 파일이 도움이 될것이다.
반드시 Partition Pruning 과 Access Pattern 을 정복하기 바란다.

invalid-file

Partition Access Pattern

PS :
첨부된 문서는 Oracle 11.1까지의 Partition Pruning 과 Access Pattern을 정리한 것이다.
배포시 출처를 밝혀주기 바란다.

참조서적:
1.Trouble Shooting Oracle Performance (Christian Antognini)
2.오라클 메뉴얼

'Oracle > Data Access Pattern' 카테고리의 다른 글

Inlist Iterator를 사용하지 말아야 할 때 (13)	2010.06.23
오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
모든 상식을 의심하라 (16)	2009.11.05
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22

Posted by extremedb

,

모든 상식을 의심하라

Oracle/Data Access Pattern 2009. 11. 5. 00:01

상식이 발목을 잡다
SQL 튜닝을 해본 사람들은 "같은 테이블을 반복해서 사용하지 말라" 라는 보편화된 상식을 가지고 있다. 위와 같은 말들은 상창력(상상력 + 창의력)이 필요한 튜닝의 세계에서 오히려 그것을 발휘하지 못하게 발목을 잡을 수 있다. 상식을 비판 없이 따르는 것이 얼마나 위험한지 증명해보자.

환경 Oracle 11.1.0.6

--테스트를 위한 회원탈퇴고객 테이블 생성

CREATE TABLE SH.WITHDRAWAL_CUST AS
SELECT * FROM sh.CUSTOMERS WHERE rownum <= 5000;

--UNIQUE INDEX 생성

CREATE UNIQUE INDEX SH.PK_WITHDRAWAL_CUST ON SH.WITHDRAWAL_CUST (CUST_ID);

--SALES 테이블에 PROD_ID를 선두로 하는 결합인덱스 생성

CREATE INDEX SH.SALES_IDX_02 ON SH.SALES (PROD_ID, CUST_ID);

dbms_stats.gather_table_stats('SH','SALES',cascade=>true);

dbms_stats.gather_table_stats('SH','WITHDRAWAL_CUST',cascade=>true);

후행테이블에서 건수가 줄어드는 경우

회원탈퇴고객 테이블(WITHDRAWAL_CUST)을 생성 하였고 SALES 테이블에 결합인덱스를 생성 하였다. 이제 모든 준비가 끝났으므로 상품번호 144을 구입한 고객 중 회원탈퇴고객을 조회해 보자.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */

s.cust_id, w.cust_first_name, w.cust_last_name,

s.prod_id, s.time_id, s.channel_id, s.quantity_sold

FROM sh.sales s, sh.withdrawal_cust w

WHERE s.prod_id = 144

AND s.cust_id = w.cust_id ;

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

상식적인 수준에서 SQL이 작성되었다. 아래의 Plan을 보자.

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

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

| 1 | NESTED LOOPS | | 423 |00:00:00.11 | 3708 |

| 2 | NESTED LOOPS | | 423 |00:00:00.11 | 3285 |

| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 4091 |00:00:00.07 | 3209 |

|* 4 | INDEX RANGE SCAN | SALES_IDX_02 | 4091 |00:00:00.01 | 17 |

|* 5 | INDEX UNIQUE SCAN | PK_WITHDRAWAL_CUST | 423 |00:00:00.02 | 76 |

| 6 | TABLE ACCESS BY INDEX ROWID | WITHDRAWAL_CUST | 423 |00:00:00.01 | 423 |

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

Predicate Information (identified by operation id):

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

4 - access("S"."PROD_ID"=144)

5 - access("S"."CUST_ID"="W"."CUST_ID")

최적의 SQL인가?

Plan을 보면 SALES 테이블(테이블 건수: 약 92만건)의 SALES_IDX_02 인덱스를 이용하여 4091건을 Select 하였고 연이어 SALES 테이블을 같은 건수만큼 Select 하였다. 그런 후에 WITHDRAWAL_CUST 테이블의 Unque 인덱스를 엑세스 하여 건수가 423 건으로 거의 10분의 1 수준으로 감소하였다. 건수가 줄어드는 이유는 전체고객에 비하여 회원탈퇴고객이 많지 않은 까닭이다. 많은 사람들이 위의 Plan은 현재 상태에서 최적이라고 생각할 것이다. 아래의 SQL은 결과는 같지만 SALES 테이블을 2번 사용하는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) LEADING(S1 W S) */

s.cust_id, w.cust_first_name, w.cust_last_name,

s.prod_id, s.time_id, s.channel_id, s.quantity_sold

FROM sh.sales s1, sh.withdrawal_cust w, sh.sales s

WHERE s1.prod_id = 144

AND s1.cust_id = w.cust_id

AND s1.rowid = s.rowid ;

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

위의 SQL을 보면 SALES 테이블을 두 번 사용하였다. 상식 대로라면 같은 테이블의 중복사용으로 Read 한 Block 의 개수(Buffers 항목)가 원본 SQL보다 증가하여야 한다. 과연 그런가? 아래의 Plan을 보자.

--------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS |    |    423 |00:00:00.08 | 842 |
| 2 | NESTED LOOPS    |    |    423 |00:00:00.07 | 516 |
|* 3 |    INDEX RANGE SCAN | SALES_IDX_02 | 4091 |00:00:00.02 |    17 |
| 4 |    TABLE ACCESS BY INDEX ROWID| WITHDRAWAL_CUST    |    423 |00:00:00.04 | 499 |
|* 5 | INDEX UNIQUE SCAN | PK_WITHDRAWAL_CUST |    423 |00:00:00.01 |    76 |
| 6 | TABLE ACCESS BY USER ROWID | SALES    |    423 |00:00:00.01 | 326 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S1"."PROD_ID"=144)
   5 - access("S1"."CUST_ID"="W"."CUST_ID")

오히려 성능이 향상 되었다
원본 SQL에 비하여 Block 의 I/O 개수가 4배 이상 줄어들었다. 대단하지 않은가? ID 기준으로 3 에서 SALES 테이블의 SALES_IDX_02 인덱스만 엑세스 하고 SALES 테이블(Alias 로는 S1)은 엑세스 하지않는다. 연이어 WITHDRAWAL_CUST 테이블과 조인함으로써 건수를 423으로 줄여 버린다. 건수를 10분의 1로 줄인 후에 마지막으로 Rowid를 이용하여 SALES 테이블(Alias 로는 S)에 S1의 Rowid로 엑세스 함으로서 성능향상이 가능한 것이다.

언제 같은 테이블을 두번 사용해야 하나?
Nested Loop 조인을 사용할때 선행 테이블의 인덱스 Scan후 곧장 선행 테이블로 엑세스 할때 Filter 가 없거나 Filter의 효과가 약하여 건수를 줄여주지 못하고 오히려 후행 테이블에서 건수를 획기적으로 줄여주는 경우가 있다. 이런 경우는 예외없이 같은 테이블을 2번 사용하여야 한다. 물론 Rowid를 이용해야 하는것은 당연하다. 여러분도 SQL 튜닝시 이런 상황을 많이 만났을 것이고 앞으로도 자주 만나게 될것이다. 이런 상황에서는 꼭 위의 방법을 사용하기 바란다.

지식의 저주

상창력을 이용해야 하는 경우는 상식이 오히려 방해가 될 수 있다. 이것은 지식의 저주라 불린다. 경험이 많거나 아는것이 많을수록 지식의 저주에 걸리게 될 확률은 높아진다. 누구라도 이런것이 조금씩은 있기 마련이다. 하지만 이런 종류의 저주를 많이 혹은 오래 받은 사람은 발전이 없거나 더디게 마련이다.

회피하기
지식의 저주를 받지 않은 사람은 역사를 바꾸기도 하였다. 갈릴레이의 지동설, 다윈의 진화론, 에디슨의 여러가지 발명품 등이 그것이다. 발상을 전환하기, 생각을 뒤집어 보기, 다른 방법으로 접근하기, 의도적으로 엉뚱한 생각하기, 동료와 BrainStorming 하기 등등 저주를 피할 방법은 많다. 이 개념을 SQL튜닝에만 한정시키면 곤란하다. 모든 분야에 사용할수 있다. 누가 알겠는가? 이러한 방법을 사용하여 여러분이 역사를 바꿀지……

PS
이렇게 상식을 뒤집는 케이스는 생각보다 많다. 아래에 링크된 Post 가 그런 경우이다.

Transformer - SQL 튜닝의 새로운 패러다임

Top SQL-( Rownum 의 정확한 사용법 )

위의 Link는 필자가 눈으로 확인한 것만 링크로 나타내었다. 실제로는 이것보다 훨씬 많을 것이다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

오렌지나 TOAD에서 Predicate Information을 참조하는 방법 (7)	2010.01.12
Partition Access Pattern (13)	2009.12.24
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12

Posted by extremedb

,

Oracle Data Access Pattern을 정복하라

Oracle/Data Access Pattern 2009. 10. 29. 00:01

첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL 튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

Index Scan : 인덱스 사용

Full Table Scan : 인덱스 미사용

Rowid : Rowid로 테이블 엑세스

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.

Rowid

Index Unique Scan

Index Range Scan

Index Inlist Iterator

Index Skip Scan

Index Full Scan

Index Fast Full Scan

Bit Map Index Combination

Full Table Scan

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

Rowid

Index Unique Scan

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending

Index Full Scan (min/max)

Index Fast Full Scan

BIT MAP OR

BIT MAP AND

BIT MAP MINUS

BIT MAP MERGE

Full Table Scan

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우 출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

invalid-file

PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Partition Access Pattern (13)	2009.12.24
모든 상식을 의심하라 (16)	2009.11.05
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28

Posted by extremedb

,

Oracle11g 에서 Full Table Scan의 성능 향상

Oracle/Data Access Pattern 2009. 7. 22. 03:40

문제는 수동이라는 점
예전에 필자가 Full Table Scan 의 비밀 이라는 글에서 _serial_direct_read 파라미터를 true 로 수정하여 Full Table Scan의 성능을 빠르게 한것을 기억하는가? 이것은 Full Table Scan시에 Disk 에서 데이터를 Scan 하여 SGA의 Buffer Cache에 적재한 후에 Logical Reads로 Fetch 를 하게되므로 성능이 저하될 수 있다. 이때 Disk 에서 Buffer Cache를 거치지 않고 바로 Fetch를 하면 성능향상이 가능한데 문제는 10g 까지는 항상 수동으로 파라미터를 수정하여야 한다는 것이다.

11g 부터 자동으로 바뀌어..
또한 테이블의 Size 가 중간크기인 경우 Direct I/O 를 하는것이 좋은지 Buffer Cache I/O 를 하는것이 좋은지 테스트를 해야만 한다. 하지만 Oracle 11g 부터 Direct I/O 를 수동으로 Control 할 필요가 없게 되었다. 11g 부터는 CBO(혹은 Cost Based 분석모듈)가 모든것을 알아서 한다. 이기능은 공식적으로 Cost Based Analysis of Direct I/O Access 라고 불린다.

2010 년 부터는 Oracle 11g 가 대세 라는데...
세계적인 전문가들도 용어는 물론이며 이러한 기능의 허와 실을 모르고 있다. 전세계에서 손에 꼽는 사람들 까지도 ... 이번기회에 확실히 용어와 개념을 잡아서 선구자가 되길 바란다. 2010 년 부터는 Oracle 11g로 가는것이 대세가 될것이며 누구나 이기능을 사용하게 될것이기 때문이다.
그럼이제 11g 에서 Direct I/O가 어떻게 수행되는지 알아보자.

Oracle 11g 에서 Direct I/O 을 수행하기 위한 일반적인 SQL 의 수행과정은 아래와 같다.

1. SQL을 실행한다.

2. 옵티마이져가 DATA 를 FETCH 하기 위하여 최종 PLAN 을 만든다.

3. Direct I/O 의 후보인지 판별한다.
   예를 들면 full table scan 인가? , 테이블의 블럭사이즈가 _db_block_buffers 의 10% 보다 큰가? 등을 만족한다면 후보 이다.
   참고로 _small_table_threshold 파라미터는 아무상관도 없다. 값을 줄이거나 증가시켜서 테스트 해보기 바란다.

4. Direct I/O 의 후보라면 Cost Based 분석 모듈을 call 하여 Buffer Cache 를 엑세스 시의 Cost 와 Direct I/O 시의 Cost 를 비교하여 비용이 낮은것으로 선택한다. 4번의 로직은 중요하므로 아래의 Cost Based 분석 모듈에서 자세히 설명된다.

5. 4번의 비교에 의하여 Direct I/O를 한다고 판단되었다면 해당 테이블의 Commit 되지 않은 데이터가 Buffer Cache 에 있는경우 데이터를 Disk 로 내려쓴다.

6. 데이터를 Fetch 한다. 이때 Direct I/O 일 경우가 아니라면 Buffer Cache 에서 Fetch 하고 Cost Based 분석 모듈을 끝낸다.
   Direct I/O 일 경우 Disk 에서 Fetch 된다.

7. Direct I/O 일 경우 4~6을 Fetch 단위마다 반복한다. Buffer Cache를 I/O 일 경우는 계속하여 Fetch만 하게된다.

이것이 전부이다. 7번 까지의 로직은 크게 어렵지 않은 것을 알수 있다. 다만 Direct I/O 를 수행할 경우 경우 잘못이해 하는 부분이 있는데 Disk 의 데이터를 PGA 로 내려쓰지 않는다는 것이다. _kdli_sio_pga 파라미터가 Default 로 False 이기 때문이다.

이제 4번에서 소개되었던 가장 중요한 Cost Based 분석 모듈을 소개할 차례다.

이 모듈이 CBO 내의 모듈인지는 확실치 않다. 만약 아니라면 다양한 통계정보를 사용하여 Cost 를 구하는 최초의 모듈이 될것이다. 거의 CBO 의 기능에 준한다고 할수 있다. 과연 이것이 가능할까? 이점은 필자로서도 의문이다.
RAC의 경우 이모듈의 특징은 각 노드마다 모듈이 존재한다는 점이다. 각 노드마다 Cost 를 구해야 되기 때문이다.

401. SQL 이 Buffer 에 CACHING 하기 좋은 후보인지 알아본다.
   예를 들면 Buffer CACHE SIZE 가 충분한가? 자주 엑세스 되는가? 적어도 작은 부분이 CACHE 에 있는가?
   (자주 엑세스 되지만 CACHE 에 없을수 있다)

402. Buffer 에 CACHING 하기 좋은 후보라면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.

403. Direct I/O 가 NETWORK I/O 를 증가 시킨다면 Cost Based 분석 모듈을 끝낸다. 이후로 Buffer Cache를 I/O 하게된다.
   왜냐하면 Direct I/O를 해봐야 Fetch Call에 의해서 수행속도가 매우 저하되므로 Direct I/O를 할필요가 없는것이다.
   다행히 Insert~Select, 혹은 CTAS(Create Table as Select), Delete, Update 등은 NETWORK I/O 를 증가시키지
   않으므로 Direct I/O로 동작할 것이다. 이부분을 테스트 해보라. ROWNUM 을 계속 변경해서 테스트 하다보면 어느시점에
   Direct I/O 가 사라지게 된다. 필자가 Heurastic Rule 이 아닌 Cost Based 분석 모듈임을 확신하게 만든 대목이다.

404. Buffer Cache를 엑세스 하는 Cost가 Direct I/O 의 Cost + Commit 되지않은 데이터를 디스크에 쓰는 Cost 보다 큰지
비교한다.

405. Buffer Cache 엑세스의 Cost가 크다면 Direct I/O 를 해야 하므로 Commit 되지않은 데이터를 디스크에 쓰고
Cost Based 분석 모듈을 종료한다. 이때 Commit 된 데이터도 Buffer Cache에 남아 있을수 있는데 이 데이터 또한 Disk 로
내려쓴다. Cost Based 분석 모듈이 체크포인트를 좋아하는 이유가 이것이다. 체크포인트를 만나면 디스크로 내려쓰므로
Direct I/O 의 Cost 가 줄어들기 때문이다. 참고로 Log File Switch 가 발생하면 같은 효과를 볼수 없다.
필자의 기억으로 (9i 혹은 8i 마지막 버젼 부터) Log File Switch 발생시 체크포인트가 발생하지 않을수 있기 떄문이다.

406. Commit 되지않은 데이터를 디스크에 쓰는 작업이 끝났으면 Cost Based 분석 모듈을 종료한다.

407. 만약 404번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 이번에는 Direct I/O 의 Cost와 비교한다.

408. 407번에서 Buffer Cache를 엑세스 하는 Cost가 크다면 Direct I/O 의 후보 FLAG 를 Y 로 한다.
   이작업은 Cost Based 분석 모듈이 종료된 이후에 5~7번에서 FLAG 값을 이용하기 위함이다.

409. 408번에서 Direct I/O 의 후보 FLAG 를 Y 로 했다면 Buffer Cache를 엑세스 하기위한 준비를 하고 분석을 끝낸다.
   이경우는 Direct I/O 가 좋은지 Buffer Cache I/O 가 좋은지 확실히 알수 없기 때문에 일단 Buffer Cache I/O를 하고 다음번
   Fetch 시에 재평가 하게된다.

410. 407번에서 Buffer Cache를 엑세스 하는 Cost가 비용이 적게든다면 Buffer Cache를 엑세스 하기 위한 준비를 하고 분석을
   끝낸다.

Cost Based 분석 모듈의 모든기능을 설명하였다. 문제는 이렇게 구해진 Cost 가 Plan 상이나 10053 Event 의 Trace 상에 반영되지 않는다는 점이다. Plan 상에서는 Direct I/O를 로 수행되건 Buffer Cache I/O 로 수행되건 간에 Cost의 변화가 전혀 없는데 이것은 매우 아쉬운 부분이다.

Cost 가 반영되지 않을것 이라면 따로 보여주던지...
Cost 는 수행시간에 비례하는 개념인데 엑세스 방식이 바뀜에 따라 수행시간이 바뀜에도 불구하고 Cost 는 아무런 변화가 없는 것이다. 그럴것이라면 위에서 구한 Cost 라도 따로 보여 주었으면 하는것이 필자의 바램이다. 그렇지 않다면 Cost 와 현실과의 괴리감이 11g 에서 증폭될 것이기 때문이다. Plan 상이나 10053 Event 의 Trace 상에 변화가 나타나지 않으므로 Direct I/O를 확인 할수 있는 제일 위운 방법은 10046 이벤트 Trace에서 Direct Path Read 이벤트를 확인 하는것이다.

이제 Cost를 구하기 위해 참조되는 통계정보는 어떤것이 있는지 알아보자.
아래와 같이 CBO 혹은 CBAM(Cost Based Analysis Module)은 여러가지 통계정보들을 사용한다.

-디스크에 미쳐 쓰지못한 BUFFER CACHE 에 존재하는 데이터의 블럭수
-반드시 엑세스 해야하는 데이터의 BLOCK 수
-해당 테이블의 데이터가 Buffer Cache에 존재하는 데이터의 BLOCK 수
-RAC 의 경우 분산된 INSTANCE 의 DISK 블럭수와 CACHE 의 블럭수
-Direct I/O 시 하나의 블럭을 읽는 속도
-Buffer Cache I/O 시 하나의 블럭을 읽는 속도
-RAC 의 경우 다른 인스탄스의 CACHE 된 BLOCK 과 DISK 에 있는 BLOCK 을 가져오는 속도

지면의 한계상 Cost 를 구하는 공식(Formular)은 생략한다. 그것은 다음 기회에...
지면이 길어지면 도망가는 독자를 많이 보아 왔다.^^

결론 :
우리는 이제 Oracle11g 의 새기능인 Cost Based Analysis of Direct I/O Access 에 대하여 알아보았으며 Cost Based 분석모듈이 어떻게 동작하는지도 알게 되었다. 한가지더 첨언 하자면 위의 기능이 끝이 아니다 라는 사실을 강조 하고 싶다.

11g 부터 Parallel 힌트를 사용한 Select 문이 Serial 하게 수행될수있다. 기준은 테이블의 블럭수가 _small_table_threshold
파라미터 보다 작아야 한다. 필자는 이기능을 더 좋아한다. 왜냐하면 덩치가 작은 테이블은 사실상 Parallel Query 가 필요없으며 오히려 수행속도가 저하되는 경우를 많이 보아왔기 떄문이다.

기억하자!
11g 부터는 Parallel SQL이 Serial로 수행될수 있으며 Parallel 을 사용하지 않는 Full Table Scan 이 Direct Path Read 가 가능하다는 사실을..

P.S
작년 봄부터 이글을 쓸지 고민하다가 이제서야 올리게 되었다. 위의 글이 난위도가 있기 때문에 쉽게 쓰려고 고민 한 것이다. 어려운 개념이지만 단 한명이라도 이글을 보고 이해한다면 필자의 기쁨이 될것이다.

아래의 link 는 유명한 오라클 Guru 인 Doug Burns의 블로그인데 몇년째 위에서 설명한 사실들을 몰라서 고생하고 있다.
누가 대신 설명해주길 바란다.(필자는 영어가 짧아서....)

http://oracledoug.com/serendipity/index.php?/archives/1321-11g-and-direct-path-reads.html
http://oracledoug.com/serendipity/index.php?/archives/1320-Parallel-Query-and-11g-Part-2.html

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

모든 상식을 의심하라 (16)	2009.11.05
Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실 (2)	2008.06.23

Posted by extremedb

,

InList / Concatnation / Range Scan Control 하기

Oracle/Data Access Pattern 2008. 12. 12. 02:44

프로젝트를 하다보면 결합인덱스의 2번째 혹은 3번째 컬럼에 IN 조건을 많이 사용하는것이 관찰된다.
오늘은 결합인덱스 에서 IN 을 사용한경우 나타날수있는3가지 경우(InList, Concatnation, Range Scan)에
대하여 알아 보겠다.

버젼 11.1.0.6
인덱스 : EMP_JOB_MGR_SAL_IDX (JOB_ID, MANAGER_ID, SALARY)

아래는 인덱스의 첫번째 컬럼이 = 조건으로 들어오고 두번째 컬럼이 IN 변수조건으로 들어 왔을때의
전통적인 방식의 PLAN 이다.

EXPLAIN PLAN FOR
SELECT /*+ RULE */
a.*
FROM hr.employees a
WHERE job_id = :v_job
AND manager_id IN (:v_manager1, :v_manager2);

select * from table(dbms_xplan.display);

------------------------------------------------------------
| Id | Operation    | Name    |
------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | CONCATENATION | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
|* 3 |    INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES |
|* 5 |    INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX |
------------------------------------------------------------

별로 특별할것이 없는 전통적인 방식의 CONCATENATION Plan 이 나왔다.
그렇다면 Rule 힌트를 빼면 어떻게 되는겠는가?
CONCATENATION Plan 대신에 InList Plan 을 기대하는가?
아래의 Plan 을 보자.

EXPLAIN PLAN FOR
SELECT a.*
FROM hr.employees a
WHERE job_id = :v_job
AND manager_id IN (:v_manager1, :v_manager2) ;

---------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    | | 1 |    62 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |    62 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JOB_ID"=:V_JOB)
   filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

InList Plan 이 나오지 않는다.
어떻게 된것인가?
혹자는 NO_EXPAND 힌트를 사용하라고 이야기 하지만 그것은 CONCATENATION Plan 을 방지하는 힌트일뿐
InList Plan으로 유도하지 않는다.

EXPLAIN PLAN FOR
SELECT /*+ NO_EXPAND */
a.*
FROM hr.employees a
WHERE job_id = :v_job
AND manager_id IN (:v_manager1, :v_manager2);

---------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    | | 1 |    62 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |    62 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("JOB_ID"=:V_JOB)
   filter("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER2))

예상과는 다르게 Range Scan 이 나오고 IN 절은 Filter 조건으로 되고말았다.
그렇다면 이런경우 도데체 어떻게 InList Plan 으로 유도 한단 말인가?
이런경우에 사용할수 있는 힌트가 바로 NUM_INDEX_KEYS 이다.
아래의 SQL 을보자.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
   a.*
FROM hr.employees a
WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2)
   ;

----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |    62 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR    | | | |    |    |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |    62 | 2 (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
"MANAGER_ID"=TO_NUMBER(:V_MANAGER2)))

깔끔하게 InList Plan 으로 유도 되었다.
그렇다면 NUM_INDEX_KEYS 힌트의 마지막 인자인 숫자 2는 무엇을 의미하는가?
결합 인덱스의 2번째 컬럼(MANAGER_ID) 까지는 InList Plan 으로 풀리는 것을 의미한다.
거꾸로 이야기하면 NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) 로 힌트를 주면 인덱스의 3번째
컬럼인 SALARY 조건에 IN 조건을 주어도 InList Plan 으로 풀리지 않는다.
아래의 SQL및 Plan 을 보고 InList Plan 이 나왔다고 생각하면 오산이다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 2) */
   a.*
FROM hr.employees a
WHERE job_id = :v_job
   AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
   AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |    62 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR    | | | |    |    |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |    62 | 2 (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
"MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)))
   filter("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR
"SALARY"=TO_NUMBER(:V_SAL3))

위 Plan 에서의 InList 는 Salary 조건과는 상관없이 Job_id 와 Manager_id 두가지 조건만으로 InList 가
나온것이다.
Salary 조건은 역시 Filter 로 빠진것을 알수 있다.
아래처럼 3으로 바꾼다면 인덱스의 3번째컬럼 까지 InList 로 풀리는걸 볼수 있다.

EXPLAIN PLAN FOR
SELECT /*+ NUM_INDEX_KEYS(a EMP_JOB_MGR_SAL_IDX 3) */
a.*
FROM hr.employees a
WHERE job_id = :v_job
AND manager_id IN (:v_manager1, :v_manager2, :v_manager3)
AND salary IN (:v_sal1, :v_sal2, :v_sal3);

----------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |    62 | 2 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | |    |    |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 |    62 | 2 (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN    | EMP_JOB_MGR_SAL_IDX | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("JOB_ID"=:V_JOB AND ("MANAGER_ID"=TO_NUMBER(:V_MANAGER1) OR
"MANAGER_ID"=TO_NUMBER(:V_MANAGER2) OR "MANAGER_ID"=TO_NUMBER(:V_MANAGER3)) AND
("SALARY"=TO_NUMBER(:V_SAL1) OR "SALARY"=TO_NUMBER(:V_SAL2) OR "SALARY"=TO_NUMBER(:V_SAL3)))

하지만 manager_id 종류가 많지않은 경우에는 Range Scan이 더 나은 성능을 보이는 경우가 많으므로
무조건 적용해서는 안된다.
오늘 사용한 Rule 힌트나 NUM_INDEX_KEYS 힌트는 Production 시스템에서는 사용하면 안된다.
사용하려면 deprecate 된 힌트나 undocument 힌트를 사용한 SQL 만 따로 목록을 만들어서 관리해야 할것이다.

PS: 테스트 환경은 11g 에서 테스트 됬지만 10g 에서 테스트 해도 동일한 결과를 얻을수 있다.

저작자표시 비영리 동일조건

'Oracle > Data Access Pattern' 카테고리의 다른 글

Oracle Data Access Pattern을 정복하라 (18)	2009.10.29
Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실 (2)	2008.06.23
Full Table Scan 의 비밀 (8)	2008.06.15

Posted by extremedb

,

Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상

Oracle/Data Access Pattern 2008. 7. 28. 21:48

Min/Max 처리시 인덱스가 있을경우 대부분의 Plan 이 자동으로 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 처리가 된다.
위의 처리가 지원 되면서 부터는 전통적인 튜닝 방법인 INDEX_DESC 힌트 사용후에 WHERE 절에 ROWNUM = 1 을 추가하는 방식이 더이상 필요없는걸까?
모든규칙에는 예외가 있다는 말을 기억하자.
파티션이 되어있는 테이블은 PLAN 상에 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 로 처리가 되어
이상이 없는것 처럼 보이지만 비효율이 있다.
심지어 INDEX_DESC + ROWNUM = 1 조합도 비효율이 있다.
아래의 스크립트를 보자.

아래는 전형적인 MAX 일자를 구하는 SQL 이다.
계좌번호별로의 특정일자보다 작은일자중에 MAX 일자를 구하는 로직이다.
인덱스는 계좌번호 + 거래일자 로 되어있고 해당테이블은 거래일자기준으로 월별로 RANGE 파티션이 적용되어 있다.
물론 인덱스는 Global 이 아니다.

explain plan for
select max(거래일자)
from 일별계좌거래내역 b
where b.계좌번호 = '1234567890'
and b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 654469635

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    | | 1 |    23 |    34 (0)| | |
| 1 | SORT AGGREGATE | | 1 |    23 |    | | |
| 2 | PARTITION RANGE ITERATOR    | | 128 | 2944 |    34 (0)|    67 | 1 |
| 3 |    FIRST ROW    | | 128 | 2944 |    34 (0)| | |
| 4 | INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 | 128 | 2944 |    34 (0)|    67 | 1 |
----------------------------------------------------------------------------------------------------------

해석 : 뭔가 특별히 이상할것이 없는 PLAN 이다. 즉 예측 실행계획으로는 정확한 정보를 알수가 없다.
단지 Rows 컬럼과 Pstart/Pstop 컬럼이 좀 이상하긴 하다.

그러면 위의 쿼리를 다른방식으로 좀더 자세히 살펴보자.
SQL TRACE 를 사용해도 되지만 여기서는 dbms_xplan.display_cursor 로 실행된 SQL 의 Plan을 구한다.

1. 원본쿼리

select /*+ gather_plan_statistics */
max(거래일자)
from 일별계좌거래내역 b
where b.계좌번호 = '1234567890'
and b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 654469635

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | |    1 | | |    1 |    91 |
| 2 | PARTITION RANGE ITERATOR    | |    1 |    67 | 1 | 11 |    91 |
| 3 |    FIRST ROW    | | 67 | | | 11 |    91 |
|* 4 | INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 | 67 |    67 | 1 | 11 |    91 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."계좌번호"='1234567890' AND "B"."거래일자"<'20080729')

해석 : Plan 테이블의 Starts 컬럼을 보면 min/max 구하는 작업을 모든파티션마다 67번 반복하였다.
   Pstart, Pstop 컬럼에서도 보듯이 67 개의 파티션을 모두 엑세스 하였다.
   그결과 buffers 컬럼에 91 블럭이 나왔다



2. 1번쿼리에서 index_desc 힌트와 where 절에 rownum = 1 을 추가한다.(전통적인 튜닝방법)

select /*+ gather_plan_statistics index_desc(b) */ --> 힌트추가
   max(거래일자)
from 일별계좌거래내역 b
where b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    rownum = 1; --> where 절 추가

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 4244920012

----------------------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE    | |    1 | | |    1 | 6 |
|* 2 | COUNT STOPKEY    | |    1 | | |    1 | 6 |
| 3 |    PARTITION RANGE ITERATOR    | |    1 |    67 | 1 |    1 | 6 |
|* 4 | INDEX RANGE SCAN DESCENDING| PK_일별계좌거래내역 |    2 |    67 | 1 |    1 | 6 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM=1)
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729')

해석 : Pstart, Pstop 컬럼을 보면 67 개의 파티션을 모두 엑세스 한거 같지만 rownum = 1 이 추가되어 있으므로 Starts 컬럼을 보면 min/max 구하는 작업을 단 두번만 하였으므로 buffers 컬럼에 6 블럭이 나왔고
Starts 컬럼으로 예상해 보면 단 두개의 파티션만을 엑세스 하였다.
하나의 파티션만 엑세스 해야 하지만 Index Range Scan 시에 1 row 를 더 Scan 하는것과 같은 이치라고 할수 있다.

3. 특정파티션만 access 하게 쿼리를 b.거래일자 >= '20070701' 추가함. (최적의 방법)

select /*+ gather_plan_statistics index(b) */
   max(거래일자)
from 일별계좌거래내역 b
where b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    b.거래일자 >= '20080701'; --> 파티션을 특정월로 고정함

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 161631393

---------------------------------------------------------------------------------------------------------
| Id | Operation | Name    | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
| 1 | SORT AGGREGATE | |    1 | | |    1 | 3 |
| 2 | PARTITION RANGE SINGLE    | |    1 |    67 |    67 | 1 | 3 |
| 3 |    FIRST ROW    | |    1 | | | 1 | 3 |
|* 4 | INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |    1 |    67 |    67 | 1 | 3 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729' AND
"B"."CAM_CLC_BAS_DT">='20080701')

해석 : Pstart, Pstop 컬럼에서 보듯이 단하나의 파티션(67번째 파티션) 에서 min/max 작업을 하였다.
   그결과 buffers 컬럼에 3 블럭이 나왔다.

4 결론
min/max - first row Plan 은 index_desc + rownum 과 성능상 같을수 있으나 파티션이 적용된 테이블이면서 Local 인덱스를 사용하는경우 전체파티션에 대하여 min/max - First Row 처리를 하므로 index_desc + rownum 처리를 하여야 비효율을 없앨수 있다. 하지만 이경우도 최적은 아니므로 특정파티션을 지정하여야 단 하나의 파티션만 엑세스 한다는 점을 기억하자.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실 (2)	2008.06.23
Full Table Scan 의 비밀 (8)	2008.06.15
LIKE '%XXX' 검색에 에 대한 오해와 진실 (6)	2008.06.03

Posted by extremedb

,

Re) LIKE '%XXX' 검색에 에 대한 오해와 진실

Oracle/Data Access Pattern 2008. 6. 23. 00:06

지난번에 필자는 LIKE '%XXX%' 검색에 에 대한 글을 실었었다.
그글은 필자의 착각 이었으며 지인(조동욱님) 의 도움으로 Trace 상의 Plan은 Range Scan 이지만
일량이 INDEX FULL SCAN 과 같다는걸 알게 되었다.
이경우는 % + 변수 + % 형태에서만 일어나는것 같다.

자세한내용은 아래 URL 에서 내용과 댓글을 참조하면 된다.
http://scidb.tistory.com/entry/LIKE-XXX-검색에-에-대한-오해와-진실

다시한번 동욱님에게 감사드린다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28
Full Table Scan 의 비밀 (8)	2008.06.15
LIKE '%XXX' 검색에 에 대한 오해와 진실 (6)	2008.06.03

Posted by extremedb

,

Full Table Scan 의 비밀

Oracle/Data Access Pattern 2008. 6. 15. 00:14

Full table scans (FTS) 에 관한 안개가 서서히 걷히고 있다.
FTS 는 DW환경에서는 필수적이며 심지어 OLTP 시스템의 온라인 환경에서도 FTS 는 필요하다.
하지만 FTS 가 일어나면 DBMS에 여러가지 악영향을 끼친다.

첫째로는 I/O 가 2단계(디스크에서 Buffer Cache 에 올리고 다시 Buffer Cache 에서 Read 함)로 인한 성능저하다.
이말은 디스크에서 바로 읽어올수 있으면 성능이 향상된다는 의미이다.
두번째로는 2단계 I/O 로 인해여 Buffer Cache 에 있는 데이터 들이 Aging Out 이 될가능성이 있다는 것이다.

그렇다면 2가지의 악영향을 제거하고 FTS 를 어떻게 효율적으로 수행할수 있을까?
이질문에 답하기 위해서는 2가지의 경우를 생각해야만 한다.

1.Buffer Cache 를 우회하여 성능을 향상시켜야 하는경우(Direct Path Read) --> 빠른성능을 보장하기위함
이경우의 문제점은 조회화면인 경우 에 Direct Path Read 를 실행해야 한다는 것이다.
하지만 통상 Direct Path Read 는 Insert 시에 Append 힌트를 사용하거나 Parallel Process 를 사용해야만 했다.
Parallel Process 를 이용하지 않고 Select 만 실행시키는 조회화면을 위해서 본 블로그에서는 이문제에대한 해법을 제시한다.
2.데이터를 Buffer Cache 에 올려서 Hit Ratio 를 올려야 하는경우(해당 데이터를 자주 FTS 해야 하는경우)

이경우 필자의 해법은 단 2가지이다.

1.Buffer Cache 를 우회하여 성능을 향상시켜야 하는경우의 Solution:

"_serial_direct_read = true" 를 사용하라

기존의 관행적으로 수행되던 방식으로는 해법이 없다.
왜냐하면 Full Scan(테이블이나 인덱스)시에 Insert - Select 가 아니라 Select 만 수행할때는 Buffer cache 를 우회하여 빠른성능의 Direct I/O 를 사용할수 있는 방법이 없기 떄문이다.
물론 Insert - Select 라면 Append 힌트를 사용하면 Buffer cache 를 우회하여 빠른성능의 Direct I/O 를 사용할수가 있다는건 많이 알려진 사실이다.
혹자는 "SELECT 시에 Parallel Process 를 사용하면 된다" 라고 하지만 실제 Online 운영환경에서 Parallel Process 를 사용한다는건 얻는 이득보다 손실이 많기 때문에 견딜수 없다.
이럴때 사용할수 있는것이 _serial_direct_read 파라미터이다.
"_serial_direct_read = true" 로 바꾸면 single threaded multi block read 환경에서 direct path reads가 일어난다.
위파라미터를 적용하고 FTS 를 실행하면 오라클은 체크포인트를 수행하여 해당 dirty buffer 들을 모두 디스크로 내리고 direct path reads를 발생시킨다.
아래의 실행결과를 보자

TEST 실행:

SQL> alter session set "_serial_direct_read" = true;
Session altered.

SQL> select avg(id) from test;

SQL> select event, total_waits from v$session_event
2 where sid = (select sid from v$mystat where rownum = 1)
3 and event like '%read%';

EVENT TOTAL_WAITS
------------------------------ -----------
direct path read 124 --> single mode select 에서 direct path read 만 발생됨.
Labels: buffer cache _serial_direct_read direct path read full table scan parallel

2.데이터를 Buffer Cache 에 올려서 Hit Ratio 를 올려야 하는경우:

"SELECT 문에 CACHE 힌트를 사용하라"

위방법을 사용하면 해당 데이터가 Aging Out 될 확률이 현저히 줄어든다.
하지만 대용량 데이터의 Select 시에 이방법을 사용하면 무리가 따르기 마련이다.
그런경우 아래의 방법을 사용해야 한다.

"Buffer Cache 의 Keep Pool 을 사용하라"

이방법의 보다 자세한 내역은 엑셈의 조동욱님 블로그에 자세히 나와 있다.
정말 주인장 동욱님의 포스가 느껴지는 블로그이다.

결론

1.single mode(Parallel 사용안함) 조회시 FTS 를 사용할경우 시에 Buffer Cache 를 우회하는 유일한 방법은 "_serial_direct_read = true" 이다.
반드시 해당 세션에서만 적용시켜야 함을 잊지말자.
upgrade 시에 해당 파라미터는 없어질수 있으므로 유의 해야한다.
또한 _로 시작하는 파라미터는 default 값을 바꾸면 매우 위험하다는걸 알아야 한다.
2.FTS 시에 데이터를 Aging Out 되지않게 하는방법은 Select 문에 Cache 힌트를 사용하거나 해당테이블에 Keep Pool 을 적용하는것이다.

물론 11g 라면 Result Cache를 사용하는 것이 바람직 하다.
Result Cache는 shared pool에 Result Cache Memory로 불리는 영역에 SQL 및 PL/SQL funtion의 결과를 저장하는 것이다.
특정 query가 반복적으로 수행될 때 이 결과를 캐시하여 그 다음 부터는 해당 query를 다시 execute하는 것이 아니라 캐시 메모리에 저장된 결과값을 그대로 가지고 오게 된다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실 (2)	2008.06.23
LIKE '%XXX' 검색에 에 대한 오해와 진실 (6)	2008.06.03

Posted by extremedb

,

LIKE '%XXX' 검색에 에 대한 오해와 진실

Oracle/Data Access Pattern 2008. 6. 3. 19:24

거의모든 튜닝책(원서포함)에 LIKE '%XXX 검색은 테이블 혹은 인덱스를 full scan 한다고 되어있다.
하지만 과연 이것이 사실일까?
필자는 이런류의 오류를 "공공의 오류"라고 표현한다.
대부분 혹은 대다수의 개발자나 심지어 튜너라고 하는사람들 까지 이 공공의 오류를 믿고있다.
물론 필자도 예외는 아니며 공공의 오류를 범하지 않기위해 꾸준히 노력 하고 있다.

아례 예제를 보면서 이런 오해에서 벗어나도록 하자.

1.테스트 환경
버젼 : 10gR2(10.2.0.3) ,
Optimizer mode :all_rows/first_row_n 상관없이 실행가능
계정 : scott/tiger

2. ENAME 에 인덱스 생성

CREATE INDEX EMP5 ON EMP (ENAME);

EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> Analyze

3. LIKE '%XXX' 실행

select *
from emp
where ename like '%'||:V_ENAME||'%'; --> 변수에 'A' 대입

-- 결과는 당연히 A 를 포함한 결과만 나왔으며 ENAME 으로 Sort 되어있다.

4. Trace 내용확인

Compile Time : 2008/06/03 19:37:48
Trace File Name : /ora_dump/NBCORET/udump/nbcoret2_ora_6064.trc
Trace Version : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Environment : Array Size = 10
Long Size = 80

********************************************************************************

select * from emp where ename like '%'||:V_ENAME||'%'

Call Count CPU Time Elapsed Time Disk    Query    Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse    1    0.000    0.000    0    0    0    0
Execute    1    0.000    0.001    0    0    0    0
Fetch    2    0.000    0.000    0    4    0    7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total    4    0.000    0.001    0    4    0    7

Elapsed Time for Client(sec.): 0.007
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
7 TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=592 us)
7 INDEX RANGE SCAN EMP5 (cr=2 pr=0 pw=0 time=872 us)OF EMP5 (NONUNIQUE)

-- 정확히 'A' 가 포함된 블럭만 READ 했음을 알수 있다.

5.결론

과거에는 LIKE '%XXX%' 류를 빠르게 수행하기 위하여 Domain Index 사용하거나 비싼 검색엔진을 도입하였다.
하지만 이제부터는 Like 를 사용해도 똑같은 효과를 낼수있음을 명심해야 할것이다.

편집후기:
어떻게 LIKE '%XXX%' 구분이 index range scan 을 할수 있는지는 필자도 현재 분석중이다.

'Oracle > Data Access Pattern' 카테고리의 다른 글

Oracle11g 에서 Full Table Scan의 성능 향상 (8)	2009.07.22
InList / Concatnation / Range Scan Control 하기 (4)	2008.12.12
Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상 (2)	2008.07.28
Re) LIKE '%XXX' 검색에 에 대한 오해와 진실 (2)	2008.06.23
Full Table Scan 의 비밀 (8)	2008.06.15

Posted by extremedb

,


	오동규 블로그 odong91@naver.com by extremedb

'Oracle/Data Access Pattern'에 해당되는 글 17건

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

'Oracle > Data Access Pattern' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바