ORACLE_008. MONITORING LOCK CONTENTION - Diagnostic

MONITORING LOCK CONTENTION - Diagnostic                              

DIAGNOSTIC

 DBA_WAITER & DBA_BLOCKER를 통해 누가 테이블을 홀딩하고 있고 누가 기다리고 있는지의 정보를 얻을 수 있습니다.  이 뷰를 이용하기 위해서는 CATBLOCK.SQL 을 수행해야 합니다.

            * $ORACLE_HOME/rdbms/admin 에서 찾을 수 있습니다.

 

      TRANSACTION 1

       UPDATE employee SET salary = salary * 1.1;

       //V$LOCK

      TRANSACTION 1

       UPDATE employees SET salary = 1.1;

       //V$LOCKED_OBJECT

 

V$LOCK VIEW

 LOCK TYPE        ID1

 TX                        롤백 세그먼트와 슬롯 번호

 TM                       바뀌기 시작한 테이블의 오브젝트 ID

 

 V$LOCK 뷰의 리소스 ID 1과 부합하는 테이블의 이름을 찾기위해 다음 쿼리를 이용합니다.

 

 SQL> SELECT owner, object_id, object_name, object_type, V$lock.type

    2>    FROM dba_objects, v$lock

    3>    WHERE object_id = v$lock.id1 and object_name = table_name;

 

V$LOCKED_OBJECT VIEW

 LOCK TYPE            ID1

 XIDUSN                  롤백 세그먼트 번호

 OBJECT_ID            변경되기 시작한 오브젝트의 ID

 SESSION_ID          오브젝트를 LOCK한 세션의 ID

 ORACLE_USERNAME

 LOCKED_MODE

 

 V$LOCKED_OBJECT 뷰에 있는 오브젝트 ID와 부합하는 테이블 이름을 찾기.

 

 SQL> SELECT xidusn, object_id, session_id, locked_mode,

    2>    FROM v$locked_object;

       XIDUSN   OBJECT_ID  SESSION_ID  LOCKED_MODE

      ------------   ----------------  ----------------- ------------------------

                    3                2711                       9                               3

                    0                2711                       7                               3

 

 SQL>SELECT object_name FROM dba_object WHERE object_id=2711;

      OBJECT_NAME

       ----------------------

       EMPLOYEE

 

 XIDUSN 의 값이 0이면 XIDUSN 의 값이 0이 아닌 다른 세션에 의해 걸린 LOCK이 WAIT 상태에 있는 세션을 뜻하게 됩니다.

 

UTLLOCKT SCRIPT

 $ORACLE_HOME/rdbms/admin/에 있는 utllockt.sql 스크립트를 이용하는 방법도 좋습니다. LOCK&WAIT 의 상태를 계층적으로 보여주기 때문에 가시적으로 확인하기가 더욱 쉽습니다. UTLLOCKT.SQL 스크립트를 수행하기 전에 CATBLOCK.SQL 스크립트를 SYS권한으로 우선 수행해야 합니다.

 

  WAITING_SESSION        TYPE       MODE  REQUESTED      MODE HELD      LOCK ID1   LOCK ID2

  ---------------------------    ------------  --------------------------------  ---------------------  ---------------- ---------------

  8                                       NONE      None                                    NONE                   0                    0

          9                               TX            Shares (S)                            Exclusive (X)         65547            16

                 7                        RW           Exclusive (X)                       S/ROW-X (SSX)   33554440      2

               10                        RW           Exclusive (X)                       S/ROW-X (SSX)   33554440      2

 

 위 예제에서 보듯이 9번 세션은 8 세션이 TRANSACTION이 끝나길 기다리고 있고 7,10번은 9번 세션을 기다리고 있습니다. (참 쉽죠잉~)

 

RESOVING CONTENTION

 세션을 죽이십쇼!(KILL!!). 그것이 유일한 방법 일 것 입니다. (TRX)가 끝나지 않는다면 말이죠. 이는 (DEAD-LOCK)에서도 마찬가지 입니다.

 

 ALTER SYSTEM KILL SESSION 'SID, SERIAL#';

 

 SID와 SERIAL#을 조회하는 방법입니다.

 

 SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE TYPE='USER';

 

FIN.

REF)Oracle9i Performance Tuning Volume - I