블랙박스 접근법

프로젝트 실패하는 가장 일반적인 이유 중 하나는 데이터베이스에 대한 실질적인 이해 부족 때문

• 사용하고 있는 기본 툴(데이터베이스)에 대한 기초지식 부족으로 블랙박스처럼 다루게 됨
• 막연한 두려움 : 데이터베이스, SQL, 트랜잭션, 무결성은 "어렵다"
• (필자는) 데이터베이스 보다 자바/C 개념을 배우는 것이 훨씬 더 어려움

데이터베이스 애플리케이션을 구축하고 있다면, 가장 중요한 부분은 데이터베이스임을 인정하고 집중해야 함

하지만 일반적인 개발 시나리오는

• 개발자는 GUI 툴이나 자바와 같이 화면단을 구축하는 데 사용하는 언어를 잘 다룸
• 개발자는 오라클 경험/교육이 전무하고, "데이터베이스 독립적" 강요 받음 (데이터베이스가 어떤 것인지 충분히 알지 못하는데)
• 멋진 화면을 개발 했으나, 성능/무결성/장애 문제에 직면

이런일도...

• 개발은 오라클 7.3 인데, SQL Reference 는 6.0 것을 참조 (트레이스, 튜닝 및 수백가지 추가된 기능들 무시...)
• 개발자는 데이터베이스를 잘 다루지 못해도 된다는 태도는 여전하다 (데이터베이스 공부 하는데 시간을 들이면 안되고, 할 필요도 없다는 주장도 있다)
• 오라클이 가장 확장성이 좋은 데이터베이스라고 해도, 잘못 이용하면 재앙이 될 수 있다.
  • 호두를 호두까기로 망치처럼 내려칠것인가? (가루가 된 호두를 먹거나 손가락을 다치는 등 옳지 않다)

Instrumenting code

• 버그 추적, 성능 모니터링, 레벨들을 측정하기 위해 프로그램 안에 심어놓은 프로그램
• 오라클 데이터베이스는 조금은 무겁게 Instrumenting code가 내부에 담겨 있으며 손쉽게 활용 가능

문제투성이 프로젝트 사례

• 많은 성능 이슈에 직면, 시스템은 많은 트랜잭션을 직렬화(Serializing) 하고 있음 (마치 줄을 서는 것 처럼)
• 3-tier 구조 : JSP - EJB <데이터베이스 독립적 형태로 SQL 수행> - 데이터베이스
• Instrumenting code 부재 등으로 진단이 어려움
• 오라클의 V$ 테이블 진단으로 비트맵 인덱스 확인 (프로세스 플래그 컬럼 : 값<Y,N> / low-cardinality)
• 비트맵 인덱스는 한 개의 키(key entry)가 수백 개 이상의 많은 로우를 가르키며, 변경시 많은 로우가 동시에 락(Lock) 되므로 동시성이 떨어짐

autonomus 트랜잭션

• 세션 내부에 이미 만들어져 있는 트랜잭션과는 분리되고 구별된 서브트랜잭션(subtransaction)을 시작한다.
• 서브트랜잭션은 아주 다른 세션에서 실행된 것 처럼 동작 하며, 부모 트랜잭션에 의해 블로킹될 수 있음, 부모 트랜잭션은 거의 중단됨
• 오류 로깅 메커니즘(error-logging mechanism)으로 사용 가능하며 그 외는 잘못된 것으로 볼 수 있음
• autonomus 트랜잭션은 개발자들에게 공개되서는 안되는 기능이라 믿음 (부정확하게 사용시 데이터 무결성을 해침)

문제투성이 프로젝트 문제점1 데모

SQL> drop table t;

Table dropped.

SQL>
SQL>
SQL> create table t
  2  ( processed_flag varchar2(1)
  3  );

Table created.

SQL> create bitmap index t_idx on t(processed_flag);

Index created.

SQL> insert into t values ( 'N' );

1 row created.

SQL> declare
  2     pragma autonomous_transaction;
  3  begin
  4     insert into t values ( 'N' );
  5     commit;
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

• 데드락(deadlock) 발생, 두개 세션 이었다면 두번째 입력은 단지 블로킹 되었음 (직렬화 이슈)
  • 오라클 내 큐(queue) 관련 내부 기능(AQ)이 있는데, 별도의 프로그램 코드로 직접 구현 함

• 해결책
  • 프로세스 처리 여부 플래그 컬럼에 B*Tree 인덱스 생성
  • 대부분의 값인 'Y' 를 제외하고 관심있는 값인 'N' 에 대해서만 인덱스 생성 : FBI / DECODE(PROCESSED_FLAG, 'N', 'N')

문제투성이 프로젝트 문제점2 데모

• PROCESSED_FLAG = 'N' 인 데이터를 큐에서 꺼내는 처리를 직렬로 수행

-- 한 번에 한 세션만 사용 가능(10g)
SELECT * FROM QUEUE_TABLE WHERE DECODE(PROCESSED_FLAG, 'N', 'N') = 'N' FOR UPDATE;

• 해결책

SQL> drop table t purge;

Table dropped.

SQL> create table t
  2  ( id       number primary key,
  3    processed_flag varchar2(1),
  4    payload  varchar2(20)
  5  );

Table created.

SQL> create index
  2  t_idx on
  3  t( decode( processed_flag, 'N', 'N' ) );

Index created.

SQL> insert into t
  2  select r,
  3        case when mod(r,2) = 0 then 'N' else 'Y' end,
  4        'payload ' || r
  5    from (select level r
  6               from dual
  7        connect by level <= 5)
  8  /

5 rows created.

SQL> select * from t;

        ID P PAYLOAD
---------- - --------------------
         1 Y payload 1
         2 N payload 2
         3 Y payload 3
         4 N payload 4
         5 Y payload 5

-- 해결책 타입1
SQL> create or replace
  2  function get_first_unlocked_row
  3  return t%rowtype
  4  as
  5     resource_busy exception;
  6     pragma exception_init( resource_busy, -54 );
  7     l_rec t%rowtype;
  8  begin
  9     for x in ( select rowid rid
 10                              from t
 11                              where decode(processed_flag,'N','N') = 'N')
 12     loop
 13     begin
 14             select * into l_rec
 15               from t
 16              where rowid = x.rid and processed_flag='N'
 17                for update nowait;
 18             return l_rec;
 19     exception
 20             when resource_busy then null;
 21     end;
 22     end loop;
 23     return null;
 24  end;
 25  /

Function created.

SQL> set serveroutput on
declare
        l_rec  t%rowtype;
begin
        l_rec := get_first_unlocked_row;
        dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
end;

/SQL>   2    3    4    5    6    7
I got row 2, payload 2

PL/SQL procedure successfully completed.

SQL> declare
  2     pragma autonomous_transaction;
  3     l_rec  t%rowtype;
  4  begin
  5     l_rec := get_first_unlocked_row;
  6     dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
  7     commit;
  8  end;
  9  /
I got row 4, payload 4

PL/SQL procedure successfully completed.

-- 해결책 타입2 : SKIP LOCKED (11g)
SQL> declare
  2     l_rec t%rowtype;
  3     cursor c
  4     is
  5     select *
  6       from t
  7      where decode(processed_flag,'N','N') = 'N'
  8        FOR UPDATE
  9       SKIP LOCKED;
 10  begin
 11     open c;
 12     fetch c into l_rec;
 13     if ( c%found )
 14     then
 15             dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
 16     end if;
 17     close c;
 18  end;
 19  /
I got row 2, payload 2

PL/SQL procedure successfully completed.

SQL> declare
  2     pragma autonomous_transaction;
  3     l_rec t%rowtype;
  4     cursor c
  5     is
  6     select *
  7       from t
  8      where decode(processed_flag,'N','N') = 'N'
  9        FOR UPDATE
 10       SKIP LOCKED;
 11  begin
 12     open c;
 13     fetch c into l_rec;
 14     if ( c%found )
 15     then
 16             dbms_output.put_line( 'I got row ' || l_rec.id || ', ' || l_rec.payload );
 17     end if;
 18     close c;
 19     commit;
 20  end;
 21  /
I got row 4, payload 4

PL/SQL procedure successfully completed.

위의 해결책 두가지는 직렬화 문제를 해결 하는데 도움이 되나, 오라클에서 제공하는 Advanced Queuing 을 사용 검토 필요

• 일반적으로 데이터베이스가 애플리케이션의 초석이 되어야 함
• 블랙박스가 망가지면 우리는 "왜 잘 작동하지 않지?" 라며 궁금해 하는 일만 할 수 있을 뿐이다.
• 데이터베이스를 블랙박스 다루듯 하지 말고, 어떻게 일하는지, 어떤 일을 할 수 있는지 알고 모든 능력을 사용하자