가능한가?
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.
-------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
-------------------------------------------------------------------------------------
| 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건이 나와버렸다. 답이 틀리므로 이것은 버그이다.
왜 이런 일이 발생할까?
비밀은 실행계획에 있다.
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------
| 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.
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------------------
| 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.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 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.
--------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | Buffers |
--------------------------------------------------------------------------------------
| 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.
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts |A-Rows | Buffers |
--------------------------------------------------------------------------------------------
| 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 전략이 될 수 있다.
