ITPub博客

首页 > 数据库 > Oracle > 0324resumable_timeout library cache lock

0324resumable_timeout library cache lock

原创 Oracle 作者:lfree 时间:2016-03-24 15:32:36 0 删除 编辑

[20160324]参数resumable_timeout和library cache lock.txt

--今天测试环境遇到library cache lock的情况,主要测试磁盘空间很紧张,但是设置了参数resumable_timeout。
--开发通过ctas建立表时,空间不够挂起,估计他程序挂起异常关闭,ctas依旧在后台运行。但是访问到这个表的程序全部挂起。
--通过例子来说明:

1.环境:

SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE tea DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' SIZE 1536K AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SCOTT@book> alter user scott quota unlimited on tea;
User altered.

SCOTT@book> alter session set resumable_timeout=3600 ;
Session altered.

SCOTT@book> create table t1 tablespace tea as select * from dba_objects ;
...

-- 由于我限制表空间tea大小,加上参数resumable_timeout,操作会暂时挂起,等待空间分配。

SYS@book> @ &r/lcl
display  library cache lock problem
no rows selected
display wait library cache lock
no rows selected

2.如果我访问表问题就出现了:
SCOTT@book> select * from dba_objects  where owner=user and object_name='T1';
no rows selected

SYS@book> select * from obj$ where name='T1';
no rows selected

SCOTT@book> select * from t1 where rownum<=1;
--挂起!

SYS@book> @ &r/wait

P1RAW            P2RAW            P3RAW                    P1         P2         P3        SID    SERIAL#       SEQ# EVENT                                    STATE               WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00               1650815232          1          0         80       1353         10 SQL*Net message to client                WAITED SHORT TIME                 2               0
000000007BA1B5C8 000000007BBEB7D0 FFFFFFFF00010002 2074195400 2076096464 1.8447E+19         90        815         85 library cache lock                       WAITING                    35152734              35
00               00               00                        0          0          0        232        597        330 statement suspended, wait error to be cl WAITING                     1567627               2
                                                                                                                     eared

--看到等待事件出现了library cache lock。实际上如果仔细看还出现了statement suspended, wait error to be cleared。
--当时出现library cache lock的行很多,主要精力集中在这里,后面的事件没有引起重视。而且根本不存在表T1并不存在,感觉有点奇怪。

SYS@book> @ &r/lcl

display  library cache lock problem

   INST_ID SADDR            HANDLE                  MOD        REQ OBJECT               SQL_ID        HASH_VALUE KGLNAOBJ                       USER_NAME            C50
---------- ---------------- ---------------- ---------- ---------- -------------------- ------------- ---------- ------------------------------ -------------------- --------------------------------------------------
         1 00000000854DC040 000000007BA1B5C8          3          0 T1                   gsmky1za3hww0 3560469376 T1                             SCOTT                alter system kill session '232,597' immediate;

display wait library cache lock

       SID    SERIAL# SPID       PID  P_SERIAL# EVENT                                    C50
---------- ---------- ------ ------- ---------- ---------------------------------------- --------------------------------------------------
        90        815 53621       32         97 library cache lock                       alter system kill session '90,815' immediate;


SYS@book> @ &r/sharepool/shp4 gsmky1za3hww0 3560469376
old  17:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  17:  WHERE kglobt03 = 'gsmky1za3hww0'  or kglhdpar='gsmky1za3hww0' or kglhdadr='gsmky1za3hww0' or KGLNAHSH= 3560469376
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
父游标句柄地址 000000007BA1B5C8 000000007BA1B5C8 T1                                       000000007BB675C0 00                     4704          0          0      4704       4704 3560469376                        0

--看不到sql_id=gsmky1za3hww0.补充
select replace(wmsys.wm_concat(c),',') from (
select c from (
SELECT SUBSTR ('0123456789abcdfghjkmnpqrstuvwxyz', a + 1, 1) c,rownum rn
  FROM (WITH data (a, b)
             AS (SELECT MOD (&1, 32) a, TRUNC (&1 / 32) b FROM DUAL
                 UNION ALL
                 SELECT MOD (b, 32) a, TRUNC (b / 32) b
                   FROM data
                  WHERE b !=0
                  )
        SELECT a
          FROM data)) order by rn desc);
--带入3560469376。

REPLACE(WMSYS.WM_CONCAT(C),',')
-------------------------------------
3a3hww0

--后面几位是对上的sql_id,奇怪一般一些对象仅仅存在HASH_VALUE。没有sql_id的,不知道为什么这里有记录。

select * from V$DB_OBJECT_CACHE where name='T1' ;
Record View
As of: 2016/03/24 15:19:07

OWNER:               SCOTT
NAME:                T1
DB_LINK:            
NAMESPACE:           TABLE/PROCEDURE
TYPE:                TABLE
SHARABLE_MEM:        4704
LOADS:               4
EXECUTIONS:          0
LOCKS:               1
PINS:                1
KEPT:                NO
CHILD_LATCH:         29568
INVALIDATIONS:       0
HASH_VALUE:          3560469376
LOCK_MODE:           EXCLUSIVE
PIN_MODE:            EXCLUSIVE
STATUS:              VALID
TIMESTAMP:           2016-03-24/14:33:13
PREVIOUS_TIMESTAMP: 
LOCKED_TOTAL:        21
PINNED_TOTAL:        22
PROPERTY:           
FULL_HASH_VALUE:     f1cd2b1c8166d406fc4e5e0fd4387380
--还真不知道对象的FULL_HASH_VALUE是如何计算的。
--------------------------

SYS@book> select * from V$OPEN_CURSOR where sid=232;
SADDR                   SID USER_NAME            ADDRESS          HASH_VALUE SQL_ID        SQL_TEXT                                                     LAST_SQL_ACTIVE_TIM SQL_EXEC_ID CURSOR_TYPE
---------------- ---------- -------------------- ---------------- ---------- ------------- ------------------------------------------------------------ ------------------- ----------- ------------------------
00000000854DC040        232 SCOTT                000000007DD7F9E0 1572239410 7jpt4cpfvcy1k select ts#,online$ from ts$ where name=:1                                                    SESSION CURSOR CACHED
00000000854DC040        232 SCOTT                000000007D7F9F68  594593636 4kp0kn4jr1jv4 table_1_ff_21f_0_0_0                                                                         OPEN-RECURSIVE
00000000854DC040        232 SCOTT                000000007C36D2F0 1853064805 5hrvvu1r771m5  SELECT VALUE$ FROM SYS.PROPS$ WHERE NAME = 'OGG_TRIGGER_OPT                                 OPEN-RECURSIVE
00000000854DC040        232 SYS                  000000007C66F5E8  785625969 at1ygf4rd7cvj select file#, block#, blocks from seg$ where type# = 3 and t                                 SESSION CURSOR CACHED
00000000854DC040        232 SYS                  000000007C66F5E8  785625969 at1ygf4rd7cvj select file#, block#, blocks from seg$ where type# = 3 and t                                 SESSION CURSOR CACHED
00000000854DC040        232 SYS                  000000007D5D6AD0  534114327 0hhmdwwgxbw0r select obj#, type#, flags, related, bo, purgeobj, con#    fr                                 SESSION CURSOR CACHED
00000000854DC040        232 SYS                  000000007D5D6AD0  534114327 0hhmdwwgxbw0r select obj#, type#, flags, related, bo, purgeobj, con#    fr                                 SESSION CURSOR CACHED
00000000854DC040        232 SCOTT                000000007D75F7C8  838595833 7vvm9vcszrx7t create table t1 tablespace tea as select * from dba_objects  1970-01-01 08:59:37    16777218 OPEN
8 rows selected.

SYS@book> @ &r/sharepool/shp4 7vvm9vcszrx7t 838595833
old  17:  WHERE kglobt03 = '&1'  or kglhdpar='&1' or kglhdadr='&1' or KGLNAHSH= &2
new  17:  WHERE kglobt03 = '7vvm9vcszrx7t'  or kglhdpar='7vvm9vcszrx7t' or kglhdadr='7vvm9vcszrx7t' or KGLNAHSH= 838595833
TEXT           KGLHDADR         KGLHDPAR         C40                                      KGLOBHD0         KGLOBHD6           KGLOBHS0   KGLOBHS6   KGLOBT16   N0_6_16        N20   KGLNAHSH KGLOBT03        KGLOBT09
-------------- ---------------- ---------------- ---------------------------------------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
子游标句柄地址 000000007C757648 000000007D75F7C8 create table t1 tabl                     000000007DF65E68 000000007C473E90       4528     269096       3925    277549     277549  838595833 7vvm9vcszrx7t          0
子游标句柄地址 000000007D4D4AA8 000000007D75F7C8 create table t1 tabl                     000000007D9F2400 000000007C474298       4528     269120       3925    277573     277573  838595833 7vvm9vcszrx7t          1
父游标句柄地址 000000007D75F7C8 000000007D75F7C8 create table t1 tabl                     000000007D079730 00                     4752          0          0      4752       4752  838595833 7vvm9vcszrx7t      65535

--基本可以确定是还在建立表,如果查看select * from V$ACTIVE_SESSION_HISTORY更容易明确。时间长了,会提示:

SCOTT@book> select * from t1 where rownum<=1;
select * from t1 where rownum<=1
              *
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object。

$ oerr ora 04021
04021, 00000, "timeout occurred while waiting to lock object %s%s%s%s%s"
// *Cause:  While waiting to lock a library object, a timeout occurred.
// *Action: Retry the operation later.

3.解决问题:
1.可以扩大表空间。
2.或者kill 建表的进程,然后重新建立。

4.附上lcl检查脚本:

$ cat lcl.sql
PROMPT
PROMPT display  library cache lock problem
PROMPT

column object format a20
column user_name format a20
SELECT inst_id
      ,kgllkses saddr
      ,kgllkhdl handle
      ,kgllkmod MOD
      ,kgllkreq REQ
      ,kglnaobj object
      ,KGLLKSQLID sql_id
      ,kglnahsh hash_value
      ,KGLNAOBJ
      ,user_name
      ,   'alter system kill session '''
       || s.sid
       || ','
       || s.serial#
       || ''''
       || ' immediate;'
          c50
  --,lock_a.*
  FROM x$kgllk lock_a, v$session s
WHERE    s.saddr = lock_a.kgllkses and
kgllkmod > 0
       AND EXISTS
              (SELECT lock_b.kgllkhdl
                 FROM x$kgllk lock_b
                WHERE     kgllkses IN (SELECT saddr
                                         FROM v$session
                                        WHERE event like 'library cache lock') /* blocked session */
                                        --WHERE event like 'library cache pin') /* blocked session */
                      AND lock_a.kgllkhdl = lock_b.kgllkhdl
                      AND kgllkreq > 0);

PROMPT
PROMPT display wait library cache lock
PROMPT
SELECT s.sid
      ,s.serial#
      ,p.spid
      ,p.pid
      ,p.serial# p_serial#
          ,s.event
      ,   'alter system kill session '''
       || s.sid
       || ','
       || s.serial#
       || ''''
       || ' immediate;'
          c50
  FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.event= 'library cache lock';

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

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6291352