v$blocking_quiesce 沒有資料

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                      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：

: ~ >  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。