How to locate library cache lock

The method of locating library cache lock often shows that some of the brothers on PUB say that library cache lock and PIN errors do not know how to handle them, and the problem cannot be located. Let me say a few words, based on my work experience, I hope it will be useful to everyone. Generally, this type of error is caused by DDL statements when the package or process is frequently called. What should we do? In fact, we can check the cause in this way. What the boss wants is generally the fault caused by this fault and who will perform the operation. Especially after the fault is handled, the boss will be answered. 1. Create a DDL-level trigger in the database in advance. I think this is necessary because it has little impact on production, but it allows us to monitor a lot of useful information ., for example, in the abc table, information such as logon users, Operation statements, and operation time can be recorded. 2. When a large number of libriary cache lock wait events occur in the database, the system has serious problems. We can start from around this time point immediately, for example, there is a problem between 12:00:00 PM and PM on November 12, the following statement is: select * from dba_objects wherelast_ddl_time> to_date ('2017 ', 'yyyymmdd hh24: mi: ss ') AND last_ddl_time <= to_date ('2014 12:30:00 ', 'yyyymmdd hh24: mi: ss ') and (object_type like '% PACK %' or object_type like 'function' OR object_type = 'Procedure ') and status = 'invalid' order by last_ddl_time desc actually passed This basically finds out what the problem is. Basically, there will be only one or two objects, such as the packet BBB failure 3, and then find the object associated with the package, whether there is a record in the table recorded by our trigger, and then execute the following statement (Remember, the statement that records the DDL action plays a role) select * from abc where ddl_time> to_date ('2014 12:00:00 ', 'yyyymmdhh24: mi: ss') AND ddl_time <= to_date ('2014 12:30:00', 'yyyymmdd hh24: mi: ss ') and schema_object in (SELECT referenced_name FROM DBA_DEPENDENCIES where name = 'bbb') order by DDL_time desc (please note that this BBB is the one I found above, for example, a failed package) is definitely the cause of the accident. The token action. (Ddl_time and schema_object are fields in the abc table and record the DDL operation time and object of the login.) These methods are frequently used in my work and are easy to use. Generally, there is no problem. Of course, I did not describe the solution to the problem. The solution is as follows. However, sometimes it is very important to find out the cause of the problem and avoid the next occurrence. Of course, using DUMP systemstate and other methods is complicated, this idea is simple and clear. In addition, it is mandatory to create a DDL-Level Trigger! So I want to talk about the above method and hope it will be useful to everyone! Step 1. Check the objects that generate the library cache lock, such as the no packages and the Stored Procedure select kglnaown, kglnaobj from x $ kglob WHERE kglhdadr in (select P1RAW from v $ session_wait where event like 'library cache % '); 2. Check whether the user performed this operation causes the library cache lock select sid, program, machine from v $ session where paddr in (SELECT s. paddr FROM x $ kglpn p, v $ session s WHERE p. kglpnuse = s. saddr (+) AND p. kglpnmod <> 0 and kglpnhdl in (select p1raw from v $ session_wait where event in ('library cache pin', 'library cache lock', 'library cache load lock '))); 3. The following statement is used to kill a session. (check the preceding statement and decide whether to kill the process to solve the problem) select 'Kill-9' | spid from v $ process where addr in (SELECT s. paddr FROM x $ kglpn p, v $ session s WHERE p. kglpnuse = s. saddr -- AND p. kglpnmod <> 0 and kglpnhdl in (select p1raw from v $ session_wait where event in ('library cache pin', 'library cache lock'); appendix: ddl trigger statement create or replace trigger tr_trace_ddlAFTER ddlON databaseDECLARE SQL _TEXT ORA_NAME_LIST_T; STATE_ SQL VARCHAR2 (4000); -- DDL $ TRACE. DDL_ SQL % TYPE; V_ERR_INFO VARCHAR2 (200); begin for I IN 1 .. ORA_ SQL _TXT (SQL _TEXT) LOOP STATE_ SQL: = STATE_ SQL | SQL _TEXT (I); END LOOP; INSERT INTO SYSTEM. ABC (LOGIN_USER, AUDSID, IPADDRESS, SCHEMA_USER, SCHEMA_OBJECT, DDL_TIME, DDL_ SQL) VALUES (VALUES, USERENV ('sessionid'), SYS_CONTEXT ('userenv', 'IP _ address '), ORA_DICT_OBJ_OWNER, ORA_DICT_OBJ_NAME, SYSDATE, STATE_ SQL, in this step, determine whether to kill the process to solve this problem.) to locate the spid, you can also use the following SQL: SELECT spid FROM v $ process p, v $ session s WHERE p. addr = s. paddr AND s. sid in (SELECT sid FROM v $ session_wait B WHERE B. EVENT in ('library cache pin', 'library cache lock') the premise is that the sesion is not killed, if it is killed, v $ session. paddr <> v $ process. addr.