1. 首页
  2. IT资讯

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

/****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-PC123                     7264:4756                WORKGROUP123-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/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:℅傍ㄖ免沦陷dε鬼,转转请注明出处:http://www.cxybcw.com/192922.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code