'2010/05'에 해당되는 글 3건

  1. 2010.05.27 Partition Exchange의 함정 13
  2. 2010.05.20 튜닝 프로젝트 시 이슈 극복 사례 15
  3. 2010.05.07 PL/SQL-면접문제 808

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

 

하지만.......

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

 

박대리 : 현재 파티션이 2009 3월 까지 만들어져 있고 이후로 파티션을 Split 하지 않아서 20094월 데이터가 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 절에 특정월의 파티션명을 지정하는 것의 결과가 다를 수 있는지 없는지를. 아마 많은 사람들이 결과는 다를 수 없다라고 이야기 할 것인데 정답은 다를 수 있다는 것이다. 여러분이 위에서 본 것처럼....



Posted by extremedb
,

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

 

이 글을 보기 전 고려사항

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

 

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% 에 그침으로써 OPEN3달밖에 남지 않은 상황이었으므로 매우 급박한 상황이었음.

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

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

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


invalid-file

튜닝시_이슈극복_사례



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

Posted by extremedb
,

작년에 회사에서 기술면접에 필요한 문제를 여러 개 만들었는데 그 중에 하나를 소개한다. 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 정리

Posted by extremedb
,