Library Cache Lock Solution case

cache| Solution
In the afternoon, the business staff reported that any operations related to the Zzss03201281cs_no table would be hang, including Desc zzss03201281cs_no, and hang there.

The first feeling was locked, so I looked at the lock

Sql> SELECT * from V$lock where block=1;

No rows selected

Sql>
Sql> SELECT * from Gv$lock where block=1;

No rows selected

Sql>



Then look at the wait event:

Sql> Col event for A30
Sql> L
1* Select Event,p1,p2,sid from v$session_wait where event= ' Library cache lock '
Sql>/

EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
Library Cache lock 1.3835E+19 1.3835E+19 32

Sql>/

EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
Library Cache lock 1.3835E+19 1.3835E+19 32

Sql>/

EVENT P1 P2 SID
------------------------------ ---------- ---------- ----------
Library Cache lock 1.3835E+19 1.3835E+19 32

。。。



Strange, why so many library cache lock?

Sql> Show User
USER is "SYS"
sql> exec Dbms_system.set_ev (32,27506,10046,12, ");

Pl/sql procedure successfully completed.

elapsed:00:00:00.10
Sql> L
1 SELECT D.value
2 | | '/'
3 | | LOWER (RTRIM (i.instance, CHR (0)))
4 | | ' _ora_ '
5 | | P.spid
6 | | '. TRC ' Trace_file_name
7 from (SELECT p.spid
8 from V$mystat m, v$session s, v$process p
9 WHERE m.statistic# = 1 and S.sid = M.sid and p.addr = s.paddr) p,
(SELECT t.instance
One from V$thread T, V$parameter v
WHERE v.name = ' thread '
V.value = 0 OR t.thread# = to_number (v.value)) I,
(SELECT VALUE
From V$parameter
16* WHERE NAME = ' user_dump_dest ') d
Sql>/

Trace_file_name
--------------------------------------------------------------------------------
/ora9i/app/oracle/admin/csmisc/udump/csmisc2_ora_2708.trc

elapsed:00:00:00.10
Sql>



Sql> Select Xidusn, object_id, session_id, Locked_mode from V$locked_object;

XIDUSN object_id session_id Locked_mode
---------- ---------- ---------- -----------
14 35202 31 3
15 18 30 3

sql> Col object_name format A30
Sql> Select Owner,object_name,status from dba_objects where object_id=35202;

OWNER
------------------------------
object_name
--------------------------------------------------------------------------------
STATUS
-------
SYS
Plan_table
VALID


Sql>

This object is clearly not our concern.


Sql> L
/1* Select Owner,object_name,status from dba_objects where object_id=18
Sql>

OWNER object_name STATUS
------------------------------ ------------------------------ -------
SYS obj$ VALID

This is the object, it is estimated that the developer exited some processes unexpectedly

Sql> C/18/30
1* Select Serial#,username,command,lockwait,status,schemaname,osuser,machine,terminal,program,module from v$ Session where sid=30
Sql>/

serial# USERNAME COMMAND lockwait STATUS
---------- ------------------------------ ---------- ---------------- --------
SCHEMANAME Osuser
------------------------------ ------------------------------
MACHINE
----------------------------------------------------------------
TERMINAL Program
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
17921 Pubuser 0 ACTIVE
Pubuser report16
Cs_dc02

serial# USERNAME COMMAND lockwait STATUS
---------- ------------------------------ ---------- ---------------- --------
SCHEMANAME Osuser
------------------------------ ------------------------------
MACHINE
----------------------------------------------------------------
TERMINAL Program
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
SQLPLUS@CS_DC02 (TNS v1-v3)
Sql*plus


Sql> Select B.username Username, b.terminal terminal,b.program program,b.spid
2 from V$session A, v$process b
where a.paddr=b.addr and a.sid = ' &sid ';
3 Enter value for sid:30
Old 3:where a.paddr=b.addr and a.sid = ' &sid '
New 3:where a.paddr=b.addr and a.sid = ' 30 '

USERNAME TERMINAL
--------------- ------------------------------
Program SPID
------------------------------------------------ ------------
Ora9i UNKNOWN
ORACLE@CS_DC02 (TNS v1-v3) 835

Obviously, the report16 user performs some DDL operations, and then exits unexpectedly, causing the system to lock (estimated and bug-related, pending verification)
Sql> Host
Ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > Ps-ef | grep 835
ora9i 4619 4617 1 14:48:18 pts/te 0:00 grep 835
Ora9i 835 1 0, 5? 0:01 ORACLECSMISC2 (Local=no)
Ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > Kill 835
Ora9i@cs_dc02:/ora9i/app/oracle/product/920/rdbms/admin > Exit

Sql> Select Xidusn, object_id, session_id, Locked_mode from V$locked_object;

XIDUSN object_id session_id Locked_mode
---------- ---------- ---------- -----------
14 35202 31 3

Sql>

After you kill the process, the problem is solved. (Sadly, forget to look at this guy executing the SQL, hehe)
Sql> desc zzss03201281cs_no
ERROR:
Ora-04043:object Zzss03201281cs_no does not exist


Sql> desc zzss03201281cs_no
ERROR:
Ora-04043:object Zzss03201281cs_no does not exist


Sql>

sql> exec Dbms_system.set_ev (32,27506,0,0, ");

Pl/sql procedure successfully completed.

Sql>

To view the trace file:

Sure enough, a lot of wait:

Wait #1: nam= ' Library cache lock ' ela= 316 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301
Wait #1: nam= ' Library cache lock ' ela= 326 p1=-4611686013647472824 p2=-4611686013691747544 p3=1301
Wait #1: nam= ' Library cache lock ' ela= 398 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301
Wait #1: nam= ' Library cache lock ' ela= 552 p1=-4611686013647483736 p2=-4611686013691747816 p3=1301
Wait #1: nam= ' Library cache lock ' ela= p1=-4611686013649700264 p2=-4611686013691715248 p3=1301
Wait #1: nam= ' Library cache lock ' ela= p1=-4611686013649700264 p2=-4611686013691715248 p3=1301
Wait #1: nam= ' Library cache lock ' ela= 223 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301
Wait #1: nam= ' Library cache lock ' ela= p1=-4611686013647485472 p2=-4611686013691762016 p3=1301
Wait #1: nam= ' Library cache lock ' ela= 223 p1=-4611686013595934816 p2=-4611686013642107320 p3=1301