'Oracle' 카테고리의 글 목록 (2 Page)

파티션과 IN 조건의 잘못된 만남

Oracle/TroubleShooting 2010. 8. 20. 00:01

가능한가?

SQL문은 실행순서가 있다. 일반적으로 Order By가 가장 마지막에 수행된다. 만약 Order By가 없다면 마지막에 실행되는 것은 Group By이다. 데이터를 처리하는 작업은 Order By나 Group By 작업을 수행하기 이전에 끝난다. 상식적으로 보더라도 데이터를 읽는 작업과 조인작업을 먼저 처리해야 전체 데이터를 Grouping 할 수 있기 때문이다. 그런데 만약 데이터를 처리하는 작업이 Group By 이후에 발생한다면? 이런 일은 논리적으로 발생할 수 없다. 하지만 오늘 한가지 경우를 보여주려 한다.

먼저 오라클 SH 스키마의 Sales 테이블에 Local Partition 인덱스를 하나 생성한다.

Sales 테이블은 Time_id로 분기별 Range Partition이 되어있다.

CREATE INDEX SALES_TIME_CHANNEL_IX ON SALES

(TIME_ID, CHANNEL_ID) LOCAL;

먼저 정상적으로 처리되는 경우를 보자. IN 조건을 Pair로 여러개 주어본다.

SELECT /*+ gather_plan_statistics */ TIME_ID, COUNT(TIME_ID)

FROM SALES

WHERE (TIME_ID, CHANNEL_ID) IN ( (TO_DATE('19980214'), 3),

(TO_DATE('19980214'), 2),

(TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

위의 SQL에서 주의해야 될 것은 IN 조건이 TIME_ID와 CHANNEL_ID 로 동시에 공급된다는 것이다.

TIME_ID COUNT(TIME_ID)

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

98/02/14 391

1 row selected.

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

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

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

| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 3 |

| 2 | PARTITION RANGE SINGLE| | 1 | 391 | 3 |

|* 3 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 391 | 3 |

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

Predicate Information (identified by operation id):

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

3 - access("TIME_ID"=TO_DATE('19980214'))

filter(("CHANNEL_ID"=2 OR "CHANNEL_ID"=3 OR "CHANNEL_ID"=4))

정상적으로 Grouping 되어 1건이 나왔고 Plan상에 Group By도 마지막에 수행되었다.

INLIST ITERATOR ! 니가 나를 배신하다니...

이제 1998년 2월 12일의 데이터를 추가로 공급해보자.

SELECT /*+ gather_plan_statistics */

TIME_ID, COUNT(TIME_ID)

FROM SALES

WHERE (TIME_ID, CHANNEL_ID) IN ( (TO_DATE('19980212'), 4),

(TO_DATE('19980214'), 2),

(TO_DATE('19980214'), 3),

(TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

TIME_ID COUNT(TIME_ID)

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

98/02/12 50

98/02/14 54

98/02/14 287

98/02/14 50

4 rows selected.

답이 틀리다(Wrong Result Bug)

뭔가 이상하다. TIME_ID로 Grouping 하였으므로 결과건수는 1998년 2월 12일과 1998년 2월 14일로 두건만 나와야 한다. 그런데 2월 14일 데이터가 Grouping 되지 않고 3건이 나와버렸다. 답이 틀리므로 이것은 버그이다.

왜 이런 일이 발생할까?

비밀은 실행계획에 있다.

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

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

| 0 | SELECT STATEMENT | | 1 | 4 | 7 |

| 1 | INLIST ITERATOR | | 1 | 4 | 7 |

| 2 | PARTITION RANGE ITERATOR| | 4 | 4 | 7 |

| 3 | SORT GROUP BY NOSORT | | 4 | 4 | 7 |

|* 4 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 4 | 441 | 7 |

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

상식적으로는 Grouping을 가장 마지막에 한번만 해야 한다. 하지만 실행계획을 보면 그렇지 못하고 INLIST ITERATOR 작업 이전에 Grouping을 해버린다. ID 기준으로 3번이 그것인데 INLIST의 개수만큼 SORT GROUP BY가 반복된다. Starts 항목이 그것을 증명한다. 개념적으로 말하면 각각의 WHERE 조건마다 Group By를 수행하고 그 결과들을 Union 한 것이다.

항상 버그가 발생하는 것은 아니다

파티션과 IN 조건이 만나야 버그가 발생한다. 또한 WHERE 조건이 변경되지 않고 INLIST ITERATOR가 발생해야 버그가 발생한다. 이 글에서 최초로 실행 시킨 SQL의 WHERE 조건은 TIME_ID = TO_DATE('19980214') AND CHANNEL_ID IN (2,3,4) 로 바뀌어 INLIST ITERATOR로 처리되지 않고 INDEX RANGE SCAND으로 처리되었으므로 버그가 없다.

해결책은 처리순서를 변경하는 것

이런 경우의 해결책은 아주 간단하다. 우리의 상식대로 하면 된다. 즉 데이터를 모두 처리하고 Grouping을 가장 마지막에 실행하는 것이다. 아래의 SQL이 그것이다.

SELECT TIME_ID, COUNT(*)

FROM ( SELECT /*+ NO_MERGE */ TIME_ID

FROM SALES

WHERE (TIME_ID, CHANNEL_ID) IN ( (TO_DATE('19980212'), 4),

(TO_DATE('19980214'), 2),

(TO_DATE('19980214'), 3),

(TO_DATE('19980214'), 4) )

)

GROUP BY TIME_ID;

TIME_ID COUNT(TIME_ID)

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

98/02/12 50

98/02/14 391

2 rows selected.

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 9 |

| 1 | SORT GROUP BY NOSORT | | 1 | 2 | 9 |

| 2 | INLIST ITERATOR | | 1 | 441 | 9 |

| 3 | PARTITION RANGE ITERATOR| | 4 | 441 | 9 |

| 4 | VIEW | | 4 | 441 | 9 |

|* 5 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 4 | 441 | 9 |

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

예상대로 Group By는 가장 마지막에 처리되었으므로 결과도 정상적으로 두건이 출력되었다. SQL의 실행순서만 알고 있으면 이 정도의 버그는 패치가 없어도 해결이 가능하다. NO_MERGE 힌트를 사용한 이유는 View Merge(뷰 해체)를 방지하기 위함이다. Simple View 이므로 힌트를 빼면 무조건 인라인뷰가 해체되어 버그가 재생된다.

또 다른 해결책을 보자.

SELECT /*+ INDEX(SALES SALES_TIME_CHANNEL_IX) */

TIME_ID, COUNT(TIME_ID)

FROM SALES

WHERE (TIME_ID = TO_DATE('19980214') AND CHANNEL_ID BETWEEN 2 AND 4 )

OR (TIME_ID = TO_DATE('19980212') AND CHANNEL_ID =4 )

GROUP BY TIME_ID;

TIME_ID COUNT(TIME_ID)

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

98/02/12 50

98/02/14 391

2 rows selected.

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 5 |

| 1 | HASH GROUP BY | | 1 | 2 | 5 |

| 2 | CONCATENATION | | 1 | 441 | 5 |

| 3 | PARTITION RANGE SINGLE| | 1 | 391 | 3 |

|* 4 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 391 | 3 |

| 5 | PARTITION RANGE SINGLE| | 1 | 50 | 2 |

|* 6 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 50 | 2 |

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

SQL의 WHERE절을 수정하여 INLIST ITERATOR가 아닌 CONCATENATION으로 유도 하였다. 위의 실행계획 또한 Group By가 가장 마지막이 실행되므로 버그가 발생되지 않는다. SQL을 수정하기 싫고 힌트만으로 해결하려면 아래의 SQL을 보라.

SELECT /*+ USE_CONCAT(1) */

TIME_ID, COUNT(TIME_ID)

FROM SALES

WHERE (TIME_ID, CHANNEL_ID) IN ( (TO_DATE('19980212'), 4),

(TO_DATE('19980214'), 2),

(TO_DATE('19980214'), 3),

(TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

TIME_ID COUNT(TIME_ID)

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

98/02/12 50

98/02/14 391

2 rows selected.

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 9 |

| 1 | HASH GROUP BY | | 1 | 2 | 9 |

| 2 | CONCATENATION | | 1 | 441 | 9 |

| 3 | PARTITION RANGE SINGLE| | 1 | 50 | 2 |

|* 4 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 50 | 2 |

| 5 | PARTITION RANGE SINGLE| | 1 | 287 | 3 |

|* 6 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 287 | 3 |

| 7 | PARTITION RANGE SINGLE| | 1 | 54 | 2 |

|* 8 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 54 | 2 |

| 9 | PARTITION RANGE SINGLE| | 1 | 50 | 2 |

|* 10 | INDEX RANGE SCAN | SALES_TIME_CHANNEL_IX | 1 | 50 | 2 |

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

위의 SQL 또한 정상적으로 2건이 출력된다.

버그를 유발하는 원인을 제거하라
마지막으로 볼 해결책은 파티션과 INLIST ITERATOR의 불편한 만남(?)을 제거하는 것이다.

SELECT /*+ INDEX_COMBINE(a) */

TIME_ID, COUNT(TIME_ID)

FROM SALES a

WHERE (TIME_ID, CHANNEL_ID) IN ( (TO_DATE('19980212'), 4),

(TO_DATE('19980214'), 2),

(TO_DATE('19980214'), 3),

(TO_DATE('19980214'), 4) )

GROUP BY TIME_ID;

TIME_ID COUNT(TIME_ID)

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

98/02/12 50

98/02/14 391

2 rows selected.

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

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

| 0 | SELECT STATEMENT | | 1 | 2 | 61 |

| 1 | PARTITION RANGE INLIST | | 1 | 2 | 61 |

| 2 | HASH GROUP BY | | 1 | 2 | 61 |

| 3 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES | 1 | 441 | 61 |

| 4 | BITMAP CONVERSION TO ROWIDS | | 1 | 441 | 17 |

| 5 | BITMAP OR | | 1 | 1 | 17 |

| 6 | BITMAP AND | | 1 | 1 | 4 |

|* 7 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | 1 | 2 |

|* 8 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 1 | 1 | 2 |

| 9 | BITMAP AND | | 1 | 1 | 4 |

|* 10 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | 1 | 2 |

|* 11 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 1 | 1 | 2 |

| 12 | BITMAP AND | | 1 | 1 | 5 |

|* 13 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | 1 | 2 |

|* 14 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 1 | 2 | 3 |

| 15 | BITMAP AND | | 1 | 1 | 4 |

|* 16 | BITMAP INDEX SINGLE VALUE | SALES_TIME_BIX | 1 | 1 | 2 |

|* 17 | BITMAP INDEX SINGLE VALUE | SALES_CHANNEL_BIX | 1 | 1 | 2 |

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

Bitmap Operation으로 인하여 INLIST ITERATOR가 사라졌으므로 버그는 보이지 않는다.

결론

이 버그는 Oracle 10g 에서 발견되었지만 11gR2 버전까지 해결되지 않고 있다. 파티션과 Local Index를 사용하고 Pair IN 조건이 INLIST ITERATOR로 풀리면 발생된다. 해결방법은 SQL을 수정하여 Group By를 마지막에 수행하거나 INLIST ITERATOR를 제거하면 된다. 버그패치가 없다고 해서 잘못된 결과를 보여줄 수는 없다. 패치가 나올 때 까지는 다른 해결책을 강구해야 한다. 잘 생각해보면 위의 경우처럼 원리만 안다면 상식 선에서 해결할 수 있는 문제가 매우 많다.

버그, 오라클에 알려야 해
대부분의 경우 버그는 사용자가 오라클에 보고해야 패치를 만들게 된다. 그러므로 버그가 발견되면 반드시 SR을 진행하여 오라클에 알려야 한다. 버그를 발견한 사람은 SQL을 수정하여 버그를 피할 수 있지만 동료나 다른 사이트에 있는 모든 사람들은 해결책을 모를 수 있고 심지어 버그가 있는지 조차 모를 수 있다. 하지만 많은 수의 사람들은 버그를 피해가는 방법만 발견된다면 내 문제는 해결 되었으므로 오라클에 버그를 통보하지 않고 조용히 넘어갈 것이다. 혹시 위의 버그가 몇년간 남아 있는 이유도 조용한(?) 사람들 때문이 아닐까?

한가지 방법은 오라클사에서 버그리포팅을 하는 사람에게 작은 선물을 주는 것이다. 제품의 품질도 높이고 고객의 참여를 유도하는 Win Win 전략이 될 수 있다.

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

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

Partition Exchange의 함정 (13)	2010.05.27
튜닝 프로젝트 시 이슈 극복 사례 (15)	2010.05.20
RAC에서 특정 Instance만 성능이 느린 이유 (0)	2010.03.24
Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05

Posted by extremedb

,

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

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

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

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

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

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

테스트 환경 Oracle 11.1.0.6

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

create index idx01 on sales (CUST_ID);

create index idx02 on sales (CUST_ID, TIME_ID);

이제 SQL을 실행 해보자.

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

count(*)

FROM sales a

WHERE cust_id BETWEEN 33 AND 44 ;

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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

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

COUNT (*)

FROM sales a

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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

"CUST_ID"=44))

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

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

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

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

결합인덱스를 사용할 때

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

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

COUNT (*)

FROM tlo.sales a

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

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

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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

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

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

hh24:mi:ss'))

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

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

count(*)

FROM tlo.sales a

WHERE cust_id BETWEEN 33 AND 44

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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

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

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

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

hh24:mi:ss')))

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

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

결론

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

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

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

PS

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

select *

from tab

where col1 between 1 and 5

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

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

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

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

Posted by extremedb

,

Partition Exchange의 함정

Oracle/TroubleShooting 2010. 5. 27. 16:05

Partition Exchange(이하 PE)는 특정 파티션과 다른 테이블의 데이터를 바꿔 치기 하는 기능이다. 많은 사람들이 PE를 실행 할 때 성능향상을 위하여 Without Validation 옵션을 자주 사용한다. 이 옵션은 Constraint의 Validation Check를 하지 않기 때문에 성능이 향상된다. 그렇지만 조건에 맞지 않는 값이 파티션에 들어갈 수 있다는 단점도 있다. 그래서 많은 사람들이 PE를 실행 하기 전에 NOT NULL 이나 FK등의 제약조건을 검사한 후에 PE를 실행한다.

하지만.......

그러한 검사를 하여도 여전히 오류는 존재할 수 있다는 걸 아는 사람은 많지 않다. 이 내용을 모른다면 데이터를 찾을 수 없는 심각한 상황을 맞게 된다. 2009년 4월에 발생한 장애 상황을 보자.

박대리 : 현재 파티션이 2009년 3월 까지 만들어져 있고 이후로 파티션을 Split 하지 않아서 2009년 4월 데이터가 MAX 파티션에 들어가 있습니다. 파티션을 Split 하여 4월 파티션을 만들고 거기에 데이터를 옮기고 싶습니다. 어떻게 하면 되나요?

김과장 : 빈 테이블을 하나 만들고 MAX 파티션과 PE를 실행하면 됩니다. 그 후에 파티션을 Split 하여 4월 파티션을 만든 후에 다시 PE를 실행한다면 해결됩니다. 단 데이터의 건수가 많으므로 Without Validation 옵션을 주면 성능이 향상됩니다.

박대리 : 알겠습니다.

대화를 끝낸 A씨는 김과장이 알려준 대로 하였다. 작업은 밤 늦게 시작하여 정상적으로 끝났지만 문제는 다음날 아침에 발생하였다.

사용자 : 데이터 중에 한 건이 조회되지 않습니다. 어제는 조회가 되었는데 갑자기 오늘부터 안되네요.

박대리: 그럴리가요?

이상하지 않은가?
데이터를 두 번 바꿔 치기 했을 뿐 데이터를 조작(Update)하거나 삭제한적이 없다. 그런데 조회가 되질 않는다니.....

이제 위의 장애 상황을 직접 만들어보자.

--테스트 환경 Oracle 11.1.0.6

--파티션 테이블과 인덱스 그리고 Constraint 생성

CREATE TABLE TB_EX_TEST

( VAL1 NUMBER,

TR_DT CHAR(8) NOT NULL )

PARTITION BY RANGE (TR_DT)

(

PARTITION PT_R200902 VALUES LESS THAN ('20090301') , --2월 파티션

PARTITION PT_R200903 VALUES LESS THAN ('20090401') , --3월 파티션

PARTITION PT_RMAX VALUES LESS THAN (MAXVALUE) --max 파티션

);

CREATE UNIQUE INDEX PK_EX_TEST ON TB_EX_TEST (VAL1, TR_DT ) LOCAL;

ALTER TABLE TB_EX_TEST

ADD CONSTRAINT PK_EX_TEST PRIMARY KEY (VAL1, TR_DT);

-- 데이터 입력

INSERT INTO TB_EX_TEST VALUES(1 ,'20090201'); --2월 데이터

INSERT INTO TB_EX_TEST VALUES(2 ,'20090202'); --2월 데이터

INSERT INTO TB_EX_TEST VALUES(3 ,'20090203'); --2월 데이터

...중간생략

INSERT INTO TB_EX_TEST VALUES(11,'20090301'); --3월 데이터

INSERT INTO TB_EX_TEST VALUES(12,'20090302'); --3월 데이터

INSERT INTO TB_EX_TEST VALUES(13,'20090303'); --3월 데이터

...중간생략

INSERT INTO TB_EX_TEST VALUES(21,'20090401'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(22,'20090402'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(23,'20090403'); --4월 데이터

INSERT INTO TB_EX_TEST VALUES(24,'30090404'); --잘못된 데이터 입력.

COMMIT;

--PE를 실행하기 위한 Temp 테이블 생성

CREATE TABLE TMP_TB_EX_TEST

( VAL1 NUMBER,

TR_DT CHAR(8) NOT NULL ) ;

CREATE UNIQUE INDEX PK_TMP_TB_EX_TEST ON TMP_TB_EX_TEST (VAL1, TR_DT);

ALTER TABLE TMP_TB_EX_TEST

ADD CONSTRAINT PK_TMP_TB_EX_TEST PRIMARY KEY (VAL1, TR_DT);

이제 모든 준비가 끝났으므로 PE를 실행해보자.

ALTER TABLE TB_EX_TEST EXCHANGE PARTITION

PT_RMAX WITH TABLE TMP_TB_EX_TEST WITHOUT VALIDATION;

SELECT * FROM TMP_TB_EX_TEST;

VAL1 TR_DT
---------- --------
21 20090401
22 20090402
23 20090403
24 30090404 -->원래는 '20090404' 이나 업무팀에서 잘못된 데이터를 넣은 건임.

4 rows selected

PE를 성공적으로 수행하여 잘못된 데이터를 포함한 모든 데이터가 TEMP 테이블로 들어갔다 . 이제 MAX 파티션을 Split 하여 4월 파티션을 만들고 그 파티션과 TEMP 테이블을 이용하여 PE를 실행하면 모든 작업이 완료된다.

--MAX 파티션을 Split 하여 4월달 파티션 생성

ALTER TABLE TB_EX_TEST SPLIT PARTITION

PT_RMAX AT ('20090501') INTO (PARTITION PT_R200904, PARTITION PT_RMAX);

Table altered.

--TEMP 테이블의 데이터를 이용하여 4월 파티션에 PE 수행

ALTER TABLE TB_EX_TEST EXCHANGE PARTITION

PT_R200904 WITH TABLE TMP_TB_EX_TEST WITHOUT VALIDATION;

Table altered.

-- 2009년 파티션의 인덱스를 REBUILD 해준다.

ALTER INDEX PK_EX_TEST REBUILD PARTITION PT_R200904;

Index altered.

이제 모든 작업이 끝났으므로 2009년 4월 데이터를 조회해보자.

SELECT *

FROM TB_EX_TEST

WHERE TR_DT LIKE ‘200904%’;

VAL1 TR_DT

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

21 20090401

22 20090402

23 20090403

3 rows selected.

3건이 조회되었고 여기까지는 정상적이다. 2009년 4월을 지정하였으므로 잘못된 데이터가 조회되지 않음은 당연한 것이다. 하지만 충격적이게도 잘못된 데이터를 where 조건에 직접 입력해도 전혀 조회되지 않는다. 아래 SQL의 결과를 보라.

SELECT *

FROM TB_EX_TEST

WHERE TR_DT='30090404';

no rows selected.

단 한 건도 나오지 않는다

이상하지 않은가? 데이터는 존재하는데 조회되지는 않는다니.... 버그인가?

전혀 아니다. Partition Pruning이라는 기능 때문이다. 2009년 4월 보다 큰 값을 where 조건에 주게 되면 MAX 파티션을 가리키게 되므로 조회가 되지 않는 것이다. 다시 말하면 잘못된 데이터는 2009년 4월 파티션에 존재하지만 WHERE 조건은 MAX 파티션을 지정하였으므로 데이터가 조회되지 않는 것이다.

그럼 어떻게 조회해야 하나?

조회가 되어야 잘못된 값을 수정할 것이 아닌가? 이때는 아래처럼 FROM 절에서 특정 파티션을 지정하면 된다.

SELECT ROWID, VAL1, TR_DT
FROM TB_EX_TEST PARTITION (PT_R200904);

ROWID VAL1 TR_DT

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

AAASWxAAEAAAYf+AAA 21 20090401

AAASWxAAEAAAYf+AAB 22 20090402

AAASWxAAEAAAYf+AAC 23 20090403

AAASWxAAEAAAYf+AAD 24 20090404

4 rows selected.

박대리는 MAX 파티션에 4월 데이터만 있을 것으로 예상하였고 3009년 4월 데이터가 있을 줄은 꿈에도 생각하지 못한 것이다. 하지만 현실의 데이터는 언제나 지저분하게 마련이다.

그렇다면 잘못된 데이터를 어떻게 수정해야 하나?

UPDATE 시에 3009년 4월 4일을 WHERE절에 지정하면 데이터가 없으므로 수정에 실패한다. 따라서 ROWID를 찾아서 UPDATE 해야 한다.

UPDATE TB_EX_TEST
SET TR_DT = '20090404'
WHERE ROWID = CHARTOROWID('AAASWxAAEAAAYf+AAD');

1 row updated.

COMMIT;

Commit complete.

결론

Partition Exchange 시에 Without Validation을 지정하게 되면 Constraint 뿐만 아니라 파티션의 경계도 무시된다. 따라서 Exchange 할 소스테이블의 파티션 key 값에 대한 검증을 하여 잘못된 값을 바로잡아야 한다. 그렇지 않으면 데이터가 사라진 것처럼 보일 것이다.

지금 주위에 있는 사람에게 질문해 보라. WHERE 절에 특정월을 지정하는 것과 FROM 절에 특정월의 파티션명을 지정하는 것의 결과가 다를 수 있는지 없는지를. 아마 많은 사람들이 결과는 다를 수 없다라고 이야기 할 것인데 정답은 다를 수 있다는 것이다. 여러분이 위에서 본 것처럼....

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

파티션과 IN 조건의 잘못된 만남 (0)	2010.08.20
튜닝 프로젝트 시 이슈 극복 사례 (15)	2010.05.20
RAC에서 특정 Instance만 성능이 느린 이유 (0)	2010.03.24
Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05

Posted by extremedb

,

튜닝 프로젝트 시 이슈 극복 사례

Oracle/TroubleShooting 2010. 5. 20. 15:18

처음에 이 글을 블로그에 올릴지 말지 고민을 많이 하였다. 왜냐하면 튜닝이나 모델링의 기술적인 문제가 아니고 튜닝 프로젝트 시 이슈의 해결 과정이기 때문이다. 하지만 의외로 이런 사례를 알고 싶어하는 사람이 많이 있을 것으로 생각한다. 오래된 자료이긴 하지만.....

이 글을 보기 전 고려사항

이 글은 튜닝 프로젝트 시 발생한 문제이다. 개인이 급한 불을 끄기 위해 소방수로 들어가서 튜닝을 하는 것과 튜닝프로젝트를 하는 것은 전혀 다르다. 이점을 알고 글을 보기 바란다. 또한 튜닝 프로젝트마다 이슈는 다를 수도 있고 아래의 이슈가 전혀 문제가 되지 않고 쉽게 해결되는 경우도 있다. 아래의 이슈들은 철저히 필자의 관점임을 밝혀둔다. 이슈는 다음과 같다.

1. 뱅킹 시스템 RDBMS 경험자 전무(AS-IS 는 Network DB)

2. DBMS 설치후 성능관련 파라미터 튜닝이슈

3. 업무변경 시 SQL튜닝 반영의 문제

4. CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라

이제 각각의 이슈 사항을 자세히 알아보기 위해 타임머신을 타고 그때 그 시각으로 돌아가 보자.

그림을 클릭하면 크게 볼수 있다.

1. 뱅킹 시스템 RDBMS 경험자 전무(AS-IS 는 Network DB) 상황:

AS-IS 의 DB 가 Network DB 이므로 RDBMS 경험이 없음.

RDBMS 를 사용하는 다른시스템 및 Legacy 시스템의 DBA 및 개발자들은 기존 시스템의 유지보수 관계로 차세대 뱅킹 프로젝트에 참여하지 못함.

외부업체의 개발자의 경우 상대적으로 RDBMS 에서 개발경험이 많으나 시스템 OPEN 직후부터의 유지보수는 은행직원들이 수행함으로 개발 시 직원의 적극적인 참여가 필수적으로 요구되는 상황임.

과연 시스템을 제대로 개발할 수 있을 것인가에 관한 의구심이 증폭됨.

2. DBMS 설치 후 성능관련 파라미터 튜닝이슈 상황:

개발 DB 가 설치된 지 한달 이 지났지만 성능관련 파라미터의 세팅을 완료하지 못함.

지연된 가장 큰 이유는 일반적인 가이드는 존재하지만 그 사이트에 맞는 정확한 파라미터값은 어디에도 존재하지 않음.

DBMS 밴더사 조차 초기값을 제시하지 못함. 개발이 진행되고 있는 상황에서 계속 지연될 경우 개발후기에는 실행계획이 대폭 바뀔 수 있으므로 빠른 시일 내에 최적의 성능관련 파라미터의 세팅이 필요함.

잘못되었을 경우 전체시스템에 악영향을 끼치므로 책임이 막중하여 DBMS 밴더사, DBA, 인프라팀 누구도 나서지 못함.

3. 업무변경 시 SQL튜닝 반영시 문제의 상황:

개발자가 작성한 SQL을 DA 팀에서 품질점검 및 튜닝을 수행할 경우 SQL을 수정해야만 하는 경우가 발생한다. 그런 다음 튜닝 된 SQL 은 개발자에게 전달되어 SQL 이 수정되게 된다. 이때 개발자가 수정을 잘못하여 SQL 의 결과가 틀릴 수 있다.

또 하나의 경우는 DA 팀에서 원본 SQL 을 튜닝하고 개발자에게 전달하는데 이틀이 걸렸다. 하지만 그 이틀 사이에 업무가 수정되어 원본 SQL 이 수정되는 경우가 있다. 하지만 DA 팀에서는 업무가 바뀐 것을 알지 못하므로 무조건 개발자에게 수정을 요청하는데 이때 해당 개발자가 업무가 바뀐 것을 확인하지 않고 DA 팀에서 수정한 SQL 로 바꿔버리는 경우가 있다. 이 경우에도 SQL의 결과가 틀리게 된다.

이런 일이 자주 발생될 경우 SQL 의 결과가 틀린 건이 많아지며 개발자는 DA팀에게 SQL 튜닝신청을 꺼리게 된다. 실제로 4000 여개의 SQL 중에서 약 100 개정도가 답이 틀리며 업무 팀에서는 앞으로 상황이 더욱 악화될 것으로 예상함.

튜닝을 하면 값이 잘못 나온다는 소문이 돌기 시작하면서 DA 팀에서 SQL 을 튜닝 해도 개발자가 반영을 하지 않는 심각한 상황이 발생됨.

SQL 의 결과 값이 틀릴 경우 개발자는 SQL의 수정을 DA 팀에서 하였으므로 개발자 자신은 잘못이 없음을 강조함.

4. CPU 사용율 60% 미만에서 초당 4700 TPS 를 확보하라

상황:

성능목표 달성기준 : CPU소모량이 60% 미만에서 초당 4700 TPS 를 확보한다.

A은행의 초당 최대 TPS 가 2100 정도임을 감안할 때 이것은 매우 높은 목표임을 인식함.

이를 확보하려면 평균처리시간은 건당 0.12 초를 확보하여야 한다. 참고로 AS-IS 에서는 초당 최대 TPS 는 1800 이었음.

-OPEN 6개월 전 단위성능 테스트 :

1) 건당 0.12 초가 목표였으나 평균 2.5초가 나왔음.

2) DA 팀이 4월부터 품질점검 및 SQL 튜닝을 하였으므로 이것은 매우 실망스러운 결과임

3) 단말로부터 수신한 전문의 해석과 로깅을 하고 본 서비스를 Forward 시키는 서비스는 모든 거래 시작 시 사용하는 아주 중요한 서비스이므로 집중 튜닝을 실시함(이 서비스에서 사용하는 테이블에 파티션 정책반영, 불필요한 인덱스 제거, 업무팀의 주요거래테이블도 파티션 정책 반영)

-OPEN 4개월 전 1차 통합 성능 테스트:

1)성능이 초당 2000 TPS 정도밖에 나오지 않고 CPU 사용율도 매우높음.

2)모든거래에서 사용하는 고객,상품,공통쪽 SQL 에 대하여 집중 튜닝 실시

-OPEN 3개월 전 2차 통합 성능 테스트:

1)목표 달성율이 64% 에 그침으로써 OPEN이 3달밖에 남지 않은 상황이었으므로 매우 급박한 상황이었음.

2)성능튜닝은 DB 튜닝만으로는 해결하기 어려워 OS 의 패치적용, OS 의 커널 파라미터 튜닝, 개발 프레임워크의 성정파일 수정등을 통하여 1차 실거래 테스트에 대비함.

3)지속적인 튜닝에도 불구하고 전체적인 Apllication 의 성능이 나오지 않는 원인에 대한 규명이 필요해짐.

여기까지가 4가지 문제의 이슈들을 요약한것이다.
글이 길어지므로 문의의 해결과정은 아래의 파일을 참조하기 바란다.
단순히 슬라이드만 보지말고 Note의 내용을 같이 보야야 이해가 빠를것이다.

invalid-file

튜닝시_이슈극복_사례

PS
이것들 외에 여러분이 격었던 이슈들 있었다면 필자에게도 공유해 주기 바란다.
공유할 수록 세상은 살기 편해지지 않을까?

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

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

파티션과 IN 조건의 잘못된 만남 (0)	2010.08.20
Partition Exchange의 함정 (13)	2010.05.27
RAC에서 특정 Instance만 성능이 느린 이유 (0)	2010.03.24
Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05

Posted by extremedb

,

PL/SQL-면접문제

Oracle/PL/SQL Pattern 2010. 5. 7. 15:45

작년에 회사에서 기술면접에 필요한 문제를 여러 개 만들었는데 그 중에 하나를 소개한다. PL/SQL의 예외처리에 관련된 것이고 개념문제이므로 응시자들이 어렵지 않게 풀 수 있을 거라 생각하였다. 하지만 결과는 예상 밖이었다.

오라클 내부구조, OWI, AWR, Query Transformation 등 어려운 개념은 맞추는 사람이 있는 반면 PL/SQL의 기본에 속하는 예외처리에 대해서는 아무도 정답을 맞추지 못했다. 정답에 근접한 사람도 아무도 없었다. 도대체 얼마나 어려운 문제이길래? 여러분도 아래의 문제를 풀어보기 바란다.

문제3)

1. Built In Exceptions

2. User-Defined Exceptions

3. RAISE_APPLICATION_ERROR

4. EXCEPTION_INIT Pragma

위의 네 가지는 오라클 PL/SQL 상에서 예외처리방법을 나열한 것이다.
이 네 가지의 차이점을 설명하시오.

이 네 가지의 차이점을 정확히 알고 있는 사람은 아래의 파일을 다운받을 필요가 없다. 하지만 차이점을 정확히 설명할 수 없다면 이 기회에 정리하기 바란다. 총 4장(8 페이지) 이므로 10분만에 볼 수 있을 것이다. 정답은 마지막 페이지에 있다.

모든 분야에는 기본이라고 불리는 것이 있다. 오라클의 세계에서도 예외는 아닌것 같다. 필자 또한 기본을 놓치지 않으려고 노력하는 사람중의 하나이다.

invalid-file

Oracle PL/SQL - Exceptions 정리

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

오라클에서 is_number, is_date 함수 사용하기 (457)	2009.11.11
묵시적인 형변환을 피하라 (414)	2008.05.09
PL/SQL 에서 NUMBER 타입의 성능 테스트 (0)	2008.05.02

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

,

오라클의 Update문은 적절한가?

Oracle/SQL Tuning 2010. 4. 14. 15:08

bypass_ujvc 힌트와 관련하여 필자와 의견을 달리하는 전문가도 있음을 밝혀둔다. 특정 버젼에서 특정 상황에서 힌트를 확실히 이해하고 성능문제가 큰 경우일 때만 사용한다면 된다는 것이다. 제약사항을 4가지나 붙였으므로 공감이 가는 부분이 있다. 아래의 댓글을 반드시 읽어보기 바란다. 2010-04-19 (추가)

ANSI SQL의 UPDATE문은 오라클과 달리 FROM 절이 존재하며 여러 테이블 혹은 뷰와 자유로이 조인할 수 있다. 아래의 SQL을 보자.

UPDATE DEPT

SET DEPT_COUNT = E.CNT

FROM DEPT D,

(SELECT DEPTNO, COUNT(*) CNT

FROM EMP

WHERE JOB = ‘CLERK’

GROUP BY DEPTNO) E

WHERE D.DEPTNO = E.DEPTNO ;

위의 SQL은 MS-SQL 서버에서 사용할 수 있는 UPDATE문이지만 오라클에서 사용할 수 없다. 위의 SQL을 오라클로 바꾼다면 조인이 불가능하므로 아래처럼 스칼라 서브쿼리와 서브쿼리를 사용해야 한다.

UPDATE DEPT D

SET DEPT_COUNT = (SELECT COUNT(*)

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’)

WHERE EXISTS (SELECT 1

FROM EMP E

WHERE E.DEPTNO = D.DEPTNO

AND E.JOB = ‘CLERK’) ;

중복 조인을 피할 수 있나?

언뜻 보기에도 비효율이 극심하게 드러난다. EMP와 조인이 두 번 발생한 것이다. 중복된 조인을 피하기 위해서 아래처럼 인라인뷰와 스칼라 서브쿼리를 혼합하여 사용할 수 있지만 이 또한 중복 조인을 피할 수 없다. 보기에는 중복조인이 없는 것처럼 보이지만 Query Transformation을 공부하였다면 중복 조인이 보일 것이다.

UPDATE (SELECT d.deptno, d.dept_count,
   (SELECT COUNT (*)
FROM emp e
   WHERE e.deptno = d.deptno
   AND e.job = 'CLERK') cnt
FROM dept d)
   SET dept_count = cnt
WHERE cnt > 0;

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

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

| 0 | UPDATE STATEMENT | | 4 | 64 | 5 (0)|

|* 2 | FILTER | | | | |

| 3 | TABLE ACCESS FULL | DEPT | 4 | 64 | 3 (0)|

| 4 | SORT AGGREGATE | | 1 | 11 | |

|* 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 11 | 2 (0)|

|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)|

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

Predicate Information (identified by operation id):

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

2 - filter( (SELECT COUNT(*) FROM "SCOTT"."EMP" "E" WHERE "E"."DEPTNO"=:B1

AND "E"."JOB"='CLERK')>0)

5 - filter("E"."JOB"='CLERK')

6 - access("E"."DEPTNO"=:B1)

Predicate Information을 보면 ID 기준으로 2번에서 서브쿼리가 FILTER로 사용되었고 6번에서 다시 스칼라 서브쿼리가 사용되었으므로 두 번 조인이 발생한 것이다. 이해가 되지 않는 독자는 스칼라 서브쿼리를 서브쿼리로 변환하라 글을 읽기 바란다.

또 다른 제약

조인이 안 된다는 제약을 피하기 위해 VIEW 혹은 인라인뷰를 UPDATE 하곤 한다. 하지만 아래의 새로운 예제를 본다면 또 다른 제약이 있음을 알 수 있다.

create or replace view v_emp

as

select e.empno, e.ename, e.job, e.sal, d.dname, d.deptno

from emp e, dept d

where e.deptno = d.deptno;

update V_EMP

set dname = ‘NO_DEPT’ --> DEPT 쪽을 UPDATE 하고 있으므로 에러

where empno = ‘7369’;

ERROR at line 2:

ORA-01779: cannot modify a column which maps to a non key-preserved table

Dept와 emp는 1:N의 관계인데 1쪽을 Update 하지 못하는 제약을 만나게 된다. 이 제약을 피하기 위해서 bypass_ujvc 힌트를 사용하는 사람이 있는데 절대 사용하면 안 되는 힌트이다. 힌트를 해석하자면 Updatable Join View의 Check를 By-Pass(무시) 하겠다는 뜻이다. 실제로 Wrong Result(답이 잘못됨)가 나오므로 사용해서는 안 된다.

끝없는 제약

ODS 시스템이나 데이터를 이행하기 위한 Temp성 테이블에는 Key가 없는 경우가 많다. 아래는 PK를 제거한 상태에서 인라인뷰를 UPDATE 해보았다. 단순히 사번이 들어오면 부서번호가 부서 테이블에 존재하는지 체크하여 급여를 UPDATE 하는 SQL이다.

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE;

Update (select a.empno, a.ename, a.sal, b.dname

from emp a, dept b

where a.deptno = b.deptno

and a.empno = 7369)
set sal = 5000;

ORA-01779: cannot modify a column which maps to a non key-preserved table

Key를 사용할 수 없으므로 뷰 혹은 인라인뷰를 update할 때 키 보존 제약이 걸리게 된다. 이때 마찬가지로 bypass_ujvc를 사용하면 에러는 피할 수 있지만 결과를 보장 하지 않는다. 이 힌트는 “건널목 신호등에 빨강 불이 들어왔지만 알아서 건너가시오. 자동차에 부딪혀도 책임지지 않습니다.” 로 비유할 수 있다.

Bypass_ujvc 힌트를 사용하지 않고 해결해야 해

위에서 언급한 세가지 제약조건(조인이 안됨, 뷰에서 1쪽 update 안됨, 뷰에서 키가 없으면 update 불가)과 한가지 문제(중복 조인)를 피할 수 있는 방법이 있다.

1) 조인이 안 되는 문제와 중복 조인문제 해결

MERGE INTO dept d

USING (SELECT deptno, COUNT (*) cnt

FROM emp

WHERE job = 'CLERK'

GROUP BY deptno) e

ON (e.deptno = d.deptno)

WHEN MATCHED THEN

UPDATE SET d.dept_count = e.cnt;

Merge successfully completed.

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

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

| 1 | MERGE | DEPT | 1 |00:00:00.01 | 18 |

| 2 | VIEW | | 3 |00:00:00.01 | 13 |

| 3 | NESTED LOOPS | | 3 |00:00:00.01 | 13 |

| 4 | NESTED LOOPS | | 3 |00:00:00.01 | 9 |

| 5 | VIEW | | 3 |00:00:00.01 | 7 |

| 6 | SORT GROUP BY | | 3 |00:00:00.01 | 7 |

|* 7 | TABLE ACCESS FULL | EMP | 4 |00:00:00.01 | 7 |

|* 8 | INDEX UNIQUE SCAN | PK_DEPT | 3 |00:00:00.01 | 2 |

| 9 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 |00:00:00.01 | 4 |

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

Predicate Information (identified by operation id):

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

7 - filter("JOB"='CLERK')

8 - access("E"."DEPTNO"="D"."DEPTNO")

2) 1쪽이 UPDATE 안 되는 문제 해결

MERGE INTO dept d

USING emp e

ON (e.deptno = d.deptno AND e.empno = '7369')

WHEN MATCHED THEN
UPDATE SET d.dname = 'NO_DEPT' ;

Merge successfully completed.

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

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

| 1 | MERGE | DEPT | 1 |00:00:00.01 | 5 |

| 2 | VIEW | | 1 |00:00:00.01 | 4 |

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

| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |00:00:00.01 | 2 |

|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 |00:00:00.01 | 1 |

| 6 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |00:00:00.01 | 2 |

|* 7 | INDEX UNIQUE SCAN | PK_DEPT | 1 |00:00:00.01 | 1 |

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

Predicate Information (identified by operation id):

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

5 - access("E"."EMPNO"=7369)

7 - access("E"."DEPTNO"="D"."DEPTNO")

3) 키가 없으면 UPDATE가 불가한 문제 해결

ALTER TABLE SCOTT.DEPT MODIFY CONSTRAINT PK_DEPT DISABLE;

MERGE /*+ USE_HASH(D) */ INTO emp e

USING dept d

ON (e.deptno = d.deptno AND e.empno = 7369)

WHEN MATCHED THEN
UPDATE SET e.sal = 5000;

Merge successfully completed.

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

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

| 1 | MERGE | EMP | 1 |00:00:00.01 | 12 | |

| 2 | VIEW | | 1 |00:00:00.01 | 9 | |

|* 3 | HASH JOIN | | 1 |00:00:00.01 | 9 | 316K (0)|

| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |00:00:00.01 | 2 | |

|* 5 | INDEX UNIQUE SCAN | PK_EMP | 1 |00:00:00.01 | 1 | |

| 6 | TABLE ACCESS FULL | DEPT | 4 |00:00:00.01 | 7 | |

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

Predicate Information (identified by operation id):

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

3 - access("E"."DEPTNO"="D"."DEPTNO")

5 - access("E"."EMPNO"=7369)

결론

위의 SQL 세가지는 큰 문제 4가지를 해결한 것이므로 익혀서 적재적소에 활용하기 바란다.
MERGE문의 문법은 매우 간단하다. 하지만 이 문법을 보고 그것을 어디에 어떻게 활용할 것인가는 전혀 다른 문제이다. DBMS의 버전이 올라가면 신기능이 탄생한다. 그때마다 이 기능을 어디에 사용하면 가장 큰 효과가 나타날 것인지를 생각해 보라. 오늘보다 더 발전된 내일의 당신을 위해서.

'Oracle > SQL Tuning' 카테고리의 다른 글

SQL튜닝 방법론 (20)	2011.01.27
Pagination과 분석함수의 위험한 조합 (26)	2010.12.23
Connect By VS ANSI SQL (7)	2010.02.11
USE_CONCAT 힌트 제대로 알기 (5)	2009.07.17
분석함수를 이용한 TOP SQL은 튜닝이 불가한가? (5)	2009.05.22

Posted by extremedb

,

Oracle Analytic Function의 모든 것

Oracle/SQL Pattern 2010. 3. 29. 01:23

필자는 가끔 분석함수의 기능에 관해 질문을 받는다. 그때마다 대답을 하지 않고 대신에 질문에 부합하는 Analytic SQL을 보여주고 결과를 분석하게 한다. 바로 답을 주게 되면 개발자가 의존적이 되고 분석함수 각각의 기능도 금방 잊어버리기 때문이다.

개발자만 모르는 것이 아니다

얼마 전에 어느 DBA로 부터 요청이 왔다. 자신을 포함한 개발자들이 분석함수를 어려워하니 블로그에 분석함수의 모든 것을 정리한 문서를 올려달라는 것이었다. 물론 오라클 매뉴얼이나 Tomas Kyte의 Expert One on One 등의 책에 이 함수들의 기능이 있지만 페이지 수가 너무 많고 영문이라는 단점이 있다는 것이었다. 이것은 놀라운 일이다. 개발자뿐 아니라 DBA, 심지어 컨설턴트까지 Analytic Function에 관해 필자에게 질문을 던지곤 한다. Oracle8i 부터 기능이 구현되었으니 기간으로 따지자면 10년 이상 존재했던 함수인데 아직까지......

도대체 Analytic Function이 얼마나 어려우면 전문가 까지도 개념이 서지 않는 걸까? 고민 끝에 핵심만 정리한 문서를 올리기로 했다.

핵심은 3가지다

분석함수의 기능이 복잡한 것 같지만 사실은 3가지만 알면 90%를 이해한 것이다.

1) Over 절에서 사용하는 Order by의 기능

2) Over 절에서 사용하는 Partition by의 기능

3) Over 절에서 사용하는 Windowing 기능

이것이 90% 이다. 대부분의 개발자와 DBA들은 1)번과 2)번에 대해서 많이 알고 있지만 이상하게도 3)번에 대해서 개념이 서질 않는다고 하였다. 따라서 아래의 문서를 다운받아서 공부할 때 3)번을 집중적으로 보기 바란다.

그럼 나머지 10%는?

나머지는 아래와 같다. 위의 3가지를 안다면 아래의 함수들은 쉽게 이해할 수 있다. 그저 종류가 많을 뿐이다.

• RANK, DENSE_RANK, and ROW_NUMBER --> 3가지 함수의 차이점

• FIRST/LAST

• NTILE, WIDTH_BUCKET, CUME_DIST and PERCENT_RANK

• Hypothetical Functions

• FIRST_VALUE/LAST_VALUE , LAG/LEAD

• Reporting Functions/RATIO_TO_REPORT

• Handling null

빨강색 부분은 개발자들이 많이 질문하는 것들이다. 참고하기 바란다.

Paper라고 다 같은 것은 아니다

매뉴얼이나 관련서적의 문제점은 페이지 수가 많다는 것이다. 예를 들어 분석함수 부분이 60페이지가 넘어간다면 기능을 익히는데 며칠 혹은 몇 주가 걸릴 수 있다. 필자는 페이지 수가 많은 것을 아주 싫어한다. 아래의 문서는 앞쪽의 목차와 중요성, 그리고 뒤쪽의 마무리 부분을 제외하면 9장(18 페이지)으로 모든 기능과 개념을 설명하였다. 아마 한 두 시간 이내에 다 볼 수 있을 것이다.

invalid-file

테이블 생성 파일

invalid-file

Mastering Oracle Analytic Function ppt 파일

PS

분석함수를 문법이라고 치부해 버리는 사람들이 있다. 그렇지 않다. 많은 경우에 분석함수를 쓰는 것이 SQL 튜닝이 된다. 오죽하면 옵티마이져가 평범한 SQL을 분석함수를 사용하는 것으로 바꾸겠는가?
이제부터 필자에게 분석함수를 질문하는 개발자가 없기를 바란다. ^^

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

검색조건을 동적으로 바꾸어야 할 때 (1)	2010.12.14
오라클 Regular Expressions 완전정복 (22)	2009.10.07
간단한 집합개념 Test (7)	2009.07.06
스칼라 서브쿼리에서 Multi Column, Multi Row Return 하기. (7)	2009.06.29
Model 절에 대하여 (10)	2009.05.28

Posted by extremedb

,

RAC에서 특정 Instance만 성능이 느린 이유

Oracle/TroubleShooting 2010. 3. 24. 23:23

지난번에 Range 파티션에서 maxvalue의 진정한 의미 라는 글에서 Multi-Column으로 Range 파티션을 구성할 때 주의사항에 대하여 알아 보았다. 이 글을 쉽게 이해하려면 위의 글을 먼저 보기 바란다. 테스트용 스크립트도 위의 글에서 사용한 것을 그대로 사용한다.

RAC로 4 Node로 구성되어있는 환경에서 동일한 SQL이 모든 Instance에서 골고루 수행될 때 1번 Instance 만 유독 느리다면 무엇을 의심해야 할까? 네트워크 등의 문제일 수 있지만 가장 먼저 조사해야 할 것은 gc_current_grant_busy 이벤트가 발생하느냐 이다.

테스트 환경을 만들어 보자.

CREATE TABLE t (

id NUMBER,

d1 DATE,

day_num VARCHAR2(2),

inst_id NUMBER(1),

pad VARCHAR2(4000),

CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

PARTITION pt_2_3 VALUES LESS THAN ('2', 5),

...중간생략

PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

Table created.

---> 여기서 이전 글에서 사용했던 Insert 문과 dbms_stats.gather_table_stats 수행

상황 : 아래의 SQL 2개가 모든 Instance에서 동시에 여러 번 수행된다.

SELECT COUNT(*)

FROM T

WHERE DAY_NUM = '3'; --> 3번 파티션

UPDATE T

SET pad = LPAD('A', 4000, 'B')

WHERE DAY_NUM = '4' --> 4번 파티션

AND INST_ID = :V_INST_ID; --> 현재 수행되고 있는 Instance 번호 대입

이 상황에서 1번 Instance의 Update문만 유독 느리게 수행된다. 아래는 개발자와 필자의 대화내용이다.

개발자 : 왜 Update문의 Bind 변수에 1번만 넣으면 느린가요?

필자 : 1번 Instance에서 Update 하려면 다른 Instance에서 Exclusive Mode의 Lock 권한을 받아야 하기 때문으로 추측됩니다.

개발자 : 권한이라뇨?

필자 : SELECT 시에 DAY_NUM 4번에 해당하는 파티션을 5번 이상 Access 했기 때문에 권한이 다른 INSTANCE로 넘어간 것 같습니다. 이 현상을 FDC(Fairness Down Convert) 라고 합니다. FDC가 발생한 후에 DAY_NUM 4번에 해당하는 첫번째 파티션(pt_4_1)의 해당 블록에 UPDATE문을 수행하려면 권한을 받는 작업(gc_current_grant_busy 이벤트)이 필요합니다.

개발자 : 그럴 리가요? Update 문은 DAY_NUM = '4' 조건이고 Select 문은 DAY_NUM = '3' 조건이므로 서로 다른 파티션 입니다. 따라서 SELECT 문과 UPDATE문이 동일 파티션을 Access 할 이유가 없습니다.

필자 : 그 SELECT 문이 실제로는 DAY_NUM = '4' 의 첫번째 파티션을 항상 Access 합니다. MAXVALUE를 지정하지 않았으므로 그런 것 입니다.

개발자 : 그렇군요. 어쩐지 trace에 gc_current_grant_busy가 많이 보였습니다.

아래는 개발자가 제시한 Trace 내용 중 Wait Event 부분을 발췌한 것이다.

core1_ora_13638.trc

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

WAIT #11: nam='gc current grant busy' ela= 947 p1=28 p2=1672046 p3=33619969 obj#=12043270 tim=12372374088207

WAIT #11: nam='gc current grant busy' ela= 992 p1=29 p2=2310876 p3=33619969 obj#=12070599 tim=12372374089432

...중간생략

WAIT #11: nam='gc current grant busy' ela= 767 p1=28 p2=1673090 p3=33619969 obj#=12043272 tim=12372374096882

Fairness Down Convert란 무엇인가?
Exclusive mode의 lock이 Shared lock 모드로 Down Convert 된다는 뜻이다. 다른 Instance의 요청에 의해서 Exclusive mode의 lock 상태에서 블럭을 다른 INSTANCE로 전송하는 작업은 무거운 연산이므로 특정 횟수 이상 블럭을 요청할 경우 Shared lock 모드로 전환하겠다는 뜻이다. FDC 발생 이후로는 블럭을 요청한 INSTANCE로는 블럭 전송이 불필요 하다. 따라서 성능이 향상된다. 하지만 반대로 원래의 Instance에서 그 블럭을 Update 하려면 권한을 받아야만 하므로 성능이 느려지는 것이다.

FDC를 Control 할 수 있는 파라미터는 _FAIRNESS_THRESHOLD 이다. 이 파라미터는 Default로 4 이다. 즉 특정 블록을 다른 Instance에서 5번 이상 Access 하는 경우 FDC가 발생하여 요청한 Instance로 권한이 넘어간다.

결론:

FDC 기능은 성능을 향상 시키기 위한 용도로 만들어 졌다. 하지만 위의 경우와 같이 오히려 느려지는 경우도 있다. Trade Off 특징이 잘 나타난다. 파티션의 특징을 잘 모르고 사용하였기 때문인데 해당 Select 문 뿐만 아니라 DML문까지 성능이 느려질 수 있으므로 주의해야 한다.

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

Partition Exchange의 함정 (13)	2010.05.27
튜닝 프로젝트 시 이슈 극복 사례 (15)	2010.05.20
Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05
한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19

Posted by extremedb

,

Range 파티션에서 maxvalue의 진정한 의미

Oracle/TroubleShooting 2010. 3. 16. 13:45

"멀티 컬럼으로 Range 파티션을 할 경우 Where 절에 파티션 선두 컬럼에 해당하는 조건만 주어도 Partition Pruning이 수행된다"

위의 말이 사실일까? 어디서 흘러나온 말인지 모르겠으나 위의 경우는 Partition Pruning이 제대로 되지 않는다. 함정이 기다리고 있기 때문이다.

아래의 스크립트를 실행해서 직접 증명해보자.

환경: Oracle 10.2.0.4

4 Node RAC

CREATE TABLE t (

id NUMBER,

d1 DATE,

day_num VARCHAR2(2),

inst_id NUMBER(1),

pad VARCHAR2(4000),

CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

PARTITION pt_1_4 VALUES LESS THAN ('1', 5),

PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

PARTITION pt_2_4 VALUES LESS THAN ('2', 5),

PARTITION pt_3_1 VALUES LESS THAN ('3', 2),

PARTITION pt_3_2 VALUES LESS THAN ('3', 3),

PARTITION pt_3_3 VALUES LESS THAN ('3', 4),

PARTITION pt_3_4 VALUES LESS THAN ('3', 5),

PARTITION pt_4_1 VALUES LESS THAN ('4', 2),

PARTITION pt_4_2 VALUES LESS THAN ('4', 3),

PARTITION pt_4_3 VALUES LESS THAN ('4', 4),

PARTITION pt_4_4 VALUES LESS THAN ('4', 5),

PARTITION pt_5_1 VALUES LESS THAN ('5', 2),

PARTITION pt_5_2 VALUES LESS THAN ('5', 3),

PARTITION pt_5_3 VALUES LESS THAN ('5', 4),

PARTITION pt_5_4 VALUES LESS THAN ('5', 5),

PARTITION pt_6_1 VALUES LESS THAN ('6', 2),

PARTITION pt_6_2 VALUES LESS THAN ('6', 3),

PARTITION pt_6_3 VALUES LESS THAN ('6', 4),

PARTITION pt_6_4 VALUES LESS THAN ('6', 5),

PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

PARTITION pt_7_4 VALUES LESS THAN ('7', 5)

);

Table created.

먼저 실습을 진행할 테이블을 생성하였다. day_num 컬럼은 1~7 로 구성되어 있다. 모든 일자 데이터를 7등분 하기 위함이다. inst_id 컬럼은 RAC에서 Instance 번호이며 1~4로 구성된다. gc buffer busy등을 줄이기 위한 용도로 자주 사용된다.

이제 테스트용 데이터를 insert 하자.

INSERT INTO t

SELECT rownum AS id,

trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4) AS d1,

to_char(trunc(to_date('2007-01-01','yyyy-mm-dd')+rownum/27.4),'d') AS day_num,

1+mod(rownum,4) AS inst_id,

dbms_random.string('p',255) AS pad

FROM dual

CONNECT BY level <= 10000

ORDER BY dbms_random.value;

10000 rows created.

commit;

BEGIN

dbms_stats.gather_table_stats(

ownname => user,

tabname => 'T',

estimate_percent => 100,

method_opt => 'for all columns size skewonly',

cascade => TRUE

);

END;

/

PL/SQL procedure successfully completed.

각 파티션에 데이터가 골고루 Insert 되었는지 살펴보자.

SELECT partition_name, partition_position, num_rows

FROM user_tab_partitions

WHERE table_name = 'T'

ORDER BY partition_position;

PARTITION_NAME PARTITION_POSITION NUM_ROWS

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

PT_1_1 1 356

PT_1_2 2 358

PT_1_3 3 355

PT_1_4 4 355

PT_2_1 5 364

PT_2_2 6 364

PT_2_3 7 362

PT_2_4 8 362

PT_3_1 9 358

PT_3_2 10 355

PT_3_3 11 355

PT_3_4 12 356

PT_4_1 13 357

PT_4_2 14 355

PT_4_3 15 355

PT_4_4 16 358

PT_5_1 17 355

PT_5_2 18 355

PT_5_3 19 357

PT_5_4 20 358

PT_6_1 21 355

PT_6_2 22 355

PT_6_3 23 358

PT_6_4 24 356

PT_7_1 25 355

PT_7_2 26 358

PT_7_3 27 358

PT_7_4 28 355

28 rows selected.

모든 파티션에 건수가 골고루 분배되었다. 그럼 이제 파티션의 선두 컬럼 조건만 있을 경우 Partition Pruning이 수행되는지 알아보자.

SELECT /*+ GATHER_PLAN_STATISTICS */

COUNT(*)

FROM T

WHERE DAY_NUM = '3';

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

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

| 1 | SORT AGGREGATE | | 1 | | | 1 | 115 |

| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 13 | 1424 | 115 |

|* 3 | TABLE ACCESS FULL | T | 5 | 9 | 13 | 1424 | 115 |

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

불필요한 파티션을 액세스 한다
Pstart와 Pstop 항목을 보면 9번째 파티션부터 13번째 파티션까지 Scan 되었다.

Partition Pruning이 잘된 것처럼 보이지만 자세히 보면 DAY_NUM이 3인 파티션은 4개뿐인데 5개의 파티션을 액세스 하였다.

어떻게 된 것인가?

DAY_NUM이 3인 파티션은 4개 이지만 inst_id 컬럼에 어떤 값이 있을지 알 수 없으므로 13번째 파티션을 액세스 할 수 밖에 없는 것이다. 다시 말하면 DAY_NUM이 3이면서 inst_id가 6인 데이터는 13번째 파티션에 들어갈 수 있으므로 불필요한 Scan이 일어나는 것이다.

물론 아래와 같이 DAY_NUM 조건과 INST_ID 조건을 같이 준다면 이런 현상은 발생하지 않는다.

SELECT /*+ GATHER_PLAN_STATISTICS */

COUNT(*)

FROM T

WHERE DAY_NUM = '3'

AND INST_ID = 2;

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

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

| 1 | SORT AGGREGATE | | 1 | | | 1 | 23 |

| 2 | PARTITION RANGE SINGLE| | 1 | 10 | 10 | 355 | 23 |

|* 3 | TABLE ACCESS FULL | T | 1 | 10 | 10 | 355 | 23 |

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

정확히 10번째 파티션만 액세스 하였다.

만약 INST_ID 조건을 줄 수 없는 경우라면?

모든 경우에 INST_ID 조건을 줄 수는 없을 것이다. 예를 들면 전체를 처리해야 하는 경우는 INST_ID 조건을 줄 수 없을 것이다. 이때 어떻게 하면 비효율을 없앨 수 있겠는가?

해결방법1

특정 inst_id 조건을 주지 못할 때는 inst_id 의 범위를 주어 불필요한 파티션 scan을 방지한다.

SELECT /*+ GATHER_PLAN_STATISTICS */

COUNT(*)

FROM T

WHERE DAY_NUM = '3'

AND INST_ID BETWEEN 1 AND 4;

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

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

| 1 | SORT AGGREGATE | | 1 | | | 1 | 92 |

| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 12 | 1424 | 92 |

|* 3 | TABLE ACCESS FULL | T | 4 | 9 | 12 | 1424 | 92 |

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

정확히 4개의 파티션만 액세스 하여 비효율이 사라졌다..

해결방법2

조건을 주지 못할 때는 4번째 inst_id 파티션을 maxvalue로 바꾼다.

CREATE TABLE t (

id NUMBER,

d1 DATE,

day_num VARCHAR2(2),

inst_id NUMBER(1),

pad VARCHAR2(4000),

CONSTRAINT t_pk PRIMARY KEY (id)

)

PARTITION BY RANGE (day_num,inst_id) (

PARTITION pt_1_1 VALUES LESS THAN ('1', 2),

PARTITION pt_1_2 VALUES LESS THAN ('1', 3),

PARTITION pt_1_3 VALUES LESS THAN ('1', 4),

PARTITION pt_1_4 VALUES LESS THAN ('1', maxvalue),

PARTITION pt_2_1 VALUES LESS THAN ('2', 2),

PARTITION pt_2_2 VALUES LESS THAN ('2', 3),

PARTITION pt_2_3 VALUES LESS THAN ('2', 4),

PARTITION pt_2_4 VALUES LESS THAN ('2', maxvalue),

...중간생략

PARTITION pt_7_1 VALUES LESS THAN ('7', 2),

PARTITION pt_7_2 VALUES LESS THAN ('7', 3),

PARTITION pt_7_3 VALUES LESS THAN ('7', 4),

PARTITION pt_7_4 VALUES LESS THAN ('7', maxvalue)

);

위에서 실행한 insert문과 dbms_stats을 여기서 다시 실행하고 아래의 SQL을 수행하자.

SELECT /*+ GATHER_PLAN_STATISTICS */

COUNT(*)

FROM T

WHERE DAY_NUM = '3';

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

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

| 1 | SORT AGGREGATE | | 1 | | | 1 | 92 |

| 2 | PARTITION RANGE ITERATOR| | 1 | 9 | 12 | 1424 | 92 |

|* 3 | TABLE ACCESS FULL | T | 4 | 9 | 12 | 1424 | 92 |

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

정확히 4개의 파티션만 액세스 하였다. 비효율이 없어진 것이다.

결론

이제 maxvalue의 진정한 의미를 알겠는가?

단순히 마지막 값이라는 의미 이외에 Partition Pruning에 영향을 끼치며 이것은 성능과 직결된다.

또한 멀티 컬럼으로 Range 파티션을 할 경우 where 조건에 따라 불필요한 파티션을 액세스 하므로 주의해야 한다.

PS

위의 해결책 1,2 를 통해 문제를 해결하지 않으면 해당 select문의 성능저하뿐만 아니라 해당 테이블을 사용하는 DML문의 성능이 저하되는 끔찍한 현상이 발생할 수 있다. 다음시간에 이 문제에 대하여 논의 해보자.

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

튜닝 프로젝트 시 이슈 극복 사례 (15)	2010.05.20
RAC에서 특정 Instance만 성능이 느린 이유 (0)	2010.03.24
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05
한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19

Posted by extremedb

,

COPY_T를 대신하여 Connect By 문을 사용하는가?

Oracle/TroubleShooting 2010. 3. 5. 17:14

Dummy Table(흔히 Copy_t 라고 불림) 대신에 9i 이후부터는 Connect By level 문을 사용하곤 한다. 하지만 조심하지 않으면 해당 SQL이 종료되지 않는 장애를 만나게 된다. 오늘은 Connect By level 문을 오용하는 사례와 해결책을 제시하고자 한다.

상황
업무팀에서 새로운 SQL을 작성하고 컴파일하여 운영 시스템에 반영되었다. 문제의 SQL이 실행되자 너무 오래걸려서 Time Out이 발생하였다. 아래는 상황을 최대한 간단히 표현하여 테스트를 수행하기 위한 스크립트 이다.

--입사년도 테이블 생성
create table hire as
select '2006' hire_date from dual union all
select '2003' hire_date from dual union all
select '2002' hire_date from dual union all
select '1999' hire_date from dual union all
select '1997' hire_date from dual ;

--현재년도 에서 입사년도를 빼서 차이(gap)를 나타냄
select hire_date,
to_char(sysdate, 'YYYY') this_year,
to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap
from hire ;

결과:
HIRE THIS    GAP
---- ---- ----------
2006 2010    4
2003 2010    7
2002 2010    8
1999 2010 11
1997 2010 13

5 rows selected.

업무요건
위의 SQL의 결과에서 나타난 GAP만큼 가상의 ROW를 생성하여야 한다. 즉 입사년도가 2006년인 사람은 ROW가 4개로 되어야 하고 2003년인 사람은 ROW가 7개가 되어야 한다. 전체적으로 43건이 나와야 한다. 아래에 원하는 답이 있다.

원하는 답
HIRE THIS    GAP    NUM
---- ---- ---------- ----------
2003 2010    7    7
2003 2010    7    6
2003 2010    7    5
2003 2010    7    4
2003 2010    7    3
2003 2010    7    2
2003 2010    7    1
2006 2010    4    4
2006 2010    4    3
2006 2010    4    2
2006 2010    4    1
....중간생략

문제의 SQL
아래의 SQL은 gap을 Connect By Level 절에 적용시킨 것이다. 아래처럼 SQL을 작성한다면 지옥을 경험할 수 있다.

select hire_date,
to_char(sysdate, 'YYYY') this_year,
to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
level
from hire
connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) ;

위의 SQL은 전체건을 Fetch하려면 10분이 걸려도 끝나지 않았다. 시간이 너무 오래 걸리므로 아래처럼 COUNT 로 바꿔서 실행해 보았다.

select /*+ gather_plan_statistics */ count(*)
from ( select hire_date,
   to_char(sysdate, 'YYYY') this_year,
   to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
   level
from hire
   connect by level <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date)
);

결과:
COUNT(*)
----------
3773280

버그인가?
무려 370만건 이상의 건수가 나왔다. 이상하지 않은가? 건수의 예측도 할 수 없었다. 건수와 관련해서 일정한 규칙도 존재하지 않았다. 버그인지 아닌지 알 수 없지만 결론적으로 위의 SQL처럼 사용하면 안된다는 것을 알 수 있다. 아래는 Count에 대한 실행통계인데 무려 44초나 걸렸다.

-------------------------------------------------------------------------------
| Id | Operation    | Name | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------
| 1 | SORT AGGREGATE    |    |    1 |00:00:44.39 | 3 |
| 2 | VIEW |    | 3773K|00:00:45.28 | 3 |
| 3 |    CONNECT BY WITHOUT FILTERING|    | 3773K|00:00:41.51 | 3 |
| 4 | TABLE ACCESS FULL    | HIRE |    5 |00:00:00.01 | 3 |
-------------------------------------------------------------------------------

해결방법

select /*+ gather_plan_statistics leading(hire) */
hire_date,
to_char(sysdate, 'YYYY') this_year,
to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date) gap,
num
from hire,
(select level as num
from dual
connect by level <= 40 --> 충분한 값을 주어야 한다.
) b
where num <= to_number(to_char(sysdate, 'YYYY')) - to_number(hire_date);

Connect By 절을 인라인뷰로 만들어 Dummy 테이블처럼 사용하였다. 주의 사항은 connect by level <= 40 에서 숫자값을 충분히 주어야 한다. GAP 중에 가장 큰것이 13 이므로 넉넉히 40을 주었다.

결과:
HIRE THIS    GAP    NUM
---- ---- ---------- ----------
1997 2010 13 13
1997 2010 13 12
1997 2010 13 11
.....중간생략
2006 2010    4    4
2006 2010    4    3
2006 2010    4    2
2006 2010    4    1

43 rows selected.

정상적으로 원하는 결과가 나왔고 성능도 이상적이다.

-------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------
| 1 | MERGE JOIN |    | 43 |00:00:00.01 | 3 |    |
| 2 | SORT JOIN |    |    5 |00:00:00.01 | 3 | 2048 (0)|
| 3 |    TABLE ACCESS FULL    | HIRE |    5 |00:00:00.01 | 3 |    |
|* 4 | SORT JOIN |    | 43 |00:00:00.01 | 0 | 2048 (0)|
| 5 |    VIEW |    | 40 |00:00:00.01 | 0 |    |
| 6 | CONNECT BY WITHOUT FILTERING|    | 40 |00:00:00.01 | 0 |    |
| 7 |    FAST DUAL    |    |    1 |00:00:00.01 | 0 |    |
-------------------------------------------------------------------------------------------

주의사항
만약 위의 SQL처럼 인라인뷰를 사용한다고 해도 Nested Loop 조인으로 풀리고 Connect By문을 사용한 인라인뷰가 후행집합이 된다면 선행집합의 건수만큼 반복해서 Connect By문이 수행되므로 조심해야 한다.

결론
Dummy 테이블을 대신하는 Connect By Level을 사용할 때 주의하지 않으면 SQL이 종료되지 않는다. 이를 방지하려면 반드시 DUAL과 함께 사용해야 하며 인라인뷰를 만들어서 사용해야 된다. 만약 예전처럼 Dummy 테이블을 사용했다면 이런 성능저하는 발생하지 않을 것이다. 아무리 새롭고 좋은것도 오용한다면 결과가 어떻게 되는지 잘 보여주는 예제이다.

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

RAC에서 특정 Instance만 성능이 느린 이유 (0)	2010.03.24
Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16

Posted by extremedb

,

Oracle 조인 방법 25가지

Oracle/Data Join Method 2010. 2. 26. 16:18

3 부작의 마지막 편
첫 번째, Oracle Data Access Pattern을 정복하라
두 번째, Data Access Pattern중의 파티션에 관련된 Partition Access Pattern 에 이어서 마지막 편이다.

지난 글에서 Data Access Pattern 과 Join Method 이 두 가지는 기본 중에 기본이라고 하였다.
또한 이 두 가지를 정복한다면 SQL 튜닝중의 많은 부분을 커버할 수 있다고 하였다.
튜닝에서 이것보다 중요한 것이 있을까? 이것들 중에 하나라도 빠트린다면 제대로 된 튜닝을 할 수 없다.

단순 분류 5가지
데이터의 연결방법은 단순히 분류해 보면 다음과 같다.

1.Nested Loop Join
2.Sort Merge Join
3.Hash Join
4.Outer Join
5.Using Subquery

세분화
하지만 이것으로는 부족하다. Join Method를 좀더 자세히 나타내면 다음과 같다.

01. Nested Loop Join
02. Sort Merge Join
03. Hash Join
04. Cartesian Join (혹은 Cross Join)
05. Sub Query-In,
06. Sub Query-Any
07. Sub Query-All
08. Sub Query-Exists
09. Subquery Factoring
10. Semi Join-Nested Loop
11. Semi Join-Sort Merge
12. Semi Join-Hash
13. Semi Join-Hash Join Right
14. Anti Join-Nested Loop
15. Anti Join-Sort Merge
16. Anti Join-Hash
17. Anti Join-Hash Join Right
18. Index Join
19. Outer Join-Full
20. Outer Join-Nested Loop
21. Outer Join-Sort Merge
22. Outer Join-Hash
23. Outer Join-Hash Join Right
24. Partition Outer Join
25. Star Query Transformation

극한의 세분화
물론 여기서 더 세분화 시킬 수 있다. 예를 들면 Nested Loop Join은 아래와 같이 분류할 수 있다.

Full(선행집합)-Unique (후행집합)
Full(선행집합)-Range (후행집합)
Range(선행집합)-Range (후행집합)
Unique(선행집합)-Unique (후행집합)
....중간생략

이런 방법으로 Sort Merge Join과 Hash Join까지 계속 나열한다면 아마 끝이 없을 것이다.

단 한 줄도 놓치지 마라
아래의 첨부파일에는 Nested Loop Join도 위와 같은 방법으로 가능한 세분화 하였다. 따라서 이 파일에 담긴 Join method는 25가지가 넘는다. 오늘 이야기하는 조인방법들은 튜닝을 하려면 반드시 정복해야 할 주제이니 꼼꼼히 보기 바란다.

invalid-file

Oracle Data Join Method

PS
Star Join은 Star Query Transformation이 나온 후로 설 땅을 잃었으므로 나타내지 않았다.

'Oracle > Data Join Method' 카테고리의 다른 글

Sort Merge Join에 대한 오만과 편견 (476)	2011.04.28
같은 테이블을 두 번 읽었지만 일량이 틀려요 (16)	2011.01.14
Nested Loop Join 성능향상과 관련된 2가지 원리 (17)	2009.05.09
Hash Join Right (Semi/Anti/Outer) 의 용도 (17)	2009.03.02
Full Outer Join 의 비밀 (5)	2009.02.23

Posted by extremedb

,

한방 Query를 사용하지 말아야 할 때

Oracle/TroubleShooting 2010. 2. 19. 17:03

SQL의 길이에 따른 분석시간

언제부터인가 복잡한 업무의 배치작업에 한방 SQL이 유행하기 시작했다. 좋은 현상이다. 하지만 이제 정도가 지나친 SQL들이 가끔 눈에 뛴다. 한방 SQL을 사용하지 말아야 할 때와 사용해야 할 때를 구분할 줄 알아야 한다. SQL이 어느 정도 길어지면 PL/SQL 이나 PRO*C 등을 이용하여 절차형으로 바꾸어야 한다. 이렇게 하더라도 Bulk Collect나 For all 등으로 처리하거나 배열처리를 병행한다면 만족할 만한 속도를 낼 수 있다..

아래는 SQL의 길이와 SQL을 전체적으로 이해하는데 걸리는 시간을 조합한 그래프이다.

SQL이 길어지면 이해하기 힘들어

이 그래프를 본다면 SQL을 길게 작성하는 것이 얼마나 위험한지 알 수 있다. SQL의 길이가 짧으면 짧을수록 그것을 이해하는 데 걸리는 시간은 얼마 되지 않음을 알 수 있다. 반대로 SQL의 길이가 길수록 이해하는 데 걸리는 시간은 무한대로 늘어난다. 누구도 위의 그래프에 예외일 수 없다.

만약 여러분이 업무 인수인계를 받는 입장인데 SQL 하나가 A4 용지 기준으로 40페이지 라면? 아마 인수인계 받는데 한달이 걸려도 전체 SQL을 이해하기 힘들 것이다. 하지만 1페이지짜리 SQL 40개 라면 웃으며 차근 차근 인수인계를 받을 수 있다. 하루에 SQL 3~4개 혹은 그 이상도 인수인계 받을 수 있다. 하루에 4개씩 인수 인계 받는다면 10일 이면 인수 인계가 끝난다. 40 페이지나 되는 한방 Query는 유지보수 하기가 대단히 어려움을 알아야 한다.

이제 위의 그래프에 근거하여 한방 SQL을 사용해도 되는 경우와 사용하지 말아야 할 경우를 구분해 보자.

한방 SQL을 사용해도 되는 경우

첫 번째, SQL이 A4 용지 기준으로 4페이지 이하인 경우.

4페이지라고 한 것은 꼭 정해진 것은 아니다. 하지만 유지보수의 관점에서 가독성이 좋아야 한다. 4페이지면 조금 길어서 가독성이 낮아진다고 생각할 수 있지만 필자의 경우 SQL을 출력할 때 한 면에 인쇄할 페이지 수를 2로 설정하면 2페이지만 보면 전체 SQL이 출력 되므로 4페이지 까지는 조금만 노력해도 분석이 용이했다. 하지만 한면에 인쇄할 페이지 수를 4로 하자 글자가 너무 작아져서 볼 수 없는 수준이었다. 필자의 경우 기준은 4페이지 이지만 개인에 따라 기준은 2페이지 일 수도 있고 6페이지 일 수도 있다. 하지만 아무리 SQL에 능통한 사람도 SQL의 길이가 A4 용지 기준으로 8페이지 이상이 된다면 분석시간이 급속도로 늘어날 것이다..

두 번째, SQL이 5페이지가 넘어 가더라도 Union 혹은 Minus 등으로 명확히 구분되거나 누가 보더라도 이해가 빠른 SQL인 경우.

이 경우는 5페이지가 넘어가지만 빠른 시간에 분석할 수 있으므로 5페이지가 넘어 가더라도 유지보수가 용이하다. 하지만 이 경우에도 8페이지가 넘어간다면 고민해야 한다.

세 번째, SQL이 5페이지가 넘어 가고 업무의 변경이 있더라도 SQL을 변경하는 것이 아니라 SQL을 새로 작성하기로 합의하거나 혹은 이러한 정책이 수립된 경우.

이 경우는 SQL을 수정할 일이 없으므로 길어도 상관없다. 하지만 SQL을 새로 작성하는 사람이 모델과 업무를 잘 알고 있고 튜닝을 할 줄 알아야 고품질의 SQL을 작성할 수 있다.

네 번째, 유지보수의 중요성 보다 성능이 더 중요한 경우.

대용량의 복잡한 업무를 처리하는데 일주일이 넘어간다면 견딜 수 없을 것이다. 예를 들면 요금청구 작업의 성능은 기업의 흥망을 좌우할 수 있다. 이런 경우는 유지보수를 희생하더라도 한방 Query를 사용할 수 있다.

다섯 번째, SQL이 5페이지가 넘어 가지만 업무의 변경이 전혀 없어 SQL을 수정 할 일이 없는 경우.

유지보수를 할 필요가 없는 경우이다. 하지만 이런 상황은 아주 예외적인 경우일 것이다.

위의 5가지 경우가 아니라면 한방 SQL을 작성해서는 안 된다.

한방 Query와 관련한 유명한 일화

HR(인적자원 관리) 프로젝트에서 급여를 계산하는 프로그램을 한방 SQL로 개발하였고 40페이지가 넘는다고 했다. 급여계산은 한방 Query의 성능이 빨라 Open을 성공적으로 했다고 한다. 하지만 문제는 Open후 2년 뒤에 찾아왔다. 업무가 변경되어 급여계산의 SQL을 수정해야 하는데 아무도 SQL을 수정할 수 있는 사람이 없었다. 조직내부에서 몇 주간 고민해 보았으나 결국 분석을 포기하고 원작자를 불렀다.

핵심은 이렇다. 돈을 많이 쳐줄 테니 SQL을 고쳐달라는 것이었다. 하지만 누가 그랬던가? 사람은 망각의 동물이라고...... 결국 원작자도 2년이 지난 이상 40페이지가 넘는 SQL을 외우고 다닐 수는 없는 노릇이 아닌가? 그는 분석을 포기하였다고 한다. 아래는 원작자가 분석을 포기한 이유이다.

원작자: 돈을 아무리 준다고 해도 그 기간 내에는 할 수가 없습니다. 인라인 뷰가 80개가 넘는데 분석하는 데만 2~3달 걸릴 것 같습니다.

요청자: 두달 안에 변경된 업무를 반영해야 하는데 큰일 났네....

결국 원작자는 돌려보내고 급여 담당자가 프로그램을 절차형으로 모두 새로 작성했다고 한다. 새로 작성하는데 꼬박 한달이 걸렸다고 한다. 위의 원작자는 분석하는데만 두 달이 넘는다고 하였다. 하지만 급여담당자는 한달안에 모든 프로그램이 작성 완료되었음을 주목하라. 담당자는 한방 Query 보다 성능은 떨어졌지만 상관이 없다고 하였다. 아래는 급여 담당자의 이야기이다.

급여 담당자: 급여 배치가 30분 정도 결렸는데 절차형으로 바꾸니 두 시간이 걸리네요. 하지만 상관 없습니다. 오늘 저녁에 급여 배치를 돌리고 내일 급여가 지급되기 때문에 내일 오후 1시까지 배치가 끝나면 됩니다.

원작자는 유지보수의 중요성을 무시한채 Critical 하지도 않은 성능만 고려한 것이다. 아무리 좋은것 이라도 지나치면 괴로워진다. 이제는 한방 Query를 남발하지 않았으면 한다.

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

Range 파티션에서 maxvalue의 진정한 의미 (6)	2010.03.16
COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
천당과 지옥의 차이 (3)	2009.06.17

Posted by extremedb

,

Connect By VS ANSI SQL

Oracle/SQL Tuning 2010. 2. 11. 15:27

필자는 예전에 Recursive SQL이라는 글을 통하여 IBM DB2나 Microsoft의 SQL Serve에서 사용하는 Recursive With문을 소개한적이 있다. 시간이 흘러 이제는 Oracle11gR2에서 Recursive With문을 사용할 수 있게 되었다. 오라클에서도 DB2나 SQL Serve처럼 ANSI SQL을 사용할 수 있게 된 것이다.

타 DBMS에 능숙한 사람들에게는 환영할만한 일이나 오라클을 사용한 사람들은 별로 달가워하지 않는 것 같다. 왜냐하면 기존의 오라클 사용자들은 Connect By의 기능이 워낙 막강하였으므로 굳이 ANSI SQL을 사용할 필요가 없다고 생각하기 때문이다.

하지만 과연 그럴까? 모든 기능은 용도가 있다.

이 글을 보고 알 수 있는 사항은 다음과 같다.

1. Connect By와 Recursive With의 문법 비교

2. Connect By와 Recursive With의 기능 비교

3. Connect By와 Recursive With의 성능 비교

4. 1, 2, 3번을 통하여 Connect By와 Recursive With의 장단점 파악

1) 문법 관점에서 Connect By VS Recursive With

SELECT employee_id, first_name, manager_id, LEVEL

FROM employee

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID FIRST_NAME MANAGER_ID LEVEL

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

100 Steven 1

101 Neena 100 2

108 Nancy 101 3

109 Daniel 108 4

110 John 108 4

...중간생략

202 Pat 201 3

107 rows selected.

WITH recursive(employee_id, name, manager_id, recursive_level) AS

( SELECT employee_id, first_name, manager_id, 1 recursive_level

FROM employee

WHERE manager_id IS NULL --> START WITH 절에 해당함

UNION ALL

SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id --> CONNECT BY 절에 해당함

)

SELECT *

FROM recursive;

EMPLOYEE_ID NAME MANAGER_ID RECURSIVE_LEVEL

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

100 Steven 1

201 Michael 100 2

101 Neena 100 2

102 Lex 100 2

114 Den 100 2

...중간생략

107 Diana 103 4

107 rows selected.

SQL문이 길어졌다. 하지만 가독성은?

위의 예제에서 볼 수 있듯이 Recursive With 문은 Connect By에 비하여 가독성이 좋아졌다. 왜냐하면 시작조건과 찾아가는 조건이 Union All로 분기되어 있으므로 SQL이 조금 길어지긴 하였으나 해석하는데 전혀 어려운 점이 없기 때문이다. 또한 SQL이 분리되어 있으므로 각각의 성능 최적화도 쉽게 할 수 있다. 주의 사항이 있다. Recursive With 문에서는 LEVEL을 사용할 수 없지만 위의 경우처럼 숫자 초기값을 지정하고 거기에 1을 계속 더해가면 같은 기능을 사용할 수 있다. 또 하나의 주의사항은 Sort의 순서가 다르다는 것인데 Order By 기능을 설명하는 부분에서 자세히 다루어진다.

무한루프 발생 테스트

먼저 무한루프에 만들기 위하여 TOP LEVEL의 manager를 조작하고 Connect By와 Recursive With문을 각각 실행시켜보자.

UPDATE employee

SET manager_id = 101

WHERE employee_id = 100;

commit;

SELECT employee_id, first_name, manager_id, LEVEL

FROM employee

START WITH manager_id = 100

CONNECT BY PRIOR employee_id = manager_id;

ORA-01436: CONNECT BY loop in user data

WITH recursive(employee_id, name, manager_id, recursive_level) AS

( SELECT employee_id, first_name, manager_id, 1 recursive_level

FROM employee

WHERE manager_id = 100

UNION ALL

SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id

)

SELECT *

FROM recursive;

ORA-32044: cycle detected while executing recursive WITH query

위에서 보는 것처럼 에러코드가 서로 다르다. 기존의 Connect By문은 이러한 무한루프를 성공적으로 제거하는 기능이 존재했다. 아래가 그 기능을 구현한 SQL이다.

SELECT employee_id,

manager_id,

CONNECT_BY_ISCYCLE AS iscycle

FROM employee

START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id ;

107 rows selected.

Connect By절에 NOCYCLE을 명시하면 무한루프를 방지할 수 있다. 뿐만 아니라 CONNECT_BY_ISCYCLE 기능을 사용하면 무한루프를 발생시키는 Row를 발견할 수 있다. 또한 Oracle 9i와 10g를 거치면서 기능이 막강해져서 아래와 같이 사용할 수 있게 되었다.

SELECT employee_id,

manager_id,

LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') as name_tree,

CONNECT_BY_ROOT last_name as root_name,

CONNECT_BY_ISLEAF as isleaf,

CONNECT_BY_ISCYCLE as iscycle

FROM employee

START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id ;

107 rows selected.

위의 기능 중에 하나라도 모르는 것이 있다면 Hierarchical Queries를 참조하라

2) 기능 관점에서 Connect By VS Recursive With

위에서 보았던 막강한 기능들을 Recursive With에서 모두 사용할 수 있는지 아닌지는 매우 중요하다. 왜냐하면 기능의 사용 가능 유무에 의해서 Recursive With의 존재가치가 판가름 날 것이기 때문이다. 위의 예제에서 사용한 모든 기능들을 Recursive With문에서 사용할 수 있으면 좋겠지만 아쉽게도 위에서 굵게 표시된 모든 예약어와 함수 등을 사용할 수 없다. 하지만 Recursive With문에서는 모든 기능을 예약어나 함수가 아닌 수동으로 재연할 수 있다. 그것도 아주 간편하게 말이다. 손 맛을 느끼게 해주는 SQL이다. 아래를 보자.

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

FROM employee

WHERE employee_id = 100

UNION ALL

SELECT e.employee_id, e.manager_id,

recursive.name_tree||'-'||e.last_name --> SYS_CONNECT_BY_PATH 함수에 해당함

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX --> ORDER SIBLINGS BY 기능에 해당함

CYCLE manager_id SET iscycle TO "1" DEFAULT "0" --> CONNECT_BY_ISCYCLE 기능에 해당함

SELECT employee_id, manager_id, name_tree,

regexp_substr(name_tree, '[^-]*' ) root_name, --> CONNECT_BY_ROOT 기능에 해당함

decode( regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

--> CONNECT_BY_ISLEAF 기능에 해당함

iscycle

FROM recursive;

102 rows selected.

이것 봐라?

너무나 쉽게 기능을 구현하였다. 이 정도라면 기존의 Connect By 사용자도 “어라 이것 봐라?” 라고 이야기 할 것이다. 기능을 살펴보자. 먼저 SEARCH DEPTH FIRST BY 기능은 ORDER SIBLINGS BY 기능에 해당한다. 물론 employee_id 컬럼에 ASC나 DESC를 사용할 수 있다. SET IDX라는 기능은 Sort된 순서대로 일련번호를 생성하는 기능이다. 이것은 ROWNUM과 기능이 비슷하지만 다른 점은 정렬된 순서대로 채번된다는 것이다. 주의사항은 SEARCH DEPTH FIRST 기능을 사용하지 않고 보통의 Order By 구문을 사용하게 되면 Tree 구조가 유지되지 않는다는 것이다. 이런 관점은 Connect By의 ORDER SIBLINGS BY 기능과 같다.

CYCLE manager_id SET iscycle TO "1" DEFAULT "0" 라고 선언하면 manager_id의 값이 잘못되어 무한루프가 발생될 때 식별할 수 있다. 이때 iscycle을 가상의 컬럼으로 이용하게 된다. 주의사항은 iscycle의 값은 숫자가 될 수 없으며 1 Byte 문자만 가능하다. “1” 과 “0”을 사용한 이유도 여기에 있다. regexp_substr를 사용한 이유는 문자를 첫번째 ‘-‘ 까지 잘라내야 root_name을 구할 수 있기 때문이다. Regular Expression에 대하여 자세히 알고 싶은 독자는 오라클 Regular Expressions 완전정복을 참조하라. regexp_count는 버전 11g에 추가된 기능이다. 이 기능은 다음처럼 사용할 수 있다.

ex) regexp_count(text1, text2) :

text1에 text2가 포함된 횟수를 Return한다. 예를 들면 regexp_count(‘yahoo.co.kr’, ‘o’) 라고 했다면 o 가 3번 포함되어 있으므로 3이 Return 된다.

건수가 차이 난다

하지만 문제가 있다. 결과건수가 차이가 난다. Connect By는 결과가 107건이며 Recursive With문은 102건을 Return 하였다. 이 5건의 차이는 무엇인가? 아래는 차이의 분석을 위하여 두 SQL의 결과값 중에서 필요한 부분만 표로 정리한 것이다.

Connect By 사용			Recursive With 사용
EMP_ID	NAME_TREE	IS CYCLE	EMP_ID	NAME_TREE	IS CYCLE
101	King-Kochhar	1	101	King-Kochhar	0
출력되지 않음			100	King-Kochhar-King	1
204	King-Kochhar-Baer	0	204	King-Kochhar-Baer	1
108	King-Kochhar-Greenberg	0	108	King-Kochhar-Greenberg	1
110	King-Kochhar-Greenberg-Chen	0	출력되지 않음
109	King-Kochhar-Greenberg-Faviet	0
113	King-Kochhar-Greenberg-Popp	0
111	King-Kochhar-Greenberg-Sciarra	0
112	King-Kochhar-Greenberg-Urman	0
205	King-Kochhar-Higgins	0	205	King-Kochhar-Higgins	1
206	King-Kochhar-Higgins-Gietz	0	출력되지 않음
203	King-Kochhar-Mavris	0	203	King-Kochhar-Mavris	1
200	King-Kochhar-Whalen	0	200	King-Kochhar-Whalen	1

어떤 차이가 있나?

결론을 이야기하면 Connect By의 NoCycle, IsCycle 기능과 Recursive With의 Cycle및 IsCycle 기능과는 차이가 있다.즉 각각의 기능이 다르므로 용도를 구분할 줄 알아야 한다는 뜻이다.

Connect By의 NoCycle 기능은 King과 Kochhar의 관계가 반복되는 것을 Skip하고 표시된다. 즉 직원번호 204의 name_tree 항목을 보면 King-Kochhar-Baer 라고 되어 있지만 사실은 King-Kochhar- King-Kochhar - King-Kochhar ....무한반복... Baer 처럼 표시할 수 있다. 하지만 Connect By + NoCycle을 사용하면 무한반복을 Skip하고 한번만 나타낸다. 또한 IsCycle 도 반복이 시작되기 직전의 데이터를 식별해주는 기능을 한다.

Connect By + NoCycle을 사용하면 직원번호 101의 데이터는 Cycle 로 표시되는데 사실은 아직 Cycle이 아니다. 왜냐하면 오른쪽의 Recursive With를 사용한 경우를 보면 직원번호 100번의 데이터는 King-Kochhar-King인데 King이 두 번 반복 된 것을 볼 수 있고 이 경우가 첫 번째 반복되는 Cycle 이기 때문이다. Recursive With의 IsCycle 항목에는 이런 관점이 잘 반영되어 나타난다. 이 데이터는 Connect By + NoCycle을 사용하면 볼 수 없다.

마지막으로 Connect By를 사용하면 데이터가 출력되지만 Recursive With를 사용하면 출력되지 않는 Row가 있다. 그 이유는 Connect By는 관계의 반복만 제거하고 마지막까지 데이터를 찾아가지만 Recursive With는 반복되는 첫 번째 데이터만 찾고 거기서 멈춘다. 즉 Connect By를 사용하면 King-Kochhar-Greenberg-Chen로 데이터의 끝까지 찾아가지만 Recursive With를 사용하면 King-Kochhar-Greenberg 여기서 멈춘다. 위의 표를 유심히 비교해 보기 바란다. 지금까지 문법과 여러 가지 기능의 차이를 알아보았다.

3) 성능관점에서 Connect By VS Recursive With

이제 가장 중요한 성능을 비교해보자.

SELECT /*+ GATHER_PLAN_STATISTICS */

employee_id,

manager_id,

LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

CONNECT_BY_ROOT last_name root_name,

CONNECT_BY_ISLEAF isleaf,

TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

FROM employee

START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

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

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

| 0 | SELECT STATEMENT | | 107 | 16 | |

|* 1 | CONNECT BY WITH FILTERING | | 107 | 16 | 2048 (0)|

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 2 | |

|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | |

| 4 | NESTED LOOPS | | 106 | 14 | |

| 5 | CONNECT BY PUMP | | 107 | 0 | |

| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 106 | 14 | |

|* 7 | INDEX RANGE SCAN | EMP_MANAGER_IX | 106 | 5 | |

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

Predicate Information (identified by operation id):

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

1 - access("MANAGER_ID"=PRIOR NULL)

3 - access("EMPLOYEE_ID"=100)

7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

FROM employee

WHERE employee_id = 100

UNION ALL

SELECT e.employee_id, e.manager_id,

recursive.name_tree||'-'||e.last_name

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

employee_id, manager_id, name_tree,

regexp_substr(name_tree, '[^-]*' ) root_name,

decode(regexp_count(lead(name_tree) OVER(ORDER BY IDX), name_tree), 0, 1, null, 1, 0) isleaf,

iscycle

FROM recursive;

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

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

| 0 | SELECT STATEMENT | | 107 | 16 | |

| 1 | WINDOW BUFFER | | 107 | 16 | 8192 (0)|

| 2 | VIEW | | 107 | 16 | |

| 3 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | 107 | 16 | |

| 4 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 2 | |

|* 5 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | |

| 6 | NESTED LOOPS | | 106 | 14 | |

| 7 | NESTED LOOPS | | 106 | 5 | |

| 8 | RECURSIVE WITH PUMP | | 107 | 0 | |

|* 9 | INDEX RANGE SCAN | EMP_MANAGER_IX | 106 | 5 | |

| 10 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 106 | 9 | |

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

Predicate Information (identified by operation id):

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

5 - access("EMPLOYEE_ID"=100)

9 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

IsLeaf 기능을 사용하려면 Connect By가 유리하다

Scan한 블럭수는 16으로 동일하다. 하지만 PGA 사용량이 4배나 차이 난다. 하지만 이것은 Connect By와 Recursive With의 성능 차이가 아니라 분석함수 lead의 사용 유무에 의한 차이이다. 즉 IsLeaf 기능이 필요하다면 기존의 Connect By를 사용하는 것이 유리할 것이다. 하지만 Connect By나 Recursive With 자체의 성능만 비교한다면 결과가 달라진다.

Connect By와 Recursive With에서 IsLeaf 항목을 빼고 다시 실행 해보자.

SELECT /*+ GATHER_PLAN_STATISTICS */

employee_id,

manager_id,

LTRIM(SYS_CONNECT_BY_PATH (last_name, '-'),'-') name_tree,

CONNECT_BY_ROOT last_name root_name,

TO_CHAR(CONNECT_BY_ISCYCLE) iscycle

FROM employee

START WITH employee_id = 100

CONNECT BY NOCYCLE PRIOR employee_id = manager_id

ORDER SIBLINGS BY employee_id;

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

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

| 0 | SELECT STATEMENT | | 107 | 16 | |

|* 1 | CONNECT BY WITH FILTERING | | 107 | 16 | 2048 (0)|

| 2 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 2 | |

|* 3 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 | |

| 4 | NESTED LOOPS | | 106 | 14 | |

| 5 | CONNECT BY PUMP | | 107 | 0 | |

| 6 | TABLE ACCESS BY INDEX ROWID| EMPLOYEE | 106 | 14 | |

|* 7 | INDEX RANGE SCAN | EMP_MANAGER_IX | 106 | 5 | |

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

Predicate Information (identified by operation id):

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

1 - access("MANAGER_ID"=PRIOR NULL)

3 - access("EMPLOYEE_ID"=100)

7 - access("connect$_by$_pump$_002"."PRIOR employee_id "="MANAGER_ID")

WITH recursive(employee_id, manager_id, name_tree) as

( SELECT employee_id, manager_id, last_name

FROM employee

WHERE employee_id = 100

UNION ALL

SELECT e.employee_id, e.manager_id,

recursive.name_tree||'-'||e.last_name

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id

)

SEARCH DEPTH FIRST BY employee_id SET IDX

CYCLE manager_id SET iscycle TO "1" DEFAULT "0"

SELECT /*+ GATHER_PLAN_STATISTICS */

employee_id, manager_id, name_tree,

regexp_substr(name_tree, '[^-]*' ) root_name,

iscycle

FROM recursive;

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

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

| 0 | SELECT STATEMENT | | 107 | 16 |

| 1 | VIEW | | 107 | 16 |

| 2 | UNION ALL (RECURSIVE WITH) DEPTH FIRST| | 107 | 16 |

| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 2 |

|* 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 1 |

| 5 | NESTED LOOPS | | 106 | 14 |

| 6 | NESTED LOOPS | | 106 | 5 |

| 7 | RECURSIVE WITH PUMP | | 107 | 0 |

|* 8 | INDEX RANGE SCAN | EMP_MANAGER_IX | 106 | 5 |

| 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 106 | 9 |

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

Predicate Information (identified by operation id):

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

4 - access("EMPLOYEE_ID"=100)

8 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

일반적인 경우 Recursive With가 유리함

보았는가? 상황이 역전되어 Recursive With를 사용하면 PGA를 전혀 사용하지 않는다. 따라서 IsLeaf를 사용하지 않는 일반적인 경우 Recursive With가 유리함을 알 수 있다.

대용량 배치일 경우 Recursive With를 주의하라

하지만 이것이 끝이 아니다. 대용량 배치일 경우는 상황이 다시 반전된다.

WITH recursive(employee_id, name, manager_id, recursive_level) AS

( SELECT employee_id, first_name, manager_id, 1 recursive_level

FROM employee

WHERE manager_id IS NULL

UNION ALL

SELECT e.employee_id, e.first_name, e.manager_id, recursive_level + 1

FROM employee e, recursive

WHERE e.manager_id = recursive.employee_id

)

SELECT /*+ GATHER_PLAN_STATISTICS */ *

FROM recursive;

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

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

| 0 | SELECT STATEMENT | | 107 | 22 |

| 1 | VIEW | | 107 | 22 |

| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 107 | 22 |

|* 3 | TABLE ACCESS FULL | EMPLOYEE | 1 | 8 |

| 4 | NESTED LOOPS | | 106 | 14 |

| 5 | NESTED LOOPS | | 106 | 5 |

| 6 | RECURSIVE WITH PUMP | | 107 | 0 |

|* 7 | INDEX RANGE SCAN | EMP_MANAGER_IX | 106 | 5 |

| 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 106 | 9 |

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

Predicate Information (identified by operation id):

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

3 - filter("MANAGER_ID" IS NULL)

7 - access("E"."MANAGER_ID"="RECURSIVE"."EMPLOYEE_ID")

대용량 배치일 경우 Recursive With는 비효율이 발견되었다. manager_id IS NULL 조건 때문에 EMPLOYEE 테이블을 Full Scan 하였지만 Union ALL 아래의 SQL에서 또다시 EMPLOYEE 테이블을 Scan하고 있다. 동일한 블록을 두 번 Scan한 셈이다. 하지만 아래를 보라.

SELECT /*+ GATHER_PLAN_STATISTICS */ employee_id, first_name, manager_id, LEVEL

FROM employee

START WITH manager_id IS NULL

CONNECT BY PRIOR employee_id = manager_id;

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

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

| 0 | SELECT STATEMENT | | 107 | 7 |

|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | 107 | 7 |

| 2 | TABLE ACCESS FULL | EMPLOYEE | 107 | 7 |

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

Predicate Information (identified by operation id):

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

1 - access("MANAGER_ID"=PRIOR NULL)

filter("MANAGER_ID" IS NULL)

Connect By를 사용하면 Union을 사용하지 않으므로 Full Table Scan 한 번으로 끝낼 수 있다. Scan한 블럭수는 세 배 이상 차이가 난다.

Connect By VS Recursive With 의 결론

1) 문법적인 측면에서 거의 차이가 없다. Recursive With가 조금 길어지기는 하지만 Union All로 분리되어 있기 때문에 오히려 가독성과 성능 최적화가 쉽게 될 수 있다.

2) 기능면에서도 거의 차이가 없다. Connect By의 모든 기능이 구현 가능하다. 다만 Nocycle 기능과 IsCycle 기능의 용도가 서로 다르므로 구분해서 사용하면 된다.

3) 성능면에서는 Sort 기능을 사용해도 PGA를 전혀 사용하지 않는 Recursive With가 일반적으로 유리하다. 하지만 IsLeaf 기능을 구현하려면 분석함수를 사용해야 하기 때문에 Connect By가 유리할 수 있다. 또한 Scan할 범위가 넓은 경우 Start With 조건을 Full Table Scan으로 유도하면 같은 블럭을 반복해서 Scan하지 않는 Connect By가 유리하다.

결국 각 기능들의 허와 실을 제대로 파악하고 성능 이슈를 최소화 한다면 모두가 웃을 수 있을 것이다.
모두가 웃는 그날까지......

'Oracle > SQL Tuning' 카테고리의 다른 글

Pagination과 분석함수의 위험한 조합 (26)	2010.12.23
오라클의 Update문은 적절한가? (15)	2010.04.14
USE_CONCAT 힌트 제대로 알기 (5)	2009.07.17
분석함수를 이용한 TOP SQL은 튜닝이 불가한가? (5)	2009.05.22
Top SQL-( Rownum 의 정확한 사용법 ) (2)	2009.03.26

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

,

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

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

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

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

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

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

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

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

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

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

explain plan for

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

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

FROM employee e, department d

WHERE e.department_id = d.department_id

AND e.job_id = 'SH_CLERK';

select * from table(dbms_xplan.display);

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

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

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

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

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

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

Predicate Information (identified by operation id):

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

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

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

FILTER와 ACCESS 정보는 중요하다

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

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

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

1. TOAD에서 Predicate Information 보기

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

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

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

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

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

2. 오렌지에서 Predicate Information 보기

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

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

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

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

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

Posted by extremedb

,

내가 사용한 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

,

Partition Access Pattern

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

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

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

1.RANGE
2.LIST
3.HASH

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

Partition RANGE Access Pattern

1.PARTITION RANGE SINGLE

2.2.PARTITION RANGE ITERATOR

3.3.PARTITION RANGE INLIST

4.4.PARTITION RANGE ALL

5.5.PARTITION RANGE EMPTY

6.6.PARTITION RANGE OR

7.7.PARTITION RANGE SUBQUERY

8.8.PARTITION RANGE JOIN-FILTER

9.9.PARTITION RANGE MULTI-COLUMN

Partition LIST Access Pattern

1.
1.PARTITION LIST SINGLE

2.2.PARTITION LIST ITERATOR

3.3.PARTITION LIST INLIST

4.4.PARTITION LIST ALL

5.5.PARTITION LIST EMPTY

6.6.PARTITION LIST OR

7.7.PARTITION LIST SUBQUERY

8.8.PARTITION LIST JOIN-FILTER

Partition HASH Access Pattern

1.
1.PARTITION HASH SINGLE

2.2.PARTITION HASH ITERATOR

3.3.PARTITION HASH INLIST

4.4.PARTITION HASH ALL

5.5.PARTITION HASH SUBQUERY

6.6.PARTITION HASH JOIN-FILTER

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

invalid-file

Partition Access Pattern

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

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

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

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

Posted by extremedb

,


	오동규 블로그 odong91@naver.com by extremedb

'Oracle'에 해당되는 글 120건

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

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

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

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

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

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

'Oracle > SQL Tuning' 카테고리의 다른 글

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

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

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

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

'Oracle > Data Join Method' 카테고리의 다른 글

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

'Oracle > SQL Tuning' 카테고리의 다른 글

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

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

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

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

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

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

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바