04021: timeout occurred while waiting to lock object错误的猜

本文的实验环境为：

[oracle@rhel63single ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 07:18:11 2015Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

— session 71

SQL> set timing onSQL> alter procedure lc0019999.dummy11gR2 compile; alter procedure lc0019999.dummy11gR2 compile*ERROR at line 1:ORA-04021: timeout occurred while waiting to lock objectElapsed: 00:15:00.09SQL> show parameter lockNAMETYPEVALUE———————————— ———– ——————————db_block_buffersinteger0db_block_checkingstringFALSEdb_block_checksumstringTYPICALdb_block_sizeinteger8192db_file_multiblock_read_countinteger128ddl_lock_timeoutinteger0distributed_lock_timeoutinteger60dml_locksinteger1088lock_name_spacestringlock_sgabooleanFALSESQL> select * from v$mystat where rownum<2;SID STATISTIC#VALUE———- ———- ———-7100Elapsed: 00:00:00.02SQL>

从上面可以看出，等待15分钟后，由于timeout而失败，不过这个15分钟我没有找到对应的数据库初始化参数。

而在另外的一个实验中，在22分钟之后，才报出的ORA-04021: timeout occurred while waiting to lock object。见如下的实验。

–如下脚本来自：How to Analyze Library Cache Timeout with Associated: ORA-04021 ‘timeout occurred while waiting to lock object %s%s%s%s%s.’ Errors (文档 ID 1486712.1)–若是lock or pin的 session比较多，如下脚本不太容易看出来哪个是持有者（即:阻塞者）

SQL> select /*+ ordered */ w1.sid waiting_session, 2h1.sid holding_session, 3w.kgllktype lock_or_pin, 4w.kgllkhdl address, 5decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 6'Unknown') mode_held, 7decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 8'Unknown') mode_requested 9from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 10 where 11(((h.kgllkmod != 0) and (h.kgllkmod != 1) 12and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 13and 14(((w.kgllkmod = 0) or (w.kgllkmod= 1)) 15and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 16and w.kgllktype= h.kgllktype 17and w.kgllkhdl = h.kgllkhdl 18and w.kgllkuse= w1.saddr 19and h.kgllkuse= h1.saddr 20 /WAITING_SESSION HOLDING_SESSION LOCK ADDRESSMODE_HELD MODE_REQU————— ————— —- —————- ——— ———19871 Lock 00000000976AE938 Exclusive Exclusive1471 Lock 00000000976AE938 Exclusive Exclusive71136 Pin 00000000976AE938 ShareExclusiveSQL> /—->此时sid为71的（见本文的最上边）session由于ORA-04021而失败，然后重新执行查询后，，构成如下的阻塞者和持有者关系：198变成了持有者。WAITING_SESSION HOLDING_SESSION LOCK ADDRESSMODE_HELD MODE_REQU————— ————— —- —————- ——— ———198136 Pin 00000000976AE938 ShareExclusive14198 Lock 00000000976AE938 Exclusive Exclusive

—另外一个session 198

SQL> select * from v$mystat where rownum<2;SID STATISTIC#VALUE———- ———- ———-19800Elapsed: 00:00:00.00SQL> alter procedure lc0019999.dummy11gR2 compile;alter procedure lc0019999.dummy11gR2 compile*ERROR at line 1:ORA-04021: timeout occurred while waiting to lock objectElapsed: 00:22:28.64 —–>请注意，消耗了22分钟之后，才报出ORA-04021SQL>

因此，我猜测，session 198由等待者变为持有者的时候，Library Cache Timeout 的时间计数会清零。

代替你主持夕阳的葬礼。