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

파티션과 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

,

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

,

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

,

한방 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

,

유명한 Function Based Index 의 버그

Oracle/TroubleShooting 2009. 11. 19. 14:53

일부 스크립트에 오타가 있어 수정했음을 알립니다.(2009.11.19)

거의 모든 시스템에 버그가 존재해
아주 유명한 FBI 인덱스 관련 버그를 소개한다. 이 버그는 Wrong result 버그로 분류되며 특징은 조회가 되지 않는다는 것이다. 최근까지 Patch 가 나오지 않아 악명이 높았다. 필자의 경험으로는 거의 모든 시스템에서 이 버그가 재연되었다.

버그 재연방법
CHAR 컬럼의 길이가 2 BYTE 이상이고 이 컬럼에 SUBSTR 함수로 FBI인덱스 생성할 때.

이제 아래의 스크립트를 사용하여 버그를 재연해보자.

환경 : Oracle 11.1.0.6

drop table fbi_test;

create table fbi_test(col1 char(14) );

insert into fbi_test values('01234567890123');
commit;

create index fbi_idx on fbi_test(substr(col1,1,10));

analyze table fbi_test compute statistics;
analyze index fbi_idx compute statistics;

이제 모든 준비가 끝났으므로 데이터를 조회해보자.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 = '01234567890123' ;

no rows selected.

한 건도 나오지 않는다. 이것은 분명 버그이다.

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| FBI_TEST |    1 |    1 |    0 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN    | FBI_IDX |    1 |    1 |    0 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')

위의 Plan을 보면 Virtual Column을 사용하였다. 오라클은 FBI를 만들면 Virtual Column을 생성 한다.
실행계획상에서 Virtual Column을 사용하는 경우는 위와 같이 조회 되지 않는 버그가 발생한다.
이런 상황에서 버그를 피하려면 해당 인덱스를 사용하지 않거나(Full Scan 이용) 아니면 아래와 같이 명시적으로 SQL을 수정해야 한다.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 = '01234567890123'
and substr(col1,1,10) = '0123456789' ;

COL1
--------------
01234567890123

1 row selected.

정상적으로 1건이 나왔다.

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | TABLE ACCESS BY INDEX ROWID| FBI_TEST |    1 |    1 |    1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN    | FBI_IDX |    1 |    1 |    1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')

편법은 안돼... 패치가 정답이다
하지만 이런 편법으로 문제를 해결하는 것은 임시방편 밖에 되지 못한다. 또한 모든 개발자들이 substr 함수를 추가하여 개발한다고 보장 할 수 없다. 이 편법을 사용 하더라도 조건절에 > 혹은 < 사용시에는 여전히 조회가 되지 않는다.

select /*+ gather_plan_statistics index(a fbi_idx ) */ *
from fbi_test a
where col1 > '0123456789012'
and substr(col1,1,10) > '012345678' ;

no rows selected.

한가지 다행스러운 점은 최근에 BUG Patch 2개가 새로 나왔다는 것이다. 또한 아쉬운 점은 HP 시스템용 Patch는 아직 없다는 것이다. 각자의 플랫폼에 맞는 Patch가 있는지 확인해보라.

문서번호 : 6131467
패치 ID : 6131467

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

COPY_T를 대신하여 Connect By 문을 사용하는가? (11)	2010.03.05
한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19

Posted by extremedb

,

SQL 튜닝이 불가한 경우

Oracle/TroubleShooting 2009. 9. 16. 10:17

SQL 튜닝시의 업무적 접근
SQL 튜닝시 Buisiness 관점으로 접근하는 것은 매우 중요한 일이다. 예를 들면 SQL을 아무리 튜닝을 해도 해결되지 않는 경우가 있다. 당신이 아무리 화려한 튜닝 테크닉을 가지고 있다고 해도 말이다. 이런 경우 발상을 전환하는 것이 필요하다. SQL 튜닝을 하지않고 이슈를 해결해야 한다는 것 이다. 튜닝시 이슈가 가장 많이 발생하는 3가지 경우를 소개한다.

조회조건은 제약이 필요없나?
웹화면에서 조회조건으로 일자 From ~ To 조건으로 주는 경우가 다반사이다. 사용자는 많은 기간을 조회하고 싶어한다. 실제로 10년간의 거래내역집계를 조회하는 경우도 보았다. 이런 경우는 반드시 화면에서 기간의 제한을 두어야 한다. 한달로 제한 한다든지 아니면 일주일로 한다든지 상황에 따라서 얼마든지 제약을 줄수 있다. 조회조건 선택시 Combo Box 나 Drop Down List Box 에서 전체보기를 없앤다면 마찬가지로 성능향상을 기대할수 있다. 개발자와의 대화 혹은 화면분석이 필요한 순간이다. 필자는 이러한 작업들을 튜닝 프로젝트시에 반드시 실행해야하는 필수 과정으로 생각한다. 이러한 과정을 사용자 화면 튜닝으로 정의 해야한다.

집계 테이블은 필요한가?
또 다른 예제는 SQL이 집계(Group By + Count)를 하는 경우 아무리 튜닝을 해보아야 느릴수 밖에 없다. 가장 좋은 것은 회의를 통하여 이러한 화면들을 DW시스템으로 넘기는 것이다. 이것은 어느정도의 정치력이 필요하다. 주장하는 사람의 권위와 Power 가 필요하다는 이야기 이다. 하지만 이것이 안될경우 업무팀과 협의 하여 집계 테이블을 만들어야 한다. 집계테이블은 최소화 시켜야 한다. 예를 들어 년별, 지점별 집계 SQL이 있고 월별 지점별 집계 SQL이 있다면 집계 테이블은 월별로 하나만 만들어야 한다. 집계 테이블이 많아지면 정합성을 저해할수 있다.

저작의도를 알면 길이 보인다
계약 테이블과 고객 테이블을 불필요하게 조인 하는 SQL 이 있었다. 하지만 조인만 하고 Select 절에서는 고객 테이블의 컬럼이 하나도 없었다. 상식적으로 보면 계약테이블의 고객번호는 100% 고객 테이블에 있어야 한다. 그렇다면 고객 테이블을 From 절에서 삭제 하면 될까? 아주 위험한 발상이다. 특수한 SQL 의 경우에는 검증용 SQL 을 프로그램으로 만들기도 한다. 이러한 경우 방법은 딱 한가지이다. SQL 을 작성한 개발자에게 질문하여 이것이 계약 테이블의 고객번호를 검증하기 위한 SQL 인지 물어보는 것이다. 경험상 대부분의 경우 불필요한 조인을 삭제할수 있었다. 이러한 상황은 특히 개발자들 끼리 SQL을 Copy 후 약간 수정하여 사용하는 경우 많이 발생 하게된다.

회의나 대화도 중요한 튜닝 Skill 이라는 점
여러분은 위의 3가지 경우를 어떻게 생각하는가? 튜닝의 기술적인 Skill이 아니므로 무시하고 넘어가는 경우를 많이 보아왔다. 절대 문제를 해결할수 없으며 문제를 키울 뿐이다. 이렇게 되지 않으려면 튜닝시에 사용자 혹은 개발자와 자주 회의를 가져야 한다. 경험상 대화를 자주 할수록 문제의 해결 혹은 대안을 빨리 찾는 경우가 많았다. 이때 고객과의 대화의 기술 혹은 설득의 기술이 많은 도움이 된다. 이러한 기술들은 튜닝 자체의 Skill 보다 상위의 개념이며 문제를 해결할 경우 팀원이나 직원들에게 존경을 받거나 부러움의 대상이 될 가능성이 크다.

결론:
실제로 SQL 튜닝 등으로 성능향상이 불가능한 경우는 당신이 생각하는 것 보다 훨씬 많다.
이럴 경우 화려한 튜닝 테크닉으로 문제를 해결을 시도하는 사람, 대화와 설득으로 해결하는 사람 누가 더 뛰어난 튜너인가?

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

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19
Sequence 의 오작동 (번호를 건너뛰는 Sequence) (4)	2009.02.10

Posted by extremedb

,

천당과 지옥의 차이

Oracle/TroubleShooting 2009. 6. 17. 19:47

얼마전에 ORACLE DBMS ShutDown 과 관련한 회의가 있었는데 문제는 아래와 같다.

지옥이 시작되다.
악성 SQL 이 하나 있었다. 그 SQL 은 한번수행시 평균 Elapsed Time 이 0.95 초 정도 걸리고 자주 사용되는 중요한 SQL 인데 DBA 가 조인방법을 바꾸어 Elapsed Time 을 0.8 초로 줄였다. 조인방법만 바꾸었을 뿐인데 성능이 10% 이상 향상 되었다. 그리고 그 DBA 는 튜닝된 SQL을 개발자에게 운영 시스템에 반영하라고 지시하였다. 그 SQL을 테스트 해본 개발자는 성능이 빨라진것을 확인하고 해당 힌트를 적용하여 운영시스템에 반영하였다.

여기서 부터 지옥이 시작되었다. 잘운영되던 DBMS 가 버벅이기 시작한것이다.
결국 운영할수 없는 지경에 까지 이르러서 운영조직은 ORACLE DBMS ShutDown을 결정하였고 튜닝된 SQL 을 원복시켰다.
다시 천당이 되었다. 결국 원래의 SQL 은 문제가 없었고 DBA 가 튜닝한 SQL 이 악성 SQL 이었던 셈이다.

메모리 사용량을 계산해보자.
원인은 튜닝된 SQL 이었다. 그 SQL 은 Peak Time 에 무려 초당 700 번이나 실행되었고 한번 수행시 사용되는 HASH AREA SIZE 를 계산해보니 5MB 정도를 소비하였다. 하나의 SQL 이 초당 3.5 Giga Byte(700 * 5MB) 를 소모한 것이다. 전체 PGA 의 1/3 이 넘는 메모리를 하나의 SQL 이 소모해버린것이다. 당연히 그 SQL 을 제외한 다른 SQL은 PGA 영역의 메모리를 사용하려고 줄을 서게 될것이고 시스템 전반적인 성능이 저하될 것이다. 그뿐인가? 원래 0.95 초 걸리던 SQL 도 수행속도가 1초가 넘어버렸다.
주로 개발자 출신의 DBA 가 이와같은 실수를 많이 저지른다. 시스템 엔지니어 출신의 DBA 는 절대 이런실수가 없다. 물론 개발자 출신 DBA 의 장점은 헤아릴수 없이 많다.

메모리 증설이 해결책인가?
결국 Hash 조인을 을 Nested Loop 조인으로 바꾸고 Access Path를 파악하여 적절한 인덱스를 생성하는것으로 사태는 진정 되었다. 운영조직은 현재 메모리를 증설할 계획이라고 한다. 하지만 메모리 증설로 해결되는것은 미시적 관점이며 거시적 관점에서 해결책은 되지 못한다. 자주 사용하는 SQL 을 튜닝 할때마다 메모리를 증설 할것인가?

위의 문제해결과정이 우리에게 주는 교훈은 무엇인가?
수행속도의 최적화 혹은 Logical Reads의 수를 줄이는것이 항상 튜닝의 목표가 아니라는 것이다. 자원(CPU, MEMORY) 등은 한정적이다. 따라서 이자원들을 전체 시스템관점에서 적절하게 분배하는것 또한 튜닝의 목표가 되어야 한다.
자주 사용되는 SQL을 튜닝할때 Hash Join 을 남발하지 말아야 한다. Hash 조인은 조인횟수를 획기적으로 줄여주지만 반대급부로 메모리 소모가 심하다. 물론 0.95 초 걸리던 SQL 이 0.001 초만에 끝난다면 그방법이 고려될수도 있다. 자원을 독점하는 시간과 SQL 의 수행속도가 현저하게 줄어들었으므로...

수행속도의 최적화가 항상 튜닝의 목표인가?
튜닝의 목표는 물론 Response Time 을 줄이는것 혹은 Logical Read 등을 줄이는 것에 있다.
하지만 항상 추가적으로 고려해야 할것이 전체 시스템 관점에서 자원의 효율적인 배분이다. 이것이 시스템 튜닝의 기본이다.
생각해보라. 같은시간대에 수행되는 야간배치 SQL 이 여러개 있고 그중에 하나가 Parallel 힌트를 다음과 같이 사용하였다면 얼마나 끔찍한 일이 일어날 것인가?

INSERT /*+ Parallel(B 256) */ INTO ~
SELECT /*+ Parallel(A 256) */
FROM ~ ;

천당과 지옥의 차이는 힌트와 같은 아주 조그만 코드에서도 좌우될수 있다.

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

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19
Sequence 의 오작동 (번호를 건너뛰는 Sequence) (4)	2009.02.10

Posted by extremedb

,

Sequence 의 오작동 (무조건 채번되는 Sequence)

Oracle/TroubleShooting 2009. 2. 19. 00:30

오늘은 Sequence 의 오작동 (번호를 건너뛰는 Sequence) 라는 글에 이어서
Sequence 사용시 주의해야 되는 또다른 상황을 설명한다.
예전에 고객사에서 일어난 일이다.
Sequence 채번시 IF 조건에 따라 채번 할수도 혹은 안할수도 있는상황에서
Sequence 가 IF 조건을 무시하고 무조건 채번된다는 보고가 있었다.
아래는 T1 테이블에 INSERT 하는 아주 간단한 SQL 로서 t1_temp 테이블의 id 컬럼값이 null 이면 채번하고
null 이 아니면 id 컬럼값을 그대로 사용하는 로직이 들어있다.

insert into T1 (id, x_padding)
select decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
,x_padding
from t1_temp;

하지만 DECODE 문과 관계없이 Sequence 는 무조건 채번되었다.
상황을 재현하기 위하여 테이블및 시퀀스를 생성한다.

CREATE SEQUENCE my_seq --> 5만 1번부터 시작하는 SEQUENCE 생성
START WITH 50001
CACHE 20;

create table t1_temp as --> 작업용 테이블 생성
with generator as ( select /*+ materialize */
rownum as id
   from all_objects
   where rownum <= 3000 )
select case when rownum > 50000 then null --> 10만건을 생성 하는데 5만번 까지만 채번함.
else rownum
   end id,
   rpad('x',1000)    x_padding
from generator    v1,
   generator    v2
where rownum <= 100000;

create table T1 as --> 작업용 테이블을 이용하여 10만건을 insert 할 테이블 생성
select *
from t1_temp
where rownum < 1;

alter table T1 add constraint T1_PK primary key(id); --> PK 생성

이제 테이블및 Sequence 가 생성되었으므로 맨위의 SQL 을 실행하고 결과를 SELECT 해보자

insert into T1 (id, x_padding)
select decode( id, null, my_seq.nextval, id) as id --> id 가 null 이면 nextval 아니면 id 그대로 사용.
,x_padding
from t1_temp;

commit;

select count(*)
from T1
where id between 50001 and 100000;

위 count 쿼리의 결과는 0 이다.
분명히 5만1번부터 10만번까지 시퀀스로 채번이 되어 5만건이 count 되어야 한다.
뭔가 이상하지 않은가?
이것을 Sequence 버그라고 생각하는 사람도 있지만 필자는 조금 다르게 생각한다.
이유는 Sequence 의 원리에 있다.
Sequence 는 where 절 조건을 만족하면 무조건 채번이 되어버린다.
즉 decode 나 case 문 등의 분기 로직은 where 절이 아니라 select 절에 있기 때문에 무조건 채번 되는 것이다.
그렇다면 조건에 따라서 채번할수 있는 방법은 없는것일까?

Solution 1:
아래와 같은 간단한 함수를 만들어서 call 한다.

CREATE OR REPLACE function my_func return number as
v_seq number;
begin

   select my_seq.nextval
   into v_seq
   from dual;

return v_seq;

end;
/

insert into T1 (id, x_padding)
select decode(id, null, my_func, id) as id --> id 가 null 인 경우만 채번함수가 실행됨.
,x_padding
from t1_temp;

Solution 2:
insert 문을 id 컬럼 기준으로 null 일 때와 null 이 아닐때로 분리한다.

insert into T1 (id, x_padding)
select my_seq.nextval
,x_padding
from t1_temp
where id is null
;

insert into T1 (id, x_padding)
select id
,x_padding
from t1_temp
where id is not null
;

한가지 주의사항은 많은수의 Function call 이 부담이 되는 상황이라면 2번방법을 택해야 한다.
과도한 Function call 은 DBMS 에 무리를 주기 때문이다.

편집후기: 위의 글중에 일부가 오류가 있어서 일부 수정되었음을 밝혀둔다.

다중 INSERT 문을 사용해야 한다는 의견이 있었다.

INSERT FIRST
WHEN id is not null THEN
INTO t1 VALUES( id, x_padding)
WHEN id is null THEN
INTO t1 VALUES( my_seq.nextval, x_padding)
select id
,x_padding
from t1_temp
;

가능할 것처럼 보이지만 위의 SQL 로도 해결이 안된다는걸 알아야 한다.

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

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

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (번호를 건너뛰는 Sequence) (4)	2009.02.10

Posted by extremedb

,

Sequence 의 오작동 (번호를 건너뛰는 Sequence)

Oracle/TroubleShooting 2009. 2. 10. 10:13

고객사의 DBA 로 부터 Sequence가 오작동 한다는 보고를 받았다.
현상은 Sequence 의 NEXTVAL 을 하면 몇만건이 없어진다는데 있었다.
일단 필자는 NOORDER 로 설정되어 있는지 질문을 하였으나 시스템이 RAC 가 아니었다.

해당 Sequence를 분석해보니 CACHE 가 10만이 잡혀 있었고 MAXVALUE가 99999999 으로 되어 있었으며
NOCYCLE 로 설정된 상태였다.
계산상으로 1년정도가 지나면 MAXVALUE 에 도달해서 해당 Sequence를 select 하는 순간 error 가
날수 있는 상황 이었다.

특이한점은 Sequence의 사용주기였다.
토, 일,월~목요일날 전혀 사용하지 않고 금요일에만 일괄배치로 최대 몇만건이 들어올수 있다는 것이었다.

그렇다면 이런 현상의 원인은 무었일까?
원인은 아주 간단하다.
자주 사용하지 않는 OBJECT 는 Shared Pool 에서 밀려난다.
밀려난 Sequence는 Caching 된 번호들이 메모리상에서 없어지게 됨으로 번호가 건너뛰는 것 같이 느낀것이다.
결국은 Sequence의 오작동이 아닌 자연스러운 현상인 것이다.

그렇다면 위와같이 Sequence의 사용주기가 긴(일주일에 한번) 시스템에서 이런현상을 방지하려면 어떤 방법이 있을까?
이런경우는 dbms_shared_pool.keep 을 사용하는 것이 정답이다.
아래의 예제를 보자.

dbms_shared_pool.keep('SCOTT.PKG_PAYCALCULATE', 'P');

PKG_PAYCALCULATE 라는 패키지를 Shared Pool 에 keep 시켰다.

'P' 의 의미는 PL/SQL 로 만들어진 프로그램이라는 뜻이고 들어올수 있는 인자값은 아래와 같다.

P :package/procedure/function
Q :sequence
R :trigger
T :type
JS :java source
JC :java class
JR :java resource
JD :java shared data

dbms_shared_pool.keep 사용후에 아래와 같은 스크립트로 적재여부를 확인 할수 있다.

SELECT owner, NAME, TYPE, sharable_mem, loads, kept, executions, locks, pins
FROM v$db_object_cache
WHERE TYPE IN ('PACKAGE', 'PACKAGE BODY', 'JAVA CLASS')
AND OWNER = 'SCOTT'
ORDER BY 3, 7
;

dbms_shared_pool.keep을 사용하기 전에 아래와 같은 스크립트로 Object 의 크기를 확인하는 습관을 들여야 한다.

SELECT owner, name, trunc((sum(parsed_size)+sum(code_size))/1024)||'K'
FROM dba_object_size
WHERE owner = 'SCOTT'
AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'JAVA CLASS')
GROUP BY owner, name;

한가지 주의사항은 alter system flush shared_pool 명령을 수행해도 이미 keep 시킨 object는
Shared Pool 에서 떨어지지 않는다.
Aging Out 시키는 유일한 방법은 dbms_shared_pool.unkeep 을 사용하는 것이다.

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

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

한방 Query를 사용하지 말아야 할 때 (17)	2010.02.19
유명한 Function Based Index 의 버그 (9)	2009.11.19
SQL 튜닝이 불가한 경우 (12)	2009.09.16
천당과 지옥의 차이 (3)	2009.06.17
Sequence 의 오작동 (무조건 채번되는 Sequence) (0)	2009.02.19

Posted by extremedb

,

Science of Database

'Oracle/TroubleShooting'에 해당되는 글 12건

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

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

Partition Exchange의 함정

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

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

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

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

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

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

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

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

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

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

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

유명한 Function Based Index 의 버그

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

SQL 튜닝이 불가한 경우

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

천당과 지옥의 차이

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

Sequence 의 오작동 (무조건 채번되는 Sequence)

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

Sequence 의 오작동 (번호를 건너뛰는 Sequence)

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

카테고리

최근에 올라온 글

최근에 달린 댓글

글 보관함

링크

태그목록

최근에 받은 트랙백

티스토리툴바


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