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

고객사의 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 을 사용하는 것이다.