How to solve a library cache pin fault

 

This article we saw on dbsnake is transferred. The main idea is to learn how to solve the problem. This is often more important than solving the problem.

The original Article link is as follows:

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

 

The content is as follows:

 

Today, I received a call from my colleague, saying that one of his stored procedures had been run for more than an hour and he was still running. He thought it was extremely abnormal, it should not be run for such a long time.

Let me take a look.

 

This library is 10.2.0.4 on AIX. I collected the AWR report for the problem period:

 

From the AWR report results, we can see that the system has experienced severe library cache pin and library cache lock waits during the time period of the problem.

According to the load profile information, we can see that the above library cache pin and library cache lock are not hardparse.

For the library cache pin wait, AWR reports have limited functions. The most effective way is to find the session that holds the library cache pin and waits for the library cache pin, and then 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

396 0 2 6381970

396 0 2 6381970

396 0 2 6381970

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

341 2 0 4092132

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

363 0 2 3514690

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

304 0 2 3977478

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

354 0 3 3137874

 

20 rows selected

 

The session where my colleague from the run stored procedure is located is 396. From the above results, we can see that 396 now wants to hold the library cache pin in the share mode (that is, req = 2, at the same time, session 341 is held for the above library cache pin, and the 341 holding mode is also share (that is, mode = 2 ).

Share and share can be shared, but unfortunately before 396, session 354 wants to hold the above librarycache pin in exclusive mode (that is, req = 3, this directly causes 396 to be in the waiting queue, and 363 and 304 in the queue.

 

Why do I say this, because Oracle's interpretation of the library cache pin is very classic:

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.

 

Therefore, we can draw the following conclusions from the AWR report and the above query results:

1. Why did my colleague who ran the stored procedure run for more than an hour? The reason is that the stored procedure is experiencing a serious library cache pin wait;

2. Why does it cause serious library cache pin wait because session 341 and session 354 have joined hands to achieve this effect, that is, 341 holds the library cache pin in share mode, then, 354 is held in exclusive mode, which directly causes all subsequent requests to be in the waiting queue. That is to say, 341 blocks 354, and 354 indirectly blocks 396.

 

Now that you know the reason, let's take a 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

 

Run the query again after 10 seconds: -- the query interval is 10 seconds to determine whether the session content has changed, so as to determine what the 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

 

Execute again after 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;

 

From the results, we can see that 341 has been running a stored procedure.

I called the elder sister who held 341 and asked her what she was doing. She told me that she had run the stored procedure since last night. This morning, she found that she was dead, that's why she has no worries.

 

It is easy to understand the cause of post-processing. When I kill session 341, the library cache pin of the entire system will be dropped at once, then my colleague ran the Stored Procedure for more than an hour.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

Blog: http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 chat group: 40132017 chat group 2: 69087192

-- Add the group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, the application is rejected.