Guessing about how long the wait will throw ora-04021:timeout occurred while the waiting to lock object error

The experimental environment of this paper is:

[[email protected] ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 10 07:18:11 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

---session 71

SQL> set timing on
SQL>  alter procedure lc0019999.dummy11gR2 compile;
 alter procedure lc0019999.dummy11gR2 compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


Elapsed: 00:15:00.09
SQL> show parameter lock

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TYPICAL
db_block_size                        integer     8192
db_file_multiblock_read_count        integer     128
ddl_lock_timeout                     integer     0
distributed_lock_timeout             integer     60
dml_locks                            integer     1088
lock_name_space                      string
lock_sga                             boolean     FALSE
SQL> select * from v$mystat where rownum<2;  

       SID STATISTIC#      VALUE
---------- ---------- ----------
        71          0          0

Elapsed: 00:00:00.02
SQL> 

As can be seen from the above, after waiting 15 minutes, due to timeout and failed, but this 15 minutes I did not find the corresponding database initialization parameters.

In another experiment, after 22 minutes, the ora-04021:timeout occurred while waiting to lock object was reported. See the following experiment.

--The following script is from: How to Analyze the Library Cache Timeout with associated:ora-04021 ' Timeout occurred and waiting to lock object%s %s%s%s%s. ' Errors (document ID 1486712.1)
-If the lock or pin session is more, the following script is not easy to see which is the holder (that is, the blocking person)

SQL> select /*+ ordered */ w1.sid  waiting_session,
  2           h1.sid  holding_session,
  3           w.kgllktype lock_or_pin,
  4           w.kgllkhdl address,
  5           decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  6              'Unknown') mode_held,
  7           decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
  8            'Unknown') mode_requested
  9     from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 10    where
 11     (((h.kgllkmod != 0) and (h.kgllkmod != 1)
 12        and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
 13      and
 14        (((w.kgllkmod = 0) or (w.kgllkmod= 1))
 15        and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
 16     and  w.kgllktype      =  h.kgllktype
 17     and  w.kgllkhdl =  h.kgllkhdl
 18     and  w.kgllkuse     =   w1.saddr
 19     and  h.kgllkuse     =   h1.saddr
 20   /

WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
            198              71 Lock 00000000976AE938 Exclusive Exclusive
             14              71 Lock 00000000976AE938 Exclusive Exclusive
             71             136 Pin  00000000976AE938 Share     Exclusive

SQL> / ----> At this time the sid of 71 (see the top of this article) session failed due to ORA-04021, and then after re-executing the query, the following blocker and holder relationship is formed: 198 becomes Holder.
WAITING_SESSION HOLDING_SESSION LOCK ADDRESS          MODE_HELD MODE_REQU
--------------- --------------- ---- ---------------- --------- ---------
            198             136 Pin  00000000976AE938 Share     Exclusive
             14             198 Lock 00000000976AE938 Exclusive Exclusive

---another session 198

SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#      VALUE
---------- ---------- ----------
       198          0          0

Elapsed: 00:00:00.00
SQL> alter procedure lc0019999.dummy11gR2 compile;
alter procedure lc0019999.dummy11gR2 compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object


Elapsed: 00: 22: 28.64 -----> Please note that it took 22 minutes to report ORA-04021SQL>

Therefore, I suspect that the time count of the Library Cache Timeout will be zeroed when session 198 becomes the holder by the waiting person.

Guessing about how long the wait will throw ora-04021:timeout occurred while the waiting to lock object error