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

At last ,I've filed SR 7372626.992 for ORACLE.

--------------------------------------

---------------------------------------

最后更新下：

根据SR 7372626.992分析师的回答，已经可以确认这是一个bug。He will file a bug for it。