会话一：

create or replace procedure dummy is
begin
null;
end;
/

begin
Dummy;
Dbms_lock.sleep(1000);
End;
/

会话二：

SQL> alter procedure dummy compile;

卡住

会话三：

SQL> alter procedure dummy compile;

卡住

处理过程：

select event,count(*) from gv$session_wait where wait_class<>'Idle' group by event;
 
EVENT                                                              COUNT(*)
---------------------------------------------------------------- ----------
asynch descriptor resize                                                  1
library cache pin                                                              1
library cache lock                                                             1

select sid,saddr from v$session where event= 'library cache lock';
 
       SID SADDR
---------- ----------------
        18 00000000C5A7B4A0

select kgllkhdl Handle,kgllkreq Request, kglnaobj Object  from x$kgllk where kgllkses = '00000000C5A7B4A0'  and kgllkreq > 0;
 
HANDLE                     REQUEST        OBJECT
----------------------------------------------------------------------------
00000000A4547440          3             DUMMY

select kgllkses saddr,kgllkhdl handle,kgllkmod mod,kglnaobj object
   from x$kgllk lock_a
    where kgllkmod > 0
    and exists (select lock_b.kgllkhdl from x$kgllk lock_b
   where kgllkses = '00000000C5A7B4A0' /* blocked session */
    and lock_a.kgllkhdl = lock_b.kgllkhdl
and kgllkreq > 0);
  7  /
 
SADDR            HANDLE                  MOD   OBJECT
-------------------------------------------------------------------------------------------
00000000C5A696B0 00000000A4547440          1    DUMMY
00000000C5BE3B08 00000000A4547440          3    DUMMY

select sid,SERIAL#,username,terminal,program from v$session where saddr = '00000000C5BE3B08';
 
       SID    SERIAL# USERNAME                       TERMINAL                       PROGRAM
---------- ---------- ------------------------------ ------------------------------ ------------------------------------------------
       145       4027 TEST123                        pts/1                          sqlplus@yikuer (TNS V1-V3)

alter system kill session '145,4027' immediate;
 
System altered.

会话二被杀掉：

alter procedure dummy compile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 21474
Session ID: 145 Serial number: 4027

ash 报告：

定位到语句：

087rrdjwc2act  alter procedure dummy compile exit

查看该存储过程最后编译时间：

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
SELECT OBJECT_NAME, LAST_DDL_TIME FROM dba_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' AND OBJECT_NAME='DUMMY';
 
OBJECT_NAM LAST_DDL_TIME
---------- -------------------
DUMMY      2021-03-10 15:59:27

从而判断出此次“ library cache lock ”是由于频繁编译 dummy 存储过程导致