======================================================================
ORA-00030 : ALTER SYSTEM KILL SESSION에 대하여
======================================================================

◈ 현상
  사용자는 다음과 같은 상황에서 session 을 kill 하려는 시도를 하게 된다.

  1. os 에는 process 가 존재하지 않지만, v$session 에는 active 로 존재하고 있을 경우
  2. shadow process 는 살아 있는데, client machine 을 rebooting 한 경우
  3. session 이 걸고 있던 lock 을 release 해야 할 경우
  4. OS 나 Oracle 의 자원을 지나치게 많이 사용하여 성능을 저하시키는 process

  그런데, alter system kill session ('sid, serial#'); 후에 다음과 같은 에러가 발생할 경우가 있다.

  ora-00030, 00000, "user session ID does not exist"
  // *Cause:  The user session id no longer exists, probably because the
  //          session was logged out.
  // *Action: Use a valid session ID.

◈ 원인
  kill session을 할 수  없는 이유는 PMON이 이미  이 session을 delete하고 있는  중이기 때문이다. 즉,
  PMON 이 dead session 을 clean-up 하고 있는 중에는 serial number의 값이 증가한다.

  문제는  PMON이 process를  kill하는 시간인데,  transaction의 크기에  따라, PMON의  rollback 시간이
  결정된다. 먼저 PMON은 dead process를 찾아내어, 이 process가 사용한 resource 를 release하는  시도를
  한다. PMON은 계속 이 작업을 시도하다가  마침내, free buffer의 부족으로 더 이상  resource를 free-up
  하지 못하게 된다. 이 때, 이 process를 delete하고 있다는 message를 trace file에 출력하는데,  이것은
  process를  delete하는 데  필요한 resource(data  cache 내의  free buffer)의  부족으로 위의  작업이
  지연되고 있다는 의미이다.

◈ 조치
  PMON이 process 를  clean-up 할 때  걸리는 시간은, 5분에서  24 시간까지 소요될  수 있다. 문제는  이
  process가 hold 하고  있는 lock으로 인해  특정 작업이 수행되지  못하는 데 있다.  MTS 를 사용할 때는
  configuration MTS  setting, sqlnet.expire_time  사용)에 따라  다르지만, clean-up  작업을 하는데 72
  시간 이 소요된 경우도 있다.
  아직까지는 PMON이 작업을 마칠 때까지 기다리는 방법 또는 db를 restartup하는 방법 밖에는 없다.

  --- PMON 의 작업

  PMON은 network failure 나 기타의 원인으로 생긴 old process connection을 clean-up하는 역할을  한다.
  그런데, PMON 은 clean-up 해야 하는 connection  중에 정해진 개수 만큼의 transaction 을 rollback  할
  수 있는데, 이 값은  initSID.ora 의 cleanup_rollback_entries(default =  20) 에 의해 결정된다.  예를
  들어,  1000  개의 uncommitted  update가  있다면, 일정한  시간마다  cleanup_rollback_entries의 개수
  만큼의 record만 rollback 할 수 있으므로 이 작업 동안에 lock 은 그대로 유지된다.

  PMON 은 위의 작업 이외에 DB maintenance 역할이 있으므로, 위의 rollback 이 비교적 빠르게 처리  되지
  못할 수도  있다. 이러한  rollback을 빠르게  처리하기 위하여  cleanup_rollback_entries 를 늘릴 수도
  있다. 그러나, 그  만큼 일정시간 동안  PMON의 작업이 많아지게  되므로, 다른 사용자들의  작업 요청이
  느려지게 되는 trade-off가 있으므로, 신중히 고려한 후에 수정하는 것이 바람직하다.

  alter system kill session 에 의해서도 위와 같이 rollback 이 이루어지는데, 이 session 이 완전히
  clean-up 되기 전까지 v$session, v$process에 남아 있게된다.

  --- ALTER SYSTEM KILL SESSION 을 하기 전에 ...

  kill session 을 원할 경우는 다음의 순서대로 작업하는 것이 좋다.

  1. kill the user process first
  2. wait for 3 - 4 minutes
  3. query v$session
  4. if any information find in v$session, query v$lock
     like
     select count(*) from v$lock where SID ='sid';
  위의 count(*)  가 0  이 아니라면,  아직 PMON  이 rollback을  끝내지 못한 경우이므로 다시  얼마후에
  v$lock 을  조회하여 lock  의 개수가  감소하였는지 반복적으로  확인한다. 만약,  이 값이 전혀 변하지
  않았다면, ALTER  SYSTEM KILL  SESSION 을  수행하고 v$session,  v$lock을 query  하여 변화가  있는지
  확인하여  변화가 있다면,  좀 더  기다린다. 그래도,  v$lock 의  count(*) 가  0 이  되지 않을  경우,
  마지막으로 수행할 수 있는 유일한 방법은 instance 를 restartup 하는 것이다.

 
======================================================================
ORA-00054 : TABLE에 TRANSACTION이 종료되지 않은 경우
======================================================================

  TABLE 을  DROP 하려고  할때 그  TABLE에 TRANSACTION이  종료되지 않아  ORA-54 ERROR가 나오는 경우가
  있다. DB를 RESTART하면 되지만 더 효율적인 해결 방법은 다음과 같이 할수 있다.

  * 참고 : Serial Number 가 Negative 인 경우 그 값에 65536 을 더해야 함.

  rem sqlplus system/manager
  rem
  rem

  prompt Enter table name
  accept tname CHAR
  col type format a6
  col object_name format a20

  select  a.sid,a.serial#,b.type,c.object_name
    from  v$session a,v$lock b,dba_objects c
   where  a.sid=b.sid and b.id1=c.object_id
     and  b.type='TM' and c.object_name=upper('&tname');

  Prompt Enter session ID(SID) ?
  accept sid
  Prompt Enter serial number(serial#) ?
  prompt -- if serial number < 0,
  prompt -- then serial number #=Serial number + 65536
  accept serial

  alter system kill session '&amp;sid,&amp;serial'

 
==================================================================================
ORA-00060 : DEADLOCK과 INITRANS (같은 TABLE내의 다른 범위의 DATA처리시 ORA-60)
==================================================================================

 deadlock에  관한  일반적인  사항은  <Bul:11742>에  정리되어  있다.  같은  data를  동시에   변경하는
 transaction의 경우  deadlock이 발생하는  것은 application  logic을 수정하여  해결해야 하는  경우가
 대부분이다.

 그런데 같은 table에 대해서 동시에 수행되는  transaction이 각자 서로 다른 data를 처리하는  경우에도
 ora-60(deadlock detected while waiting for resource)이 발생할 수 있다.

 예를 들어 한 transaction은 A table의 1월 data를 처리하고, 동시에 다른 transaction은 같은 A  table의
 2월 data를 처리하는 것과 같은 경우이다.

 이러한 경우에도 initrans가  작게 설정되어 있으면,  ora-60이 발생할 수  있는데, 이 자료에서는  이와
 같이 다른 data를 처리하는 transaction들 사이에서의 ora-60이 발생하는 경우와 조치사항을 확인한다.

  1. transaction entry에 대해서
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 table이나 index에 포함된 모든 block에 update/delete/insert와 같은 dml을 수행하기 위해서는 일단  그
 block에 transaction정보를 저장시킬 transaction entry를 확보한 후에 원하는 작업이 수행가능하다.

 이  transaction entry의  크기는 os  dependent하기는 하나  대부분 23  bytes이며, table이나   index의
 initrans  option에 의해,  미리 확보되는  block당 transaction  entry의 갯수가  결정된다. default는
 table이 1, index가 2이다.

 이  transaction  entry가  특정 transaction에  할당되면  그  transaction이 commit이나   rollback되기
 전까지는 다른 transaction에서  사용할 수 없다.  같은 block에 다른  transaction이 dml을  수행하려면,
 남은 공간중에서 23 bytes의 transaction entry를 새로 할당하거나, 공간이 없으면 앞에서 먼저 사용중인
 transaction이 commit/rollback되기를 기다려야 한다.

  2. ORA-60이 발생하는 경우
  ~~~~~~~~~~~~~~~~~~~~~~~~~
 deadlock을 유발시키는 transaction이 서로간에 완전히 다른 data(같은 table)를 처리하더라도 그  data가
 같은 block에  함께 들어가  있는 경우라면  ORA-60이 발생할  수 있다.  즉, transaction  entry를  잡는
 과정에서  block내에 남은  space가 부족한   경우, 서로  상대방의 transaction이  종료되기를  기다리는
 deadlock이 발생가능하다는 것이다. 아래에 실제 예를 들어 자세한 발생 시나리오를 정리하였다.

① ORDER table은  날짜별로 data가 추가,    변경, 삭제되는 100만건  이상의 data를 가진  table이다. 이
 table에   대해서   월별로   통계작업을  수행하는데,   6개월씩   처리하기   위해  6개  transaction을
 동시에 수행하였다. 즉, T1은 1월 data, T2는 2월 data, T6는 6월 data를 처리하는 식이다.

② ORDER table은 initrans값이 1로 지정되어 있고 현재 1000개의 block이 이 table에 할당되어 있다.

③  100번지  block에  2월,  3월,  5월  data가  함께  저장되어  있다.  200번지  block에는  2월,   3월
 data가 저장되어 있다.

④ T2  transaction이  100번지  block에  이미  확보되어  있는  1개의  transaction   entry를  사용하여
 transaction정보를 저장하였다. 그리고, 2월달 data에 대한 작업을 수행하였다.

⑤ T3 transaction이 200번지 block에 initrans  1에 의해 확보되어 있는 23 bytes의  transaction entry를
 이용하여 transaction정보를 저장한 후 3월달 data에 대한 처리를 수행하였다.

⑥ T2 transaction이 2월달 data중 나머지 부분을  처리하기 위해 200번지를 access하여 23 bytes의 T2  를
 위한   transaction     entry를   확보하려고      하였으나,   block에     남은   공간이      없어서,
 T3 transaction이  commit할때까지   기다린다.   5번  단계에서,    initrans에  의해  미리    확보되어
 있는 공간을  사용하는 T3 transaction이 종료되면, 그 부분을 T2가 사용할 수 있게 되는 것이다.

⑦ T3  transaction도  100번지에  있는 3월  data를  처리하기  위해  100번지내에 transaction   entry를
 추가적으로   확보하려 하였으나,    공간이 없어서,    마찬가지로  미리   100번지 block을    사용하고
 있는 T2 transaction이 종료되기를 기다리게 된다.

⑧ 6과 7상황에  의해 T2와 T3  transaction은 서로 상대방이  종료되기를 기다리는, deadlock이  발생하게
 되므로   deadlock상황을  유발시킨     T3   transaction이   ORA-60을    발생시키면서   종료   되고,
 T3  transaction은   rollback된다.  200번지에   확보한    transaction   entry부분도  반환하여   다른
 transaction이 사용가능한 상태가 된다.

⑨ 8번에서 release된 200번지  block내의 transaction entry 23  bytes를 6번 단계에서 기다리고  있던 T2
  transaction이 확보하고 작업을 진행한다.

  3. deadlock을 피하기 위한 방법
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

결론적으로,  같은  table에 대해서  다른  data를 처리하는  transaction이라  하더라도 동시에  수행하는
transaction이 많은 경우라면 initrans 값이 작은 경우 ora-60이 발생할 수 있게 된다.

하나의 table에 대해서  performance등의 이유로 동시에  다른 data를 처리하는  transaction을 수행하고자
한다면,  table의  initrans  값을  크게하여,  하나의  block에  여러  개의  transaction이  dml  처리를
수행하더라도 space가 부족하여 기다리는 상황은 없도록 하여야 한다.

initrans를 n으로 지정한다면 23*n bytes가 항상 transaction entry로 미리 확보되어 있는 것이다.  이렇게
transaction entry로 초기에 확보된  공간은 data를 저장할 수  없는 공간이 되므로, 너무  크게 하는 것은
space 낭비가 초래된다. 하나의 block에 대해서  동시에 여러 transaction이 처리되지 않는 경우라면  미리
확보된  transaction entry는  사용도 되지  않고 낭비되어,  full table  scan 등의  작업에 성능  악화만
초래하게 된다.

initrans값은 하나의 table에 대해서 동시에  처리하는 transaction의 갯수 이하로 지정하도록  한다. 해당
table에  대한  동시  transaction의  갯수만큼 initrans를  지정하면  앞에서  설명한  상황의 deadlock은
발생하지 않는 것이 보장되나 space를 고려할 때 그 보다는 약간 작게 하는 것이 일반적인다.

initrans는 table이나 index에  대해서 create나 alter문장시  지정, 변경이 가능하나,  alter의 경우 이미
확보된 block에는 영향을 미치지 못하므로 export/import를 이용하여 새로 지정하는 것이 필요하다.

다음에 scott.dept table에 대해서 예를 들었다. column정의 storage등은 임의의 값을 예로 사용한 것이며,
initrans도 예로 3을 지정하였다.

  os> exp scott/tiger file=test.dmp tables=dept

  os> sqlplus scott/tiger
  SQL> drop table dept;
  SQL> create table dept (deptno number(2), dname varchar2(10))
    initrans 3
    storage(initial 10m next 2m pctincrease 0);

  os> imp scott/tiger file=test.dmp tables=dept ignore=y