Troubleshooting - Library Cache pin wait

Hi Everyone!

One of the interesting troubleshooting that I had recently.

One of the developer reported that his query is running forever, never coming with result in production database.
He reported page long query and indicated that one of the function in the query that is is causing the issue.

First I have figured out the way to reproduce the issue with simple sql statement using the using the function that he has mentioned.

When I ran the below query it didn't come back with result.

"select F_FINDINGS(5433) from dual;"

I have opened two sessions. First session, I ran below

SQL> select distinct sid from v$mystat;

SID
----------
240

SQL> select sid,serial# from v$session where sid=240;

SID SERIAL#
---------- ----------
240 5332

SQL> select F_FINDINGS(2739) from dual;


....

.....
hangs


I have opened another session and enabled 10046 tracing. Trace log was getting updated with

WAIT #4: nam='library cache pin' ela= 2930625 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939058 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2938812 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939034 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939119 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939372 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939317 p1=59136743424 p2=60059058432 p3=200
WAIT #4: nam='library cache pin' ela= 2939034 p1=59136743424 p2=60059058432 p3=200


I have ran the below query to get p1raw.

SQL> l
1 select
2 sid,
3 event,
4 p1raw,
5 seconds_in_wait,
6 wait_time
7 from
8 v$session_wait
9 where
10 event = 'library cache pin'
11 and
12* state = 'WAITING'
SQL> set heading off
SQL> /

240 library cache pin
0000000DC4D31800 208 0

And to figure out who is causing the wait for the session with sid 240, I ran

SQL>
SQL> l
1 SELECT s.sid, kglpnmod "Mode", kglpnreq "Req"
2 FROM x$kglpn p, v$session s
3 WHERE p.kglpnuse=s.saddr
4 AND kglpnhdl='&P1RAW'
5*
SQL>

Enter value for p1raw: 0000000DC4D31800
old 4: AND kglpnhdl='&P1RAW'
new 4: AND kglpnhdl='0000000DC4D31800'

SID Mode Req
---------- ---------- ----------
240 0 2
1146 3 0


--An X request (3) will be blocked by any pins held S mode (2) on the object.
--An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.


This indicates that session with id 1146 is holding exclusive pin hold. Session 1146 was hang session left over for some reason on the DB. After killing the session 1146 with app owner's approval query started working!!

Happy Troubleshooting.