V$BLOCKING_QUIESCE 何时有数据?

stuff990 · 发表于 2008-8-28 11:29

V$BLOCKING_QUIESCE 何时有数据?

oracle 文档上说

QUOTE:
--------------------------------------------------------------------------------
You can determine the sessions that are blocking
the quiesce operation by querying the V$BLOCKING_QUIESCE view.
--------------------------------------------------------------------------------

做如下操作
1  session 1 登陆 update tmp_a set a=1;
2  session 2 ,sqlplus / as sysdba ; alter system quiesce  restricted; 此时该语句阻塞
3  session 3  ,sqlplus / as sysdba ; select * from V$BLOCKING_QUIESCE ; 没有任何输出
4  回到session 1 ,commit;
5  回到session 2中查看, system alterd.  确实是session1 阻塞了quiesce

什么时候 V$BLOCKING_QUIESCE  才有数据呢?

Yong Huang · 发表于 2008-8-29 01:29

Always state your Oracle version.

I reproduced the problem in 10.2.0.4. The blocking session is not in SYS_GROUP consumer group according to v$rsrc_session_info, so v$blocking_quiesce ignores it. You can find this out by checking this view's definition in v$fixed_view_definition:

SQL> select view_definition from v$fixed_view_definition where 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'

So, a workaround can be directly querying its underlined x$ table without the group restriction:

select inst_id, sid_kgskvft from x$kgskvft where active_kgskvft = 1;

I encourage you to do more research, and also search on Metalink, possibly open an SR with Oracle.

Yong Huang

stuff990 · 发表于 2008-8-29 14:30

version: 10.2.0.1 + windows xp(32)

v$blocking_quiesce 是来源于 gv$blocking_quiesce 的，定义如下：
select inst_id, sid_kgskvft from x$kgskvft       where active_kgskvft = 1       and mapped_cg_name_kgskvft <> 'SYS_GROUP'

查询x$kgskvft 发现 SID 145  的  MAPPED_CG_NAME_KGSKVFT is NUll
145 正是我做update的 session ID

SQL> select inst_id, sid_kgskvft ,mapped_cg_name_kgskvft from x$kgskvft       where active_kgskvft = 1  ;

INST_ID  SID_KGSKVFT    MAPPED_CG_NAME_KGSKVFT
---------- ----------- ----------------------------------------------------------------
      1       145
      1       151                SYS_GROUP
      1       159                SYS_GROUP

viadeazhu · 发表于 2009-1-22 12:37

好老的帖子啊，我才发了SR 7372626.992,oracle已经基本确定为bug。
我自己的一个workaround是：
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';

Oracle给的一个workaround是：
select p.spid, s.osuser, s.machine, s.username, s.sid, s.serial#
from v$session s, v$process p
where p.addr = s.paddr
and s.sid in (select sid from v$lock where type = 'TX');