ITPub博客

首页 > Linux操作系统 > Linux操作系统 > v$blocking_quiesce 没有数据

v$blocking_quiesce 没有数据

原创 Linux操作系统 作者:viadeazhu 时间:2009-01-16 15:25:10 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-22

  • 博文量
    79
  • 访问量
    371766