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  才有数据呢?

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

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

好老的帖子啊，我才发了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');

QUOTE:

---

原帖由 viadeazhu 于 2009-1-21 22:37 发表
好老的帖子啊，我才发了SR 7372626.992,oracle已经基本确定为bug。

---

We can't view your SR. But can you tell us the bug number?

Yong Huang

学习了

bug 7832504

QUOTE:

---

原帖由 Yong Huang 于 2009-1-22 22:19 发表


We can't view your SR. But can you tell us the bug number?

Yong Huang

---

BUG Reference 7832504