今天在ERP测试机上安装，配置STATSPACK。前面的安装过程都比较顺利，最后测试的时候遇到问题。我的测试就是在不同的时间点运行两次statspack.snap,然后用spreport.sql生成报告。当我第一次运行statspack.snap的时候会话就hung住了。
SQL> exec statspack.snap --回车后没反应。
因为ORACLE没有任何报错信息，所以怀疑是发生了等待。通过v$session ,v$session_wait 查出目前这个session到底在等待什么。
SQL> select * from v$session where username='SYS' --得到当前session的sid号。
SQL> select * from v$session_wait where sid=37;
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------- --------------- -------------------
37 1037 library cache lock handle address 5.04403159 070000003BC8D0A0 lock address 5.04403159 070000003C89B8B0 100*mode+namespace 302 000000000000012E 0 2317 WAITING
很明显当前session发生了library cache lock等待。那么library cache lock等待的对象到底是什么呢？通过等待的对象的handle地址可以找出来。这里是P1RAW对应的值：070000003BC8D0A0（P1 列是Library Cache Handle Address,Pn字段是10进制表示,PnRaw字段是16进制表示。PnTEXT是对Pn字段的解释）。通过这个地址值我们可以查询这个对象的具体信息。
X$KGLOB--[K]ernel [G]eneric [L]ibrary Cache Manager [OB]ject
SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
2 from X$KGLOB
3 where KGLHDADR ='070000003BC8D0A0';
ADDR KGLHDADR KGLHDPAR KGLNAOWN KGLNAOBJ KGLNAHSH KGLHDOBJ
---------------- ---------------- ---------------- ---------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------------
00000001103774D8 070000003BC8D0A0 070000003BC8D0A0 SYS DBMS_UTILITY 2824718994 070000003BC8CD70
--这里KGLNAHSH代表该对象的Hash Value。从这个查询得知DBMS_UTILITY对象上正经历library cache lock的等待
SQL> select a.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
2 b.kGLPNLCK, b.KGLPNMOD, b.KGLPNREQ
3 from v$session a,x$kglpn b
4 where a.saddr=b.kglpnuse and b.kglpnhdl = '070000003BC8D0A0' and b.KGLPNMOD<>0;
SID USERNAME PROGRAM ADDR KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD KGLPNREQ
---------- ------------------------------ ------------------------------------------------ ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------- ----------
60 APPS 00000001103771C8 070000003C754A70 070000003695E7C0 070000003695E7C0 070000003BC8D0A0 070000003C74D818 2 0
SQL> select * from v$session_wait where sid=60; --根据上面的查询得出SID
SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
---------- ---------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------------------------------------------------------------- ---------- ---------------- ---------- --------------- -------------------
60 24011 PL/SQL lock timer duration 1500 00000000000005DC 0 00 0 00 0 286 WAITING
--这个用户正在等待一次PL/SQL lock timer计时.
SQL> select * from v$session where sid=60;
SADDR SID SERIAL# AUDSID PADDR USER# USERNAME COMMAND OWNERID TADDR LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS MACHINE TERMINAL PROGRAM TYPE SQL_ADDRESS SQL_HASH_VALUE PREV_SQL_ADDR PREV_HASH_VALUE MODULE MODULE_HASH ACTION ACTION_HASH CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIME LAST_CALL_ET PDML_ENABLED FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER RESOURCE_CONSUMER_GROUP PDML_STATUS PDDL_STATUS PQ_STATUS CURRENT_QUEUE_DURATION CLIENT_IDENTIFIER
---------------- ---------- ---------- ---------- ---------------- ---------- ------------------------------ ---------- ---------- ---------------- ---------------- -------- --------- ---------- ------------------------------ ------------------------------ ------------ ---------------------------------------------------------------- ------------------------------ ------------------------------------------------ ---------- ---------------- -------------- ---------------- --------------- ------------------------------------------------ ----------- -------------------------------- ----------- ---------------------------------------------------------------- -------------------- ------------- -------------- --------------- ------------- ----------- ------------ ------------ ------------- --------------- ----------- -------------------------------- ----------- ----------- --------- ---------------------- ----------------------------------------------------------------
070000003695E7C0 60 9 1276227 0700000036801B60 44 APPS 0 2147483644 ACTIVE DEDICATED 44 APPS appldev 909464 erptest USER 070000003F5C4248 2942148014 00 0 CVTINNERAPP 796656718 Concurrent Request 1021472160 106 0 466767 47320 14 0 0 2008-4-24 9 358548 NO NONE NONE NO DEFAULT_CONSUMER_GROUP DISABLED ENABLED ENABLED 0
--通过v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段关联v$sqltext,v$sqlarea等视图获得当前session正在执行的操作如下：
SQL>select sql_text from v$sqlarea where v$sqlarea.hash_value='2942148014';
SQL> alter system kill session '60,9';
alter system kill session '60,9'
ORA-00031: 标记要删除的会话。
--没有删除成功。
SQL> select ADDR,PID,SPID from v$process where ADDR='0700000036801B60';
ADDR PID SPID
---------------- ---------- ------------
0700000036801B60 57 1749020
--找出SESSION对应的OS进程。
$kill -9 1749020 --在OS杀掉进程！
SQL> exec statspack.snap
PL/SQL procedure successfully completed. --OK！
总结以上查询：
1.获得Library Cache Pin等待的对象：
SELECT addr, kglhdadr, kglhdpar, kglnaown, kglnaobj, kglnahsh, kglhdobj
FROM x$kglob WHERE kglhdadr IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
2.获得持有等待对象的session信息：
SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')
3.获得持有对象用户执行的代码：
SELECT sql_text
FROM v$sqlarea
WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
SELECT sql_address, sql_hash_value
FROM v$session
WHERE SID IN (
SELECT SID
FROM v$session a, x$kglpn b
WHERE a.saddr = b.kglpnuse
AND b.kglpnmod <> 0
AND b.kglpnhdl IN (SELECT p1raw
FROM v$session_wait
WHERE event LIKE 'library%')))
以上三个查询摘自EYGLE的网站：
