ORACLE:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
OS:redhat enterprise edition 2.6.18-8.el5 @ X86
根据文档,v$blocking_quiesce 会显示谁block了"alter system quiesce restricted;"命令,但是自己试一试才发现这个视图并不那么好用。
可以测试,让session 1 block session 2,然后v$blocking_quiesce并没有数据显示:
session 1:
SQL> create table test (id number);
Table created.
SQL> insert into test values(1);
1 row created.
session 2:
SQL> alter system quiesce restricted;
----it hung and is stuck by session 1
session 3:
SQL> select * from v$blocking_quiesce;
no rows selected
通过wait event,我们可以看出quiesce命令确实被block了:
SID USERNAME MACHINE EVENT PARAM W WT SQL ST LT LOGON_TIME
------ ---------- ------------------------------ ------------------------------ -------------------- ---- ---- ------------------------ -- ------ ----------
131 SYS sqlplus@HaoRedHat wait for possible quiesce fini 0/0/0 0 2 0/0 A 10438 10452
通过查询v$fixed_view_definition:
SQL> select VIEW_DEFINITION from v$fixed_view_definition where lower(view_name)='gv$blocking_quiesce';
VIEW_DEFINITION
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select inst_id, sid_kgskvft from x$kgskvft where active_kgskvft = 1 and mapped_cg_name_kgskvft <> 'SYS_GROUP'
SQL> select inst_id, sid_kgskvft ,mapped_cg_name_kgskvft from x$kgskvft
2 where active_kgskvft = 1 ;
INST_ID SID_KGSKVFT MAPPED_CG_NAME_KGSKVFT
---------- ----------- --------------------------------
1 127
1 130 SYS_GROUP
1 135
1 136
1 137
1 140
1 141
1 143
1 145
1 147
1 148
1 149
1 151
1 154
1 155
1 156
1 157
1 158
1 159
1 160
1 161
1 162
1 163
1 164
1 165
1 166
1 167
1 168
1 169
1 170
30 rows selected.
原来,block quiesce的session是sid=135,但是它的MAPPED_CG_NAME_KGSKVFT这一列是null,所以并没有被显示出来。
而其他MAPPED_CG_NAME_KGSKVFT为null的sid基本都是background进程。
如何区分开来呢?很简单,跟v$session join一下就行了,自己建一个view:
create or replace view v$blocking_quiesce2
as
select x.inst_id, v.sid,v.serial#,v.SQL_HASH_VALUE,v.PREV_HASH_VALUE,v.status,v.service_name from x$kgskvft x,v$session v
where active_kgskvft = 1
and (x.mapped_cg_name_kgskvft <> 'SYS_GROUP' or x.mapped_cg_name_kgskvft is null)
and v.sid=x.sid_kgskvft
and v.service_name<>'SYS$BACKGROUND';
SQL> set lines 180 pages 999
SQL> select * from v$blocking_quiesce2;
INST_ID SID SERIAL# SQL_HASH_VALUE PREV_HASH_VALUE STATUS SERVICE_NAME
---------- ---------- ---------- -------------- --------------- -------- ----------------------------------------------------------------
1 135 17 0 4172321976 INACTIVE haols
如上,这就是罪魁祸首。
同时,如果pstack session 2 的pid:
oracle@HaoRedHat: ~ > pstack 2405
#0 0x00865402 in __kernel_vsyscall ()
#1 0x005d3b54 in semtimedop () from /lib/libc.so.6
#2 0x0e57691f in sskgpwwait ()
#3 0x0e5758ae in skgpwwait ()
#4 0x0e2c3a44 in ksliwat ()
#5 0x0e2c33b1 in kslwaitctx. ()
#6 0x0e2c06f1 in kslwait ()
#7 0x0c5a03e0 in kcqdbqur ()
#8 0x0c48ad94 in kkyasy ()
#9 0x0e45f8e4 in kksExecuteCommand ()
#10 0x0e3c7820 in opiexe ()
#11 0x08b544d0 in kpoal8 ()
#12 0x0e3be5db in opiodr ()
#13 0x0e5361f2 in ttcpip ()
#14 0x089a877b in opitsk ()
#15 0x089aa9d0 in opiino ()
#16 0x0e3be5db in opiodr ()
#17 0x089a4e46 in opidrv ()
#18 0x08c1623f in sou2o ()
#19 0x08539abb in opimai_real ()
#20 0x08c19a12 in ssthrdmain ()
#21 0x08539a38 in main ()
通过查询metalink doc:175982.1
kco kcq kcra kcrf kcrfr kcrfw kcrp kcrr kcs kct kcv | rcv | various buffer cache operation such as quiesce operation , managing fast start IO target, parallel recovery operation , etc. |
At last ,I've filed SR 7372626.992 for ORACLE.
--------------------------------------
---------------------------------------
最后更新下:
根据SR 7372626.992分析师的回答,已经可以确认这是一个bug。He will file a bug for it。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15415488/viewspace-539871/,如需转载,请注明出处,否则将追究法律责任。