The first step is to see who is waiting for Library Cache Pins:
SQL> COL event FORMAT a20 TRUNC
SQL>
SQL> tti "Users Waiting for Library Cache Pins"
SQL> SELECT sid, event, p1raw, seconds_in_wait, wait_time
2 FROM sys.v_$session_wait
3 WHERE event = 'library cache pin'
4 AND state = 'WAITING'
5 /
Wed Aug 11 page 1
Users Waiting for Library Cache Pins
SID EVENT P1RAW SECONDS_IN_WAIT
---------- -------------------- ---------------- ---------------
374 library cache pin 000000051862E5F0 1531
944 library cache pin 000000051862E5F0 10383
1057 library cache pin 000000051862E5F0 10554
776 library cache pin 000000051862E5F0 2405
4 rows selected.P1raw is the "Handle Address" of the object that is blocking. Execute the following query to get the object's owner and name:
SQL> tti "Object that is Blocking"
SQL> COL owner format a8
SQL> COL object format a70
SQL> SELECT kglnaown AS owner, kglnaobj as Object
2 FROM sys.x$kglob
3 WHERE kglhdadr='&P1RAW'
4 /
Enter value for p1raw: 000000051862E5F0
old 3: WHERE kglhdadr='&P1RAW'
new 3: WHERE kglhdadr='000000051862E5F0'
Wed Aug 11 page 1
Object that is Blocking
OWNER OBJECT
-------- -------------------------------------------------- --------------------
begin SP_EMP.PROC1@orcl(:a,:b,:c); end;Identify the users that are waiting/ blocking:
SQL> tti "Blocking/Waiting Users"
SQL> col SID_SERIAL format a12
SQL> SELECT s.sid||','||s.serial# SID_SERIAL, kglpnmod "Mode Held", kglpnreq "Request"
2 FROM sys.x$kglpn p, sys.v_$session s
3 WHERE p.kglpnuse = s.saddr
4 AND kglpnhdl = '&P1RAW'
5 /
Enter value for p1raw: 000000051862E5F0
old 4: AND kglpnhdl = '&P1RAW'
new 4: AND kglpnhdl = '000000051862E5F0'
Wed Aug 11 page 1
Blocking/Waiting Users
SID_SERIAL Mode Held Request
------------ ---------- ----------
374,1390 0 2
776,2906 0 2
944,2193 0 2
991,59496 3 0
1057,1966 0 2
5 rows selected.In the above example, session 991 is blocking the other sessions listed. Killing session 911 should solve the problem. However, before killing the session, you may want to collect evidence of the problem so you can log a TAR. To do so, collect 3 SYSTEMSTATE dumps at 30 seconds intervals, then submit them to Oracle support for further analysis
来自 “ ITPUB博客 ” ,链接:https://blog.itpub.net/6456/viewspace-112238/ ,如需转载,请注明出处,否则将追究法律责任。