9208下遭遇library cache lock
DB: 9208
OS: SunOS 5.8
对某个schema下的所有表收集统计信息，但是运行了很长时间也没有结束，这个schema很小；
查询发现其受阻于library cache lock
SQL> select s.sid,s.SADDR,s.SERIAL#,s.SQL_HASH_VALUE,s.SQL_ADDRESS,s.LOGON_TIME from v$session s where s.sid in (select SID from v$session_wait where event='library cache lock') order by 6;
SID SADDR SERIAL# SQL_HASH_VALUE SQL_ADDRESS LOGON_TIM
---------- ---------------- ---------- -------------- ---------------- ---------
99 00000005137DCF50 27928 386388955 0000000519C146A0 14-AUG-12
110 00000005137E4380 34361 3711789087 0000000546469708 08-OCT-12
141 00000005137F8AF0 2849 1120568383 0000000530926990 12-OCT-12
41 00000005137B6AB0 51562 187410634 0000000525528270 15-MAR-13
40 00000005137B6020 61904 1557907713 000000053640E018 15-MAR-13
206 0000000513823980 23252 1798515045 0000000519EA0A68 22-MAR-13
152 00000005137FFF20 16133 1557907713 000000053640E018 24-MAR-13
173 000000051380DCF0 45610 1798515045 0000000519EA0A68 24-MAR-13
247 000000051383EA90 61375 1798515045 0000000519EA0A68 24-MAR-13
207 0000000513824410 42631 1557907713 000000053640E018 25-MAR-13
18 00000005137A77C0 42294 3048577817 00000005237B3D38 25-MAR-13
149 00000005137FDF70 417 3603470908 000000051A287438 26-MAR-13
129 00000005137F0C30 37610 1037133390 0000000519E07828 26-MAR-13
13 rows selected.
执行统计信息的session正是等待library cache lock，
Select kgllkhdl Handle, kgllkreq Request, kglnaobj Object
From x$kgllk
Where kgllkses in (select saddr from v$session where sid in ( select sid from v$session_wait where event ='library cache lock'))
And kgllkreq > 0;
HANDLE REQUEST OBJECT
---------------- ---------- ------------------------------------------------------------
0000000519B9A4F8 3 MOLECULAR_STRUCTURE_CHEM_REPR
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
000000051D3899B8 3 MOLSTRUCT_COMP_MOLTABLE
0000000519C36978 3 V_COMPOUND
0000000519B9BB68 3 V_MOLECULAR_STRUCTURE
0000000519C36978 3 V_COMPOUND
000000051D3899B8 2 MOLSTRUCT_COMP_MOLTABLE
0000000519C34D58 3 COMPOUND
000000051D3899B8 3 MOLSTRUCT_COMP_MOLTABLE
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
进行hang analyze
Found 11 objects waiting for
<0/56/52600/0x137080c8/29976/No Wait>
Found 11 objects waiting for
<0/158/23970/0x136fb488/7241/No Wait>
Open chains found:
Chain 1 : :
<0/56/52600/0x137080c8/29976/No Wait>
-- <0/18/42294/0x13724f08/6261/library cache lock>
Chain 2 : :
<0/131/2173/0x13714248/10976/No Wait>
-- <0/87/45424/0x13705b28/1124/library cache lock>
-- <0/149/417/0x13721ea8/654/library cache lock>
Chain 3 : :
<0/158/23970/0x136fb488/7241/No Wait>
-- <0/18/42294/0x13724f08/6261/library cache lock>
Other chains found:
Chain 4 : :
<0/22/63087/0x13707b68/5712/No Wait>
Chain 5 : :
<0/40/61904/0x1370ec48/20031/library cache lock>
-- <0/41/51562/0x136fb9e8/22334/library cache lock>
-- <0/206/23252/0x13706b48/27227/library cache lock>
-- <0/247/61375/0x1370b688/4605/library cache lock>
Chain 6 : :
<0/99/27928/0x1370e188/19668/library cache lock>
-- <0/141/2849/0x1370d6c8/22052/library cache lock>
Chain 7 : :
<0/113/51705/0x13715268/19018/No Wait>
Chain 8 : :
<0/116/11413/0x136fe4e8/23128/No Wait>
Chain 9 : :
<0/170/10881/0x13700528/7511/library cache lock>
-- <0/149/417/0x13721ea8/654/library cache lock>
Chain 10 : :
<0/202/25627/0x13706088/28076/No Wait>
按照上述情况，察看blocker的信息
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 56
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
52600 ACTIVE 20371 49 0 0 20-JUN-12 24121943 00 0
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 131
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
2173 ACTIVE 331 1 0 0 13-SEP-12 14508166 000000052EBF25C0 3116404256
--相应sql_text
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_TEXT
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ --------------------------------------------------
2173 ACTIVE 331 1 0 0 13-SEP-12 14508225 select c.index_name, i.parameters from all_ind_col
umns c, all_indexes i where c.table_owner = 'ISAC_
CMSQ_RCG' and c.table_owner = i.owner and c.table_
name = 'MOLSTRUCT_COMP_MOLTABLE' and c.column_name
= 'CTAB' and c.index_name = i.index_name and i.do
midx_status = 'VALID' and i.index_type = 'DOMAIN'
and i.ityp_name = 'IXMDL'
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 158
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
23970 ACTIVE -1 0 0 0 21-JUN-12 24036119 00 0
这些存活超过半年且依旧active的sql，应该就是元凶了；
通过truss跟踪，发现全部在sleeping，统统杀掉；
$ truss -p 29976
read(7, 0x10352B886, 8208) (sleeping...)
^C$
$ kill -9 29976
$ ps -ef | grep 10976
oracle 19042 16603 0 09:13:27 pts/11 0:00 grep 10976
oracle 10976 1 0 Sep 13 ? 0:01 oracleJUSTIN (LOCAL=NO)
$ truss -p 10976
read(9, 0x10352A3F6, 8208) (sleeping...)
^C$ kill -9 10976
$ ps -ef | grep 7241
oracle 19639 16603 0 09:15:16 pts/11 0:00 grep 7241
oracle 7241 1 0 Jun 21 ? 0:00 oracleJUSTIN (LOCAL=NO)
$ truss -p 7241
read(7, 0x10352B886, 8208) (sleeping...)
^C$
$ kill -9 7241
然后不到2分钟，gathering就执行完毕；
疑问：
为什么都执行半年了会话状态依旧为active？ 且sid 131还能查到其执行的sql;
既然它们已经sleeping半年了，SunOS为何不能自动清除这些会话？
OS: SunOS 5.8
对某个schema下的所有表收集统计信息，但是运行了很长时间也没有结束，这个schema很小；
查询发现其受阻于library cache lock
SQL> select s.sid,s.SADDR,s.SERIAL#,s.SQL_HASH_VALUE,s.SQL_ADDRESS,s.LOGON_TIME from v$session s where s.sid in (select SID from v$session_wait where event='library cache lock') order by 6;
SID SADDR SERIAL# SQL_HASH_VALUE SQL_ADDRESS LOGON_TIM
---------- ---------------- ---------- -------------- ---------------- ---------
99 00000005137DCF50 27928 386388955 0000000519C146A0 14-AUG-12
110 00000005137E4380 34361 3711789087 0000000546469708 08-OCT-12
141 00000005137F8AF0 2849 1120568383 0000000530926990 12-OCT-12
41 00000005137B6AB0 51562 187410634 0000000525528270 15-MAR-13
40 00000005137B6020 61904 1557907713 000000053640E018 15-MAR-13
206 0000000513823980 23252 1798515045 0000000519EA0A68 22-MAR-13
152 00000005137FFF20 16133 1557907713 000000053640E018 24-MAR-13
173 000000051380DCF0 45610 1798515045 0000000519EA0A68 24-MAR-13
247 000000051383EA90 61375 1798515045 0000000519EA0A68 24-MAR-13
207 0000000513824410 42631 1557907713 000000053640E018 25-MAR-13
18 00000005137A77C0 42294 3048577817 00000005237B3D38 25-MAR-13
149 00000005137FDF70 417 3603470908 000000051A287438 26-MAR-13
129 00000005137F0C30 37610 1037133390 0000000519E07828 26-MAR-13
13 rows selected.
执行统计信息的session正是等待library cache lock，
Select kgllkhdl Handle, kgllkreq Request, kglnaobj Object
From x$kgllk
Where kgllkses in (select saddr from v$session where sid in ( select sid from v$session_wait where event ='library cache lock'))
And kgllkreq > 0;
HANDLE REQUEST OBJECT
---------------- ---------- ------------------------------------------------------------
0000000519B9A4F8 3 MOLECULAR_STRUCTURE_CHEM_REPR
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
000000051D3899B8 3 MOLSTRUCT_COMP_MOLTABLE
0000000519C36978 3 V_COMPOUND
0000000519B9BB68 3 V_MOLECULAR_STRUCTURE
0000000519C36978 3 V_COMPOUND
000000051D3899B8 2 MOLSTRUCT_COMP_MOLTABLE
0000000519C34D58 3 COMPOUND
000000051D3899B8 3 MOLSTRUCT_COMP_MOLTABLE
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
0000000519C34D58 3 COMPOUND
进行hang analyze
Found 11 objects waiting for
<0/56/52600/0x137080c8/29976/No Wait>
Found 11 objects waiting for
<0/158/23970/0x136fb488/7241/No Wait>
Open chains found:
Chain 1 :
<0/56/52600/0x137080c8/29976/No Wait>
-- <0/18/42294/0x13724f08/6261/library cache lock>
Chain 2 :
<0/131/2173/0x13714248/10976/No Wait>
-- <0/87/45424/0x13705b28/1124/library cache lock>
-- <0/149/417/0x13721ea8/654/library cache lock>
Chain 3 :
<0/158/23970/0x136fb488/7241/No Wait>
-- <0/18/42294/0x13724f08/6261/library cache lock>
Other chains found:
Chain 4 :
<0/22/63087/0x13707b68/5712/No Wait>
Chain 5 :
<0/40/61904/0x1370ec48/20031/library cache lock>
-- <0/41/51562/0x136fb9e8/22334/library cache lock>
-- <0/206/23252/0x13706b48/27227/library cache lock>
-- <0/247/61375/0x1370b688/4605/library cache lock>
Chain 6 :
<0/99/27928/0x1370e188/19668/library cache lock>
-- <0/141/2849/0x1370d6c8/22052/library cache lock>
Chain 7 :
<0/113/51705/0x13715268/19018/No Wait>
Chain 8 :
<0/116/11413/0x136fe4e8/23128/No Wait>
Chain 9 :
<0/170/10881/0x13700528/7511/library cache lock>
-- <0/149/417/0x13721ea8/654/library cache lock>
Chain 10 :
<0/202/25627/0x13706088/28076/No Wait>
按照上述情况，察看blocker的信息
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 56
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
52600 ACTIVE 20371 49 0 0 20-JUN-12 24121943 00 0
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 131
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
2173 ACTIVE 331 1 0 0 13-SEP-12 14508166 000000052EBF25C0 3116404256
--相应sql_text
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_TEXT
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ --------------------------------------------------
2173 ACTIVE 331 1 0 0 13-SEP-12 14508225 select c.index_name, i.parameters from all_ind_col
umns c, all_indexes i where c.table_owner = 'ISAC_
CMSQ_RCG' and c.table_owner = i.owner and c.table_
name = 'MOLSTRUCT_COMP_MOLTABLE' and c.column_name
= 'CTAB' and c.index_name = i.index_name and i.do
midx_status = 'VALID' and i.index_type = 'DOMAIN'
and i.ityp_name = 'IXMDL'
SQL> select SERIAL#,STATUS ,ROW_WAIT_OBJ#,ROW_WAIT_FILE#, ROW_WAIT_BLOCK# ,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,SQL_ADDRESS,SQL_HASH_VALUE from v$session where sid='&1';
Enter value for 1: 158
SERIAL# STATUS ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET SQL_ADDRESS SQL_HASH_VALUE
---------- -------- ------------- -------------- --------------- ------------- --------- ------------ ---------------- --------------
23970 ACTIVE -1 0 0 0 21-JUN-12 24036119 00 0
这些存活超过半年且依旧active的sql，应该就是元凶了；
通过truss跟踪，发现全部在sleeping，统统杀掉；
$ truss -p 29976
read(7, 0x10352B886, 8208) (sleeping...)
^C$
$ kill -9 29976
$ ps -ef | grep 10976
oracle 19042 16603 0 09:13:27 pts/11 0:00 grep 10976
oracle 10976 1 0 Sep 13 ? 0:01 oracleJUSTIN (LOCAL=NO)
$ truss -p 10976
read(9, 0x10352A3F6, 8208) (sleeping...)
^C$ kill -9 10976
$ ps -ef | grep 7241
oracle 19639 16603 0 09:15:16 pts/11 0:00 grep 7241
oracle 7241 1 0 Jun 21 ? 0:00 oracleJUSTIN (LOCAL=NO)
$ truss -p 7241
read(7, 0x10352B886, 8208) (sleeping...)
^C$
$ kill -9 7241
然后不到2分钟，gathering就执行完毕；
疑问：
为什么都执行半年了会话状态依旧为active？ 且sid 131还能查到其执行的sql;
既然它们已经sleeping半年了，SunOS为何不能自动清除这些会话？
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-757230/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
MyISAM和InnoDB的行格式
下一篇：
linux 2.6的IO调度算法
请登录后发表评论
登录
全部评论
