Did your standalone storage procedure become INVALID (need to be recompiled)?
If so, as Tom said: Don't use standalone procedures! Use packages. Packages break the dependency chain.
Quoted from:
https://asktom.oracle.com/pls/apex/asktom.search?tag=compile-procedure-automatically-how-to-avoid-cascading-invalidations Notice that, standalone storage procedure (espatially dependency chain exists) may cause 'Cursor: pin S wait on X'.
for instance, modify table structure (event add a column) may result in 'Cursor: pin S wait on X'.
[EXAMPLE]
SQL> create table a2(id1 int,cc varchar2(200));
Table created.
SQL> insert into a2 values (1,'one');
1 row created.
SQL> insert into a2 values (2,'two');
1 row created.
SQL> create table b2(id1 int);
Table created.
SQL> insert into b2 values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> create or replace procedure test1 is
2 mname varchar2(200);
3 begin
4 select cc into mname from a2 join b2 on a2.id1=b2.id1 where a2.id1=1;
5 dbms_lock.sleep(3000); -- SUPPOSE THAT TEST1 TAKES 3000 SECONDS TO FINISH RUNNING.
6 end;
7 /
Procedure created.
SQL>
>> SESSION 1:
SQL> SELECT SID, SERIAL# FROM V$SESSION WHERE SID = SYS_CONTEXT('USERENV', 'SID');
SID SERIAL#
---------- ----------
428 131
SQL> exec test1;
>> SESSION 2:
SQL> alter table b2 add c int;
Table altered.
SQL> SELECT object_name, object_type, STATUS FROM dba_objects WHERE object_name='TEST1' AND owner='N2ADMIN';
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE STATUS
----------------------- -------
TEST1
PROCEDURE INVALID
-- NOTICE THAT: the procedure became INVALID!!!!
SQL>
SQL> exec test1; -- HANGS
>> SESSION 3:
SQL> select sid,event,p1raw from gv$session where wait_class<>'Idle' order by event;
SID EVENT
---------- ----------------------------------------------------------------
P1RAW
----------------
20 SQL*Net message to client
0000000054435000
294 library cache pin
00000000749B8910
SQL> exec test1;
>> SESSION 4
SQL> exec test1;
>> SESSION 5
SQL> exec test1;
-- ALL THE EXCUTION OF TEST1 WOULD HANG!!!
>> SESSION 6:
SQL> set linesize 160
SQL> select sid,final_blocking_session,event,p1raw from gv$session where wait_class<>'Idle' order by event;
SID FINAL_BLOCKING_SESSION EVENT P1RAW
---------- ---------------------- ---------------------------------------------------------------- ----------------
19 SQL*Net message to client 0000000054435000
20 428 cursor: pin S wait on X 00000000AFF613A7
434 428 cursor: pin S wait on X 00000000AFF613A7
305 428 cursor: pin S wait on X 00000000AFF613A7
294 428 library cache pin 00000000749B8910
-- PLZ NOTICE THAT, SESSION 3/4/5 WERE BLOCKED BY SESSION 1(SID=428), CAME ALONG WITH WAIT EVENT 'cursor: pin S wait on X'.
SQL>
SQL> ALTER SYSTEM KILL SESSION '428,131';
System altered.
SQL> select sid,final_blocking_session,event,p1raw from gv$session where wait_class<>'Idle' order by event;
SID FINAL_BLOCKING_SESSION EVENT P1RAW
---------- ---------------------- ---------------------------------------------------------------- ----------------
19 SQL*Net message to client 0000000054435000
SQL>
-- AFTER SESSION 1 BEING KILLED, THE WAIT EVENT JUST GONE...TO AVOID FROM THIS SITUATION, USE PACKAGES RATHER THAN STANDALONE PROCEDURES!
REFERENCES FROM: Oracle内核技术揭秘 (吕海波) -- book written by Lv Haibo