/****oracle11g之v$libcache_locks解決library cache lock及library cache pin的等待者及持鎖者*******/

/******持鎖會話*************/
SQL> create or replace procedure proc_lib
2 authid current_user
3 as
4 begin
5 dbms_lock.sleep(360000);
6 end;
7 /

Procedure created.

SQL> select sid from v$mystat where rownum=1;

SID
----------
197

SQL> exec proc_lib;

/*************等待鎖會話********/
SQL> select sid from v$mystat where rownum=1;

SID
----------
69

SQL> alter procedure proc_lib compile;

/*******處理過程**************/

---定位等待會話的p1,p1raw即object handle,具體含義：參v$event_name
SQL> select * from v$session_wait where sid=69;

SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- --------------------------
69 39 library cache pin handle address 8793426503 000007FF611028C8 pin address 8793274245 000007FF57FCE220 100*mode+namespace 3105948550 00011A7C00010003 3875070507 4 Concurrency 0 398 WAITING 397920007 502079993 0

---定位等待會話的saddr會話地址
SQL> select * from v$session where sid=69;

SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE PORT TERMINAL PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE SQL_ID SQL_CHILD_NUMBER SQL_EXEC_START SQL_EXEC_ID PREV_SQL_ADDR PREV_HASH_VALUE PREV_SQL_ID PREV_CHILD_NUMBER PREV_EXEC_START PREV_EXEC_ID PLSQL_ENTRY_OBJECT_ID PLSQL_ENTRY_SUBPROGRAM_ID PLSQL_OBJECT_ID PLSQL_SUBPROGRAM_ID MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# TOP_LEVEL_CALL# LOGON_TIME LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER BLOCKING_SESSION_STATUS BLOCKING_INSTANCE BLOCKING_SESSION FINAL_BLOCKING_SESSION_STATUS FINAL_BLOCKING_INSTANCE FINAL_BLOCKING_SESSION SEQ# EVENT# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS WAIT_TIME SECONDS_IN_WAIT STATE WAIT_TIME_MICRO TIME_REMAINING_MICRO TIME_SINCE_LAST_WAIT_MICRO SERVICE_NAME SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS SQL_TRACE_PLAN_STATS SESSION_EDITION_ID CREATOR_ADDR CREATOR_SERIAL# ECID
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- --------- ---------- ------------------------------ ------------------------------ ------------------------ ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- -------------- ------------- ---------------- -------------- ----------- ---------------- --------------- ------------- ----------------- --------------- ------------ --------------------- ------------------------- --------------- ------------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- --------------- ----------- ------------ ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ---------------------------------------------------------------- ----------------------- ----------------- ---------------- ----------------------------- ----------------------- ---------------------- ---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ------------- ----------- ---------------------------------------------------------------- ---------- --------------- ------------------- --------------- -------------------- -------------------------- ---------------------------------------------------------------- --------- --------------- --------------- -------------------- ------------------ ---------------- --------------- ----------------------------------------------------------------
000007FF626D16A0 69 471 350999 000007FF624E7390 66 TBL_BCK 25 2147483644 000007FF5ED05E98 ACTIVE DEDICATED 66 TBL_BCK 123-PC\123 7264:4756 WORKGROUP\123-PC 0 123-PC sqlplus.exe USER 000007FF57FCD538 238670938 9kaw06473mp2u 0 2013/4/9 20:32 16777217 000007FF57FCD538 238670938 9kaw06473mp2u 0 2013/4/9 20:12: 16777216 SQL*Plus 3669949024 0 69894 -1 0 0 0 94 2013/4/9 20 405 NO NONE NONE NO DISABLED ENABLED ENABLED 0 VALID 1 197 VALID 1 197 39 280 library cache pin handle address 8793426503 000007FF611028C8 pin address 8793274245 000007FF57FCE220 100*mode+namespace 3105948550 00011A7C00010003 3875070507 4 Concurrency 0 405 WAITING 404969564 495030436 0 SYS$USERS DISABLED FALSE FALSE FIRST EXEC 100 000007FF624E7390 51

---基於上述的object handle定位持鎖會話,mode_held為排它鎖模式
SQL> select * from v$libcache_locks where object_handle='000007FF611028C8' and mode_held=3;

TYPE ADDR HOLDING_USER_SESSION HOLDING_SESSION OBJECT_HANDLE LOCK_HELD REFCOUNT MODE_HELD MODE_REQUESTED SAVEPOINT_NUMBER
---- ---------------- -------------------- ---------------- ---------------- ---------------- ---------- ---------- -------------- ----------------
LOCK 000007FF57FCD9C8 000007FF626D16A0 000007FF626D16A0 000007FF611028C8 00 1 3 0 329

--基於上述的持鎖會話holding_session定位持鎖會話的sid及執行的具體sql
SQL> select sid,(select sql_text from v$sql where sql_id=v$session.sql_id)from v$session where saddr='000007FF626D16A0';

       SID (SELECTSQL_TEXTFROMV$SQLWHERES
---------- --------------------------------------------------------------------------------
        69 alter procedure proc_lib compile

小結：
     1,library cache lock及library cache pin是為了儲存library cache object的一種鎖資源
     2,v$libcache_locks快速定位持鎖及等待鎖的會話及執行sql
     3,處理方案也可以用x$kgllk
        具體參看：http://blog.csdn.net/changyanmanman/article/details/7611758

oracle11g之v$libcache_locks處理library cache lock及library cache pin

相關文章