Science of Database

어느 회식 자리에서
  오늘(5월 18일) 어느 사이트의 회식자리에서 프로젝트의 PM 옆자리에 앉게 되었다.
나중에 알고보니 그 PM 은 술자리 기피대상 1호 였는데 이유는 "공장"(?) 이야기를 너무 많이 한다는 것이었다.
필자는 운이 좋게도(?) 자리가 그사람 옆이라 공장 이야기를 많이 들을수 있었다.

PM : 요즘 배치프로그램을 보면 영화 매트릭스의 스미스(악당 요원)가 생각이 나네요.

필자 : 왜 그렇게 생각하세요?

PM:  해도 해도 너무해요. 예전에는 년배치, 분기배치, 월배치, 일배치 가 배치의 전부 였지요.
        하지만 90년대 후반부터 하루에 3~4번 도는 배치가 생기기 시작했습니다.
        이제는 시간당 한번 실행되는 배치도 성에 안차나 봅니다.
        심지어 최근에는 Near Real Time 배치(1~10분 주기)가 많아져 온라인 프로그램과 연관된 lock 문제가
        많이 발생하고 있습니다.
        Near Real Time 배치는 처리건수가 많지는 않지만 select for update를 사용하는 경우가 많은데
        바로 이것이 문제 입니다.

그게 스미스랑 무슨 상관이지?
  그 PM 은 우리가 물어보기도 전에 배치프로그램과 스미스 요원과의 연관성을 설명 하였다.
그의 주장은 영화 메트릭스 1~ 3편을 보면 스미스가 지속적으로 upgrade 되는 것을 볼수 있는데 그것은 아래의 3가지로 설명이 가능하다는 것이다..
1편에서의 돌연변이 --> 매트릭스의 아버지격인 컴퓨터 아키텍쳐의 통제에서 벗어난 행동을 한다.
                                 이것은 하루에 N 번 도는 배치에 해당한다.

2편에서의 복제기능 --> 다른사람을 스미스로 복제 해버린다.
                                 이것은 시간당 한번 실행되는 배치에 해당한다.

3편에서의 SuperMan --> 자유자재로 날아다니는 것은 물론이고 드디어 매트릭스를 벗어나 인간세계(Zion) 에 까지 영향을 미친다. 이것은 Near Real Time 배치에 해당하며 드디어 온라인 프로그램과의 충돌을 의미 한다는 것이다.

  우리는 그 PM의 이야기를 듯고 한동안 웃음바다가 되었다. 아주 멋진 비유이다.
필자가 생각해보아도 요즘은 Near Real Time 배치가 대세가 되었다.
사람의 욕심은 끝이 없어서 각종 하드웨어와 소프트웨어의 성능이 좋아질수록 거의 실시간으로 배치가 실행되고 배치의 결과물도 아주 짧은 시간에 나오기를 원하는 것이다. 그러다 보면 온라인 프로그램과의 충돌이 자주 일어날수 밖에 없다.

최대의 실수
  아래의 짧은 이야기가 오늘 최대실수가 될줄이야....
필자 : "하지만 스미스가 있으면 The One(Neo) 도 있습니다."
PM : "네? 무슨 말인지 설명해 주시죠."
허걱 술자리에서.. 칠판이나 SQL PLUS 등도 없는데 설명을 해야만 하다니....
드디어 필자가 그 PM 의 마법에 걸려들고야 말았다. 

아래는 예제는 필자의 이야기를 증명한것이다. 물론 회식 자리에서는 전부 말로 설명 하였다.

정말 배치프로그램은 무한정 기다려야 하나?
  배치프로그램에서 최초의 SQL은 Select For Update 문이고 이것은 온라인 프로그램의 Update 문과 같은 테이블을 사용한다고 가정하자. 또한 온라인 UPDATE 의 WHERE 절이 배치 SQL 의 WHERE 절에 속하거나 걸쳐 있다면 동시에 온라인과 배치가 수행될수 없는 구조이다. 
혹자는 SELECT FOR UPDATE 에 WAIT N 이나 NOWAIT 옵션을 주면 되지 않냐고 반문할지 모른다. 물론 그렇게 하면 무작정 기다리지는 않는다. 하지만 온라인 프로그램의 UPDATE 문이 3 초안에 COMMIT이나 ROLLBACK 되지 않으면 Select for Update wait 문에서 아래의 예제처럼 에러가 발생한다.

위의 에러는 해당 사이트에서 자주 발생 된다고 한다.

해결책 : The One IS SKIP LOCKED Option
  이렇게 에러가 발생하게 되면 배치프로그램의 EXCEPTION 이 매우 지저분 하게 된다.
EXCEPTION 에서 특별 처리를 하거나 아니면 다음주기(1~10분)을 기다렸다가 다시 시도 해야 되는데 이때에도 온라인 프로그램이 UPDATE 하고 있지 않기를 바라는수 밖에 없다.
너무나 억울하지 않은가?
다른세션에서 EMP 테이블을 1건 UPDATE 했을뿐인데 부서번호 20번에 해당하는 전체건에 대하여
SELECT FOR UPDATE 를 할수 없는것이다.

그렇다면 이문제를 어떻게 해결할수 있겠는가?
ORACLE 11g 를 사용한다면 SKIP LOCKED 옵션을 사용할수 있다.

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20
 7788 SCOTT      ANALYST    7566 1987-04-19    3000.00               20
 7876 ADAMS      CLERK      7788 1987-05-23    1100.00               20
 7902 FORD       ANALYST    7566 1981-12-03    3000.00               20
     
  환상적이지 않은가?
LOCK 이걸린 7566 을 제외한 20번 부서 전체건에 대하여 성공적으로 LOCK 을 발생시켰다.
이기능을 2003 년에 발견하였지만 undocumnted 기능으로 분류되어 있었으므로 그때는 명시적으로 이기능을 사용하지 않았다. 하지만 여러분이 10g 에서 AQ(Advanced Queuing) 기능을 사용하고 있고 dequeue 옵션으로 no_wait 을 사용했다면 여러분의 의지와는 상관없이 오라클은 SKIP LOCKED 옵션을 사용한것이다.

결론 :
11g 에서 드디어 SKIP LOCKED 이 11g 매뉴얼에 모습을 드러내었다.
이것은 대단한 Upgrade 이다. 특히 아래와 같이 천건 혹은 만건씩 처리되는 상주(Near Real Time)배치 SQL 에서 말이다.

온라인 프로그램에서 1건이 UPDATE 되었다고 해도 나머지 999 건은 처리가 될것이므로 ...

주의사항 : 9i 및 10g 에서 명시적으로 SKIP LOCKED 옵션을 사용하면 안된다.
               기능을 숨겨 놓은데는 다 이유가 있다. (NEO 가 1편부터 날라다닌것은 아니다.)
               술자리에서는 공장 이야기를 하지말자. NEO 가 있다는 것을 증명하기가 너무 힘들다...^^

PRO * C 나 PL/SQL 에서 많은양의 데이터를 Cursor For Loop로  선언한후를 건건이 Update/Delete 등을 처리하는 경우가 많이 있다.
집합처리가 유리하겠지만 그것이 불가능한 경우나 집합처리가 가능하지만 SQL 이 너무 길어지는 경우는
유지보수의 관점에서 Cursor For Loop 를 사용해야 한다.
하지만 많은 개발자들이 일반적으로 Cursor For Loop 내에서 아직도 PK 인덱스나 Unique 인덱스를 사용하여
DML 문을 실행하고 있다.  
오늘은 Cursor for Loop 처리시 개발자들이 일반적으로 코딩하는 방식의 비효율성을 설명하고 Solution 을 제시한다.  
아래는 테스트용 테이블및 인덱스 생성 스크립트 이다.

1.아래는 Curso For Loop  를 사용하여  건건이 Update 하는 가장 흔하게 볼수있는 PL/SQL 패턴이다.

위 PL/SQL 의 ELAPSED TIME 은 11.80 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.

2.SOLUTION
  

위 PL/SQL 의 ELAPSED TIME 은 10.70 초 이며 이수치는 10번을 실행하여 평균값을 취한것이다.
물론 테스트를 정확히 하기위하여 각각의 테스트시에 buffer cache 를 flush 하였다.

PK 인덱스를 이용한 UPDATE 보다 Current of 를 사용한 Update 가 10% 정도 빠른걸 알수 있다.
그렇다면 10%의 차이는 어디서 오는가?

비밀은 v$sqlarea 에서 수행된 SQL 을 보면 알수 있다.

결과 :
---------------------------------------------------------------------------
UPDATE /*+ current_of_test */ T1 SET SCORE = SCORE + 0 WHERE ROWID = :B1

위의 결과에서 처럼 WHERE CURRENT OF 를 사용하면 ROWID 를 이용하여 Accsess 하는것을 알수 있다.
Rowid Access 는 오라클에서 가장 빠른 Access 방식이다.
주의할 점은 WHERE CURRENT OF 를 사용하려면  Main Cursor 가 반드시 SELECT FOR UPDATE 문이어야
한다는 것이다.
하지만 FOR UPDATE 가 없는 커서에서도 아래처럼 사용하면 같은 효과를 누릴수 있다.

결론:
다시한번 기본(SQL 및 PL/SQL)이 얼마나 중요한지 알수 있는 테스트 이다.
Where Current of 를 모르는 사람은 많이 없을것이다.
하지만 Where Current of 절을 사용하면 Index Access에 의한 부하를 없앨수 있다는 사실을 아는사람은
의외로 많지 않다.
여러분의 프로그램이 이런환경에서 PK 인덱스를 사용하도록 코딩이 되어 있다면 지금 당장 달려가서
프로그램을 수정하라고 말하고 싶다.