Re: Interesting new V$ table in 11.2

From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Wed, 14 Sep 2011 16:51:39 +0000 (UTC)
Message-ID: <pan.2011.09.14.16.51.39_at_gmail.com>

On Wed, 14 Sep 2011 09:29:55 -0700, joel garry wrote:

> On Sep 14, 6:36 am, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:

>> Database version 11.2 (I don't have any 11.1 at hand) has the table
>> V$SESSION_BLOCKERS which lists blocking sessions on any instance.
>> Before this, I've had to write "black magic" queries using GV$LOCK and
>> ID1 and ID2 columns and GV$SESSION using V$ROW_OBJECT#, FILE#, BLOCK#
>> and ROW#. This helps a lot. Good job, Oracle! Diagnosing lock
>> contention has just become a lot easier, easy enough for a DBA 2.0.
>>
>> --http://mgogala.byethost5.com

>
> Oops, that was supposed to be select sid, username, serial#, process,
> nvl(sql_id,0), sql_address, blocking_session, wait_class, event, p1, p2,
> p3, seconds_in_wait from v$session where blocking_session_status =
> 'VALID' OR sid IN (select blocking_session from v$session where
> blocking_session_status = 'VALID')
>
> jg

The view is a direct select from an X$ table: SQL> select view_definition
  2 from v$fixed_view_definition
  3 where view_name='GV$SESSION_BLOCKERS';

VIEW_DEFINITION

---

select inst_id, sid, sess_serial#, wait_id, wait_event, wait_event_text,
blocker_instance_id, blocker_sid, blocker_sess_serial# from X$KSDHNG_SESSION_BLOCKERS SQL> desc X$KSDHNG_SESSION_BLOCKERS
 Name Null? Type

• --------

  ---

  ADDR RAW(4) INDX NUMBER INST_ID NUMBER SID NUMBER SESS_SERIAL# NUMBER WAIT_ID NUMBER WAIT_EVENT NUMBER WAIT_EVENT_TEXT VARCHAR2(64) BLOCKER_INSTANCE_ID NUMBER BLOCKER_SID NUMBER BLOCKER_SESS_SERIAL# NUMBER

I can't see the source of an X$ table.

-- 
http://mgogala.byethost5.com

Received on Wed Sep 14 2011 - 11:51:39 CDT