'Oracle/Optimizer' 카테고리의 글 목록

COUNT(Distinct 컬럼)의 성능

Oracle/Optimizer 2013. 5. 6. 06:00

집계함수 내부에 Distinct를 사용할 수 있다는 것은 많은 사람들이 알고 있다. 하지만 실제로 그렇게 사용했을 때 내부적으로 무슨 일이 일어나는지 아는 사람은 드물다. 한걸음 더 나아가서 COUUNT(COL) 대신에 COUNT(Distinct COL)를 사용했다면 분명히 추가적인 부하가 존재할 것인데, 그 부하를 어떻게 해결할 것인가를 아는 사람은 거의 없을 것이다. 만약 그렇다면 SQL을 실행할 때 마다 성능이 느려질 것이고 문제를 해결할 수 없을 것이다. 여러분들에게는 그런 일이 발생하지 않는다. 이미 이 글을 읽고 있기 때문이다.

이 글은 위에서 언급된 두 가지 문제를 다룬다. 즉 내부적으로 어떤 변화가 발생하는지 알아보고, 추가적인 부하를 어떻게 없앨 수 있는지도 연구해보자.

SQL 변경에 따른 내부적인 변화를 알아보는 가장 좋은 방법은 비교하는 것이다. 다시 말해, COUUNT(COL)로 실행했을 때의 일량과 COUNT(Distinct COL)로 사용했을 때의 일량을 비교해 보는 것이다. 따라서 우리는 SQL 두 개를 실행한 다음 각각의 작업량(실행통계)을 비교할 것이다.

환경: 오라클 11.2.0.1

CREATE TABLE SALE_T AS SELECT * FROM SALES;

SELECT /*+ NO_USE_HASH_AGGREGATION */
        S.PROD_ID
       ,COUNT(S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID;

-----------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:01.12 |    4440 |          |
|   1 | SORT GROUP BY     |        |      1 |     72 |00:00:01.12 |    4440 | 6144 (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.32 |    4440 |          |
-----------------------------------------------------------------------------------------

위의 SQL이 실행되는데 시간이 1.12초 걸렸고 PGA는 6144 Byte를 소모하였다. 그런데 아래처럼 COUNT에 DISTINCT를 추가를 추가한다면 어떻게 될까?

SELECT /*+ NO_QUERY_TRANSFORMATION */
        S.PROD_ID
       ,COUNT(DISTINCT S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID;

-----------------------------------------------------------------------------------------
| Id | Operation          | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |     72 |00:00:02.20 |    4440 |          |
|   1 | SORT GROUP BY     |        |      1 |     72 |00:00:02.20 |    4440 |14336 (0)|
|   2 |   TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
-----------------------------------------------------------------------------------------

작업량이 증가된 이유
Distinct 만 추가했을 뿐인데 시간이 약 두 배나 걸리고 PGA도 약 두 배로 사용하였다. 그 이유는 Operation에는 나오지 않지만 내부적으로 SORT UNIQUE가 실행되기 때문이다. 즉 PROD_ID별로 SORT GROUP BY를 했음에도 CHANNEL_ID 별로 SORT UNIQUE를 다시 실행해야 한다. 약 92만 건의 데이터를 CHANNEL_ID 별로 SORT한 후에 중복을 제거하는 작업이 Distinct에 의해서 추가된 것이다. 그렇기 때문에 FULL TABLE SCAN의 수행시간은 거의 같지만 SORT GROUP BY의 수행시간이 0.8초에서 1.87초로 늘어나고 PGA사용량도 두 배가 된 것이다.

비효율을 제거하는 방법
첫 번째 의문점인 집계함수에 Distinct가 추가되면 어떤 일이 발생하는지 알아냈다. 그렇다면 두 번째 문제인 비효율(추가적인 Sort와 중복제거)을 없애는 방법은 무엇일까? SQL을 아래처럼 튜닝 할 수 있을 것이다.

SELECT /*+ NO_USE_HASH_AGGREGATION */
        PROD_ID,
        COUNT(S.CHANNEL_ID),
        SUM(S.AMOUNT_SOLD),
        SUM(S.QUANTITY_SOLD)
FROM (SELECT /*+ NO_USE_HASH_AGGREGATION */
                 S.CHANNEL_ID ,
                 S.PROD_ID ,
                 SUM(S.AMOUNT_SOLD) AMOUNT_SOLD,
                 SUM(S.QUANTITY_SOLD) QUANTITY_SOLD
           FROM SALE_T S
          GROUP BY PROD_ID, CHANNEL_ID) S
GROUP BY S.PROD_ID ;

-------------------------------------------------------------------------------------------
| Id | Operation            | Name   | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |        |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 | SORT GROUP BY NOSORT|        |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               |        |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |        |      1 |    228 |00:00:01.39 |    4440 |18432 (0)|
|   4 |     TABLE ACCESS FULL| SALE_T |      1 |    918K|00:00:00.33 |    4440 |          |
-------------------------------------------------------------------------------------------

비록 PGA 사용량은 약간 늘어났지만 수행시간은 DISTINCT가 없는 SQL과 비슷해졌다. 먼저 PROD_ID, CHANNEL_ID로 GROUP BY 되었기 때문에 인라인뷰 외부에서는 Distinct를 할 필요가 없다. 다른 말로 표현하면 먼저 GROUP BY했기 때문에 PROD_ID 별로는 CHANNEL_ID가 UNIQUE 하다. 따라서 인라인뷰 외부에서는 Distinct가 필요 없게 된 것이다.

더 좋은 것은 실행계획의 Id 1을 보면 SORT GROUP BY NOSORT가 나온다. NOSORT가 나온 이유는 인라인뷰가 이미 PROD_ID로 SORT 되어있기 때문에 더 이상의 SORT는 필요 없기 때문이다. 따라서 추가적인 Group By의 부하는 거의 없다. 이렇게 튜닝하면 Distinct에 의한 SORT UNIQUE의 부하가 대부분 사라진다.

옵티마이저가 사람을 대신한다
집계함수에 Distinct를 사용한다면 무조건 위의 SQL처럼 튜닝 해야 하는가? 그건 아니다. 오라클 11.2를 사용한다면 Logical Optimizer가 SQL을 자동으로 변경시켜 준다. 아래의 튜닝 되지 않은 SQL을 실행시켜보자.

SELECT /*+ NO_USE_HASH_AGGREGATION(@"SEL$5771D262") */
        S.PROD_ID
       ,COUNT(DISTINCT S.CHANNEL_ID)
       ,SUM(S.AMOUNT_SOLD)
       ,SUM(S.QUANTITY_SOLD)
FROM SALE_T S
GROUP BY S.PROD_ID

---------------------------------------------------------------------------------------------
| Id | Operation            | Name     | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |      1 |     72 |00:00:01.39 |    4440 |          |
|   1 | SORT GROUP BY NOSORT|          |      1 |     72 |00:00:01.39 |    4440 |          |
|   2 |   VIEW               | VW_DAG_0 |      1 |    228 |00:00:01.39 |    4440 |          |
|   3 |    SORT GROUP BY     |          |      1 |    228 |00:00:01.39 |    4440 |18432 (0)|
|   4 |     TABLE ACCESS FULL| SALE_T   |      1 |    918K|00:00:00.32 |    4440 |          |
---------------------------------------------------------------------------------------------

Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      …생략
      TRANSFORM_DISTINCT_AGG(@"SEL$1")
      …생략
      END_OUTLINE_DATA
*/

오라클이 내부적으로 TRANSFORM_DISTINCT_AGG 힌트를 사용하였고 SQL을 자동으로 변경하였다. 실행계획도 튜닝된 SQL과 같다. 즉 11.2 버전부터는 집계함수내부에 Distinct가 존재하면 Logical Optimizer가 SQL을 변경시킴으로써 성능이 향상되는 것이다. 이 기능을 Distinct To Aggregation이라고 부른다.

아래는 10053 Trace 파일의 내용이다. 내용이 많지만 개념은 간단하다. 쿼리변환 전의 SQL을 보여주고 쿼리변환 후의 SQL을 보여준다. 그리고 두 개의 SQL 사이에는 쿼리블럭 SEL$1이 Distinct To Aggregation 기능에 의해서 두 개로 찢어지는 과정(SPLIT QUERY BLOCK)을 보여준다.

DAGG_TRANSFORM: transforming query block SEL$1 (#0)
qbcp (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
pgactx->ctxqbc (before transform):******* UNPARSED QUERY IS *******
SELECT "S"."PROD_ID" "PROD_ID",COUNT(DISTINCT "S"."CHANNEL_ID") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("S"."AMOUNT_SOLD") "SUM(S.AMOUNT_SOLD)",SUM("S"."QUANTITY_SOLD") "SUM(S.QUANTITY_SOLD)" FROM "TLO"."SALE_T" "S" GROUP BY "S"."PROD_ID"
Registered qb: SEL$5771D262 0xea51918 (SPLIT QUERY BLOCK FOR DISTINCT AGG OPTIM SEL$1; SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$5771D262 nbfros=1 flg=0
fro(0): flg=0 objn=76169 hint_alias="S"@"SEL$1"

Registered qb: SEL$C33C846D 0xde78e84 (MAP QUERY BLOCK SEL$5771D262)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$C33C846D nbfros=1 flg=0
fro(0): flg=5 objn=0 hint_alias="VW_DAG_0"@"SEL$C33C846D"

qbcp (after transform):******* UNPARSED QUERY IS *******
SELECT "VW_DAG_0"."ITEM_2" "PROD_ID",COUNT("VW_DAG_0"."ITEM_1") "COUNT(DISTINCTS.CHANNEL_ID)",SUM("VW_DAG_0"."ITEM_4") "SUM(S.AMOUNT_SOLD)",SUM("VW_DAG_0"."ITEM_3") "SUM(S.QUANTITY_SOLD)" FROM (SELECT /*+ NO_USE_HASH_AGGREGATION */ "S"."CHANNEL_ID" "ITEM_1","S"."PROD_ID" "ITEM_2",SUM("S"."QUANTITY_SOLD") "ITEM_3",SUM("S"."AMOUNT_SOLD") "ITEM_4" FROM "TLO"."SALE_T" "S" GROUP BY "S"."CHANNEL_ID","S"."PROD_ID") "VW_DAG_0" GROUP BY "VW_DAG_0"."ITEM_2"

Distinct To Aggregation 쿼리변환은Heuristic Query Transformation에 속한다. _optimizer_distinct_agg_transform 파라미터로 이 기능을 제어할 수 있고 Default로 true이다. 힌트로는 TRANSFORM_DISTINCT_AGG / NO_TRANSFORM_DISTINCT_AGG 를 사용할 수 있다.

이제 우리는 집계함수에 Distinct가 추가되면 SORT UNIQUE의 부하로 성능이 느려짐을 안다. 또 Distinct 대신에 Group By를 사용하여 그 부하를 대부분 없애는 방법도 알게 되었다. 하지만 이제는 이런 일들을 옵티마이저가 대신하게 되었다. 이런 기능들이 계속 추가된다면 언젠가는 튜너라는 직업이 사라지 않을까? 만약 튜너가 없어진다면, 그 후에 옵티마이저를 연구하는 사람까지 사라질 것이다. 왜냐하면 옵티마이저를 연구하는 사람은 튜너를 위해 존재하기 때문이다.

PS
다들 잘 지내시죠? 개인 사정으로 지난 2년간 뵙지 못했습니다. 5월달에 글을 한 두개 더 올릴 생각 입니다. 기대해 주세요. 5월 중순 부터는 바빠서 글쓰기가 힘들 것 같습니다.

그럼 건강하세요.

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

'Oracle > Optimizer' 카테고리의 다른 글

Cardinality Feedback이 위험할 때 (10)	2010.10.25
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04

Posted by extremedb

,

Cardinality Feedback이 위험할 때

Oracle/Optimizer 2010. 10. 25. 00:01

부제: Cardinality Feed Back의 개념과 사용예제

이번 글은 난이도가 높으므로 익숙하지 않은 사람은 Cardinality Feedback의 개념 정도만 이해하기 바란다. 물론 이 블로그를 꾸준히 구독한 독자라면 어려움 없이 볼 수 있다.

현재 많은 시스템이 Oracle11g로 옮겨가고 있다. 11g는 새로운 기능이 많이 추가되었다. 하지만 새롭고 좋은 기능이라도 완벽하지 못하면 문제가 될 수 있다. 오늘은 11g의 새 기능 때문에 성능문제가 발생하는 경우를 소개한다.

시스템이 운영 중에 있을 때 가장 곤욕스러운 경우 중 하나는 SQL의 실행계획이 갑자기 바뀌어 성능이 나빠지는 것이다. SQL과 인덱스 그리고 통계정보가 모두 바뀌지 않아도 실행계획은 바뀔 수 있다. 예를 들면 Oracle11g의 기능인 Cardinality Feedback을 사용함으로 해서 얼마든지 실행계획이 바뀔 수 있는 것이다. 이번 시간에는 실행계획이 변경되는 원인 중 하나인 Cardinality Feedback 의 개념과 작동방식에 대해 알아보고 이것이 언제 문제가 되는지 분석해 보자. 이번에 소개할 예제는 종합적이다. Cardinality Feedback + Cost Based Query Transformation + Bloom Filter가 결합된 것이다. 이를 놓친다면 이들이 어떻게 결합되는지 알 수 없을 뿐만 아니라 성능이 악화된 원인을 파악할 수 없다.

예측, 실행, 비교, 그리고 전달

소 잃고 외양간 고친다는 말이 있다. 이미 늦었다는 이야기 이지만 좋은 말로 바꾸면 실수를 다시 하지 않겠다는 의지이다. cardinality feedback(이후 CF)도 이와 비슷한 개념이다. 예를 들어 col1 = ‘1’ 이라는 조건으로 filter되면 백만 건이 return된다고 옵티마이져가 예측해서 full table scan을 했다. 하지만 예측과 달리 실행결과가 100건이 나왔다면? 해당 SQL을 다시 실행할 때는 full table scan보다는 index scan이 유리할 것이다. 그런데 같은 SQL을 두 번째 실행할 때 "실제로는 백만 건이 아니라 100건 뿐이야"라는 정보를 옵티마이져에게 알려주는 전달자가 필요하다. 그 전달자가 바로 CF이다. CF가 없으면 결과가 100건 임에도 SQL을 실행 할 때마다 full table scan을 반복할 것이다. 결국 CF는 악성 실행계획을 올바로 수정하는 것이 목적이며 매우 유용한 기능임을 알 수 있다. CF의 단점은 최초에 한번은 full table scan이 필요하다는 것이다. 왜냐하면 실행해서 결과가 나와야만 실제 분포도(건수)를 알 수 있기 때문이다.

CF는 어떻게 실행되나?

CF는 같은 SQL을 두 번 이상 실행했을 때 적용된다. 그 이유는 아래의 CF 적용순서를 보면 알 수 있다.

1. 최초의 실행계획을 작성할 때(Hard Parsing 시에) 예측 분포도가 계산된다.

2. SQL이 실행된다. 한번은 실행 해봐야 예측 분포도와 실제 분포도를 비교할 수 있다.

3. 예측 분포도와 실제 분포도의 값이 차이가 크다면 실제 분포도를 저장한다.

4. 두 번째 실행될 때 CF에 의해 힌트의 형태로 옵티마이져에게 전달되어 실제 분포도가 적용된다. 이때 분포도뿐만 아니라 실행계획이 바뀔 수 있다. 두 번째 이후로 실행될 때는 CF가 계속 적용된다.

CF를 발생시켜보자

실행환경 :Oracle 11.2.0.1

ALTER SYSTEM FLUSH SHARED_POOL;

ALTER SESSION SET "_OPTIMIZER_USE_FEEDBACK" = TRUE; -- CF를 활성화 한다. default로 true이다.

SELECT /*+ GATHER_PLAN_STATISTICS 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:00.15 | 5075 | |

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

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

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 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 | | 7059 | 55 |00:00:00.15 | 4927 | |

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

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

| 9 | PARTITION RANGE ALL | | 918K| 7979 |00:00:00.11 | 4927 | |

|* 10 | TABLE ACCESS FULL | SALES | 918K| 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 적용

SQL 실행결과 sales 테이블의 예측 분포도는 918K건이며 실제 분포도는 Bloom Filter가 적용되어 7979건이다. 그리고 group by된 operation(ID 7번)의 예측 분포도는 7059건이며 실제 분포도는 55건이다. 예측과 실제의 분포도 차이는 두 경우 모두 100배 이상이다. 따라서 CF가 적용될 것이다. 이와는 반대로 customers 테이블의 예측 분포도와 실제 분포도는 162와 152로 크게 다르지 않으므로 CF가 적용되지 않을 것이다. 이제 위의 SQL을 재 실행한다면 CF가 적용되어 실제 분포도가 적용될 것이다.

--> CF를 발생시키기 위해 위의 SQL 다시 실행

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

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

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

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

|* 2 | HASH JOIN | | 270 | 3230 |00:00:05.60 | 5075 | 1201K (0)|

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 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 | | 7979 | 918K|00:00:02.82 | 4927 | |

| 7 | TABLE ACCESS FULL | SALES | 7979 | 918K|00:00:00.98 | 4927 | |

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

Predicate Information (identified by operation id):

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

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

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

Note

-----

- cardinality feedback used for this statement --> CF가 발생되었음을 나타냄.

두 번째 실행 할 때 CF가 적용되어 예측 분포도가 7979로 바뀌었고 group by 분포도는 55건으로 바뀌었다. 이에 따라 실행계획도 바뀌었다. 즉 CF에 의해서 쿼리변환(Complex View Merging)이 발생된 것이다. 그리고 note에 CF가 적용되었다고 친절히 설명된다.

이제 더 자세한 분석을 위하여 10053 Trace의 내용을 보자. 두 번째 실행된 SQL의 10053 Trace에 따르면 쿼리변환전의 SQL은 다음과 같다.

SELECT /*+ LEADING (C) */

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

s.prod_cnt, s.channel_cnt, s.tot_amt tot_amt

FROM tlo.customers c,

(SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

s.cust_id cust_id, COUNT (DISTINCT s.prod_id) prod_cnt,

COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

FROM tlo.sales s

GROUP BY s.cust_id) s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id ;

CF에 의해서 OPT_ESTIMATE 힌트가 적용되었다. 실제 건수로 적용하는 것이므로 일견 문제가 없어 보인다. 하지만 쿼리변환과정(Complex View Merging)을 거치면 문제가 생긴다. 10053 Trace에서 나타난 쿼리변환 후의 SQL은 다음과 같다.

SELECT /*+ OPT_ESTIMATE (GROUP_BY ROWS=55.000000 ) LEADING (C) OPT_ESTIMATE (TABLE S ROWS=7979.000000 ) */

c.cust_id cust_id, c.cust_first_name cust_first_name,

c.cust_last_name cust_last_name, COUNT (DISTINCT s.prod_id) prod_cnt,

COUNT (DISTINCT s.channel_id) channel_cnt, SUM (s.amount_sold) tot_amt

FROM tlo.customers c, tlo.sales s

WHERE c.cust_year_of_birth = 1987

AND s.cust_id = c.cust_id

GROUP BY s.cust_id, c.ROWID, c.cust_last_name, c.cust_first_name, c.cust_id ;

CF의 문제점은?

위의 SQL은 두 가지 문제점이 있다. 두 문제 모두 쿼리변환에 의해 발생된다. 첫 번째 문제는 Bloom Filter와 관련된 것이다. CF의 영향으로 원본 SQL에 존재했던 Group By 뷰(Complex View)가 사라졌다. 뷰가 없어짐으로써 Bloom Filter가 적용되지 않는다. Filter가 사라졌음에도 불구하고 Filter가 존재했던 Cardinality 7979를 적용해 버렸다. 이에 따라 CF를 적용했음에도 7979건과 실제건수인 91만 8천 건과는 엄청난 차이가 나고 말았다. 즉 Bloom Filter가 사라질 때는 CF를 적용하면 안 된다는 이야기이다. 비유하자면 Filter가 없는데도 불구하고 Filter가 존재할 때의 건수를 적용시킨 것이다.

두 번째 문제는 쿼리변환 후 힌트의 상속과 관련된다. 쿼리변환전의 CF의 의한 힌트를 보면 Group By된 뷰의 건수는 55건이다. 그런데 이 힌트는 오직 sales 테이블에 대한 것이다. 그런데 쿼리변환후의 힌트를 보면 그대로 55건이 적용되어 되어버렸다. Group by가 외부로 빠져 나옴으로 해서 GROUP_BY ROWS는 전체건수와 마찬가지가 되어버렸다. sales 테이블의 Group By건수는 55건이 맞다. 하지만 쿼리변환 때문에 조인 후에 Group By 하게 된다면 cardinality를 다시 계산해야 한다. 조인이 없는 테이블의 Group By건수와 조인후의 Group By건수가 어떻게 같을 수 있나?

두 가지의 문제점은 Cost를 계산할 때 그대로 적용되어 버린다. 10053 trace를 보자.

Access path analysis for SALES

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for SALES[S]

Table: SALES Alias: S

Card: Original: 918843.000000 >> Single Tab Card adjusted from:918843.000000 to:7979.000000

Rounded: 7979 Computed: 7979.00 Non Adjusted: 918843.00

Access Path: TableScan

Cost: 1328.68 Resp: 1328.68 Degree: 0

Cost_io: 1321.00 Cost_cpu: 155262306

Resp_io: 1321.00 Resp_cpu: 155262306

Bloom Filter가 없음에도 불구하고 Sales 테이블의 건수(Cardinality)가 7979로 적용되어 버렸다. 이제 Group By가 적용된 건수를 보자.

GROUP BY cardinality: 270.000000, TABLE cardinality: 270.000000

>> Query Blk Card adjusted from 270.000000 to: 55.000000

SORT ressource Sort statistics

Sort width: 583 Area size: 510976 Max Area size: 102340608

Degree: 1

Blocks to Sort: 3 Row size: 69 Total Rows: 270

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 20302068

Total Temp space used: 0

Group By Cardinality와 관련된 Trace 내용이다. 여기서도 잘못된 Group By건수인 55를 적용시키고 있다. 조인 후에 Group By할 때는 Cardinality를 다시 계산해야 옳다. 이래서는 제대로 된 Cost가 나올 수 없다. 여기에 밝혀진 문제점은 SQL 하나에서 나온 것이므로 실전에서는 두 가지 문제뿐만 아니라 더 많을 것이다. 물론 옵티마이져가 모든 경우에 완벽할 수는 없다.

해결책
CF 문제의 해결방법을 생각해보자. 갑자기 실행계획이 바뀌어 성능문제가 발생했을 때 dbms_xplan.display_cursor의 note나 10053 Trace의 실행계획 부분을 보면 CF가 적용되었는지 아닌지 알 수 있다. 만약 CF가 적용되었다면 일단 의심해보아야 한다. 아래는 10053 trace의 실행계획 부분이다.

-----------------------------------------------------------+------------------------

-----------------------------------------------------------+------------------------

| 0 | SELECT STATEMENT | | | | 1368 |

| 1 | SORT GROUP BY | | 55 | 2915 | 1368 |

| 2 | HASH JOIN | | 270 | 14K | 1367 |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 162 | 5832 | 38 |

| 4 | BITMAP CONVERSION TO ROWIDS | | | | |

| 6 | PARTITION RANGE ALL | | 7979 | 132K | 1329 |

| 7 | TABLE ACCESS FULL | SALES | 7979 | 132K | 1329 |

-----------------------------------------------------------+------------------------

Predicate Information:

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

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

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

Content of other_xml column

===========================

nodeid/pflags: 7 17nodeid/pflags: 6 17 cardinality_feedback: yes --> CF가 적용됨

...이후 생략

만약 CF가 문제가 된다면 해당 SQL을 시작하기 전에 세션단위로 _optimizer_use_feedback = false를 적용하거나 opt_param 힌트를 사용하면 된다. 이렇게 하면 CF가 방지되어 쿼리변환의 원인이 제거된다. 따라서 Bloom Filter도 보존할 수 있다. 또 다른 방법은 인라인뷰에 no_merge 힌트를 적용하여 쿼리변환을 방지하면 문제는 해결된다. 이 두 가지 방법은 결국 쿼리변환을 방지하는 것이다.

결론

CF란 건수를 예측하고, 실행해서 실제건수와 예측건수를 비교하여 차이가 많다면 다음 번에 실행할 때 옵티마이져에게 실제건수를 전달해주는 역할을 한다. CF의 개념을 정리 했으므로 이제 큰 그림을 그려보자. 위의 예제에서 성능이 악화된 직접적인 이유는 Bloom Filter가 사라졌기 때문이다. 하지만 그렇게 된 이유는 쿼리변환 때문이며 쿼리변환의 이유는 CF 때문이다. 직접적인 원인을 찾았다고 해도 포기해선 안 된다. 꼬리에 꼬리를 무는 원인이 있을 수 있기 때문이다. 이를 도식화 하면 다음과 같다.

옵티마이져의 설계관점에서 개선해야 될 사항을 논의 해보자. 옵티마이져가 CBQT를 고려할 때는 두 가지의 경우로 판단한다. 쿼리변환을 적용하기 전(Iteration 1)의 Cost와 적용 후(Iteration 2)의 Cost를 비교해야 되기 때문이다. 쿼리변환전의 Cost를 구할 때는 CF를 적용시키고 반대로 쿼리변환 후에는 CF를 적용하지 않는 것이 더 좋은 Cost를 구할 수 있다. 왜냐하면 비록 답이 같다고 하더라도 형태가 전혀 다른 SQL에 대해 CF를 적용시킬 이유는 없기 때문이다. 물론 이렇게 해도 여전히 문제가 될 수는 있다. 하지만 문제의 발생확률은 많이 줄어들지 않겠는가?

'Oracle > Optimizer' 카테고리의 다른 글

COUNT(Distinct 컬럼)의 성능 (9)	2013.05.06
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04

Posted by extremedb

,

공지 - Cardinality Feed Back이 위험할 때

Oracle/Optimizer 2010. 10. 22. 17:11

블로그가 일주일에 한번만 업데이트 되기 때문에 많은 분들이 어떤 내용이 블로그에 올라올지 궁금해 하시는것 같습니다. 그래서 시간이 허락한다면 블로그에 올라갈 내용을 미리 공지 하겠습니다.

제목: Cardinality Feed Back이 위험할 때

부제목: Cardinality Feed Back의 개념과 사용예제

문서의 목적
1. Oracle11의 새 기능인 Cardinality Feedback의 개념을 알아보고 실행예제를 분석해본다.
2. Cardinality Feedback이 문제가 되는 경우를 살펴보고 해결방법을 제시한다.

목차
1. 서론
2. Cardinality Feedback의 개념: 소제목 예측, 실행, 비교, 그리고 전달 부분
3. Cardinality Feedback의 작동방법: 소제목 CF는 어떻게 실행되나? 부분
4. Cardinality Feedback의 실행예제: 소제목 CF를 발생시켜보자 부분
5. Cardinality Feedback의 문제점: 소제목 CF의 문제점은? 부분
6. 문제의 해결방법: 소제목 해결책 부분
7. 결론

분석도구
1. 10053 Trace
2. DBMS_XPLAN.display_cursor

참조문서
Closing the Query Processing Loop in Oracle 11g - Allison Lee, Mohamed Zait

예상발행일자
2010.10.25 일

주의사항: 블로그 내용은 예고없이 변경될 수 있습니다.

많이 기대해주세요.

'Oracle > Optimizer' 카테고리의 다른 글

COUNT(Distinct 컬럼)의 성능 (9)	2013.05.06
Cardinality Feedback이 위험할 때 (10)	2010.10.25
union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04

Posted by extremedb

,

union과 union all의 숨겨진 차이점

Oracle/Optimizer 2010. 10. 18. 00:01

부제목: view에서 union, minus, intersect를 제거하라

많은 사람들이 union과 union all의 차이점에 대해 알고 있다. 즉 union은 Sort와 중복제거라는 기능으로 인해 UNION ALL에 비하여 성능이 떨어진다는 것이다. 옳은 말이다. 하지만 union대신에 union all을 써야 하는 또 다른 이유가 있다는 것을 아는 사람은 얼마나 될까? 이 사실을 알지 못하면 불필요한 엑세스가 추가될 수 있으므로 성능이 저하된다.

먼저 고객테이블을 이용하여 뷰를 하나 만들고 그것을 이용한 SQL문을 만들어 보자.
환경 : Oracle 11.2.0.1

create or replace view vw_cust5 as
select *
from customers
union --> union을 사용함
select *
from customers;

select a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
from vw_cust5 a,
   sales b
where a.cust_id = b.cust_id
   and a.cust_id = 14865 ;

뷰에서 사용하는 컬럼은 a.cust_id 하나 뿐이다. 따라서 고객 테이블에 PK인덱스를 사용한다면 customers 테이블로 엑세스 하지 않아도 된다. 하지만 아래의 실행계획을 본다면 문제를 발견할 수 있다.

-------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
-------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    62 |
| 1 | HASH JOIN | | 260 |    62 |
| 2 | VIEW | VW_CUST5    | 2 | 6 |
| 3 |    SORT UNIQUE | | 2 | 6 |
| 4 | UNION-ALL    | | | |
| 5 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 6 | INDEX UNIQUE SCAN    | CUSTOMERS_PK | 1 | 1 |
| 7 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN    | CUSTOMERS_PK | 1 | 1 |
| 9 | PARTITION RANGE ALL    | | 130 |    56 |
| 10 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 11 | BITMAP CONVERSION TO ROWIDS    | | | |
| 12 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX| | |
-------------------------------------------------------------+----------------

PK 인덱스를 사용하였지만 customers 테이블로 불필요한 엑세스를 하였다. 이유가 무엇일까? 10053 trace를 보자.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)UNION (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME"
…중간생략
"CUST_TOTAL_ID","CUSTOMERS"."CUST_SRC_ID" "CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS" WHERE "CUSTOMERS"."CUST_ID"=14865 AND "CUSTOMERS"."CUST_ID"=14865)) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "B"."CUST_ID"=14865

위의 trace는 쿼리변환을 끝낸 상태의 SQL이다. 그런데 SQL을 자세히 보면 뷰 내부의 모든 컬럼을 select 하고 있다. 다시 말해 뷰 내부의 컬럼중에 cust_id만 존재하면 되는데, 나머지 컬럼이 삭제되지 않고 남아있다. 이것 때문에 불필요한 테이블 엑세스가 나타난 것이다.

union 대신에 union all을 사용해보자

테스트를 위하여 100건 짜리 고객테이블을 만들고 unique 인덱스를 만들어 보자.

create table customers_100 as
select *
from customers
where rownum < 101;

create unique index PK_CUSTOMERS_100 on customers_100 (cust_id);

이제 union 대신에 union all로 뷰를 생성하여 테스트한다.

create or replace view vw_cust2 as
select *
from customers
UNION ALL
select *
from customers_100 ;

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM vw_cust2 a,
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

---------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    57 |
| 1 | HASH JOIN | | 260 |    57 |
| 2 | VIEW | VW_CUST2    | 2 | 1 |
| 3 |    UNION-ALL | | | |
| 4 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 5 | INDEX UNIQUE SCAN    | PK_CUSTOMERS_100| 1 | 0 |
| 6 | PARTITION RANGE ALL    | | 130 |    56 |
| 7 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 8 | BITMAP CONVERSION TO ROWIDS    | | | |
| 9 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------

SLP란 무엇인가?

실행계획에서 보듯이 union all 을 사용하니 테이블 엑세스가 사라졌다. 이것은 SLP(Select List Pruning)라는 쿼리변환의 기능 때문에 가능한 것이다. SLP는 union all이 들어있는 뷰를 사용할 때 발생하며 뷰의 컬럼중에 사용하지 않는 것을 제거한다. 이제 10053 trace 내용 중에서 SLP에 대해 분석해보자. 특히 SLP 변환 전 SQL과 SLP 변환 후 SQL을 비교해보라. 아래의 10053 trace 내용이 복잡해 보이지만 구조는 단순하며 다음과 같다.

1) 쿼리변환 전 SQL
2) 쿼리변환(SLP)
3) 쿼리변환 후 SQL

SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID","CUSTOMERS"."CUST_FIRST_NAME" "CUST_FIRST_NAME","CUSTOMERS"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS"."CUST_EFF_FROM" "CUST_EFF_FROM","CUSTOMERS"."CUST_EFF_TO" "CUST_EFF_TO" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID","CUSTOMERS_100"."CUST_FIRST_NAME""CUST_FIRST_NAME",
"CUSTOMERS_100"."CUST_LAST_NAME" "CUST_LAST_NAME","CUSTOMERS_100"."CUST_GENDER"
…중간생략
"CUST_SRC_ID","CUSTOMERS_100"."CUST_EFF_FROM""CUST_EFF_FROM","CUSTOMERS_100"."CUST_EFF_TO"
"CUST_EFF_TO" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865
Query block SEL$1 (#0) unchanged
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$3
SLP: Removed select list item CUST_FIRST_NAME from query block SEL$2
SLP: Removed select list item CUST_FIRST_NAME from query block SET$1
...중간생략
SLP: Removed select list item CUST_EFF_TO from query block SEL$3
SLP: Removed select list item CUST_EFF_TO from query block SEL$2
SLP: Removed select list item CUST_EFF_TO from query block SET$1
JE: Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT "A"."CUST_ID" "CUST_ID","B"."PROD_ID" "PROD_ID","B"."TIME_ID" "TIME_ID","B"."CHANNEL_ID" "CHANNEL_ID","B"."QUANTITY_SOLD" "QUANTITY_SOLD" FROM ( (SELECT "CUSTOMERS"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS" "CUSTOMERS") UNION ALL (SELECT "CUSTOMERS_100"."CUST_ID" "CUST_ID" FROM "TLO"."CUSTOMERS_100" "CUSTOMERS_100")) "A","TLO"."SALES" "B" WHERE "A"."CUST_ID"="B"."CUST_ID" AND "A"."CUST_ID"=14865

필요한 컬럼만 살아남다
SLP(Select List Pruning)가 발생하여 사용하지 않는 모든 컬럼을 삭제하였다. 쿼리변환 후의 SQL을 보면 뷰 내부의 컬럼은 모두 제거되고 cust_id만 남아있다. SLP 기능에 의해 테이블 엑세스가 없어진 것이다. 뷰 내부에는 union뿐만 아니라 minus,intersect 등의 집합 연산의 사용을 자제해야 한다. SLP가 발생하지 않기 때문이다.

그렇다면 뷰내부에 union all과 minus를 동시에 사용하면 어떻게 될까? 이제 union all과 minus를 동시에 사용한 뷰를 생성하고 SLP가 발생하는지 테스트 해보자.

CREATE OR REPLACE VIEW vw_cust33 AS
SELECT *
FROM customers
UNION ALL
SELECT *
FROM customers
MINUS
SELECT *
FROM customers_100;

이제 위의 뷰를 사용하여 select문을 실행해보자.

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM vw_cust33 a, sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

---------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
---------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | |    63 |
| 1 | HASH JOIN | | 260 |    63 |
| 2 | VIEW | VW_CUST33 | 2 | 7 |
| 3 |    MINUS | | | |
| 4 | SORT UNIQUE    | | 2 | |
| 5 |    VIEW    | | 2 | 4 |
| 6 | UNION-ALL    | | | |
| 7 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 8 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 9 |    TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 2 |
| 10 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 11 | TABLE ACCESS BY INDEX ROWID    | CUSTOMERS_100 | 1 | 1 |
| 12 |    INDEX UNIQUE SCAN | PK_CUSTOMERS_100| 1 | 0 |
| 13 | PARTITION RANGE ALL    | | 130 |    56 |
| 14 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 15 | BITMAP CONVERSION TO ROWIDS    | | | |
| 16 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
---------------------------------------------------------------+----------------

minus가 존재하여 SLP가 발생되지 않았다. Minus 때문에 불필요한 테이블 엑세스가 세 번이나 발생되었다. 어떻게 하면 이 문제를 해결할 수 있을까? 물론 부정형 서브쿼리(not exists)를 사용하면 minus 를 대신할 수 있으므로 불필요한 테이블 엑세스는 없어질 것이다.

minus를 사용하면서 SLP가 가능한가?

문제는 minus를 사용하면서 불필요한 엑세스를 방지할 수 있는 방법이 있냐는 것이다. 가장 쉬운 방법은 뷰에서 minus 부분을 제거하는 것이다. 즉 아래의 SQL처럼 minus 대신에 테이블을 직접 사용하면 된다.

create or replace view vw_cust as
select * from customers
UNION ALL
select * from customers ;

SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM VW_CUST a, --minus가 빠진 뷰를 사용함
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865
MINUS
SELECT a.cust_id,
   b.prod_id,
   b.time_id,
   b.channel_id,
   b.quantity_sold
FROM CUSTOMERS_100 a, --테이블을 직접 사용함
   sales b
WHERE a.cust_id = b.cust_id
   AND a.cust_id = 14865 ;

-----------------------------------------------------------------+----------------
| Id | Operation    | Name    | Rows | Cost |
-----------------------------------------------------------------+----------------
| 0 | SELECT STATEMENT | | | 115 |
| 1 | MINUS | | | |
| 2 | SORT UNIQUE    | | 260 |    59 |
| 3 |    HASH JOIN | | 260 |    58 |
| 4 | VIEW | VW_CUST | 2 | 2 |
| 5 |    UNION-ALL | | | |
| 6 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 7 | INDEX UNIQUE SCAN    | CUSTOMERS_PK    | 1 | 1 |
| 8 | PARTITION RANGE ALL    | | 130 |    56 |
| 9 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    56 |
| 10 | BITMAP CONVERSION TO ROWIDS    | | | |
| 11 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
| 12 | SORT UNIQUE    | | 130 |    56 |
| 13 |    NESTED LOOPS    | | 130 |    55 |
| 14 | INDEX UNIQUE SCAN    | PK_CUSTOMERS_100| 1 | 0 |
| 15 | PARTITION RANGE ALL    | | 130 |    55 |
| 16 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES | 130 |    55 |
| 17 | BITMAP CONVERSION TO ROWIDS    | | | |
| 18 |    BITMAP INDEX SINGLE VALUE | SALES_CUST_BIX | | |
-----------------------------------------------------------------+----------------
예상대로 뷰에서 minus를 삭제하니 성공적으로 SLP가 발생되었고 테이블 엑세스가 모두 사라졌다.
결론
이번 시간에는 union과 union all의 또 다른 차이점에 대해 알아보았다. union all을 사용하면 SLP가 발생되어 뷰에서 사용되지 않는 컬럼을 제거한다. 이때 인덱스만으로 scan을 끝낼수 있는 경우 불필요한 테이블스캔이 방지된다. 따라서 뷰 내부에서는 union, minus, intersect를 빼는 것이 유리하다. 뷰 내부의 minus는 not exists로 바꾸면 된다. 대부분의 경우 뷰 내부에 Intersect도 필요치 않다. Intersect란 교집합이며 이것은 조인으로 해결할 수 있다. 왜냐하면 조인이란 두 집합에서 조인된 컬럼을 기준으로 값이 같은 것만 추출하는 기능이기 때문이다. 그렇지 않은가?

'Oracle > Optimizer' 카테고리의 다른 글

Cardinality Feedback이 위험할 때 (10)	2010.10.25
공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25

Posted by extremedb

,

메트릭스나 터미네이터는 먼 미래의 이야기 인가?

Oracle/Optimizer 2010. 4. 28. 16:09

영화 <마이너리포트>의 주인공인 톰 크루즈가 사용한 Dragging Board는 이미 몇 년전에 구현되었고 아이폰과 아이패드의 탄생으로 누구나 사용하게 되었다. 영화 <메트릭스>나 <터미네이터>를 보면 인간보다 우월한 기계들에 의해 지배를 당하거나 고통을 받는다. 이런 일을 먼 미래의 것으로 치부해 버리기에는 기술의 발전속도가 너무 빠르다. 이미 우리는 그런 세상에 살고 있다. 근거가 뭐냐고? 현재 적지 않은 수의 개발자들이 기계(옵티마이져) 보다 SQL의 작성능력이 떨어지기 때문이다.

예를 들면 옵티마이져가 재작성하는 SQL은 튜닝을 모르는 개발자가 작성한 것 보다 우월하다. 즉 개발자(인간)가 SQL을 작성했지만 옵티마이져는 품질이 떨어진다고 판단되는 SQL을 주인의 허락 없이 변경시켜 버린다.
인간이 Software 보다 못한 것인가?

“같은 블록을 반복해서 Scan 하면 성능이 느려진다” 라는 문구는 비단 개발자, DBA, 튜너만 생각하는 것이 아니다. 옵티마이져는 분석함수를 이용하여 위의 문구를 직접 실천한다. 다시 말하면 같은 테이블을 중복해서 사용하는 경우 옵티마이져는 비효율을 없애기 위해 분석함수를 이용하여 SQL을 변경시킨다. 아래의 SQL을 보자.

WITH v AS (SELECT /*+ INLINE */

department_id, SUM (salary) AS sal

FROM employee

WHERE job_id = 'ST_CLERK'

GROUP BY department_id )

SELECT d.department_id, d.department_name, v.sal

FROM department d, v

WHERE d.department_id = v.department_id

AND v.sal = (SELECT MAX (v.sal)

FROM v ) ;

위의 SQL을 보면 인라인뷰 V를 먼저 정의해놓고 아래의 Select 절에서 두 번 사용한 것을 알 수 있다. 다시 말하면 같은 테이블을 세 번(Temp 테이블에 Loading, 메인쿼리에 한번, 서브쿼리에 한번) 사용한 것이다. 아래의 실행계획을 보고 우리의 예상이 맞는지 확인해보자.

------------------------------------------------------+-----------------------------------+

------------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 6 | |

| 1 | MERGE JOIN | | 5 | 275 | 6 | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 432 | 2 | 00:00:01 |

| 3 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |

| 4 | SORT JOIN | | 5 | 195 | 4 | 00:00:01 |

| 5 | VIEW | | 5 | 195 | 3 | 00:00:01 |

| 6 | WINDOW BUFFER | | 5 | 80 | 3 | 00:00:01 |

| 7 | HASH GROUP BY | | 5 | 80 | 3 | 00:00:01 |

| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 6 | 96 | 2 | 00:00:01 |

| 9 | INDEX RANGE SCAN | EMP_JOB_IX| 6 | | 1 | 00:00:01 |

------------------------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

4 - filter("D"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")

5 - filter("V"."SAL"="ITEM_0")

9 - access("JOB_ID"='ST_CLERK')

우리의 예상과는 달리 Employee 테이블에 대한 액세스가 단 한번 나왔다. 놀랍지 않은가? URSW라는 기능으로 인하여 중복 액세스를 제거해 버린 것이다. 즉 Logical Optimizer가 SQL을 아래와 같이 재작성 한 것이다.

SELECT d.department_id, d.department_name, v.sal sal

FROM department d,

( SELECT e.department_id, SUM (e.salary) sal,

MAX (SUM (e.salary)) OVER () item_0

FROM employee e

WHERE e.job_id = 'ST_CLERK'

GROUP BY e.department_id ) v

WHERE d.department_id = v.department_id

AND v.sal = v.item_0 ;

옵티마이져가 재작성한 SQL을 보면 employee 테이블을 단 한번 사용하고 있으므로 Plan 상에도 엑세스가 한번 나온 것이다. 이 기능은 Oracle 11gR2에서 추가되었다.

위의 예제는 Uncorrelated Subquery(비상관 서브쿼리)를 사용하는 예제이다. 비상관 서브쿼리라 함은 서브쿼리 내에 메인 쿼리와의 조인절이 없다는 뜻이다. 그런데 옵티마이져는 상관 서브쿼리에서도 같은 방식을 사용한다. 아래의 SQL을 보자.

SELECT a.employee_id, a.first_name, a.last_name, b.department_name

FROM employee a, department b

WHERE a.department_id = b.department_id

AND a.employee_id = (SELECT MAX (s.employee_id)

FROM employee s

WHERE s.department_id = b.department_id);

부서별로 MAX 사원번호에 해당하는 정보를 구하는 SQL이다. 이 SQL의 Plan은 아래와 같다.

----------------------------------------------------+-----------------------------------+

----------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 6 | |

| 1 | VIEW | VW_WIF_1 | 106 | 7208 | 6 | 00:00:01 |

| 2 | WINDOW BUFFER | | 106 | 6466 | 6 | 00:00:01 |

| 3 | MERGE JOIN | | 106 | 6466 | 6 | 00:00:01 |

| 4 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 27 | 540 | 2 | 00:00:01 |

| 5 | INDEX FULL SCAN | DEPT_ID_PK| 27 | | 1 | 00:00:01 |

| 6 | SORT JOIN | | 107 | 4387 | 4 | 00:00:01 |

| 7 | TABLE ACCESS FULL | EMPLOYEE | 107 | 4387 | 3 | 00:00:01 |

----------------------------------------------------+-----------------------------------+

Predicate Information:

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

1 - filter("VW_COL_5" IS NOT NULL)

6 - access("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

6 - filter("A"."DEPARTMENT_ID"="B"."DEPARTMENT_ID")

Plan을 보면 employee 테이블을 단 한번만 엑세스 한다. 이것 역시 사람이 작성한 SQL을 옵티마이져가 성능에 문제가 된다고 판단하여 아래처럼 변경시킨 것이다.

SELECT VW_WIF_1.ITEM_1 EMPLOYEE_ID, VW_WIF_1.ITEM_2 FIRST_NAME,
   VW_WIF_1.ITEM_3 LAST_NAME, VW_WIF_1.ITEM_4 DEPARTMENT_NAME
FROM (SELECT A.EMPLOYEE_ID ITEM_1, A.FIRST_NAME ITEM_2,
   A.LAST_NAME ITEM_3, B.DEPARTMENT_NAME ITEM_4,
   CASE A.EMPLOYEE_ID
WHEN MAX (A.EMPLOYEE_ID) OVER (PARTITION BY A.DEPARTMENT_ID)
THEN A.ROWID
   END VW_COL_5
FROM TRANSFORMER.DEPARTMENT B, TRANSFORMER.EMPLOYEE A
   WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) VW_WIF_1
WHERE VW_WIF_1.VW_COL_5 IS NOT NULL

부서별로 MAX(EMPLOYEE_ID)의 값과 EMPLOYEE_ID를 비교하여 같으면 ROWID를 출력하고 있다. 따라서 ROWID 값이 NULL이 아니라면 EMPLOYEE_ID는 부서별로 MAX(EMPLOYEE_ID)와 같음을 보장한다. 그러므로 중복 엑세스가 제거될 수 있는 것이다. 이 사실은 VW_COL_5 IS NOT NULL 조건이 추가된 이유이기도 하다. 이 기능은 Oracle10g R2 에서 추가되었다.

SQL을 재작성하는 튜너는 옵티마이져에 포함되어 있다. 내가 작성한 SQL과 PLAN이 어떻게 변경되었는지 관심을 가져야 한다. 더 나아가서 훈수를 두려면 옵티마이져에 포함되어 있는 튜너보다 더 나아야 할 것이다. “지식의 대융합”(이인식 저)이라는 책을 보면 2030년을 기점으로 하여 인간이 기계보다 더 나은 점을 발견하기 힘들 것이라 한다. 이 책의 내용은 전문가들이 작성한 논문과 책을 종합한 것이므로 함부로 무시 할 수 없다.

사람이 기계보다 우월하려면 기계(옵티마이져)의 기능과 한계를 분석하고 이해해야 한다. 영화 <메트릭스>에서 인간과 기계 사이에 평화가 찾아온 이유는 기계의 한계(약점)를 이해하고 그것을 고쳐주었기 때문이 아닌가?

참조서적: The Logical Optimizer의 2.18 장, 2.19장

'Oracle > Optimizer' 카테고리의 다른 글

공지 - Cardinality Feed Back이 위험할 때 (1)	2010.10.22
union과 union all의 숨겨진 차이점 (6)	2010.10.18
해결사 되기 (15)	2010.02.04
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20

Posted by extremedb

,

해결사 되기

Oracle/Optimizer 2010. 2. 4. 02:01

정답의 결과가 틀리다. WHERE 절에 s.sales_cust.prod_cnt > 0 조건이 추가되어야 한다.
자세한 내용은 oraking 님의 댓글을 참조하기 바란다.(2010.09.03)

좋지 않은 상황

한 고객 사에서 전화가 걸려왔는데 성능 문제였다.

특정 SQL이 성능이 좋지 않은데 더욱 상황을 악화시키는 것은 Peak Time에 수십 만 번 실행된다는 것이다. 그런데 전체 건을 조회하는 것이 아니라 고객번호 순으로 10명의 고객에 대한 통계와 실적을 보는 SQL이라고 한다. SQL은 아래와 같으며 환경은 Oracle10g R2 이다. 오라클 Install시 생성되는 SH 스키마를 이용하면 된다.

--인덱스 생성

CREATE INDEX ix_cust_birth ON customers (cust_year_of_birth, cust_id);

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(c IX_CUST_BIRTH_CUST) 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

AND ROWNUM <= 10;

-------------------------------------------------------------------------------------
| Id | Operation | Name    | A-Rows | Buffers | Used-Mem |
-------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY    | | 10 |    5035 |    |
|* 2 | HASH JOIN | | 10 |    5035 | 1150K (0)|
| 3 |    TABLE ACCESS BY INDEX ROWID| CUSTOMERS |    151 | 153 |    |
|* 4 | INDEX RANGE SCAN    | IX_CUST_BIRTH |    151 | 2 |    |
| 5 |    VIEW | |    523 |    4882 |    |
| 6 | SORT GROUP BY | |    523 |    4882 | 8288K (0)|
| 7 |    PARTITION RANGE ALL    | |    918K|    4882 |    |
| 8 | TABLE ACCESS FULL | SALES |    918K|    4882 |    |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   2 - access("S"."CUST_ID"="C"."CUST_ID")
   4 - access("C"."CUST_YEAR_OF_BIRTH"=1987)

CUSTOMERS 테이블은 10건을 정확히 Scan 하였지만 Salse 테이블을 Full Scan하여 비효율이 발생 하였다. 또한 위의 SQL은 Hash Join을 사용 하였으므로 정렬을 보장 하지 않는다.

좋지 않은 상황 + 악조건

먼저 위의 SQL을 보면 Rownum을 사용하므로 CVM(Complex View Merging)이 발생되지 않는다. 만약 발생된다고 해도 Group By가 전체범위로 처리되므로 TOP SQL에서는 대부분 JPPD(Join Predicate Push Down)의 성능이 우월하다. 또한 Nested Loop Join이 아닌 Hash Join이 수행 되었으므로 고객번호가 작은 것부터 나오지 않는다. Sort 문제도 해결해야 하지만 Oracle10g이므로 JPPD Extension 기능이 수행되지 않는다. JPPD Extension이란 Group By + 집계함수나 Distinct가 존재해도 JPPD가 수행되며 Semi/Anti Join시에도 JPPD가 수행되는 획기적인 기능이다. 이 기능은 Oracle11g부터 사용할 수 있다.

정답부터 보지말자 실력이 늘지 않는다
지금부터 여러분이 이 문제를 해결해야 한다. 여러분이 해결사 이다. 퀴즈라고 생각하고 문제를 풀어보라. 하지만 절대 답을 먼저 보아서는 안 된다. 충분히 고민한 후에 답을 풀어보고 정답을 보도록 하자.

힌트가 있다
JPPD 기능을 사용할 수 없다면 JPPD의 흉내를 내면 된다. JPPD의 효과를 만들면 된다.
아래쪽의 실행계획과 결론 부분을 보는것도 힌트가 될 수 있다.

제약사항
Sort 가 되어야 하며 Rownum 조건으로 Customers 테이블에 10건만 Scan되어야 한다. 그 10건에 해당하는 고객만 Sales 테이블에 Access 하는 것이 정답이다. 스칼라 서브쿼리를 세 번 사용하는 것은 정답이 아니다. 그것은 막노동에 가깝다. 필자의 블로그를 꾸준히 구독한 독자라면 어렵지 않게 문제를 풀 수 있다.

정답: 아래를 드래그 하면 된다.

CREATE OR REPLACE TYPE SALES_CUST_TYPE AS OBJECT

(prod_cnt NUMBER(5),

channel_cnt NUMBER(2),

tot_amt NUMBER(15,2));

/

SELECT /*+ GATHER_PLAN_STATISTICS */

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

s.sales_cust.prod_cnt, -- Alias 가 여기에 사용 되었다.

s.sales_cust.channel_cnt,

s.sales_cust.tot_amt

FROM (SELECT /*+ INDEX(c IX_CUST_BIRTH_CUST) */

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

(SELECT sales_cust_type -- 타입의 이름을 그대로 사용해야 한다

(COUNT (DISTINCT s.prod_id),

COUNT (DISTINCT s.channel_id),

SUM (s.amount_sold)

)

FROM sales s

WHERE s.cust_id = c.cust_id

) AS sales_cust -- 추후 메인쿼리에서 Alias 가 사용된다.

FROM customers c

WHERE c.cust_year_of_birth= 1987

AND ROWNUM <= 10) s ;

위의 SQL은 JPPD를 수행시킨 효과와 비슷하다. 다른말로 바꾸면 수동으로 Lateral View를 생성한 것이다.

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

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

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

| 1 | VIEW | | 10 | 1104 |

|* 2 | COUNT STOPKEY | | 10 | 13 |

| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 10 | 13 |

|* 4 | INDEX RANGE SCAN | IX_CUST_BIRTH_CUST | 10 | 3 |

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

Predicate Information (identified by operation id):

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

2 - filter(ROWNUM<=10)

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

읽은 블럭수가 무려 5배 정도 차이가 나며 PGA는 사용 하지도 았았다. 자주 수행될수록 성능의 차이는 급격히 벌어질 것이다.

결론:

JPPD 기능을 사용할 수 없으므로 JPPD의 흉내를 낸 것이다. JPPD등이 수행되지 않을 때 Logical Optimizer의 한계를 극복할 수 있는 방법은 여러분의 응용력에 달려있다. 옵티마이져가 한계를 드러낼 때 더 좋은 방법을 적용하는 것이 사람의 할 일이 되었다. 물론 그러기 위해서는 Logical Optimizer의 허와 실을 알아야 할 것이다.

PS:

만약 Oracle9i 나 Oracle 8.1.7 버전을 사용하고 있다면 위의 정답이 실행되지 않으므로 http://scidb.tistory.com/69 글을 참조하여 사용하기 바란다.

'Oracle > Optimizer' 카테고리의 다른 글

union과 union all의 숨겨진 차이점 (6)	2010.10.18
메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04

Posted by extremedb

,

Distinct Elimination : 불필요한 Distinct를 제거하라

Oracle/Optimizer 2010. 1. 25. 00:07

지난시간의 DEUI라는 기능에 이어서 이번시간에는 그 사촌격인 DE에 대해서 논의해보자.

DE (Distinct Elimination)란 무엇인가
DE는 Unique한 집합일 경우 불필요한 Distinct를 제거하는 기능이다. 이렇게 함으로써 Sort와 중복제거 등의 부하가 많은 작업을 수행하지 않을 수 있다. 이 기능은 Oracle 11g에서 추가되었다. 이제 DE가 어떻게 수행되는지 알아보자.

SELECT distinct d.department_id, l.location_id

FROM department d, location l

WHERE d.location_id = l.location_id ;

----------------------------------------+-----------------------------------+

----------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 3 | |

| 1 | NESTED LOOPS | | 27 | 270 | 3 | 00:00:01 |

| 2 | TABLE ACCESS FULL | DEPARTMENT| 27 | 189 | 3 | 00:00:01 |

| 3 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | 3 | 0 | |

----------------------------------------+-----------------------------------+

Predicate Information:

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

3 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 컬럼이 있으면 Distinct가 필요없다
실행계획에서 Distinct에 해당하는 Operation인 Sort Unique 혹은 Hash Unique가 사라졌다. 이유는 Transformer가 위의 SQL을 분석해서 Distinct가 없어도 Unique 함을 알았기 때문이다. Select 절에 있는 d.department_id와 l.location_id는 From 절에 있는 두 테이블의 PK 이다. 따라서 Distinct는 당연히 필요 없음으로 Logical Optimizer가 삭제한 것이다.

아래는 DE와 관련된 10053 Event의 Trace 내용이다.

OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************

Order-by elimination (OBYE)

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

OBYE: OBYE bypassed: no order by to eliminate.

Eliminated SELECT DISTINCT from query block SEL$1 (#0)

…이후생략

10053 Trace 상에서는 DE가 OBYE 자리에서 발생하였다. 이것은 OBYE 수행여부를 체크할 때 DE의 수행여부를 같이 체크하므로 DE가 OBYE 자리에서 발생되는 것 같다.

함정에 주의할 것
Unique 하다고 해서 항상 DE가 발생될까? 그렇지 않다. 아래의 SQL을 보자.

SELECT distinct d.department_id, d.location_id

FROM department d, location l

WHERE d.location_id = l.location_id ;

위의 SQL은 location_id를 department 테이블의 컬럼으로 대체하였다는 점을 제외하면 최초의 SQL과 완전히 같다.
-----------------------------------------+-----------------------------------+

-----------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 4 | |

| 1 | HASH UNIQUE | | 27 | 270 | 4 | 00:00:01 |

| 2 | NESTED LOOPS | | 27 | 270 | 3 | 00:00:01 |

| 3 | TABLE ACCESS FULL | DEPARTMENT| 27 | 189 | 3 | 00:00:01 |

| 4 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | 3 | 0 | |

-----------------------------------------+-----------------------------------+

Predicate Information:

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

4 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

Unique를 보장하는 모든 테이블의 컬럼이 Select 절에 사용되어야
컬럼 하나만이 바뀌었을 뿐인데 실행계획에 Hash Unique가 생긴 것이다. 여기서 알 수 있는 것은 From 절에 나열된 모든 테이블의 PK 컬럼 혹은 Unique 컬럼이 Select 절에 나와야 Distinct가 제거된다는 사실이다. 이것은 아직 DE 기능이 완성되지 않은 것을 의미한다. 논리적으로는 Select 절에 d.location_id를 사용하거나 l.location_id를 사용해도 같기 때문에 DE가 발생 해야 하지만 아직 이런 기능이 없다는 것이 아쉽다.

DE 기능을 Control 하는 파라미터는 _optimizer_distinct_elimination이며 Default로 True 이다. 하지만 이 파라미터로는 DEUI 기능을 Control 할수 없다. 한가지 주의사항은 DE가 버전 10gR2(10.2.0.4)에서도 수행된다는 점이다. 다만 _optimizer_distinct_elimination 파라미터가 없다는 것이 11g와 다른 점이다.

결론
만약 이런 일이 대용량 테이블에서 발생한다면 결과는 심각한 성능저하로 나타날 수 있으므로 조인된 컬럼을 사용할 것인지 아니면 참조되는 컬럼을 사용할 것인지 아주 신중히 결정해야 한다.

성능저하가 예상되는 부분
예를들면 서브쿼리가 Unnesting되어 Distinct가 자동으로 추가된 인라인 뷰에 CVM이 발생하면 인라인뷰가 해체되므로 전체집합에 대해서 Sort Unique 혹은 Hash Unique가 발생된다. 전체집합이 대용량이라면 성능은 심각하게 저하될 것이다. 이 사실은 어떤 컬럼을 Select 절에서 사용할 것인지 아주 신중히 결정해야 하며 Merge 힌트를 얼마나 조심스럽게 사용해야 하는지를 잘 나타내 주고 있다.

PS
이렇게 나가다간 아마 책을 출판할 필요가 없을듯 하다.^^ 하지만 책보다는 블로그가 우선이다.

'Oracle > Optimizer' 카테고리의 다른 글

메트릭스나 터미네이터는 먼 미래의 이야기 인가? (2)	2010.04.28
해결사 되기 (15)	2010.02.04
Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07

Posted by extremedb

,

Index Unque Scan은 SQL을 변경시킨다

Oracle/Optimizer 2010. 1. 20. 13:48

INDEX UNIQUE SCAN의 비밀
당신은 INDEX UNIQUE SCAN을 쉬운 Operation쯤으로 여길 것이다. 하지만 여기에는 숨겨진 기능이 있다. 대표적인 경우가 Unique 인덱스를 사용하여 INDEX UNIQUE SCAN Operation이 나오면 Distinct를 제거하는 기능이다. 이 기능의 이름이 없으므로 DEUI*(Distinct Elimination using Unique Index)라 부르기로 하자. 이제 아래의 SQL을 보자.

SELECT DISTINCT d.department_id, l.city, l.country_id

FROM department d, location l

WHERE d.location_id = l.location_id

AND d.department_id = 10 ;

--------------------------------------------------+-----------------------------------+

--------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 3 | |

| 1 | NESTED LOOPS | | 1 | 22 | 2 | 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT| 1 | 7 | 1 | 00:00:01 |

| 3 | INDEX UNIQUE SCAN | DEPT_ID_PK| 1 | | 0 | |

| 4 | TABLE ACCESS BY INDEX ROWID | LOCATION | 23 | 345 | 1 | 00:00:01 |

| 5 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 | |

--------------------------------------------------+-----------------------------------+

Predicate Information:

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

3 - access("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

SQL이 변경되었다
Distinct에 의한 Sort Unique 혹은 Hash Unique가 사라졌다. 그 이유는 테이블 department와 location에서 모두 INDEX UNIQUE SCAN을 사용하였기 때문이다. 하지만 반대로 Unique 인덱스를 사용하지 않는다면 DEUI는 절대 수행되지 않는다. 아래의 SQL은 위의 SQL에서 힌트만 추가한 것이다.

SELECT /*+ FULL(d) */ DISTINCT d.department_id, l.city, l.country_id

FROM department d, location l

WHERE d.location_id = l.location_id

AND d.department_id = 10 ;

---------------------------------------------------+-----------------------------------+

---------------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 5 | |

| 1 | HASH UNIQUE | | 1 | 22 | 5 | 00:00:01 |

| 2 | NESTED LOOPS | | | | | |

| 3 | NESTED LOOPS | | 1 | 22 | 4 | 00:00:01 |

| 4 | TABLE ACCESS FULL | DEPARTMENT| 1 | 7 | 3 | 00:00:01 |

| 5 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 | |

| 6 | TABLE ACCESS BY INDEX ROWID | LOCATION | 23 | 345 | 1 | 00:00:01 |

---------------------------------------------------+-----------------------------------+

Predicate Information:

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

4 - filter("D"."DEPARTMENT_ID"=10)

5 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

순간의 실수로
Department 테이블을 Full Scan 하자 Plan 상에 HASH UNIQUE가 나타났다. 다시 말해서 옵티마이져의 실수로 다른 종류의 인덱스를 사용하거나 Full Scan이 된다면 Query Transformation은 발생되지 않는다.

결론

우리가 무심코 사용하는 Unique Index의 INDEX UNIQUE SCAN Operation은 DEUI라는 기능을 내장하고 있다. 당연한 기능이라고 여기겠지만 이것을 모르면 많은 것을 놓친다. 서브쿼리가 Unnesting되어 Driving 집합이 되면 메인쿼리의 집합을 보존하기 위하여 Default로 Distinct가 추가된다. 이제 INDEX UNIQUE SCAN을 사용하는 서브쿼리가 Unnesting되어 Driving 집합이 될 때 Distinct가 사라진 비밀을 이야기 할 수 있겠는가? Unique 한 값을 가지는 컬럼이나 컬럼조합을 Normal 인덱스가 아닌 Unique 인덱스로 만들어야 할 이유를 알겠는가? 단순한 Operation 하나라도 무시할 수 없는 이유가 된다.

PS
다음시간에는 DEUI의 사촌격인 DE에 대하여 논의하자.
이번 내용도 집필중인 책의 일부분이다. 이제 어떤 것을 주제로 한 책인지 감이 오지 않는가?

'Oracle > Optimizer' 카테고리의 다른 글

해결사 되기 (15)	2010.02.04
Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26

Posted by extremedb

,

내가 사용한 Hint 가 무시되는 이유

Oracle/Optimizer 2010. 1. 4. 00:49

Query Transformation을 모르면 튜닝을 할 수 없다

위의 말을 보고 많은 독자들이 말도 안 된다고 생각할 것이다. Logical Optimizer와 그 결과물인 Query Transformation을 잘 알지 못했지만 지금껏 튜닝을 성공적으로 했다고 생각하는 사람이 많이 있기 때문이다. 하지만 과연 그럴까? 아래의 SQL 을 보고 Query Transformation 과 Logical Optimizer 가 얼마나 중요한지 알아보자.

여기서 한단계 더 나아가서 뷰(인라인뷰가 아니다) 내부의 테이블에 대하여 조인순서 및 Access Path 를 바꿀 수 있는 방법에 대해 논의 해보자.

준비
테스트를 위하여 인덱스 3개와 뷰 하나를 만들자.

CREATE INDEX loc_postal_idx ON location (postal_code);

CREATE INDEX dept_name_idx ON department (department_name);

CREATE INDEX coun_region_idx ON country (region_id);

CREATE OR REPLACE VIEW v_dept AS

SELECT d.department_id, d.department_name, d.manager_id, l.location_id,

l.postal_code, l.city, c.country_id, c.country_name, c.region_id

FROM department d, location l, country c

WHERE d.location_id = l.location_id

AND l.country_id = c.country_id;

실행시켜보자

이제 모든 준비가 끝났다. 아래는 매우 짧고 쉬운 SQL 이다. SQL을 실행시키고 연이어DBMS_XPLAN.display_cursor 를 실행한 후의 결과 중에서 필요한 부분만 발췌 하였다.

SELECT /*+ gather_plan_statistics */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

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

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

| 1 | NESTED LOOPS | | 20 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

|* 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 3 |

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

Predicate Information (identified by operation id):

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

6 - access("D"."DEPARTMENT_NAME"='Shipping')

7 - filter("L"."POSTAL_CODE"='99236')

8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

9 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")

filter("C"."REGION_ID"=2)

10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

11 - filter("E"."JOB_ID"='ST_CLERK')

조인순서를 바꿀 수 있겠는가

위의 실행계획에서 조인순서는 V_dept --> Employee 이다. 만약 이 상태에서 여러분이 조인의 순서를 Employee --> V_dept 로 바꿀 수 있겠는가? 아마 아래처럼 힌트를 사용할 것이다.

SELECT /*+ gather_plan_statistics LEADING(E V) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

..이후생략

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

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

| 1 | NESTED LOOPS | | 45 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

| 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 45 |00:00:00.01 | 3 |

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

힌트가 무시 되었다 원인은?

조인의 순서가 전혀 변하지 않았다. 이상하지 않은가? 간단하게 생각되는 SQL의 조인순서도 변경할 수 없다. 이유는 Query Transformation 때문이다. Outline 정보를 보면 실마리를 찾을 수 있다.

Outline Data

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

/*+

BEGIN_OUTLINE_DATA

…중간생략

MERGE(@"SEL$2")

…중간생략

END_OUTLINE_DATA

*/

원인은 Logical Optimizer 에 의한 Query Transformation 이다

뷰 V_dept 에 View Merging 이 발생한 것이다. View Merging은 Query Transformation 의 한 종류이며 뷰를 해체하여 정상적인 조인으로 바꾸는 작업이다. Query Transformation이 발생하면 많은 경우에 쿼리블럭명이 바뀌어 버린다. 따라서 바뀐 쿼리블럭명을 지정하여 힌트를 사용하거나 Query Transformation 이 발생하지 않게 하면 힌트가 제대로 적용된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

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

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

|* 1 | HASH JOIN | | 20 |00:00:00.02 | 8 |

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.02 | 2 |

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.02 | 1 |

| 4 | VIEW | V_DEPT | 1 |00:00:00.01 | 6 |

| 5 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 6 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 8 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 9 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 10 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 11 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

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

Query Transformation 이 발생하지 않으면 조인순서 변경이 가능해

No_merge 힌트를 사용하여 Query Transformation 이 발생하지 않게 하였더니 조인 순서가 Employee --> V_dept 로 바뀌었다. 이제 알겠는가? Query Transformation과 Logical Optimizer를 모른다면 힌트도 먹통이 된다. 이래서는 제대로 된 튜닝을 할 수 없다.

뷰 내부의 테이블에 대한 조인순서의 변경은 가능한가
한 단계 더 나아가 보자. No_merge 힌트를 사용한 상태에서 뷰 내부의 테이블들에 대해서 조인순서를 바꾸고 싶다. 즉 조인순서를 Employee --> 뷰(Country --> Location --> Department) 로 바꾸어야 한다. 이때 여러분은 어떻게 할 것인가? 아래처럼 Global Hint 를 사용하면 된다.

SELECT /*+ gather_plan_statistics NO_MERGE(V) LEADING(E V) LEADING(V.C V.L V.D) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

Leading 힌트를 두 번 사용하였다. 그 이유는 위의 SQL은 쿼리블럭 2개로 구성되어 있기 때문이다. 즉 전체 SQL 에 대한 Leading 힌트가 필요하며 V_dept 에 대한 Leading 힌트가 각각 필요하다. V_dept 에 대한 Leading 힌트를 사용할 때 Dot 표기법을 사용해야 한다. 즉 뷰 내부에 존재하는 테이블들의 Alias 를 사용해야 한다. 이 방법은 Global Hint 사용 방법 중에 Dot 표기법을 사용한 것이다.

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

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

|* 1 | HASH JOIN | | 20 |00:00:00.01 | 9 |

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 2 |

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.01 | 1 |

| 4 | VIEW | V_DEPT | 1 |00:00:00.01 | 7 |

| 5 | NESTED LOOPS | | 1 |00:00:00.01 | 7 |

| 6 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

|* 7 | HASH JOIN | | 1 |00:00:00.01 | 4 |

|* 8 | INDEX RANGE SCAN | COUN_REGION_IDX | 5 |00:00:00.01 | 1 |

| 9 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 3 |

|* 10 | INDEX RANGE SCAN | LOC_POSTAL_IDX | 1 |00:00:00.01 | 2 |

|* 11 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 12 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 |00:00:00.01 | 1 |

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

Global Hint 는 매우 유용해

성공적으로 조인순서가 Employee --> 뷰(Country --> Location --> Department)로 바뀌었다. Global Hint 를 사용하자 힌트가 제대로 적용된다. 이 방법은 특히 뷰나 인라인뷰를 Control 할 때 유용하므로 반드시 익혀두기 바란다.

Query Transformation 이 발생했을 경우는 힌트를 어떻게 적용할 수 있나

이제 원래 목적인 Query Transformation 이 발생했을 경우에 조인순서를 바꾸는 방법에 대해 논의 해보자. 위에서 배운 Global Hint 를 여기에 적용할 것이다.

SELECT /*+ gather_plan_statistics LEADING(E V.C V.L V.D) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

위의 SQL은 No_merge 힌트가 사라졌으므로 Query Transformation 이 발생된다. 그래서 위에서 배운 대로 Dot 표기법을 활용하여 Leading 힌트를 사용 하였다. 힌트가 적용될까?

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

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

| 1 | NESTED LOOPS | | 20 |00:00:00.01 | 11 |

| 2 | NESTED LOOPS | | 45 |00:00:00.01 | 8 |

| 3 | NESTED LOOPS | | 1 |00:00:00.01 | 6 |

| 4 | NESTED LOOPS | | 1 |00:00:00.01 | 5 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 6 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

|* 7 | TABLE ACCESS BY INDEX ROWID| LOCATION | 1 |00:00:00.01 | 2 |

|* 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 |00:00:00.01 | 1 |

|* 9 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 |00:00:00.01 | 1 |

|* 10 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 45 |00:00:00.01 | 2 |

|* 11 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 20 |00:00:00.01 | 3 |

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

실패사례: 힌트가 무시 되었다

힌트가 전혀 먹혀 들지 않는다. 그 이유는 View Merging이 발생하여 새로운 쿼리블럭이 생성되었기 때문이다. 아래의 Query Block Name 정보를 보면 쿼리블럭명과 각 테이블의 Alias 를 조회할 수 있다. / 를 기준으로 왼쪽이 쿼리블럭명이고 오른쪽이 각 테이블의 Alias 이다. 제일 왼쪽의 숫자는 실행계획상의 Id 와 일치한다.

Query Block Name / Object Alias (identified by operation id):

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

1 - SEL$F5BB74E1

5 - SEL$F5BB74E1 / D@SEL$2

6 - SEL$F5BB74E1 / D@SEL$2

7 - SEL$F5BB74E1 / L@SEL$2

8 - SEL$F5BB74E1 / L@SEL$2

9 - SEL$F5BB74E1 / C@SEL$2

10 - SEL$F5BB74E1 / E@SEL$1

11 - SEL$F5BB74E1 / E@SEL$1

힌트에 쿼리블럭명과 Object의 Alias를 사용해야 가능해

쿼리블럭명은 SEL$F5BB74E1 이며 Object의 Alias 들은 D@SEL$2, L@SEL$2, C@SEL$2, E@SEL$1 임을 알 수 있다. 따라서 이 정보들을 이용하여 아래처럼 힌트를 바꾸어 보자.

SELECT /*+ gather_plan_statistics LEADING(@SEL$F5BB74E1 E@SEL$1 C@SEL$2 L@SEL$2 D@SEL$2 ) */

e.employee_id, e.first_name, e.last_name, e.job_id, v.department_name

FROM employee e, v_dept v

WHERE e.department_id = v.department_id

AND v.department_name = 'Shipping'

AND v.postal_code = '99236'

AND v.region_id = 2

AND e.job_id = 'ST_CLERK';

위의 힌트처럼 쿼리블럭명을 처음에 지정하고 그 뒤에는 조인될 순서대로 Object Alias 를 배치하기만 하면 된다.

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

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

|* 1 | HASH JOIN | | 20 |00:00:00.01 | 8 |

|* 2 | HASH JOIN | | 20 |00:00:00.01 | 5 |

| 3 | MERGE JOIN CARTESIAN | | 100 |00:00:00.01 | 3 |

| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 20 |00:00:00.01 | 2 |

|* 5 | INDEX RANGE SCAN | EMP_JOB_IX | 20 |00:00:00.01 | 1 |

| 6 | BUFFER SORT | | 100 |00:00:00.01 | 1 |

|* 7 | INDEX RANGE SCAN | COUN_REGION_IDX | 5 |00:00:00.01 | 1 |

| 8 | TABLE ACCESS BY INDEX ROWID | LOCATION | 1 |00:00:00.01 | 2 |

|* 9 | INDEX RANGE SCAN | LOC_POSTAL_IDX | 1 |00:00:00.01 | 1 |

| 10 | TABLE ACCESS BY INDEX ROWID | DEPARTMENT | 1 |00:00:00.01 | 3 |

|* 11 | INDEX RANGE SCAN | DEPT_NAME_IDX | 1 |00:00:00.01 | 2 |

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

쿼리블럭 표기법은 Leading Hint 뿐만 아니라 모든 힌트에 적용 가능해

조인 순서가 Employee --> Country --> Location --> Department 로 바뀌었다. 이 방법은 Global Hint 사용 방법 중에 쿼리블럭 표기법을 사용한 것이다. 이 방법은 특히 View Merging 과 같은 Query Transformation 이 발생하여 쿼리블럭이 새로 생성된 경우 매우 유용하다. 이 방법으로 모든 힌트를 사용할 수 있다. 아래의 Outline Data 는 이런 점을 잘 설명 해준다.

Outline Data

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

/*+

…중간생략

MERGE(@"SEL$2")

…중간생략

INDEX_RS_ASC(@"SEL$F5BB74E1" "E"@"SEL$1" ("EMPLOYEE"."JOB_ID"))

INDEX(@"SEL$F5BB74E1" "C"@"SEL$2" ("COUNTRY"."REGION_ID"))

…중간생략

USE_HASH(@"SEL$F5BB74E1" "D"@"SEL$2")

END_OUTLINE_DATA

*/

PS
위의 내용 또한 이번에 출간될 책의 일부분이다. 블로그에 책의 내용이 많이 올라가서 걱정이다.^^

'Oracle > Optimizer' 카테고리의 다른 글

Distinct Elimination : 불필요한 Distinct를 제거하라 (6)	2010.01.25
Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15

Posted by extremedb

,

Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다

Oracle/Optimizer 2009. 12. 7. 00:01

이번 내용은 난위도가 있으므로 'Interleaving은 선수조건이 필요하다' 단락을 읽고 이해가 가는 사람만 보기 바란다. 필자의 글을 꾸준히 구독한 독자라면 어렵지 않게 볼수 있을 것이다.

전략적 결혼
사극을 보면 권력가들이 자신의 세력를 확장하기 위해 자신의 딸을 왕에게 추천하여 결혼시키는 것을 심심치 않게 볼수 있다. 자신의 목표를 이루기 위한 전단계의 포석이라고 할수 있다. 딸이 왕자를 순산하고 그 왕자가 차세대 왕이 된다면 자신은 최고의 권력을 가지는것과 마찬가지 이다. CBQT가 수행될 때에도 이러한 전략적 결혼과 같은것이 존재한다는 사실을 알고 있는가? CBQT를 최적화 하기 위해서 Transformation 간의 관계는 필수적이다. 오라클에서는 Interleaving이라는 기법을 사용하고 있다.

Interleaving은 선수조건이 필요하다
하나의 Transformation(T2) 을 수행하기 위해서 다른 Transformation(T1)이 반드시 먼저 실행되어야 하는 경우가 있다. 이럴 때 Interleaving 기법을 이용하여 T1이 수행되면 마치 기다렸다는 듯이 T2가 실행된다. 대표적인 경우가 CSU(Complex Subquery Unnesting)가 실행되면 연이어 CVM(Complex View Merging)혹은 JPPD(Join Predicate Push Down)가 실행되는 경우이다.

서브쿼리가 Unnesting 되면 초기상태는 인라인뷰로 생성된다. 인라인뷰를 제거하고 정상적인 조인으로 바꾸는 기능이 CVM 이라는 것은 이미 알것이다. 즉 CVM을 시작하기 위해서는 CSU라는 전단계의 포석이 반드시 이루어 져야만 한다. 이 규칙은 JPPD에도 똑같이 적용된다.

숫자표기법의 이해
Interleaving 에 대한 10053 Trace 내용을 분석하려면 숫자 표기법을 알아야 한다. 예를 들어 Complex Subquery가 하나 있는 SQL은 아래와 같이 0 과 1 로 Unnesting 상태를 나타낼수 있다.

CSU(1) : 서브쿼리가 Unnesting 됨
CSU(0) : 서브쿼리가 Unnesting 되지 않음.

만약 서브쿼리가 2개(SUBQ2, SUBQ1)라면 아래처럼 표현된다.
CSU(1,1) : 모든 서브쿼리가 Unnesting 됨
CSU(1,0) : SUBQ2만 Unnesting 됨
CSU(0,1) : SUBQ1만 Unnesting 됨
CSU(0,0) : 모든 서브쿼리가 Unnesting 되지 않음.

이러한 표기법은 CSU 뿐만 아니라 CVM, JPPD 등에도 똑같이 적용되며 10053 Trace에서 자주 나타나므로 반드시 알아두어야 한다.

Interleaving의 용도
Interleaving은 주로 CSU 수행시의 비용계산 오류를 줄이는 용도로 사용한다. 예를 들어 CSU(0)이 최저 Cost로 선택되었다면 JPPD의 입장에서는 전혀 다른 결과를 가져올 수 있다. CSU(0)이 아닌 CSU(1) 상태에서 JPPD을 적용하는 것이 최저 Cost가 될 수 있다. 따라서 Interleaving은 CBQT간에 최저 Cost를 구하기 위해 대화를 하는 기능이라고 할 수 있다. Interleaving 기능이 존재함으로써 CSU + CVM(혹은 JPPD)을 모두 고려한 최적의 Cost를 구 수 있다. 이제 아래의 SQL을 보자.

SELECT /*+ QB_NAME(MAIN_VIEW) */

e1.employee_id, e1.manager_id, e1.salary

FROM employee e1

WHERE e1.department_id = 10

and (e1.manager_id, e1.salary) in (select /*+ QB_NAME(SUBQ) */

e2.manager_id, max(e2.salary)

from employee e2

group by e2.manager_id )

and rownum = 1

위의 SQL에 해당하는 10053 Trace의 내용을 미리 예상해보자. 먼저 서브쿼리에 Group By를 사용하였으므로 CSU가 발생할 것이다. 연이어 CSU의 과정 중에 Interleaving 이 발생하여 CVM및 JPPD의 Cost가 같이 고려될 것이다. 하지만 위의 SQL은 조건절에 Rownum을 사용하였으므로 CVM이 발생할 수 없다. 따라서 JPPD만 고려될 것이다. 이제 우리가 예상한 내용이 맞는지 확인 해보자. 아래의 Trace 내용은 필요한 부분만 발췌하여 요약한 것이다.

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

Cost-Based Subquery Unnesting
*****************************
SU: Using search type: exhaustive

SU: Starting iteration 1, state space = (2) : (1)

Cost: 6.0035 Degree: 1 Card: 1.0000 Bytes: 41

SU: Considering interleaved complex view merging

CVM: CVM bypassed: Outer QBc referencing ROWNUM.

Cost: 5.0035 Degree: 1 Card: 1.0000 Bytes: 41

SU: Interleaved cost better than best so far.
SU: Finished interleaved complex view merging

SU: Considering interleaved join pred push down

JPPD: Using search type: linear

JPPD: Considering join predicate push-down

JPPD: Starting iteration 1, state space = (2) : (0)

Cost: 6.0035 Degree: 1 Card: 1.0000 Bytes: 41

JPPD: Starting iteration 2, state space = (2) : (1)

Cost: 4.0010 Degree: 1 Card: 1.0000 Bytes: 30

JPPD: Selected interleaved query.

SU: Finished interleaved join pred push down

SU: Updated best state, Cost = 4.00

SU: Starting iteration 2, state space = (2) : (0)

Cost: 6.0037 Degree: 1 Card: 1.0000 Bytes: 15

SU: Not update best state, Cost = 6.00

SU: Will unnest subquery SUBQ (#2)

SU: Reconstructing original query from best state.

우리의 예상대로 먼저 CSU가 발생되었다. 연이어 Interleaving 기능에 의해 CVM이 고려되고 있지만 Rownum 제약사항 때문에 CVM이 발생되지 못한다.(CVM bypassed 부분 참조) 하지만 불완전 Costing(CVM을 제외한 최적화)은 계속 진행된다.

CVM과 JPPD 는 동시에 고려된다
CVM 과정이 끝나면 JPPD의 Costing 과정이 진행된다. Interleaving이 CVM뿐만 아니라 JPPD도 동시에 고려하고 있음을 알 수 있다. JPPD를 적용한 Iteration(JPPD: Starting iteration 2, state space = (2) : (1) 부분 참조) 의 Cost가 4.0010으로 최적임을 알 수 있다. 이로서 모든 Interleaving이 완료되었다.( SU: Finished interleaved join pred push down 부분 참조) 마지막으로 CSU를 수행하지 않는 Iteration의 Cost를 구하고 모든 CSU과정을 마치게 된다.

CSU의 최저 Cost는 CSU + CVM + JPPD를 모두 고려한것
한가지 주의해야 할 사항은 4.0010은 JPPD의 Cost가 되는 것이 아니라 CSU의 Cost 이다. SU: Updated best state, Cost = 4.00 부분을 보면 이러한 사실을 알 수 있다. 즉 CSU의 Cost는 단순히 CSU 자체의 Cost만 고려하는 것이 아니라 CSU + CVM + JPPD를 모두 고려한 후 최적의 Cost를 갖는 경우를 선택하는 것을 알 수 있다. Interleaving 기능으로 인하여 더욱 정확한 Cost를 구하는 것이 가능한 것이다.

PS
1)위의 내용은 내년초 출간될 책의 일부분을 발췌한 것이다.
2)아이러니 하게도 맨처음에 설명한 전략적 결혼과 같은 비근대적인 방법을 21세기의 우리사회에서도 가끔 볼수 있다.

'Oracle > Optimizer' 카테고리의 다른 글

Index Unque Scan은 SQL을 변경시킨다 (6)	2010.01.20
내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28

Posted by extremedb

,

MERGE 문과 IN 조건이 만난다면

Oracle/Optimizer 2009. 11. 26. 14:22

질문을 받다
독자로 부터 다음과 같은 질문을 받았다. "MERGE 문에 IN 조건을 사용한다면 아래의 Plan처럼 심각한 성능저하가 발생 하였다. 왜그런가?"

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

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

| 1 | MERGE | T2 | 1 | | 2 |00:25:50.73 | 50M| 821 |

| 2 | VIEW | | 1 | | 100K|00:25:52.38 | 50M| 795 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K| 100K|00:25:52.28 | 50M| 795 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 100K|00:00:00.60 | 316 | 629 |

| 5 | VIEW | | 100K| 1 | 100K|00:25:34.26 | 50M| 166 |

|* 6 | FILTER | | 100K| | 100K|00:25:33.92 | 50M| 166 |

|* 7 | TABLE ACCESS FULL| T2 | 100K| 1 | 100K|00:25:33.68 | 50M| 166 |

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

T1의 조건을 만족하는 건수만큼 T2가 FULL SCAN을 반복하고 있다. 이것은 재앙이나 다름없다. 어떤 원리로 성능저하가 발생하는지 MERGE문을 실행할 때 내부적으로 발생하는 일들의 특징과 순서를 알아보자.

무조건 아우터 조인이 발생해
MERGE문을 실행하면 Target 쪽 테이블에는 무조건 아우터 조인으로 바뀐다. 왜냐하면 Match 되지 않는 경우(조인에 실패한 경우)에도 INSERT 해야 하기 때문이다. 그리고 아우터 조인은 다시 LATERAL VIEW로 바뀐다. 왜냐하면 View Merging이 실패할 경우 FPD(Filter Push Down)이나 JPPD(JOIN PREDICATE PUSH DOWN)이 적용 되어야 하기 때문이다. LATERAL VIEW의 개념은 아래의 POST를 참조하라.

http://scidb.tistory.com/entry/Outer-Join-의-재조명

쿼리변환 순서가 중요하다
아래는 MERGE문 실행시 쿼리변환이 발생하는 순서 이다.

1.먼저 Transformer(Logical Optimizer)는 IN 조건을 OR 로 바꾼다.

2.TRANSFORMER(Logical Optimizer)는 아우터 조인되는 쪽을 LATERAL VIEW로 바꾼다.

3.LATERAL VIEW가 해체(View Merging이라 불림) 되어 평범한 아우터 조인으로 바뀐다. 이때 View Merging에 실패하면 심각한 성능저하가 발생할 수 있다. 위의 Plan을 보면 T2 쪽의 View 가 해체되지 못했다. 이것이 실마리가 될 것이다.

아래의 스크립트를 실행하여 실제로 이런 일들이 발생하는지 Test 환경을 만들어 보자.

create table t1(c1 varchar2(10), c2 int, c3 int, c4 int);

create table t2(c1 varchar2(10), c2 int, c3 int, c4 int);

insert into t1

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

insert into t2

select decode(mod(level,2),0,'A','B'), level, level, level

from dual connect by level <= 100000

;

analyze table t1 compute statistics;

analyze table t2 compute statistics;

Merge 문을 사용해보자

-- case 1
MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 = 'A')

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

select *
from table(dbms_xplan.display_cursor(null,null,'allstats last')) ;

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

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

| 1 | MERGE | T2 | 1 | 2 |00:00:12.52 | 105K| 655 | |

| 2 | VIEW | | 1 | 100K|00:00:00.65 | 632 | 626 | |

|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K|00:00:00.65 | 632 | 626 | 4686K (0)|

|* 4 | TABLE ACCESS FULL | T2 | 1 | 50000 |00:00:00.02 | 316 | 313 | |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

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

Predicate Information (identified by operation id):

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

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter("T2"."C1"='A')

5 - filter(("C1"='A' OR "C1"='B'))

아주 정상적인 PLAN 이다.

Merge 문에 IN 조건을 사용해보자

-- case 2
MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 IN ('A', 'B'))

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

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

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

| 1 | MERGE | T2 | 1 | | 2 |00:25:50.73 | 50M| 821 |

| 2 | VIEW | | 1 | | 100K|00:25:52.38 | 50M| 795 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K| 100K|00:25:52.28 | 50M| 795 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K| 100K|00:00:00.60 | 316 | 629 |

| 5 | VIEW | | 100K| 1 | 100K|00:25:34.26 | 50M| 166 |

|* 6 | FILTER | | 100K| | 100K|00:25:33.92 | 50M| 166 |

|* 7 | TABLE ACCESS FULL| T2 | 100K| 1 | 100K|00:25:33.68 | 50M| 166 |

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

Predicate Information (identified by operation id):

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

4 - filter(("C1"='A' OR "C1"='B'))

6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

7 - filter(("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3" AND

INTERNAL_FUNCTION("T2"."C1")))

10만번 반복된다
Starts 항목에 주목하라 FTS(Full Table Scan)을 10만번 반복 실행하였다. CASE2 에서 t2.c1 IN ('A', 'B') 조건을 사용하였더니 최악의 PLAN이 만들어 졌으며 Buffers 가 50M 에 육박하고 시간상으로도 25분 이상 걸렸다. 왜 그럴까? 아래의 SQL처럼 Case1과 Case2 실행시에 Logical Optimizer에 의하여 변경된 SQL을 보면 이유를 알수 있다.

SELECT /*+ CASE1 NO_MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1

WHERE T1.C1='A' OR T1.C1='B'

) X,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND T2.C1='A' )(+) lv

정상적으로 뷰가 해체되다
Case1의 LATERAL VIEW 내부의 T2.C1='A' 조건은 아우터 조인으로 바꿀 수 있으므로 View Merging이 발생하여 인라인뷰 X와 LATERAL VIEW lv가 아래처럼 평범한 아우터 조인으로 바뀐다.

SELECT /*+ CASE1 MERGE 상태 */

T2.ROWID RID,

T2.C1 ,T2.C2 C2,T2.C3 C3,T2.C4 C4,

T1.C1 ,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1, T2

WHERE T1.C3=T2.C3(+)

AND T1.C2=T2.C2(+)

AND T1.C1=T2.C1(+)

AND T2.C1(+)='A'

AND (T1.C1='A' OR T1.C1='B')

이때 옵티마이져가 내부적으로 MERGE 힌트를 사용한다. 10053 Trace에도 다음처럼 쿼리블럭 SEL$2 와 SEL$3이 SEL$1에 MERGE 되었다는 정보가 포함되어 있다.

Registered qb: SEL$5428C7F1 0x9f7b318 (VIEW MERGE SEL$1; SEL$2 SEL$3)

Out Line 정보에도 이런 사항이 잘 나타난다.

Outline Data

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

/*+

…생략
MERGE(@"SEL$2") --> 쿼리블럭 2 와

MERGE(@"SEL$3") --> 쿼리블럭 3 이 MERGE 되어 LATERAL VIEW 가 없어짐
…생략

*/

이제 case 2를 분석 해보자.

SELECT /*+ CASE2 NO_MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM (SELECT T1.C1 C1,T1.C2 C2,T1.C3 C3,T1.C4 C4

FROM T1

WHERE T1.C1='A' OR T1.C1='B') X,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND (T2.C1='A' OR T2.C1='B') ) (+) lv

제약조건 때문에...
위의 SQL 은 LATERAL VIEW를 적용한 모습이다. 그런데 (T2.C1='A' OR T2.C1='B') 조건 때문에 아우터 조인으로 바꾸질 못한다. 이것은 오라클 제약사항 이다. 제약조건이 있을 경우는 View Merging이 실패한다. 아래처럼 말이다.

SELECT /*+ CASE2 MERGE 상태 */

lv.RID,

lv.C1, lv.C2, lv.C3, lv.C4,

X.C1, X.C2, X.C3, X.C4

FROM T1,

LATERAL( SELECT T2.C1, T2.C2, T2.C3, T2.C4, T2.ROWID AS RID

FROM T2

WHERE X.C1=T2.C1

AND X.C2=T2.C2

AND X.C3=T2.C3

AND (T2.C1='A' OR T2.C1='B') ) (+) lv

WHERE T1.C1='A' OR T1.C1='B'

IN 이 발목을 잡다
인라인뷰 X 만 View Merging이 발생하였다. 결국 IN 혹은 OR 조건이 View Merging이 되지 못하도록 발목을 잡은 셈이다. 그리하여 Lateral View 가 살아남게 되었다. Lateral View는 스칼라 서브쿼리처럼 동작하게 된다. 다시 말하면 LATERAL VIEW 는 Hash 조인으로 실행되지 못한다. 문제의 Plan에서 Nested Loop 조인이 발생한 이유도 여기 있다.

그렇다면 이 문제를 어떻게 해결할 수 있을까?

문제 해결방법 3가지
1) 만약 인덱스를 만들 수 있다면 문제가 해결된다. 아래는 T2 에 (C1, C2, C3) 인덱스를 만든후 CASE 2를 다시 실행한 Plan 이다.

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

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

| 1 | MERGE | T2 | 1 | 2 |00:00:16.95 | 303K| 934 |

| 2 | VIEW | | 1 | 100K|00:00:03.92 | 200K| 932 |

| 3 | NESTED LOOPS OUTER | | 1 | 100K|00:00:03.92 | 200K| 932 |

|* 4 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.12 | 316 | 313 |

| 5 | VIEW | | 100K| 100K|00:00:02.51 | 200K| 619 |

|* 6 | FILTER | | 100K| 100K|00:00:02.29 | 200K| 619 |

| 7 | TABLE ACCESS BY INDEX ROWID| T2 | 100K| 100K|00:00:02.09 | 200K| 619 |

|* 8 | INDEX RANGE SCAN | T2_IDX | 100K| 100K|00:00:01.24 | 100K| 320 |

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

Predicate Information (identified by operation id):

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

4 - filter(("C1"='A' OR "C1"='B'))

6 - filter(("T1"."C1"='A' OR "T1"."C1"='B'))

8 - access("T1"."C1"="T2"."C1" AND "T1"."C2"="T2"."C2" AND "T1"."C3"="T2"."C3")

filter(("T2"."C1"='A' OR "T2"."C1"='B'))

2) 인덱스를 만들 수 없는 경우라면 아래처럼 Between 을 사용하면 된다. Between 은 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 between 'A' AND 'B')

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

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

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

| 1 | MERGE | T2 | 1 | 2 |00:00:14.99 | 104K| 817 | |

| 2 | VIEW | | 1 | 100K|00:00:00.58 | 816 | 810 | |

|* 3 | HASH JOIN OUTER | | 1 | 100K|00:00:00.48 | 816 | 810 | 7600K (0)|

|* 4 | TABLE ACCESS FULL| T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

|* 5 | TABLE ACCESS FULL| T2 | 1 | 100K|00:00:00.01 | 500 | 497 | |

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

Predicate Information (identified by operation id):

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

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter(("C1"='A' OR "C1"='B'))

5 - filter(("T2"."C1"<='B' AND "T2"."C1">='A'))

정상적으로 Hash 조인이 발생하였다.

3) Between 을 사용할 수 없는 경우라면 아래처럼 Decode를 사용하면 된다. Decode 또한 아우터 조인이 가능하다.

MERGE /*+ gather_plan_statistics */ INTO t2

USING (SELECT *

FROM t1

WHERE c1 IN ('A', 'B')) x

ON ( x.c1 = t2.c1

AND x.c2 = t2.c2

AND x.c3 = t2.c3

AND t2.c1 = decode(t2.c1, 'A', 'A', 'B'))

WHEN MATCHED THEN

UPDATE SET t2.c4 = x.c4

WHEN NOT MATCHED THEN

INSERT (t2.c1, t2.c2, t2.c3, t2.c4)

VALUES (x.c1, x.c2, x.c3, x.c4) ;

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

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

| 1 | MERGE | T2 | 1 | 2 |00:00:15.16 | 104K| 967 | |

| 2 | VIEW | | 1 | 100K|00:00:00.72 | 816 | 810 | |

|* 3 | HASH JOIN RIGHT OUTER| | 1 | 100K|00:00:00.72 | 816 | 810 | 8568K (0)|

|* 4 | TABLE ACCESS FULL | T2 | 1 | 100K|00:00:00.02 | 500 | 497 | |

|* 5 | TABLE ACCESS FULL | T1 | 1 | 100K|00:00:00.01 | 316 | 313 | |

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

Predicate Information (identified by operation id):

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

3 - access("T1"."C3"="T2"."C3" AND "T1"."C2"="T2"."C2" AND "T1"."C1"="T2"."C1")

4 - filter("T2"."C1"=DECODE("T2"."C1",'A','A','B'))

5 - filter(("C1"='A' OR "C1"='B'))

정상적으로 Hash 조인이 발생하였다.

결론

Merge문 사용시 On 절에 Target 테이블의 조건으로 IN 이나 OR를 사용하면 View Merging 이 발생하지 않는다. 따라서 LATERAL VIEW가 해체되지 못하며 LATERAL VIEW의 특성상 Nested Loop 조인이 적용된다. 이때 후행 테이블은 적절한 인덱스가 없다면 Full Table Scan이 발생하여 재앙과 같은 성능저하 현상이 발생된다. 이때 BETWEEN 이나 DECODE등 상황에 맞는 해결책을 사용할 수 있다.

모르면 못한다
결국 Query Transformation의 원리와 순서 그리고 제약조건을 알게 된다면 누가 해법을 말해주지 않아도 자연스럽게 알 수 있다. IN 과 OR의 아우터 조인 제약조건은 누구나 알고 있으므로 문제가 될수 없다. 문제는 Query Transformation을 모른다면 튜닝을 못하는 시대가 이미 왔다는 사실이다. 안타깝게도 이런 원리를 설명해주는 서적은 어디에도 없다. 물론 몇가지 Query Transformation을 소개한 책은 있지만 Logical Optimizer를 주제로 하는 서적은 없다. 다시 말하면 우리는 튜닝을 하지 못할 환경에 살고 있다.

'Oracle > Optimizer' 카테고리의 다른 글

내가 사용한 Hint 가 무시되는 이유 (10)	2010.01.04
Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10

Posted by extremedb

,

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

Oracle/Optimizer 2009. 10. 15. 16:37

“SQL 작성시 같은 테이블을 반복해서 사용하지 마라”

위와 같은 말을 많이 들어보았을 것이다. 이런 말들은 개발자에게 마치 격언, 명언처럼 취급된다. 오늘도 개발자, DBA, 컨설턴트등 모든 사람들이 위의 명언에 너무도 충실하여 반복되는 테이블을 제거하기 위해 SQL을 재작성 하고 있다. 하지만 이런 말들이 명언이나 격언이 될 수 있을까? 이제는 격언이나 훈수, 명언이라고 생각하는 말도 최소한 상황에 따라 가려서 해야 한다. 왜 그럴까? 아래의 SQL을 보자.

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 9 ;

위의 SQL은 대용량 테이블인 판매 테이블(sales)을 비효율적으로 2번 Scan할 것으로 예상된다. 하지만 아래의 Plan을 보라. 과연 그렇게 수행되는가?

-----------------------------------------------------------+-----------------------
| Id | Operation | Name    | Rows | Bytes | Cost |
-----------------------------------------------------------+-----------------------
| 0 | SELECT STATEMENT    | | | | 495|
| 1 | HASH JOIN    | | 449K | 20M | 495|
| 2 | VIEW    | VW_JF_SET$0A277F6D| 2 |    50 | 2|
| 3 |    UNION-ALL    | | | |    |
| 4 | TABLE ACCESS BY INDEX ROWID | CHANNELS    | 1 |    13 | 1|
| 5 |    INDEX UNIQUE SCAN    | CHANNELS_PK | 1 | | 0|
| 6 | TABLE ACCESS BY INDEX ROWID | CHANNELS    | 1 |    13 | 1|
| 7 |    INDEX UNIQUE SCAN    | CHANNELS_PK | 1 | | 0|
| 8 | PARTITION RANGE ALL | | 897K | 18M | 489|
| 9 |    TABLE ACCESS FULL    | SALES | 897K | 18M | 489|
-----------------------------------------------------------+-----------------------

Predicate Information:
----------------------
1 - access("ITEM_1"="S"."CHANNEL_ID")
5 - access("C"."CHANNEL_ID"=3)
7 - access("C"."CHANNEL_ID"=9)

이제는 Transformer 가 튜너이다.

환상적이지 않은가? channels 테이블을 정확히 2건만 Scan 하였고 sales 테이블은 1번만 Full Scan 하였다. 오라클 Transformer가 SQL을 아래처럼 재작성 한 것이다.

SELECT s.prod_id prod_id, s.cust_id cust_id, s.quantity_sold,

s.amount_sold, vw_jf_set$0a277f6d.item_2 channel_desc

FROM (SELECT c.channel_id AS item_1, c.channel_desc AS item_2

FROM channels c

WHERE c.channel_id = 3

UNION ALL

SELECT c.channel_id AS item_1, c.channel_desc AS item_2

FROM channels c

WHERE c.channel_id = 9) vw_jf_set$0a277f6d,

sales s

WHERE vw_jf_set$0a277f6d.item_1 = s.channel_id ;

위와 같은 상황에서 SQL을 재작성 하는 기능을 JF(Join Factorization)라고 부른다. VW_JF_SET으로 시작되는 인라인뷰 명(Plan 상의 파란색 부분)은 JF가 수행되었음을 나타내는 것이다. 이것은 Oracle11g R2 에서 새로 추가된 대표적인 CBQT(Cost Based Query Transformation)기능 이다.

항상 수행되지는 않는다

CBQT는 Cost로 SQL튜닝을 수행할 것인지 아닌지를 판단한다. 그런데 복잡한 SQL의 경우에는 Cost Estimator가 판단을 잘못하여 JF를 수행하는 것이 비용이 더 비싸다고 판단할 수 있다. 이런 경우에는 Transformer가 SQL을 튜닝(재작성) 하지 않는다. 이럴 때는 아래와 같이 힌트를 사용해야 한다.

SELECT /*+ use_hash(c s) FACTORIZE_JOIN(@SET$1(S@SEL$1 S@SEL$2)) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 3

UNION ALL

SELECT /*+ use_hash(c s) */

s.prod_id, s.cust_id, s.quantity_sold,

s.amount_sold, c.channel_desc

FROM sales s, channels c

WHERE c.channel_id = s.channel_id

AND c.channel_id = 9 ;

쿼리블럭명 SET$1은 전체에 해당하는 쿼리블럭이고 SET$1 의 내부에 또 다른 쿼리블럭인 SEL$1(Union All로 분리된 것 중의 윗부분), SEL$2(Union All로 분리된 것 중의 아랫부분) 이 존재한다. 만약 Union All 이 하나 더 있다면 쿼리블럭명은 SEL$3가 될 것이다.

JF를 자세히 분석 하려면 10053 Event Trace를 이용하라

먼저 10053 의 용어 설명부분에 JF 가 아래처럼 추가 되었다.

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

…중간생략

JF - join factorization

아래는 JF 부분에 해당하는 10053 Trace 정보이다.

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

Cost-Based Join Factorization

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

Join-Factorization on query block SET$1 (#1)

JF: Using search type: exhaustive

JF: Generate basic transformation units

…이후생략

결론

이제 격언이나 명언이라고 생각되는 말들도 상황을 가려서 해야 한다는 것을 알겠는가?. 그렇지 않으면 똑똑한 개발자에게 오히려 다음과 같은 말을 들을 것이다. “지금 말씀 하신 것은 예전 이야기 입니다. 요즘은 트랜스포머가 알아서 해줍니다.” 적어도 튜닝의 세계에서는 그렇다.

새로운 패러다임

JF는 Transformer가 수행하는 SQL튜닝의 하나일 뿐이다. Oracle11g R2 기준으로 SQL 튜닝(Query Transformation)의 종류는 필자의 짧은 지식으로도 70개 이상일 것으로 판단 된다. Oracle이 발전해 가면서 SQL튜닝은 사람이 관여하는 것에서 오라클이 자동으로 SQL을 변경해주는 것으로 많은 부분이 바뀌었고 앞으로 이런 추세는 점점 강화될 것이다. Query Transformation 은 단순한 Optimizer의 기능이 아니라 SQL 튜닝의 새로운 패러다임인 것이다. 이제는 직접 튜닝 하는 것에서 벗어나 Transformer가 실수하는 경우 새로운 길을 열어주는 것이 튜너가 가야할 길이 아닌가?

'Oracle > Optimizer' 카테고리의 다른 글

Interleaving : CBQT중에는 선수조건이 필요한 경우가 있다 (14)	2009.12.07
MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Semi Join 의 재조명 (5)	2009.08.31

Posted by extremedb

,

Temp Table Transformation

Oracle/Optimizer 2009. 9. 28. 18:09

오라클 Transformer 는 때때로 반복작업이 일어날경우 같은 테이블에 대한 부하가 심해지므로 TEMP 테이블을 만들어서 저장하고 그 테이블을 이용하여 반복적인 작업을 하게되는 경우가 있다. 대표적인 경우가 GROUPING SETS 를 사용하는 경우이다.

SELECT department_id, JOB_ID, AVG (salary) avg_sal

FROM employee a

GROUP BY GROUPING SETS(department_id, JOB_ID) ;

---------------------------------------------------------------+---------------------------+

---------------------------------------------------------------+---------------------------+

| 0 | SELECT STATEMENT | | | 11 | |

| 1 | TEMP TABLE TRANSFORMATION | | | | |

| 2 | LOAD AS SELECT | | | | |

| 3 | TABLE ACCESS FULL | EMPLOYEE | 107 | 3 | 00:00:01 |

| 4 | LOAD AS SELECT | | | | |

| 5 | HASH GROUP BY | | 1 | 3 | 00:00:01 |

| 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 | 00:00:01 |

| 7 | LOAD AS SELECT | | | | |

| 8 | HASH GROUP BY | | 1 | 3 | 00:00:01 |

| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6608_434CFB| 1 | 2 | 00:00:01 |

| 10 | VIEW | | 1 | 2 | 00:00:01 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_434CFB| 1 | 2 | 00:00:01 |

---------------------------------------------------------------+---------------------------+

위의 PLAN 을 보면 먼저 ID 기준으로 3번에서 employee 테이블을 읽어서 필요한 컬럼만 TEMP 테이블에 저장해두고 그테이블을 반복해서 이용(ID 기준으로 6번, 9번, 11번)하게 되는것이다. 그렇다면 왜 3번이나 반복해서 temp 테이블을 사용하는걸까?

그 해답은 10053 trace 에 있다. id 기준으로 6번에 해당하는 SQL 이 아래와 같다.

SELECT /*+ */ NULL C0, C1 C1, BIN_TO_NUM(1, GROUPING(C1)) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C1)

위에서 C0, C1 은 TEMP 테이블의 컬럼 ALIAS 이며 각각 department_id, JOB_ID를 의미한다. 또한 BIN_TO_NUM 함수를 사용한 이유는 나중에 ID 기준으로 11번째 에서 Group by 단위를 알아내기 위함이다. COUNT(A0), SUM(A0) 를 select 한이유도 11번에서 AVG (salary) 값을 구하기 위해서이다. 참고로 avg(col1) 함수는 논리적으로 sum(col1)/count(col1) 과 같다.

또한 id 기준으로 9번에 해당하는 SQL 이 아래와 같다.

SELECT /*+ */ C0 C0, NULL C1, BIN_TO_NUM(GROUPING(C0), 1) D0, COUNT(A0), SUM(A0) FROM "SYS"."SYS_TEMP_0FD9D6608_434CFB" GROUP BY (C0)

최종적으로 Transformation이 적용된 SQL은 다음과 같다.

with SYS_TEMP_0FD9D6608_434CFB as
(
SELECT department_id AS C0, JOB_ID AS C1, salary AS A0
   FROM employee a
) ,
   TEMP1 as
   (
SELECT NULL C0, C1, COUNT(salary) AS A1, SUM(salary) AS A0
   FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C1
   ) ,
   TEMP2 as
   (
SELECT C0, NULL C1, COUNT(salary) AS A1, SUM(salary) AS A0
   FROM SYS_TEMP_0FD9D6608_434CFB
GROUP BY C0
   ) ,
SYS_TEMP_0FD9D6609_434CFB AS
(
   SELECT TEMP1. * FRPM TEMP1
   UNION ALL
   SELECT TEMP1. * FRPM TEMP1
)
SELECT C0 AS DEPARTMENT_ID, C1 AS JOB_ID, A0 AS AVG_SAL
FROM (SELECT C0, C1,
DECODE(A0, 0, TO_NUMBER(NULL), A1/A0) AS A0 --> 분모가 0 일 경우 처리
FROM SYS_TEMP_0FD9D6609_434CFB ) ;

불만
id 기준으로 6번과 9번에서 각각 job_id 와 department_id 로 group by 해놓고 이것을 id 11 번에서 합쳐서 보여주게 된다. 하지만 필자는 이런 변환에 대하여 불만이 있다. 위의 쿼리는 EMPLOYEE 테이블을 GROUP BY 하지 않은 상태로 TEMP 테이블에 적재한다. 이것은 매우 비효율적이다. EMPLOYEE 테이블을 Temp 테이블에 적재시 미리 Group BY 하여 넣을 수가 있다. 아래의 SQL 처럼 미리 GROUP BY 하여 건수를 미리 줄여놓으면 반복작업시의 부하가 획기적으로 줄어들수 있기 때문이다.

select department_id, JOB_ID, sum(sum_sal) / sum(cnt) as avg_sal

from (SELECT department_id, JOB_ID, count(salary) cnt, sum(salary) sum_sal

FROM employee a

GROUP BY department_id, JOB_ID )

GROUP BY GROUPING SETS(department_id, JOB_ID) ;

이렇게 수동으로 SQL을 바꾸는 방법은 Grouping Sets의 변환로직이 변하지 않는한 확실한 튜닝방법이 될수 있으므로 반드시 고려되어야 한다.

'Oracle > Optimizer' 카테고리의 다른 글

MERGE 문과 IN 조건이 만난다면 (8)	2009.11.26
Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Semi Join 의 재조명 (5)	2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24

Posted by extremedb

,

스칼라 서브쿼리를 서브쿼리로 변환하라

Oracle/Optimizer 2009. 9. 10. 11:50

개발자의 질문
개발자 한명이 DBA 에게 질문을 던졌다. ‘스칼라 서브쿼리를 사용한 SQL 을 인라인뷰로 싸고 인라인뷰 외부에서 스칼라 서브쿼리를 Filter 조건으로 사용하면 스칼라 서브쿼리가 없어지는 현상이 발생합니다. 이 현상이 정상인가요?’ 그런데 DBA 는 그런일은 발생할 수가 없다고 하였다. 과연 사실일까? 정답은 스칼라 서브쿼리가 없어진다는 것이다. 예리한 눈을 가진 개발자 임에 틀림없다.

백견이 불여일행
SSTS( Scalar Subquery To Subquery ) 는 스칼라 서브쿼리를 서브쿼리로 변경시키는 Transformation 과정이다. 하지만 항상 변환되지 않는다. 스칼라 서브쿼리를 인라인뷰 외부에서 Filter 조건으로 사용할때만 가능하다. 아래의 SQL 을 보자

인덱스 상황 :

EMP_JOB_IX : employee (job_id)

DEPT_ID_PK1 : department(department_id)

SELECT a.employee_id, a.first_name, a.last_name, a.email

FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

(SELECT location_id

FROM department d

WHERE d.department_id = e.department_id) AS location_id

FROM employee e

WHERE e.job_id = 'IT_PROG') a

WHERE a.location_id > 0;

위의 SQL 은 Location 을 스칼라 서브쿼리로 구현하였다. 하지만 스칼라 서브쿼리를 Select 하는데는 사용하지 않고 Where 조건으로 사용하는 것을 주목하라.

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

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

|* 1 | FILTER | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 5 | 195 | 2 (0)|

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 5 | | 1 (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

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

Predicate Information (identified by operation id):

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

1 - filter(>0)

3 - access("E"."JOB_ID"='IT_PROG')

5 - access("D"."DEPARTMENT_ID"=:B1)

위의 Plan 을 보면 본능적으로 스칼라 서브쿼리가 서브쿼리로 바뀐 것을 알수 있어야 한다. 다시말하면 옵티마이져가 SQL 을 아래처럼 바꾼 것 이다.

SELECT e.employee_id, e.first_name, e.last_name, email

FROM employee e

WHERE e.job_id = 'IT_PROG'

AND (SELECT location_id

FROM department d

WHERE d.department_id = e.department_id) > 0 ;

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

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

|* 1 | FILTER | | | | |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 5 | 195 | 2 (0)|

|* 3 | INDEX RANGE SCAN | EMP_JOB_IX | 5 | | 1 (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

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

Predicate Information (identified by operation id):

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

1 - filter(>0)

3 - access("E"."JOB_ID"='IT_PROG')

5 - access("D"."DEPARTMENT_ID"=:B1)

실행계획과 Predicate Information 이 완전히 같음을 알수 있다. 이제 10053 Trace 를 분석해보자.

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

Order-by elimination (OBYE)

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

…중간생략

CVM: Merging SPJ view SEL$2 (#0) into SEL$1 (#0)

Registered qb: SEL$F5BB74E1 0xc4643d0 (VIEW MERGE SEL$1; SEL$2)

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$F5BB74E1 nbfros=1 flg=0

fro(0): flg=0 objn=70296 hint_alias="E"@"SEL$2"

View Merging 이 발생하여 SEL$2(인라인뷰 a) 가 SEL$1(메인쿼리)에 통합 되어 버렸다. View Merging 이 발생하여 새로운 쿼리블럭인 SEL$F5BB74E1 가 생성 되었다. 하지만 쿼리블럭 SEL$F5BB74E1 의 From 절을 보면 employee(Alias 로는 E) 만 존재하고 department(D)는 존재 하지 않는다. 그렇다면 스칼라 서브쿼리는 어디로 갔을까? 해답은 FPD(Filter Push Down) 기능에 있다.

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

Predicate Move-Around (PM)

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

…중간생략

query block SEL$F5BB74E1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$F5BB74E1 (#0)

(SELECT "D"."LOCATION_ID" FROM "DEPARTMENT" "D")>0 AND "SYS_ALIAS_1"."JOB_ID"='IT_PROG'

FPD: Considering simple filter push in query block SEL$3 (#0)

"D"."DEPARTMENT_ID"=:B1

try to generate transitive predicate from check constraints for query block SEL$3 (#0)

finally: "D"."DEPARTMENT_ID"=:B1

FPD 기능에 의해서 위에서 새로 생성된 쿼리블럭 SEL$F5BB74E1 의 조건절에 서브쿼리를 생성하고 있다. 또한 새로 생성된 서브쿼리에 "D"."DEPARTMENT_ID"=:B1 조건을 밀어넣고 있다.

검증

스칼라 서브쿼리가 서브쿼리로 바뀌었으므로 서브쿼리에 사용할수 있는 힌트 Push_subq 를 사용해보자. 이것이 가능해야지만 진정한 서브쿼리이다.

SELECT /*+ PUSH_SUBQ(@SUB) */

a.employee_id, a.first_name, a.last_name, a.email

FROM (SELECT e.employee_id, e.first_name, e.last_name, email,

(SELECT /*+ QB_NAME(SUB) */ location_id

FROM department d

WHERE d.department_id = e.department_id) AS location_id

FROM employee e

WHERE e.job_id = 'IT_PROG') a

WHERE a.location_id > 0;

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

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

|* 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 39 | 2 (0)|

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

| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

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

Predicate Information (identified by operation id):

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

1 - filter(>0)

2 - access("E"."JOB_ID"='IT_PROG')

4 - access("D"."DEPARTMENT_ID"=:B1)

실행계획을 보면 알겠지만 Subquery Pushing 이 발생하여 Id 기준으로 2번과 3번이 동일 Level 상에 존재한다. Subquery Pushing 이 성공적으로 수행된것을 알수 있다.

한단계 더 나아가 보자

CREATE INDEX HR.EMP_JOB_DEPT_IX ON HR.EMPLOYEE (JOB_ID, DEPARTMENT_ID);

EMPLOYEE 테이블에 JOB_ID, DEPARTMENT_ID 를 생성 하였다. 바로 위에서 실행한 SQL 을 다시 실행 해보자.

--> 여기서 PUSH_SUBQ 를 적용한 SQL 실행한다.

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

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

| 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 39 | 2 (0)|

|* 2 | INDEX RANGE SCAN | EMP_JOB_DEPT_IX | 1 | | 1 (0)|

| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENT | 1 | 7 | 1 (0)|

|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK1 | 1 | | 0 (0)|

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

Predicate Information (identified by operation id):

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

2 - access("E"."JOB_ID"='IT_PROG')

filter(>0)

4 - access("D"."DEPARTMENT_ID"=:B1)

이것은 제공자 서브쿼리가 아니다

실행계획은 마치 제공자 서브쿼리처럼 바뀌었지만 이것은 제공자 서브쿼리가 아니고 Subquery Pushing 에 의한 효과이다. Predicate Information 을 주목하라. ID 기준으로 2번에서 Filter 가 수행된다. . (JOB_ID, DEPARTMENT_ID) 인덱스를 사용하지 않은 SQL의 Predicate Information을 보면 EMPLOYEE 테이블 엑세스 시에 filter 가 발생함을 알수 있다. 이것이 바로 Subquery Pushing 과 인덱스에 의한 Early Filter 의 효과이다. 물론 Early Filter 의 개념은 조인순서상에 서브쿼리를 최대한 먼저 조인 하는것으로 바꾸는 것이다. 하지만 위와 같은 Early Filter의 부가적인 기능도 있음을 알아야 한다.

Subquery Pushing 에 의한 Early Filter 기능은 Using Sub query Method( Filter / Access sub Query ) 글을 참조하라

결론:
이처럼 Oracle Query Transformer 는 스칼라 서브쿼리를 Filter 조건으로 사용할 때 SSTS를 발생시킨다.. 여러분이 Query Transformation 을 의도 하던 의도하지 않던 말이다.

PS :
제목이 이상하다는 의견이 있다. 제목은 옵티마이져가 변환을 수행한다는 의미이다. 수동으로 스칼라 서브쿼리를 서브쿼리로 고치라는 의미가 아니므로 착오가 없길 바란다.

'Oracle > Optimizer' 카테고리의 다른 글

Transformer - SQL 튜닝의 새로운 패러다임 (11)	2009.10.15
Temp Table Transformation (5)	2009.09.28
Semi Join 의 재조명 (5)	2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12

Posted by extremedb

,

Semi Join 의 재조명

Oracle/Optimizer 2009. 8. 31. 15:45

예전에 Hash Join Right (Semi/Anti/Outer) 의 용도 라는 글에서 서브쿼리가 Semi Join 으로 적용될때 조인의 순서를 바꿀수 있는 방법에 대하여 설명한바 있다. 이어서 오늘도 예전글과 유사한 Semi Join 의 튜닝에 대해서 이야기 하려 한다.

환경 : Oracle 11.1.0.6
먼저 테이블을 단순한 구조로 만들기 위해서 테이블을 2개 생성한다.
ORACLE 의 Sample 스키마인 SH 를 이용한다.

CREATE TABLE SALES_T AS SELECT * FROM sh.sales;

CREATE TABLE PRODUCTS_T AS SELECT * FROM sh.products ;

ALTER TABLE PRODUCTS_T ADD CONSTRAINT PK_PRODUCTS_T --> PK 및 인덱스 생성
PRIMARY KEY (PROD_ID) USING INDEX;

CREATE BITMAP INDEX SALES_T_PROD_BIX ON SALES_T (PROD_ID); --> BIT MAP 인덱스 생성

dbms_stats.gather_table_stats(user,'SALES_T',cascade=>true);
dbms_stats.gather_table_stats(user,'PRODUCTS_T',cascade=>true);

이제 SQL 을 실행할 준비가 다 되었다.
아래는 상품의 Category 가 Software/Other 인 제품의 상품코드별, 채널별 판매량을 나타낸 SQL 이다.
이러한 SQL 은 주로 DW 에서 나타난다.

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) */
s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
   p.prod_id
   FROM products_t p
WHERE p.prod_category_desc = 'Software/Other')
GROUP BY s.prod_id, s.channel_id ;

--------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Time | Buffers |
--------------------------------------------------------------------------
| 1 | HASH GROUP BY    |    |    1 |00:00:08.12 |    4445 |
|* 2 | HASH JOIN |    |    1 |00:00:06.94 |    4445 |
|* 3 |    TABLE ACCESS FULL| PRODUCTS_T |    1 |00:00:00.01 | 4 |
| 4 |    TABLE ACCESS FULL| SALES_T    |    1 |00:00:01.84 |    4441 |
--------------------------------------------------------------------------

헉 8초 이상 걸렸다!
Subquery Unnesting 이 발생하여 작은쪽 테이블인 Products_t 가 Driving 테이블로 선정되었고 Salse_t 테이블과 Hash Join 을 하게된다. Scan 한 블럭수는 4445블럭 이며 8초 이상 수행되었다. 조인건수(Starts 항목 참조)는 1회에 불과하다. 이것은 최적으로 튜닝된 SQL 인가? 이것을 알아보기 위해 Hash Join 대신 Nested Loop 조인으로 바꿔보자.

SELECT /*+ GATHER_PLAN_STATISTICS */
   prod_id, channel_id, SUM (qs) AS qs, SUM (amt) amt
FROM (SELECT /*+ NO_MERGE USE_NL(PR S) */
s.prod_id, s.channel_id,
quantity_sold AS qs, amount_sold AS amt
   FROM sales_t s, products_t pr
   WHERE s.prod_id = pr.prod_id
AND pr.prod_category_desc = 'Photo')
GROUP BY prod_id, channel_id ;

원본 SQL 의 경우 왠만해서는 Nested Loop Join 으로 바뀌지 않으므로 강제로 서브쿼리를 From 절로 끌어 올려서 Nested Loop Join 으로 유도 하였다.

------------------------------------------------------------------------------------
| Id |Operation |Name    |Starts| A-Time |Buffers|
------------------------------------------------------------------------------------
| 1 | HASH GROUP BY    |    |    1 |00:00:10.07 | 3099|
| 2 | VIEW    |    |    1 |00:00:08.92 | 3099|
| 3 | NESTED LOOPS |    |    1 |00:00:07.70 | 3099|
| 4 |    NESTED LOOPS    |    |    1 |00:00:02.03 | 41|
|* 5 | TABLE ACCESS FULL    |PRODUCTS_T    |    1 |00:00:00.01 |    4|
| 6 | BITMAP CONVERSION TO ROWIDS|    | 26 |00:00:00.41 | 37|
|* 7 |    BITMAP INDEX SINGLE VALUE |SALES_T_PROD_BIX| 26 |00:00:00.01 | 37|
| 8 |    TABLE ACCESS BY INDEX ROWID |SALES_T | 405K|00:00:02.45 | 3058|
------------------------------------------------------------------------------------

Hash Join 보다 더 느려졌다
Nested Loop Join 으로 유도 하자 인덱스는 제대로 사용하였지만 비효율이 극심하게 드러난다. 먼저 Nested Loop Join 이 40만번 이상 수행된다.(Starts항목 참조) 그결과로 View 를 만드는데 8초 이상 수행되었다. Buffer 가 30% 이상 줄어들었지만 조인건수 때문에 시간이 많이 걸리므로 이것은 견딜수 없는 결과이다.

그럼 Hash Join 에 만족해야 하나?
지금까지의 상식으로는 그렇다. 하지만 Driving Semi Join 기법을 사용한다면 생각이 달라질것이다.

Driving Semi Join 이란?
Driving Semi Join 이란 Semi Join 의 변형된 형태로서 Hash Join 의 장점(조인건수의 최소화)과 Nested Loop Join 의 장점(선행테이블이 상수화 되어 후행 테이블에서 인덱스를 효율적으로 사용) 을 합친 개념이다. 물론 Driving Semi Join 이라는 Title 자체의 의미대로 Semi Join 이 Driving 집합이 되는것은 당연하다. 다음의 SQL 을 보자

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
   s.prod_id, s.channel_id, SUM (quantity_sold) AS qs, SUM (amount_sold) AS amt
   FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
FROM products_t p
   WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id ;

----------------------------------------------------------------------------------
| Id | Operation |Name    |Starts| A-Time |Buffers|
----------------------------------------------------------------------------------
| 1| HASH GROUP BY    |    | 1|00:00:03.40| 2231|
| 2| TABLE ACCESS BY INDEX ROWID |SALES_T | 1|00:00:02.04| 2231|
| 3|    BITMAP CONVERSION TO ROWIDS|    | 1|00:00:00.42| 63|
| 4| BITMAP MERGE    |    | 1|00:00:00.02| 63|
| 5|    BITMAP KEY ITERATION |    | 1|00:00:00.02| 63|
|* 6| TABLE ACCESS FULL |PRODUCTS_T    | 1|00:00:00.01|    4|
|* 7| BITMAP INDEX RANGE SCAN |SALES_T_PROD_BIX|    26|00:00:00.02| 59|
----------------------------------------------------------------------------------

환상적이다
Hash Join (8초 이상), Nested Loop Join (10초 이상) 인데 반하여 Driving Semi Join 은 3.4초 만에 끝이 났다. 또한 조인횟수도 26 번에 불과하며 I/O Block 수도 2231 로 Hash Join 의 절반에 해당한다.

환상적인 이유는?
그 이유는 Join 대신에 BITMAP KEY ITERATION 이 수행된 까닭이다.
BITMAP KEY ITERATION 의 원리는 INLIST ITERATOR Operation 과 같다고 볼수 있다.
INLIST ITERATOR 의 개념은 InList / Concatnation / Range Scan Control 하기 을 참조하라.

Driving Semi Join은 B-Tree 인덱스에서도 사용이 가능해
이기능은 Bit Map 인덱스 뿐만 아니라 B-Tree 인덱스 에서도 사용가능하다.
하지만 _b_tree_bitmap_plans 파라미터가 True 로 되어 있어야 한다.
아래의 SQL 을 보자.

DROP INDEX SALES_T_PROD_BIX; --> Bit Map 인덱스 Drop

CREATE INDEX SALES_T_PROD_IX ON HR.SALES_T (PROD_ID); --> B-Tree 인덱스 생성

ALTER SESSION SET "_b_tree_bitmap_plans" = TRUE;

SELECT /*+ GATHER_PLAN_STATISTICS QB_NAME(MAIN) SEMIJOIN_DRIVER(@SUB) */
   s.prod_id, s.channel_id, SUM (quantity_sold) AS qs,
   SUM (amount_sold) AS amt
FROM sales_t s
WHERE s.prod_id IN (SELECT /*+ QB_NAME(SUB) */
p.prod_id
   FROM products_t p
WHERE p.prod_category_desc = 'Photo')
GROUP BY s.prod_id, s.channel_id;

-------------------------------------------------------------------------------------
| Id |Operation    |Name |Starts| A-Time |Buffers|
-------------------------------------------------------------------------------------
| 1| HASH GROUP BY | | 1|00:00:04.94| 3045|
| 2| TABLE ACCESS BY INDEX ROWID    |SALES_T    | 1|00:00:03.76| 3045|
| 3| BITMAP CONVERSION TO ROWIDS | | 1|00:00:02.14|    877|
| 4|    BITMAP MERGE | | 1|00:00:01.73|    877|
| 5| BITMAP KEY ITERATION    | | 1|00:00:01.73|    877|
|* 6|    TABLE ACCESS FULL    |PRODUCTS_T | 1|00:00:00.01|    4|
| 7|    BITMAP CONVERSION FROM ROWIDS| |    26|00:00:01.73|    873|
|* 8| INDEX RANGE SCAN    |SALES_T_PROD_IX|    26|00:00:00.86|    873|
-------------------------------------------------------------------------------------

Bit Map 인덱스에 비하여 약간의 비효율이 있다. 그것은 BITMAP CONVERSION FROM ROWIDS Operation 이 추가되었기 때문이다. 하지만 기존의 Hash Join 이나 Nested Loop Join 보다는 수행시간, I/O Block 수, 조인건수등이 우월함을 알수 있다.

당신은 이미 Driving Semi Join을 사용했다
여러분의 SQL 이 Star Transformation 을 수행하는 경우는 싫든 좋든 Driving Semi Join 기능을 이용하고 있는 것이다. 아래는 Star Transformation 을 수행하는 SQL 의 Plan 과 Outline Data 를 출력한 것이다.

SELECT /*+ gather_plan_statistics */
   p.prod_id,
   c.CHANNEL_ID,
   SUM (quantity_sold) AS qs,
   SUM (amount_sold) AS amt
FROM sh.sales s, sh.CHANNELS c, sh.products p
   WHERE s.CHANNEL_ID = c.CHANNEL_ID
   AND c.CHANNEL_DESC = 'Internet'
   AND s.prod_id = p.prod_id
   AND p.prod_category_desc = 'Photo'
GROUP BY p.prod_id, c.CHANNEL_ID;

------------------------------------------------------------------+
| Id | Operation    | Name |
------------------------------------------------------------------+
| 0 | SELECT STATEMENT |    |
| 1 | HASH GROUP BY |    |
| 2 | HASH JOIN    |    |
| 3 |    TABLE ACCESS FULL | CHANNELS |
| 4 |    HASH JOIN |    |
| 5 | TABLE ACCESS FULL    | PRODUCTS |
| 6 | PARTITION RANGE ALL    |    |
| 7 |    TABLE ACCESS BY LOCAL INDEX ROWID | SALES    |
| 8 | BITMAP CONVERSION TO ROWIDS    |    |
| 9 |    BITMAP AND    |    |
| 10 | BITMAP MERGE |    |
| 11 |    BITMAP KEY ITERATION    |    |
| 12 | BUFFER SORT    |    |
| 13 |    TABLE ACCESS FULL | CHANNELS |
| 14 | BITMAP INDEX RANGE SCAN    | SALES_CHANNEL_BIX|
| 15 | BITMAP MERGE |    |
| 16 |    BITMAP KEY ITERATION    |    |
| 17 | BUFFER SORT    |    |
| 18 |    TABLE ACCESS FULL | PRODUCTS |
| 19 | BITMAP INDEX RANGE SCAN    | SALES_PROD_BIX |
------------------------------------------------------------------+

Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
... 중간생략
STAR_TRANSFORMATION(@"SEL$1" "S"@"SEL$1" SUBQUERIES(("C"@"SEL$1") ("P"@"SEL$1")))
BITMAP_TREE(@"SEL$1" "S"@"SEL$1" AND(("SALES"."CHANNEL_ID") ("SALES"."PROD_ID")))
SEMIJOIN_DRIVER(@"SEL$1898F719")
SEMIJOIN_DRIVER(@"SEL$D750A531")
END_OUTLINE_DATA
*/

Driving Semi Join의 장점은 또 있다
Driving Semi Join Nested Loop Join 시의 조인횟수에 의한 부하나 Hash 조인시의 Full Table Scan + Hashing 작업의 부하를 획기적으로 감소켜준다. 더 좋은것은 Star Transformation 사용시에 바인드 변수를 사용할수 없었지만 Driving Semi Join은 이러한 제약이 없다. 하지만 이 기능은 오라클사의 정식힌트가 아니므로 필자의 경우 데이터 분석 용도로만 사용하고 있다. 이기능이 11.2 버젼에는 정식으로 메뉴얼에 나오길 기대하며 이글을 마칠까 한다.

'Oracle > Optimizer' 카테고리의 다른 글

Temp Table Transformation (5)	2009.09.28
스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15

Posted by extremedb

,

Group By 를 먼저 수행하고 Join 하라( Group By Placement )

Oracle/Optimizer 2009. 8. 24. 19:55

부제목 : GBP (Group By Placement ) 의 목적및 용도

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) 라는 글에서 먼저 Group By 를 수행하여 성능을 향상시키는 경우를 설명하였다. 오늘도 Group By Push Dwon 과 아주 흡사한 Query Transformation 에 대하여 설명하려 한다.

Group By Push Down 은 Parallel Query 에서 한정적으로 나타나는 기능이지만 Group By Placement 는 이러한 제약이 없다. 또한 Group By Placement 는 Query Transformation 의 종류 이지만 Group By Push Down은 SQL 자체의 변환과정이 없다는 점에서 엄연히 다르다.

GBP 가 뭐하는 거지?
GBP 란 기본적으로 조인의 부하를 줄이기 위한 수단이다. 조인을 수행하기전에 Group By 를 먼저 수행하고 건수를 줄이고 난후에 조인을 수행함으로서 조인건수가 획기적으로 감소되게 하는데 그목적이 있다. 이 기능은 주로 OLTP 보다는 DW 의 대용량 Mart 등에서 사용할 경우 성능향상을 극대화 할수 있다. 아래의 SQL 을 보자.

환경 Oracle 11g (11.1.0.7)

SELECT /*+ qb_name(main) place_group_by(@main (S@main)) */

cust_city_id, SUM (quantity_sold)

FROM customers c, sales s

WHERE c.cust_id = s.cust_id AND s.cust_id BETWEEN 5000 AND 5500

GROUP BY c.cust_city_id ;

위의 SQL의 목적은 고객 테이블(customers)과 판매 테이블(sales)을 조인하여 고객의 도시별 판매수량을 구하는 것이다.

상식적으로는 ..
일반적인 상식으로는 customers 테이블과 sales 테이블을 조인한 후에 Group BY 가 한번 수행된다고 알고 있다. 하지만 Oracle 11g 로 넘어오면서 '상식의 파괴'가 일어난다.

--------------------------------------------+-----------------------------------+

--------------------------------------------+-----------------------------------+

| 0 | SELECT STATEMENT | | | | 973 | |

| 1 | HASH GROUP BY | | 620 | 17K | 973 | 00:00:12 |

| 2 | HASH JOIN | | 7059 | 193K | 972 | 00:00:12 |

| 3 | VIEW | VW_GBC_1 | 7059 | 124K | 566 | 00:00:07 |

| 4 | HASH GROUP BY | | 7059 | 90K | 566 | 00:00:07 |

| 5 | PARTITION RANGE ALL | | 28K | 370K | 492 | 00:00:06 |

| 6 | TABLE ACCESS FULL | SALES | 28K | 370K | 492 | 00:00:06 |

| 7 | TABLE ACCESS FULL | CUSTOMERS| 54K | 542K | 405 | 00:00:05 |

--------------------------------------------+-----------------------------------+

Group By 가 두번 발생하다
위의 Plan 을 보면 Group By 가 두번 발생하였으며 조인도 sales 테이블을 Group By 한 이후에 발생하였다.

왜 두번 수행되나?
이것은 대용량 테이블인 sales 테이블을 먼저 조인 기준컬럼인 cust_id 로 먼저 Group By 하고 난후에 조인함으로서 조인의 부하를 줄이기 위함이다. 다시 말하면 오라클 Transformer는 SQL 을 아래와 같이 바꾼 것 이다.

SELECT c.cust_city_id cust_city_id, SUM (vw_gbc_1.item_2) sum_qt
FROM (SELECT s.cust_id item_1, SUM (s.quantity_sold) item_2
FROM sales s
   WHERE s.cust_id <= 5500 AND s.cust_id >= 5000
GROUP BY s.cust_id) vw_gbc_1,
   customers c
   WHERE c.cust_id = vw_gbc_1.item_1
   GROUP BY c.cust_city_id;

sales 테이블을 Group By 하여 인라인뷰를 먼저 만들고 customers 와 조인후 다시 c.cust_city_id 로 Group By 하고 있다. 인라인 뷰의 이름이 vw_gbc_1 인데 GBP 가 여러번 발생되면 vw_gbc_1, vw_gbc_2, vw_gbc_3 ... 처럼 숫자 부분이 증가 된다.

GBP 는 CBQT(Cost Based Query Transformation) 이다
Query Transformer 는 GBP 를 수행하기 위해 변환된(GBP 가 수행된) SQL 과 변환되지 않은 SQL을 각각 비용을 계산하여 가장 비용이 낮은 SQL 을 선택하게 된다. GBP 가 수행된 SQL 은 여러개 일수 있다.
아래는 작업을 수행하는 과정을 보여주는 10053 Trace 내용이다.

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

Cost-Based Group By Placement

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

GBP: Checking validity of GBP for query block MAIN (#1)

GBP: Checking validity of group-by placement for query block MAIN (#1)

GBP: Using search type: exhaustive

GBP: Considering group-by placement on query block MAIN (#1)

GBP: Starting iteration 1, state space = (1,2) : (0,0)

GBP: Transformed query
...중간생략

10053은 어렵지 않다
10053 을 어렵게 생각하는 DBA 들이 있다. 절대 어렵지 않다. GBP를 수행하기 위한 Using search type이 exhaustive 로 되어 있다. Using search type 이라는 것 은 변환 가능한 경우의 수를 어디까지 고려 할것인지 의 정도(level) 을 설명한 것이고 그 level 은 exhaustive 로 되어 있다. exhaustive 라는 것은 모든 변환가능한 경우의 수를 고려 하겠다는 뜻이다.

Iteration 이란 무엇인가?
Iteration 이란 CBQT 에서만 발생하며 기본적으로 변환이 수행된 경우와 수행되지 않은 경우의 Cost 를 비교하기 위한 경우의 수이다. 일반적으로 iteration 1 에서 변환이 수행되지 않은 경우를 나타내며 iteration 2 에서는 변환이 수행된 경우의 일련의 과정을 나타낸다. 마지막에는 iteration 1 과 iteration 2 의 Cost 를 비교하여 Cost 가 낮은 경우를 선택하게 된다.

Iteration 은 여러번 생길 수 있다
복잡한 SQL 의 경우 변환의 결과가 여러개 일수 있는데 이때는 Starting iteration 1, Starting iteration 2, Starting iteration 3 ... 등으로 증가한다. 하지만 원본 SQL 은 place_group_by 힌트를 사용하였으므로 GBP 를 수행한 경우(iteration 1)와 수행하지 않은 경우(iteration 2)의 Cost 를 비교하지 않고 iteration 1 에서 멈추게 된다.

GBP 를 Control 하자
GBP 를 Control 하는 파라미터는 _optimizer_group_by_placement 이며 Default 로 True 이다. 힌트는 GBP 를 강제하려면 place_group_by 헤제 하려면 no_place_group_by 힌트를 사용하면 된다.

결론
GBP 는 기본적으로 오라클이 자동으로 수행한다.
GBP 는 성능을 향상시키는 훌륭한 기능이지만 잘못 사용하면 오히려 독이 될수 있다. 조인을 먼저 수행하는 것이 오히려 결과 건수를 획기적으로 줄여주는 경우가 있는데 이런 경우는 GBP 를 사용하면 안된다. 이런 경우가 아니면서 조인하기 전에 먼저 Group By 하여 건수를 확실히 줄일수 있을때만 사용하여야 한다.

'Oracle > Optimizer' 카테고리의 다른 글

스칼라 서브쿼리를 서브쿼리로 변환하라 (11)	2009.09.10
Semi Join 의 재조명 (5)	2009.08.31
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09

Posted by extremedb

,

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down)

Oracle/Optimizer 2009. 8. 12. 00:40

Parallel Query 수행시 튜닝 방법에 대해 아래와 같이 2개의 글을 연재한 바 있다.
1.Parallel Query 의 조인시 Row Distribution --> Join 시 Row 의 분배방법 튜닝
2.Parallel Query 의 조인시 또다른 튜닝방법(Parallel Join Filter) --> Join Filter 튜닝

Parallel Query 의 튜닝방법은 많지 않아...
Parallel Query 의 튜닝 방법은 많지 않은데 그 이유는 Parallel Query 기능 자체를 튜닝 하는것이 아니라 Hash 조인의 튜닝 혹은 Block I/O 의 튜닝등 Parallel 자체와는 상관없는 것 을 튜닝하는 경우가 많기 때문이다.
오늘은 3번째로 Parallel Query 자체를 튜닝 하는 또다른 방법에 대하여 논의 할것이다.

잘못된 미신을 믿지 마라.
사실 필자가 Group By Push Down 기능을 설명하는 이유는 잘못된 미신 때문이다.
"Parallel Query 를 수행하면 Group By 가 두번 수행된다. 따라서 Group By 가 있는 SQL 은 Parallel 을 사용하지 마라."
실제 DBA 들의 입에서 오고 가는 말들이다. 잘못된 미신이 퍼져 있다니 참으로 안타까운 현실이 아닐수 없다.
Parallel Query 를 수행하면 Group By 가 무조건 두번 수행되는 것은 아니며 두번 수행 된다면 오히려 성능향상을 기대할 수 있다.
이 글을 읽고 개념을 확실히 하기 바란다.

TQ 를 알고 가자.
먼저 Group By Push Down 기능을 설명하기 전에 TQ(Table queues) 개념을 알아야 한다.
1.TQ 는 Processes간의 데이터를 주고받는 기능을 한다.
2.하나의 TQ 는 여러개의 parallel Slave 를 가진다.
3.TQ 는 Parallel Query 수행시 생성된다.
상세한 개념은 위에서 언급한 글중 2번을 참조하기 바란다.

Group By Push Down 이 뭐야?
1.Group By Push Down 이란 TQ 에 데이터를 전달하기 전에 Group By 를 수행하여 데이터의 건수를 대폭 줄인후에 TQ 에 데이터를 전달함으로서 일량을 줄이고 성능을 향상시키는데 목적이 있다.
2.Group By Push Down 은 Parallel Query 에 Group By 가 포함되어 있는 경우 발생한다.

원리는 같다.
이기능은 마치 DW 용 SQL 작성시 Fact 테이블(대용량) 을 먼저 Group By 한후에 Dimension 테이블(소용량 코드 테이블)과 조인하여 조인 건수를 대폭 줄임으로서 성능 향상을 꾀하는것과 같은 개념이다. 이기능은 오라클이 자동으로 해주기도 하는데 이것을 "Group By Placement" 라고 하며 추후 따로 글을 올릴 생각이다.

어떻게 하는건데?
Group By Push Down을 수행하기위해 아래의 SQL 을 보자.
환경: Oracle 11g 11.1.0.7

SELECT /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) GBY_PUSHDOWN */
prod_id, cust_id, COUNT (*) cnt
FROM sh.sales A
GROUP BY prod_id, cust_id;

GBY_PUSHDOWN 란 힌트를 사용하였으며 아래의 Plan 에서 보는바와 같이 성공적으로 Group By Push Down 이 수행되었다.

---------------------------------------------------------------------------
| Id | Operation    | Name | E-Rows | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    |    | |
| 1 | PX COORDINATOR    |    |    |    | |
| 2 | PX SEND QC (RANDOM)    | :TQ10001 |    359K|    | |
| 3 |    HASH GROUP BY    |    |    359K| 2197K (0)| |
| 4 | PX RECEIVE |    |    359K|    | |
| 5 |    PX SEND HASH    | :TQ10000 |    359K|    | |
| 6 | HASH GROUP BY | |    359K| 3284K (1)|    1024 |
| 7 |    PX BLOCK ITERATOR |    |    918K|    | |
|* 8 | TABLE ACCESS FULL| SALES    |    918K|    | |
---------------------------------------------------------------------------

원래 수행되어야 하는 Group By 는 Id 기눈으로 3번(파랑색 부분) 이지만 Id 6번에서 먼저 Group By 가 수행되었다. 그이유는 Id 5번의 :TQ10000 에게 데이터를 전달하기전에 데이터를 줄여서 성능을 향상시키기 위함이다. 실제로 옵티마이져는 Id 5번에서 Group By 된 359K Row에 대한 데이터만 처리 할것으로 예상하고 있다.

튜닝은 Trade Off 이다.
주의 사항이 있다. 실제로 TQ의 일량은 줄어들지만 불필요한 Hash Group By 혹은 Sort Group By 가 수행되어 성능이 더 나빠질수 도 있다는것이다. Id 6 에서 Hash Area Size 가 부족하여 Disk 작업(Used-Tmp 부분 참조)이 발생하였다. 따라서 Group By 를 하면 건수가 몇배~ 몇십배이상 줄어드는 경우에 적용하여야 하며 Disk 에서 Sort 및 Hash 작업이 발생하는 경우는 PGA 튜닝을 동반하여야 한다.

Group By Push Down 이 적용되지 않은 Plan 을 보여다오.
GBY_PUSHDOWN 힌트 대신에 NO_GBY_PUSHDOWN 힌트를 사용하면 Group By Push Down이 발생하지 않는다.

SELECT /*+ GATHER_PLAN_STATISTICS FULL(A) parallel(A 4) NO_GBY_PUSHDOWN */
prod_id, cust_id, COUNT (*) cnt
FROM sh.sales A
GROUP BY prod_id, cust_id;

----------------------------------------------------------------
| Id | Operation | Name | E-Rows | Used-Mem |
----------------------------------------------------------------
| 0 | SELECT STATEMENT    |    |    |    |
| 1 | PX COORDINATOR |    |    |    |
| 2 | PX SEND QC (RANDOM) | :TQ10001 |    359K|    |
| 3 |    HASH GROUP BY |    |    359K| 4492K (0)|
| 4 | PX RECEIVE    |    |    918K|    |
| 5 |    PX SEND HASH | :TQ10000 |    918K|    |
| 6 | PX BLOCK ITERATOR |    |    918K|    |
|* 7 |    TABLE ACCESS FULL| SALES    |    918K|    |
----------------------------------------------------------------

정상적으로 Group By 가 한번만 수행되었지만 옵티마이져는 TQ 의 일량이 Group By Push Down 이 적용된 SQL 에 비하여 918K 로 약 3배정도 중가한것으로 판단하였고 실제로도 그렇게 수행된다.

Group By Push Down은 11g 신기능이 아니다.
GBY_PUSHDOWN / NO_GBY_PUSHDOWN 등의 힌트는 11g 에서 새로 나온 것 이다. 하지만 이전버젼에서도 _groupby_nopushdown_cut_ratio 파라미터를 0 으로 세팅하면 Group By Push Down 을 강제로 수행할수 있다. 이파라미터의 Default 값은 3 이며 이경우는 Group By Push Down 의 수행여부를 옵티마이져가 판단한다. 아래는 옵티마이져의 Costing 과정을 10053 Trace 에서 발췌한 것이다.

*********************************
Number of join permutations tried: 1
*********************************
GROUP BY adjustment factor: 0.707107
GROUP BY cardinality: 359386.000000, TABLE cardinality: 918843.000000
Costing group-by pushdown:
SORT ressource Sort statistics
Sort width: 598 Area size:    552960 Max Area size: 104857600
Degree: 1
Blocks to Sort: 563 Row size: 20 Total Rows: 229711
Initial runs: 2 Merge passes: 1 IO Cost / pass:    306
Total IO sort cost: 869    Total CPU sort cost: 230852464
Total Temp space used: 4629000
Distribution cost: resc_cpu: 91885309 resp_cpu: 22971327
Costing final group-by:
Subtracting no-pushdown group-by:
SORT ressource Sort statistics
... 이후 생략

적재적소에 사용하자.
실제로 옵티마이져는 Group By Push Down 의 수행여부를 Cost 로서 판단하고 있으므로 기본적으로는 오라클에 맏기면 된다. 하지만 아주 복잡한 SQL 이나 옵티마이져가 판단을 잘못할 경우에 Group By Push Down 을 수동으로 적절히 발생시킨다면 성능향상을 꾀할수 있다.

편집후기:
필자가 이글을 올린다고 하니 어느 지인은 컨설턴트의 밥그릇 타령을 한다.
다시말해 이런것들을 모든 개발자가 안다면 컨설턴트가 설자리가 없다는 것인데...
글쎄...
여러분은 어떻게 생각하는가?

'Oracle > Optimizer' 카테고리의 다른 글

Semi Join 의 재조명 (5)	2009.08.31
Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20

Posted by extremedb

,

11g DBMS_STATS 패키지 성능개선의 3가지 원리

Oracle/Optimizer 2009. 6. 15. 13:00

1번부분은 오류가 있으므로 코멘트를 반드시 참조하기 바란다.(2009.06.20)

DBMS_STATS 정녕 필요악인가?
오라클 DBMS의 모든 통계정보는 DBMS_STATS 패키지에 의해서 관리된다고 할수 있다. 오라클이 제공하는 모든 패키지가 중요하지만 가장 중요한 패키지 하나만 선정하라고 하면 주저없이 이 패키지를 꼽는다. 하지만 이패키지에는 모든사람이 알고 있는 치명적인 단점이 하나 있다. 바로 통계정보 생성 속도가 느리다는 것이다. 하지만 11g 에서 몇가지의 단점을 보완하였다.

11g 에서 성능이 빨라지다.
오늘은 11g 에서 DBMS_STATS 패키지의 개선사항에 대하여 알아보려 한다. 11g 에서 개선된 것은 크게 3가지 이다.
1. 컬럼 단위의 MIN/MAX 통계정보 생성에 의한 SQL 성능향상.
DBMS_STATS 패키지를 이용한 통계정보 생성시 컬럼단위의 MIN/MAX 값을 생성한다는것을 아는가?
11g 부터 MIN/MAX aggregation 이 포함된 SQL 을 만나면 컬럼단위의 통계정보를 참조함으로서 SQL 의 성능을 향상 시킨다. 예를 들어 다음과 같은 SQL 을 수행시킬경우 10g 에 비하여 약 30% 의 성능향상을 얻을수 있다.

select min(c2), max(c2), min(c3), max(c3), min(c6), max(c6),
……
min(c32), max(c32)
from CUSTOMERX;

통계정보로 부터 컬럼별로 MIN/MAX 값을 참조한후에 테이블을 scan 하면서 MIN~ MAX 값에서 벗어나는 값들만 MIN/MAX 함수를 적용하여 보여주면 되기 때문이다. 이것은 DBMS_STATS 패키지 자체의 성능향상이 아니라 DBMS_STATS 패키지에 의한 Aggregation SQL 의 성능 향상인 셈이다.

2. NDV(Number Of Distinct Value) 통계정보 생성시의 성능향상.
NDV 값을 생성하기 위해서는 다음과 같은 SQL 이 수행되어야만 한다.

select count(distinct c1), count(distinct c2), ..
from T

위와 같은 SQL 은 테이블 SIZE 가 클경우 심각한 SORT 부하를 발생시킨다. 그렇다고 NDV 생성시 ESTIMATE 1% 로 할경우 데이터에 NULL 이 많다거나 컬럼값이 하나로 편중되는 현상이 심할경우 부정확한 NDV 를 생성할수 있다. 이것은 SQL 의 성능에 부정적으로 작용할수 있다.
11g 부터는 새로운 hash-based algorithm 을 추가하였다. 10g 에서 Sort Group By 대신에 Hash Group By 를 적용하면서 성능이 향상된것과 같은 개념이다. 주의 해야될사항은 새로운 hash-based algorithm 을 사용하려면 Sampling 을 하면 안된다는 것이다. Sampling을 하지 않으면 전체데이터를 scan 하지만 수행시간은 오히려 Sampling 보다 같거나 빠르다. 아래는 오라클사에서 테스트 한 결과이다.

DataBase Ver Elapsed Time (s)
----------------------------- ----------------
Oracle Database 10g Release 2 10% 2155
Oracle Database 10g Release 2 100% 24242
Oracle Database 11g 1516

10g R2 에서 Sampling 10% 로 한것보다 오히려 11g 에서 전체건에 대하여 생성하는것이 더빠르다는 결론이 나왔다. 또한 NDV 의 정확성 또한 거의 100% 에 가깝다. 새로운 algorithm을 사용할경우 획기적인 성능향상과 정확성을 기대할수 있다. 자세한 내용은 아래의 White Paper 를 참조하라.

3. 파티션 적용 테이블에서 Incremental NDV 생성기능.
파티션 적용테이블에서 거래내역이나 이력데이터를 보관할경우 대부분이 일자로 Range 파티션을 하게된다.
Global 통계정보를 생성하려면 10g 까지는 2단계의 과정을 거쳐야 했다.
첫번째로는 파티션 레벨의 통계정보를 생성 하기위해 각파티션을 scan 하는작업수행.
두번째로는 Global 통계정보를 생성하기위해 전체 파티션을 scan 하는 작업수행.
정말 비효율적이지 않는가? 같은 파티션을 두번씩 읽은 셈이다.

10g 에서는 아래처럼 granularity를 옵션으로 사용하거나 아니면 수동으로 2번 돌려야 했다. 아래예제 에서는
estimate_percent 를 10% 로 한정 하였다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL and PARTITION');

위의 방법대로 하면 전체 파티션을 내부적으로 2번씩 scan 해야 하므로 성능면에서 최악이다.
경험 많은 DBA 는 새로이 추가된 파티션의 통계정보만 생성하고 Global 통계정보를 생성함으로 전체 파티션에 대하여 2번씩 scan 하는 비효율을 제거한다. 아래처럼 새로 추가된 파티션을 명시하면 된다.

exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', 'PART10', estimate_percent =>10, granularity =>'PARTITION');
exec dbms_stats.gather_table_stats('CUST', 'CUSTOMERX', estimate_percent =>10, granularity =>'GLOBAL');

하지만 2번째의 방법으로도 Global 통계정보의 생성을 위한 전체 파티션 scan은 막을수 있는 방법이 없었다.
이러한 성능 이슈 때문에 11g 에서는 2단계의 과정이 없어지고 과정이 하나로 줄었다. 쉽게 말하면 각파티션 통계정보를 생성해놓고 Global 통계정보 생성시 파티션의 통계정보를 sum 하여 이용한다는 것이다. 또한 특정파티션을 지정하지 않아도 아래와 같이 gather_table_stats 을 수행하기전에 set_table_prefs를 수행하게 되면 바뀌거나 새로이 insert 된 파티션만 scan 을 하게된다.

11g :
exec dbms_stats.set_table_prefs('CUST', 'CUSTOMERX', 'INCREMENTAL', 'TRUE');
exec dbms_stats.gather_table_stats(‘CUST', 'CUSTOMERX', granularity =>'GLOBAL and PARTITION');

11g 에서는 estimate_percent를 10% 로 한정 하지 않았기 때문에 성능 어떨지 궁금하지 않은가?
아래의 테스트 결과를 보자.

DataBase Ver Elapsed Time (s)
-------------------------------    ----------------
Oracle Database 11g Incremental    1.25
Oracle Database 10g Release 2 10%    2152 --> 전체를 2번 scan 한 경우
Oracle Database 10g Release 2 10% Manual 1058 --> 추가된 파티션만 명시한 경우

10g 에서 추가된 파티션만 명시한 경우에 비하여 무려 800 배 정도 빨라졌다. 대단하지 않은가?
이것은 3가지의 효과가 서로 상호작용한 효과이다.
1.Global 통계정보 생성시 2단계의 작업(각 파티션의 작업 + Global 작업) 이 1단계로 축소되고
2.Imcremental NDV 작업이 가능 해졌다는것
3.미리 언급한 hash-based algorithm 의 효과

결론 :
10g 까지 DBMS_STATS 패키지는 그중요성에 비하여 느린 성능이슈 때문에 Necessary Evil (필요악) 으로 인식되어 왔다. 하지만 드디어 11g 에서 몇가지의 개선사항으로 좀더 귀여운 모습을 하고 우리에게 돌아왔다. 11g 로 업그레이드 할이유가 몇가지 더 생긴것이다.

Reference : DSS Performance in Oracle 11g

편집후기 : 위에 링크된 백서는 DBMS_STATS 패키지 뿐만 아니라 11g 의 매뉴얼에 없는 여러가지 비급이 실려 있으므로 필독 하기 바란다.

백서내용 요약
1.Result Cache 신기능.
2.Composite Partitioning 신기능.
3.Full Outer Join 개선사항.
4.Nested Loop Join 개선사항.

'Oracle > Optimizer' 카테고리의 다른 글

Group By 를 먼저 수행하고 Join 하라( Group By Placement ) (4)	2009.08.24
Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
오라클에 트랜스포머가 있다? (4)	2009.04.15

Posted by extremedb

,

PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라.

Oracle/Optimizer 2009. 6. 9. 14:54

Query Transformer 의 냉대
튜닝을 하는 많은 사람들이 PM 의 개념을 모른다는 결과가 나왔다. 정기모임 술자리에서 즉석으로 설문을 하였는데 결과는 충격적이었다. 참석자 10명은 DBA, 튜너, DB 컨설턴트 등등 DB 전문가들의 모임이라고 할수 있는데 단 한명도 아는사람이 없었다.

필자가 충격적이라고 한 이유는 그모임의 많은 사람들이 왠만한 SQL 의 COST 를 계산할수 있는 내공을 가진 사람들이 었기 때문이다. 다행히 JPPD 나 VIEW MERGING, Unnesting 과 같이 튜닝 책에 소개 되는 간단한 변환들은 알고 있었다. 하지만 Query Transformer 가 푸배접을 받고 있다는 생각은 지울수가 없었다.

Query Transformer 는 그 중요성이 옵티마이져의 50% 를 차지한다. 왜그럴까? 옵티마이져의 3대 Components 는 Query Transformer, Cost Estimator, Plan Generator 이지만 이중에서 우리가 연구할수 있는 것은 Query Transformer 와 Cost Estimator 이며 Plan Generator 의 비밀은 오라클사의 DBMS 설계자/개발자만이 알수 있는 영역이기 때문이다. 또한 SQL 이 Transformer 에 의하여 변형되고 재작성 되기 때문에 성능에 직접적인 영향을 끼친다. 대부분의 경우 Query Transformation 이 발생하면 성능에 긍정적인 영향을 끼치지만 예외적으로 부정정인 영향을 줄수가 있으므로 가능한 Query Transformer에 대하여 상세히 알아야 한다.

어찌되었건 모임에서 PM 관련 내용을 블로그에 올리겠다는 약속을 하였다.

PM ( Predicate Move Around ) 이란?

인라인뷰가 여러 개 있고 각각의 where 절에 공통적인 조건들이 있다고 가정하자.
이럴경우에 모든 인라인뷰의 where 절에 똑 같은 조건들을 반복해서 사용해야 할까?
물론 그렇게 해야 하는 경우가 있지만 아래의 경우에는 그렇지 않음을 알수 있다..

SELECT /*+ qb_name (v_outer) */

v1.*

FROM (SELECT /*+ qb_name (IV1) no_merge */

e1.*, d1.location_id

FROM employee e1, department d1

WHERE e1.department_id = d1.department_id

AND d1.department_id = 30

) v1,

(SELECT /*+ qb_name (IV2) no_merge */

d2.department_id, AVG (salary) avg_sal_dept

FROM employee e2, department d2, loc l2

WHERE e2.department_id = d2.department_id

AND l2.location_id = d2.location_id

GROUP BY d2.department_id

) v2

WHERE v1.department_id = v2.department_id

AND v1.salary > v2.avg_sal_dept ;

----------------------------------------------------------+----------------------------------+

----------------------------------------------------------+----------------------------------+

| 0 | SELECT STATEMENT | | | | 5 | |

| 1 | HASH JOIN | | 1 | 176 | 5 | 00:00:01|

| 2 | VIEW | | 1 | 28 | 2 | 00:00:01|

| 3 | HASH GROUP BY | | 1 | 21 | 2 | 00:00:01|

| 4 | NESTED LOOPS | | | | | |

| 5 | NESTED LOOPS | | 6 | 126 | 2 | 00:00:01|

| 6 | NESTED LOOPS | | 1 | 14 | 1 | 00:00:01|

| 7 | INDEX RANGE SCAN | DEPT_IX_01 | 1 | 11 | 1 | 00:00:01|

| 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 23 | 69 | 0 | |

| 9 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 6 | | 0 | |

| 10 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 42 | 1 | 00:00:01|

| 11 | VIEW | | 6 | 888 | 2 | 00:00:01|

| 12 | NESTED LOOPS | | | | | |

| 13 | NESTED LOOPS | | 6 | 474 | 2 | 00:00:01|

| 14 | INDEX RANGE SCAN | DEPT_IX_01 | 1 | 11 | 1 | 00:00:01|

| 15 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX| 6 | | 0 | |

| 16 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 6 | 408 | 1 | 00:00:01|

----------------------------------------------------------+----------------------------------+

Predicate Information:

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

1 - access("V1"."DEPARTMENT_ID"="V2_DEPT"."DEPARTMENT_ID")

1 - filter("V1"."SALARY">"V2_DEPT"."AVG_SAL_DEPT")

7 - access("D2"."DEPARTMENT_ID"=30)

8 - access("L2"."LOCATION_ID"="D2"."LOCATION_ID")

9 - access("E2"."DEPARTMENT_ID"=30)

14 - access("D1"."DEPARTMENT_ID"=30)

15 - access("E1"."DEPARTMENT_ID"=30)

다른뷰에 조건이 추가되었다.

Predicate Information을 보면 두번째 뷰(v2 ) 에 "D2"."DEPARTMENT_ID"=30 조건과 "E2"."DEPARTMENT_ID"=30 조건이 파고들어 간 것을 알수 있다. 이 현상 때문에 D1 과 E1 에서 인덱스를 사용하였는데 결과는 성능면에서 아주 성공적이다. 그렇다면 오라클은 어떤 과정을 거쳐서 이작업을 진행하였을까?

10053 trace 정보를 보면 PM의 진행과정이 매우 상세하게 나와 있다.

PM: Considering predicate move-around in query block V_OUTER (#1)

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

Predicate Move-Around (PM)

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

PM: Passed validity checks.

PM: Pulled up predicate "V1"."DEPARTMENT_ID"=30

from query block IV1 (#2) to query block V_OUTER (#1)

PM: Pushed down predicate "D2"."DEPARTMENT_ID"=30

from query block V_OUTER (#1) to query block IV2 (#3)

PM 은 순서가 중여하다.
10053 trace 내용을 분석하여 수행과정을 살펴보자.

1. 먼저 PM 이 수행될수 있는지 검사한다.

2. V1 에서 WHERE 조건 d1.department_id = 30 를 바깥쪽 메인쿼리로 이동시킨다.

이것을 Predicate pull up 이라고 한다.

3. 메인쿼리로 옮겨진 where 조건을 v2 에 복사한다.

이것을 Predicate Push down 이라고 한다.

4. 최종 결과에서 중복된 조건절이 존재하면 삭제한다..

V1, V2 가 메인쿼리(V_OUTER)에서 department_id 기준으로 조인되고 있기 때문에 조건절을 V_OUTER 로 빼낸 다음에 V2 에 조건절을 밀어 넣고 있다. 이것은 JPPD 기능과 유사한 면이 있지만 Predicate pull up 이 반드시 먼저 일어나야 한다는 점과 Hash 조인등에서도 PM 이 발생한다는 점에서 엄연히 다르다. 오라클은 여러분이 모르는 사이에 조건절을 이리 저리 옮겨 다니면서 SQL 의 최적화를 시도하고 있다.

PM 아무때나 발생하나?
그럼 PM 이 어떤 조건일 경우 발생하는지 짐작할수 있겠는가?

다음과 같은 조건일 경우 PM 이 발생된다.

1. 뷰(혹은 인라인뷰) 가 2개 혹은 그이상이 되어야 한다.(예제에서 V1, V2 가 있음)

2. 특정 인라인뷰내의 조건이 존재하고 뷰의 바깥쪽에서 조건을 사용한 컬럼으로 조인이 발생할 경우에 발생된다. 예제에서 는 d1.department_id = 30 으로 V1 내부에 조건이 존재하고 V_OUTER 에서 department_id 로 V2 와 조인을 하고 있다.

3. VIEW MERGE 가 발생하지 않아야 한다. Merging 이 발생되면 PM 대신에 Transitive Predicates 가 발생된다.

4. 파라미터 _PRED_MOVE_AROUND 가 true 로 지정이 되어 있어야 한다.

결론 :
제목에서 보듯이 PM 의 개념은 매우 간단하다. Where 조건을 다른뷰에 이동시키는 기능이며 Heuristic Transformatin 의 대표적인 예제이다.
오늘 올린 글은 현재 집필중인 책의 내용인데 일부를 먼저 공개하기로 결정 하였다.

편집후기 : JPPD 와 PM 이 헷갈린다는 보고가 들어왔다. 둘다 WHERE 조건이 PUSH 되는것이지만 가장 결정적이 차이점은 JPPD 는 Predicate pull up 기능이 없다는 것이다. 아주 명확하게 구분할수 있다.

'Oracle > Optimizer' 카테고리의 다른 글

Parallel Query 수행시 Group by 를 먼저 수행하라(Group By Push Down) (9)	2009.08.12
11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
Query Transformation Internal ( about JPPD using Lateral View ) (0)	2009.04.20
오라클에 트랜스포머가 있다? (4)	2009.04.15
Access Sub Query 의 함정 (4)	2008.11.19

Posted by extremedb

,

Query Transformation Internal ( about JPPD using Lateral View )

Oracle/Optimizer 2009. 4. 20. 01:30

2007년 가을이었던가? 사내 컨설턴트로 부터 조금 어려운 질문이 들어왔다.
"다른종류의 쿼리변환은 모두 내가 수동으로 쿼리를 만들수 있습니다. 하지만 JPPD 는 쿼리를 만들수 없습니다.
혹시 오라클이 JPPD 를 적용한 SQL 을 보여줄수 있는지요?"

JPPD 는 수도없이 봐왔지만 막상 개념을 적용한 SQL 을 내손으로 작성하려 하니 전혀 작성할수가 없었다.
여러분이 알만한 미국및 영국의 유명한 컨설턴트들과 접촉을 해보았지만 역시 그들도 마찬가지였다. 필자는 이문제로 1주간 고생을한 끝에 직접 원리를 알아내었다. 따라서 어떤 메뉴얼에도 어떤 튜닝책에도 이런 이야기는 없음을 먼저 밝혀둔다.

JPPD ? 그게뭐야?
쿼리변환의 중요성을 알았으므로 이제 쿼리변환중에 가장 자주나오는 Unnesting 과 JPPD 의 개념을 알아보자.
Unnesting 이란 서브쿼리를 인라인뷰로 만들어 from 절로 끌어올리는 쿼리변환을 의미한다. JPPD 란 (Join Predicate Push Down)의 약자로서 인라인뷰 외부의 조인 조건이 인라인뷰 안쪽으로 파고드는 것을 의미한다.
물론 인라인뷰는 대신에 뷰로 사용해도 마찬가지 이다.

그럼 쿼리변환을 한번 해보자.
지난번 오라클에 트랜스포머가 있다? 라는 글에서 다단계 쿼리변환(Unnesting + View Merging) 사례를 설명한바 있다. 이번에는 다단계 쿼리변환 이면서 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD가 수행되는 사례를 알아보자.

환경 : Oracle 10.2.0.4

select /*+ gather_plan_statistics PUSH_PRED(OUTER) */
outer.*
from (SELECT * FROM emp outer
UNION ALL
SELECT * FROM emp outer) OUTER
where outer.sal > ( select /*+ QB_NAME(SUB) UNNEST */ avg(inner.sal)
from emp inner
where inner.deptno = outer.deptno
) ;

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

위실행계획은 쿼리변환이 2단계로 쿼리변환이 수행되었다.
지금부터 과정을 하나하나 살펴보자.

1.단계 : Unnesting 수행
먼저 서브쿼리가 인라인뷰로 바뀌어 from 절로 올라간다.
그리고 쿼리의 바깥쪽에 WHERE 조건이 생성되며 조인절도 생성된다.
이것은 아래의 쿼리와 같다.

select /*+ gather_plan_statistics LEADING(SUB OUTER) USE_NL(OUTER) NO_PUSH_PRED(OUTER) */
   outer.*
from (SELECT * FROM SI31041.emp outer    --> JPPD not yet
   UNION ALL
   SELECT * FROM SI31041.emp outer) OUTER ,
   ( select deptno, avg(sal) AS VW_COL_1
from emp
   group by deptno
   ) SUB
where outer.sal > SUB.VW_COL_1
and outer.deptno = SUB.deptno ;

------------------------------------------------------------------------------
| Id | Operation    | Name | Starts | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------
| 1 | NESTED LOOPS    |    |    1 | 12 |00:00:00.01 |    50 |
| 2 | VIEW |    |    1 |    3 |00:00:00.01 | 7 |
| 3 |    HASH GROUP BY |    |    1 |    3 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| EMP |    1 | 14 |00:00:00.01 | 7 |
|* 5 | VIEW |    |    3 | 12 |00:00:00.01 |    43 |
| 6 |    UNION-ALL |    |    3 | 84 |00:00:00.01 |    43 |
| 7 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    22 |
| 8 | TABLE ACCESS FULL| EMP |    3 | 42 |00:00:00.01 |    21 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(("OUTER"."SAL">"SUB"."VW_COL_1" AND "OUTER"."DEPTNO"="SUB"."DEPTNO"))

위의 Predicate Information을 보면 서브쿼리가 인라인뷰로 바뀌었지만 아직 인라인뷰 내로 WHERE 조건및
조인조건이 파고들지 못했다. 따라서 뷰를 만들고 FILTER 처리가 된것이다.

2단계: JPPD 수행
조인조건및 WHERE 조건이 UNION ALL 로 분리된 각각의 SQL 에 파고든다.
최종적으로 완성된 쿼리는 아래와 같다.

select /*+ push_pred (outer) */
outer.*
from (select /*+ unnest qb_name (sub) */
avg(inner.sal) vw_col_1,inner.deptno deptno
   from emp inner
group by inner.deptno
) vw_sq_1,
( select *    --> JPPD OK using lateral view
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴 (Lateral view)
and outer.sal>vw_sq_1.vw_col_1
union all
   select *
   from emp outer
where outer.deptno=vw_sq_1.deptno --> 조건이 인라인뷰 내로 들어옴(Lateral view)
and outer.sal>vw_sq_1.vw_col_1
) outer

JPPD 의 비밀이 풀리다!
위의 SQL 에서 이상한점을 발견할수 있는가?
인라인뷰 OUTER 에서 다른 인라인뷰 VW_SQ_1 의 컬럼을 참조하고 있다. 이것은 놀라운 일이다. 인라인뷰 내에서 마치 스칼라 서브쿼리처럼 from 절의 다른 테이블 혹은 다른 인라인뷰의 정보를 사용한것이다. 바로 이것이 Lateral View 의 개념이다. 다시말하면 Lateral View란 스칼라 서브쿼리처럼 사용할수 있는 "스칼라 인라인뷰" 라고 생각하면 된다. 위의 SQL 을 보면 아래의 실행계획과 같을수 밖에 없다는것을 알수 있다.

---------------------------------------------------------------------------------------------
| Id | Operation    | Name    | Starts | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS    | |    1 | 10 |00:00:00.03 |    27 |
| 2 | VIEW | VW_SQ_1 |    1 |    5 |00:00:00.02 | 7 |
| 3 |    HASH GROUP BY | |    1 |    5 |00:00:00.02 | 7 |
| 4 | TABLE ACCESS FULL    | EMP |    1 | 14 |00:00:00.02 | 7 |
| 5 | VIEW | |    5 | 10 |00:00:00.01 |    20 |
| 6 |    UNION ALL PUSHED PREDICATE | |    5 | 10 |00:00:00.01 |    20 |
|* 7 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 |    11 |
|* 8 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 5 |
|* 9 | TABLE ACCESS BY INDEX ROWID| EMP |    5 |    5 |00:00:00.01 | 9 |
|* 10 |    INDEX RANGE SCAN    | IX_EMP_N3 |    5 | 13 |00:00:00.01 | 4 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter("OUTER"."SAL">"VW_COL_1")
   8 - access("OUTER"."DEPTNO"="DEPTNO")
   9 - filter("OUTER"."SAL">"VW_COL_1")
10 - access("OUTER"."DEPTNO"="DEPTNO")

오라클만이 Lateral View를 사용할수 있다.
아쉽게도 Lateral View 는 오라클만이 내부적으로 사용할수 있다. 필자나 여러분이 사용할 경우 에러가 발생한다. 그렇다면 위의 SQL은 어디서 나온것인가? 그것은 쿼리 트랜스포머의 쿼리변환작업을 10053 이벤트를 이용하여 Trace 파일에서 추출한 결과 이다.

결론:
이상으로 우리는 2가지 사실을 알아내었다.
첫번째는 서브쿼리 Unnsting 후에 View Merging 이 실패하는 경우에 JPPD를 시도한다는것.
두번째는 쿼리 트랜스포머는 JPPD 수행시 Lateral View를 사용한다는것이다.
마지막으로 가까운 미래에 Lateral View를 User가 직접 사용할수 있는날을 기대하면서 이글을 마치고자 한다.

Further Reading :
Lateral View 개념 : http://scidb.tistory.com/search/lateral%20view
SubQuery Unnesting : http://scidb.tistory.com/entry/SubQuery-Using-Method-1

'Oracle > Optimizer' 카테고리의 다른 글

11g DBMS_STATS 패키지 성능개선의 3가지 원리 (3)	2009.06.15
PM ( Predicate Move Around ) : Where 조건을 다른뷰에 이동시켜라. (2)	2009.06.09
오라클에 트랜스포머가 있다? (4)	2009.04.15
Access Sub Query 의 함정 (4)	2008.11.19
오라클은 얼마나 똑똑한가? (3)	2008.10.10

Posted by extremedb

,


	오동규 블로그 odong91@naver.com by extremedb

'Oracle/Optimizer'에 해당되는 글 28건

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

'Oracle > Optimizer' 카테고리의 다른 글

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바