[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';