Re: Interesting new V$ table in 11.2

From: joel garry <joel-garry_at_home.com>
Date: Wed, 14 Sep 2011 09:22:15 -0700 (PDT)
Message-ID: <bb313f30-644c-429c-b8fa-9f6d784d1206_at_x32g2000prf.googlegroups.com>

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

Taking a gander at (10.2) dbconsole with dbconsole, could this be the magic?

SELECT
s.status,s.client_identifier,s.client_info,s.sql_id,s.sql_child_number,a.name,s.last_call_et,s.prev_sql_id,s.prev_child_number,s.module,s.action,s.blocking_session,s.row_wait_file#,s.row_wait_block#,s.row_wait_row#,s.event,s.wait_class,decode(s.wait_time, 0,s.seconds_in_wait,wait_time),s.p1text,s.p2text,s.p3text,s.p1,s.p2,s.p3,decode(s.p2text,'object #',s.p2,s.row_wait_obj#),s.failed_over,s.pdml_status,s.pddl_status,s.pq_status,s.current_queue_duration,s.sql_trace FROM v$session s, audit_actions a WHERE s.sid = :1 AND s.command = a.action AND s.serial# = :2

We can all make fun of DBA 2.0, but I can't help but wonder if actually having to put out a product that does this has some feedback as to what should be there to begin with.

jg

--
_at_home.com is bogus.
"Stopped OEM and Problem went away. ! Is OEM really Schroedinger's
Cat? !" - Howard Latham

Received on Wed Sep 14 2011 - 11:22:15 CDT