Sequences NextVal과 Currval

 

       

 

Sequence 는 자동으로 증가 하기때문에 학번등에는 잘사용이 안된다.

Memoery Cache사용 할경우 안 한경우보다 성능면 에서는 뛰어나다. 하지만 갑자기 정전 및 Oracle이 멈춘 다시 시작 할경우 Memoery에 올라간 그 이후 값부터 시작(Gap이생긴다)

 

 

 

Sequence 예제

SQL> create sequence empas_seq
    start with 1
    increment by 1
    maxvalue 1000000000;

Sequence created.

 

SQL> select empas_seq.currval from dual ;
select empas_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence EMPAS_SEQ.CURRVAL is not yet defined in this session

 

Error:  ORA 8002 
Text:   <name>.CURRVAL is not yet defined in this session 
-------------------------------------------------------------------------------
Cause:  Sequence CURRVAL was selected before sequence NEXTVAL was referenced.
Action: Select NEXTVAL from the sequence before selecting CURRVAL.

    
SQL> select empas_seq.nextval from dual ;

   NEXTVAL
----------
         1
SQL> select empas_seq.currval from dual ;

   CURRVAL
----------
         1
         
         
SQL>alter sequence empas_seq
    increment by 2
    nocycle
    maxvalue 10000
    minvalue 0 ;
Sequence altered.

 

If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default. 

 

SQL> select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY,          

        CYCLE_FLAG, CACHE_SIZE, LAST_NUMBER from user_sequences ;

SEQUENCE_NAME      MIN_VALUE  MAX_VALUE INCREMENT_BY C CACHE_SIZE LAST_NUMBER
----------------- ---------- ---------- ------------ - ---------- -----------
EMPAS_SEQ             0      10000            2      N         20   3

SQL> conn / as sysdba
Connected.

 

Sequence 를 shared pool 에 고정 하기
SQL> execute dbms_shared_pool.keep('SCOTT.EMPAS_SEQ','Q') ;
PL/SQL procedure successfully completed.

 

Sequence 를 shared pool 에 고정 해제 하기

SQL> execute dbms_shared_pool.unkeep('SCOTT.EMPAS_SEQ','Q') ;
PL/SQL procedure successfully completed.

관련 문서

No. 11446

CACHING ORACLE SEQUENCES
========================

  오라클 sequence 를 CACHE option 을 enabled 시키고 사용하는 application 에서
가끔 몇몇 값들의 번호가 skip 된것을 볼 수 있다. 이제 왜 sequence 번호가
skipped 되고 그 현상을 최소화할 수 있는 방법은 무엇인지 알아보자.
  
Non-CACHE and CACHE Mode

  non-cache 모드를 사용할 때에는 'nextval' 을 요청하는 sequence 의 현재값은
'increment' 로 지정된 값만큼 증가되는 새로운 값을 얻을 수 있다. 예를들어
현재값이 '0', increment 값이 '1', 그리고 no cache 라면 현재값은 '1' 이 될
것이다. 
  그러나, cache 모드를 사용한다면 'nextval' 을 요청한 sequence 의 현재값은
increment 값과 cache 의 배수만큼 증가된 값을 얻게된다. 그리고, 다음번 nextval
가 호출될때는 반드시 cached sequence 값이 사용되며 이들 중 이전 값보다
increment 값만큼 증가된 sequence 값을 return 한다. 다시 말해 마지막에 도달할
때까지 번호는 cache 에서 return 된다. cache 값이 5 이고, increment 값이 1 인
다음의 표를 살펴보자.

         Number Returned     Sequence Current      Cache Current
*start*       *none*               0                   *none*

1st access      1                  5                      1
2nd access      2                  5                      2
3rd access      3                  5                      3
4th access      4                  5                      4
5th access      5                  5                      5
6th access      6                 10                      6
7th access      7                 10                      7

만약, cache 가 절대 손실되지 않는다고 가정한다면 cached sequence 값은 항상
non-cached 그것과 같을 것이다. 그러나, sequence cache 는 다른 cache 정보와
마찮가지로 shared pool 에 보관되어지기 때문에 충분히 자주 사용되어지지 않는다면
shared pool 로부터 aged out 되어질 수 있다. 또, instance shutdown 의 경우에도
cache 값을 잃게 된다. 위의 두 경우 특히, 두번째의 경우 때문에 application 에서
skip 되어지지 않는 sequence 를 사용해야 하는 상황이라면 반드시 non-cached
sequence 를 사용해야 한다.
  위의 표는 데이터베이스의 shutdown 이 발생할 경우 다음처럼 반복될 수 있다.

         Number Returned     Sequence Current      Cache Current
*start*      *none*               0                   *none*
1st access     1                  5                      1
2nd access     2                  5                      2
                  *cache aged out* 
3rd access     5                 10                      5
4th access     6                 10                      6
                  *shutdown*
5th access    10                 15                     10
6th access    11                 15                     11
7th access    12                 15                     12

  이러한 aging problem 을 처리하기 위해서 shared pool 에 sequences 을
PIN'd 시켜주는 DBMS_SHARED_POOL.KEEP 프로시져가 있다. 이는 물론 shutdown
의 경우에는 cache 를 보호할 수 없겠지만 sequence 값들이 aged out 되어
skipped 되는 것을 어느정도 피할 수는 있다. 그러나, sequence 가 자주
사용되어지지 않는 경우라면 이는 반드시 PIN'd 되어야 하는데 이러한 경우
sequence 를 cache 함으로 얻을 수 있는 장점은 없는 셈이다.
  sequence 를 caching 한다는 것은 오라클이 sequence 의 다음값을 얻으려 할때
disk 를 access 하는 대신에 shared pool 즉 memory 의 한부분을 바로 이용하므로
성능향상을 꾀할 수 있다는 것이다. 따라서, sequences 가 빈번히 사용되는 경우라면
시간을 절약할 수 있겠지만 그렇지 않은 경우라면 차라리 cache 를 disabled 시키는
것이 낫다고 할 수 있다.
  오라클 7.2 이하 버전에서는 sequence cache 가 rowcache(dictionary cache)
에 보관되어졌지만 7.3 이후 버전에서는 sequence cache 가 library cache 에
보관된다. 그래서, 7.3 이나 8.x 에서는 sequence cache 는 보다 쉽게 aged out
될 수 있다. 따라서, 필요한 경우 DBMS_SHARED_POOL.KEEP 프로시져를 사용할 수
있다. 예를 들어 sequence 이름이 'SEQ' 인 경우 
dbms_shared_pool.keep('seq', 'q') 로 해당 sequence 를 shared_pool 에
PIN'd 시킬 수 있다.

 

 

Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last reference to NEXTVAL.

 

Note that before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

 

Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence only once:

 

A sequence can be accessed by many users concurrently with no waiting or locking.

 

!! Sequence 와 Table 은 아무런 관계가 없다..

[ 많은 분들이 오해가 있는듯... table 에 sequence 가 엮인것으로... ]

관계를 맺는것은 App 단에서 하는것이다.

[ 즉 호출해서 해당 테이블에 row insert 시에 sequence 를 호출해서 쓰면 된다.]

 

 App에서 Sequence 를 사용시에 Nextval 을 하는 것은

1. sesesion 을 맺은 후 currval 을 사용하기 위해서는 우선 먼저 nextval 을 사용함으로

   sequence 를 초기화 해야 하기때문에.

2. sequecne 가 증가 하는 조건은 nextval 조회시 이다..

    duplicate 발생할 여기가 있는듯....

결론적으로 app에서는 nextval 을 가져다 쓰면 된다.!!!

 

 

!! 권한 분리 환경 이라면 [ Object Owner 와 DML User 의 분리 ]

즉 Sequence Onwer 와 Sequecne 를 사용하는 [ App User] 가 다른 환경 이라면

App User 를 위해서 아래와 같이 추가 작업을 한다.

create public synonym  empas_seq for empas_seq ;

grant select on empas_seq to app_user ;