Resumable Space Allocation

[출처] http://blog.naver.com/kenzo143?Redirect=Log&logNo=50003401260

(V9I) ORACLE 9I New Feature : Resumable Space Allocation
===================================================================

Purpose
-------
Oracle 9i에서 제공되기 시작한 이 기능을 이용하게 되면 out of space로 인한 오류를 방지 할 수 있게 된다. 많은 량의 data를 처리하는 문장을 수행하던 중 rollback segment나 temp segment, table, index segment의 확장이 필요한 상태에서 가용한 space를 할당 받을 수 없게 되면 이 명령은 rollback 되고 오류 메세지를 발생시킨다.  만일 이 문장으로 변경되진 자료가 많다면 전반적으로 많은 시스템 자원을 소모하게 되므로 다른 작업에게도 오류나 성능 저하 문제를 불러 오게 된다.
이 기능을 이용하게 되면 이렇게 수행중인 작업이 out of space상황에서 오류 처리를 일정 시간동안 유보시키게 되며  DBA가 이 시간 내에 적절한 조치를 하게 되면 유보되었던 작업이 자동적으로 진행 될 수 있게 된다. 즉, 새로운 Space allocation이 발생되어야 할 시점에 에러를 바로 발생하지 않고 어느정도의 Time 을 준뒤 Admin에게 이를 알림으로써 수행중인 Transaction rollback 되지 않고 계속적으로 진행할 수 있도록 하는 기능이다.

Explanation
-----------

1. resumable statement가 유보되는 조건
  Out of space condition
  max extents reached condition
  space quota exceeded condition

 

2. 필요한 system privilege
  이 기능을 사용 할 user는 resumable system privilege를 필요로 한다.

 

  SQL> grant resumable to scott;      

           

3. Enable Session Resumable Mode
  resumable tx를 수행하기 위한 session은 resumable mode로 전환되어져야
 한다.

 

  Alter session enable resumable [TIMEOUT timeout] [Name name];

 

  만일 특정 user를 항상 resumable mode에 두도록 하기 위해서는 아래와 같이 logon trigger를   

  작성하여 이용할 수 있다.

 

    SQL> create or replace trigger logon_set_resumable
     2  after logon
     3  on scott.schema
     4  begin
     5  execute immediate 'alter session enable resumable timeout 1200';
     6  end;
     7  /

 

4. Disable Session Resumable Mode

  Alter session disable resumable;

 

5. 유보중인 resumable statement

   만일 수행중인 resumable statement가 유보중에 있다면
 
  1) alert log file에 ORA-30032 오류가 발생
  2) after suspend 으로 system event를 작성하여 두었다면 이 trigger가
    수행이 됨.
 
6. dictionary view
  dba_resumable
  만일 space allocation 오류로 인하여 유보중에 있는 session 발생시
  dba_resumable view의 error_number, error_msg에 그 에러 번호나 메세지를
  확인해 볼 수 있다.
 
7. resumable Operations
  sort작업을 갖는 query
  DML
  DDL (CTAS, Create index, Alter index rebuild, Alter table
  {move, split} partition,)
  import/export
  SQL*Loader
 
8. Changing timeout
  exec DBMS_RESUMABLE.SET_TIMEOUT(...);
  exec DBMS_RESUMABLE.SET_SESSION_TIMEOUT(...);

 

9. after suspend system event trigger
  관리자는 resumable operation발생시 적절한 조치작업을 after suspend
system trigger에서 처리할 수 있다.
  이 trigger는 sys user로 작성되어져야 한다.
 
  ex)
  create or replace trigger resumable_default_timeout
  after suspend on database
  begin
     DBMS_RESUMABLE.SET_TIMEOUT(3600);
  end;

 

10. canceling resumable tx
  DBA는 아래의 procedure를 이용하여 resumable tx를 진행 하고 있는
session을 취소시킬 수 있으며 대상 session은 ora-1013을 만나게 된다.

  DBMS_RESUMABLE.ABORT(sid#);

 

Example
--------
다음은 Resumable Space Allocation Operations 을 설정하기 위해서는 RESUMABLE 을
ENABLE 시키고 또는 DBMS_RESUMABLE package를 이용한다.

1) RESUMABLE system privilege 부여
   SQL>  connect system/manager
   Connected.

   SQL> grant resumable to scott;
   Grant succeeded.

2) session level에서 RESUMABLE enable 시키기
   SQL> alter session enable resumable;
   Session altered.

   This can be set automatically through an AFTER LOGON trigger.

   SQL> create or replace trigger logon_set_resumable
     2  after logon
     3  on scott.schema
     4  begin
     5  execute immediate 'alter session enable resumable timeout 1200';
     6  end;
     7  /

   Trigger created.

3) 생성한 TEST_RESUMABLE table 에 insert 작업
-> insert 시에 hang 현상 발생(transaction rollback은 이루어지지 않는다.)
-> alert.log에 suspend message 확인
-> 만약 설정한 timeout 초과시 에러 발생되면서 transaction rollback

 a. Displaying the DBA_RESUMABLE view(DBA_RESUMABLE view에서 suspend 확인)

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, SUSPEND_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

      USER_ID SESSION_ID STATUS    START_TIME           SUSPEND_TIME
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 SUSPENDED 03/14/01 10:49:25    03/14/01 11:14:17
   insert into test_resumable select * from test_resumable
           1631
   ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE

   b. In alert.log file(alert.log에서 message 확인)

      Wed Mar 14 11:14:17 2001
      statement in resumable session 'User SCOTT(54), Session 9, Instance 1' was
      suspended due to
       ORA-01631: max # extents (5) reached in table SCOTT.TEST_RESUMABLE

 c. The statement may issue the following error when the timeout set for the
      session has expired(timeout 초과시 transaction rollback 되면서 에러 발생)

      SQL> insert into test_resumable values (1);
      insert into test_resumable values (1)
               *
      ERROR at line 1:
      ORA-30032: the suspended (resumable) statement has timed out
      ORA-01536: space quota exceeded for tablespace 'EXAMPLE'

4)  The DBA now knows why the session hangs, and needs to find which action to
   take to alleviate the ora-1631 error(DBA는 timeout 이 발생하기 전에 에러 발생)

   SQL>  connect system/manager
   Connected.

   SQL> alter table scott.test_resumable storage (maxextents 8);
   Table altered.
 
   SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

      USER_ID SESSION_ID STATUS    START_TIME           RESUME_TIME 
   ---------- ---------- --------- -------------------- --------------------
   SQL_TEXT
   -------------------------------------------------------------------------
   ERROR_NUMBER
   ------------
   ERROR_MSG
   -------------------------------------------------------------------------
           54          9 NORMAL    03/14/01 10:49:25    03/14/01 11:24:02
   insert into test_resumable select * from test_resumable
              0

5) If the session does not need to be in resumable state, the session can
   disable the resumable state(더이상 resumable 기능 사용하지 않을 경우 disable 시키기)

   SQL> alter session disable resumable;
   Session altered.

   SQL> select user_id,SESSION_ID, STATUS, START_TIME, RESUME_TIME,
     2         SQL_TEXT, ERROR_NUMBER, ERROR_MSG
     3  from dba_resumable;

   no rows selected

Reference Document
------------------
Note. 136941.1 Using RESUMABLE Session to Avoid Transaction Abort Due to Space Errors
    

 

From Technical Bulletins