Re: Interesting new V$ table in 11.2

From: Noons <wizofoz2k_at_gmail.com>
Date: Thu, 15 Sep 2011 01:37:52 -0700 (PDT)
Message-ID: <9aec8dda-dd2a-492c-8493-ed1e91d3e1f7_at_a13g2000yqd.googlegroups.com>

On Sep 14, 11:36 pm, 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

Am I the only one using
select * from dba_waiters
???
From dba_views:
SQL> select text from dba_views where view_name = 'DBA_WAITERS'; TEXT

---

select /*+ordered */ w.sid

      ,s.ksusenum
      ,decode(r.ksqrsidt,


                'MR', 'Media Recovery',

                'RT', 'Redo Thread',

                'UN', 'User Name',

                'TX', 'Transaction',

                'TM', 'DML',

                'UL', 'PL/SQL User Lock',

                'DX', 'Distributed Xaction',

                'CF', 'Control File',

                'IS', 'Instance State',

                'FS', 'File Set',

                'IR', 'Instance Recovery',

                'ST', 'Disk Space Transaction',

                'TS', 'Temp Segment',

                'IV', 'Library Cache Invalidation',

                'LS', 'Log Start or Switch',

                'RW', 'Row Wait',

                'SQ', 'Sequence Number',

                'TE', 'Extend Table',

                'TT', 'Temp Table',

                r.ksqrsidt)
      ,decode(l.lmode,
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                l.lmode)
      ,decode(bitand(w.p1,65535),
                0, 'None',           /* Mon Lock equivalent */
                1, 'Null',           /* N */
                2, 'Row-S (SS)',     /* L */
                3, 'Row-X (SX)',     /* R */
                4, 'Share',          /* S */
                5, 'S/Row-X (SSX)',  /* C */
                6, 'Exclusive',      /* X */
                to_char(bitand(w.p1,65535)))
      ,r.ksqrsid1, r.ksqrsid2

  from v$session_wait w, x$ksqrs r, v$_lock l, x$ksuse s

 where w.wait_Time = 0
   and w.event like 'enq:%'
   and r.ksqrsid1 = w.p2
   and r.ksqrsid2 = w.p3
   and r.ksqrsidt = chr(bitand(p1,-16777216)/16777215)||
                   chr(bitand(p1,16711680)/65535)
   and l.block = 1
   and l.saddr = s.addr

   and l.raddr = r.addr
   and s.inst_id = userenv('Instance') Received on Thu Sep 15 2011 - 03:37:52 CDT