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

最新推荐文章于 2023-09-04 15:20:18 发布

clg10051

最新推荐文章于 2023-09-04 15:20:18 发布

阅读量209

点赞数

文章标签：数据库

/****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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-758109/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-758109/