IDC资讯中心
下午，业务人员报告，执行任何和zzss03201281cs_no表有关的操作都会hang住，包括desc zzss03201281cs_no，也会hang在那里
第一感觉是锁了，于是，我看看锁
sql> select * from v$lock where block=1;
no rows selected
sql>
sql> select * from gv$lock where block=1;
no rows selected
sql>
再看看等待事件：
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
。。。
奇怪，怎么这么多 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,
10 (select t.instance
11 from v$thread t, v$parameter v
12 where v.name = thread
13 and (v.value = 0 or t.thread# = to_number (v.value))) i,
14 (select value
15 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>
这个对象显然不是我们关注的。
sql> l
/ 1* select owner,object_name,status from dba_objects where object_id=18
sql>
owner object_name status
—————————— —————————— ——-
sys obj$ valid
就是这个对象搞得，估计是开发人员异常退出一些进程
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
很显然，是由于report16用户执行了某些ddl操作，然后，异常退出，造成系统的锁（估计和bug有关，有待考证）
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 jan 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>
kill掉这个进程后，问题解决了。（遗憾的是，忘了看看这个家伙执行的sql了，呵呵）
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>
查看trace文件，：
果然大量的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= 330 p1=-4611686013649700264 p2=-4611686013691715248 p3=1301
wait #1: nam=library cache lock ela= 141 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= 93 p1=-4611686013647485472 p2=-4611686013691762016 p3=1301
wait #1: nam=library cache lock ela= 223 p1=-4611686013595934816 p2=-4611686013642107320 p3=1301
