a process for resolving a library cache pin fault

this article, seen on dbsnake, turns around. mainly or learn to solve the problem of a way of thinking. this is often more important than solving the problem.

the link to the original text is as follows:

http://dbsnake.com/2010/06/solve-library-cache-pin.html

here's what it says:

today received a call from a colleague that one of his stored procedures had been running for more than an hour and was continuing to run, which he felt was highly unusual and, by reason, should not have run for so long.

i said let me have a look.

THIS LIBRARY IS 10.2.0.4 ON AIX, AND I COLLECTED AN AWR REPORT FOR THE PROBLEM TIME PERIOD:

a process for resolving a library cache pin fault1

From the AWR report results, we can see that during the time period of the problem, the system is experiencing severe library cache pins and library cache lock waiting.

According to Load Profile, it is not hardparse that is seen to have contributed to the above-mentioned libry cache pin and library cache lock.

For library cache pin waiting, the AWR report has limited effect, and the most effective way is to find the session that holds the libry cache pin and wait for the libry cache pin and see what they are doing:

SQL> SELECT s.sid, kglpnmod"Mode", kglpnreq "Req", SPID "OS Process"

2 FROM v$session_wait w, x$kglpn p,v$session s ,v$process o

3 WHERE p.kglpnuse=s.saddr

4 AND kglpnhdl=w.p1raw

5 and w.event like '%library cache pin%'

6 and s.paddr=o.addr

7 /

SID Mode Req OS Process

---------- ---------- ---------- ------------

396 0 2 6381970

341 2 0 4092132

363 0 2 3514690

304 0 2 3977478

354 0 3 3137874

20 rows selected

My colleague in the run stored procedure is located in the session is 396, from the above results we can see that 396 now want to hold the library cache pin pin in Share mode (i.e. Req=2), and now holds the library cache pin is session 341, and the holding mode of 341 is also Share (i.e. Mode=2).

Originally Share and Share could have shared, but unfortunately before 396, session 354 wanted to hold the above-mentioned librycache pinache in Exclusive mode (i.e. Req=3), which directly led to 396 waiting queues, while 363 and 304 were in Queue.

why do i say this, because there is a very classic saying in oracle's interpretation of the libry cache pin:

An X request (3) will be blocked by anypins 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.

SO FROM THE AWR REPORT AND THE ABOVE QUERY RESULTS, WE CAN DRAW THE FOLLOWING CONCLUSIONS:

1, my colleague of the run stored procedure why run for more than 1 hour has not run because this stored procedure is going through a serious libry cache pin waiting;

2, and why it leads to severe libramic pin waiting because the session 341 and 354 work together to achieve this effect, that is, 341 in Share mode holding library cache pin, and then 354 want to hold in Excel mode, which directly results in all subsequent requests being in the waiting Queue. That is, 341 blocks 354, and 354 indirectly blocks 396.

now that we know why, let's look at what session 341 is doing:

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

execute again after 10 seconds interval: -- the purpose of the 10-second query is to determine whether the contents of the session have changed, and thus what session is doing

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

do it again after an interval of 10 seconds:

SQL> selectdecode(sql_hash_value,0,prev_hash_value,sql_hash_value) from v$session wheresid=341;

DECODE(SQL_HASH_VALUE,0,PREV_H

------------------------------

784727971

SQL> select sql_text from v$sqltextwhere hash_value=784727971 order by piece;

SQL_TEXT

----------------------------------------------------------------

begin -- Call the procedurep_adj_rrp_main(o_vc_flag => :o_vc_flag); end;

as you can see from the results, 341 has been a stored procedure in run.

calling the older sister, who had 341, and asking what she was doing, she told me that she had been running the stored procedure since last night and had found out this morning that she was dead, so she was gone.

it's easy to deal with when i know why, and when i dry out session 341, the entire system's libry cache pin comes down, and then my colleague's run takes more than an hour to run.

-------------------------------------------------------------------------------------------------------

QQ:492913789

Email:[email protected]

Blog: http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

DBA1 GROUP: 62697716 (FULL); DBA2 GROUP: 62697977 (FULL) DBA3 GROUP: 62697850 (FULL)

DBA SUPERGROUP: 63306533 (FULL); DBA4 GROUP: 83829929 DBA5 GROUP: 142216823

DBA6 GROUP: 158654907 CHAT GROUP: 40132017 CHAT GROUP 2: 69087192

--Adding groups is required to explain the relationship between Oracle table space and data files in the notes, otherwise the request will be rejected