Database Reference
In-Depth Information
I will use the script in Listing 11-11 that joins a table 1,024 times to create a library cache lock contention. From
session 1, I will execute a script. While session 1 is executing the script, I will try to alter table t_libtest to add a column
from session 2. A column cannot be added since the first session has not released the global lock yet, and session 2
will wait for the first session to complete.
9
REM from session #1
@listing_11_10.sql
REM from session #2
Alter table t_libtest add (n3 number);
<.. this session wait..>
Let us inspect global locks and wait events. Session 8075 is waiting for library cache lock wait event. This wait
event is a bit misleading, since the actual wait is for global lock.
SELECT sid, SUBSTR(event, 1, 28) event, state, seconds_in_wait wis
FROM v$session
WHERE state='WAITING'
AND event LIKE 'library cache%';
SID EVENT STATE WIS
------ ------------------------------ --------------- -----
8075 library cache lock WAITING 165
Output of ges_blocking_locks.sql script is printed in the following section. From the output, session 11296 is
holding a lock on resource
[0xad07b0f5][0x339db0c8],[LB]
in
KJUSERPR
mode, while session with SID 8075 is
waiting to lock the same resource in
KJUSEREX
mode. Session 8075 is requesting the lock in
exclusive
mode since the
DDL statement on a table must invalidate dependent library cache locks.
SQL>@ges_blocking_locks.sql
INST_ID : 2
RESOURCE_NAME1 : [0xad07b0f5][0x339db0c8],[LB][
GRANT_LEVEL : KJUSERPR
REQUEST_LEVEL : KJUSERPR
STATE : GRANTED
SID : 11296
EVENT : Disk file operations I/O
SEC : 197
--------------------------------------------------------------
INST_ID : 2
RESOURCE_NAME1 : [0xad07b0f5][0x339db0c8],[LB][
GRANT_LEVEL : KJUSERNL
REQUEST_LEVEL : KJUSEREX
STATE : OPENING
SID : 8075
EVENT : library cache lock
SEC : 186
9
Sandesh Rao points out that locking will happen in a single-instance database also, but the difference is that library cache locks
and pins have global resources representing them, whereas in a single-instance database, this problem will be visible in x$kgllk
and x$kglpn memory views only.
