Modified Session Queries for RAC

[MjH]

We run several Oracle RAClusters, and I've found that the Tools -> Sessions default queries are insufficient as they only look at the instance to which you are currently connected.

I made the following changes to the queries so that I can view the sessions in all instances, plus a few extra detail queries for additional information. Someone might benefit from these, so I thought I'd post them:

All Sessions filter:

select * from gv$session
where username is not null
order by logon_time, sid

My Sessions filter:

select * from gv$session
where username = user
order by logon_time, sid

Active Sessions filter:

select * from gv$session
where username is not null and status = 'ACTIVE'
order by logon_time, sid

PL/SQL Developer Active Sessions filter: (An additional filter to see what developers are up to )

select * from gv$session where username is not null and status = 'ACTIVE' and program = 'plsqldev.exe' order by logon_time, sid

Cursor detail:

select * from gv$open_cursor where sid = :sid and inst_id = :inst_id

SQL Text detail:

select sql_text from gv$sqltext_with_newlines
where address = hextoraw(:sql_address)
and hash_value = :sql_hash_value
and inst_id = :inst_id
order by piece
/* concatenate */

Statistics detail:

select names.name, stats.statistic#, stats.value
from gv$sesstat stats, gv$statname names
where stats.sid = :sid
and names.Statistic# = stats.Statistic#
and stats.inst_id = :inst_id
and stats.inst_id = names.inst_id
order by stats.statistic#

Locks detail:

select l.*, o.owner object_owner, o.object_Name
from  sys.all_objects o, gv$lock l
where l.sid = :sid and l.type = 'TM' and o.object_id = l.id1 and l.inst_id = :inst_id

SQL Monitor detail:

select m.sql_text, dbms_sqltune.report_sql_monitor(sql_id => m.sql_id, type => 'HTML', report_level => 'ALL') AS report
from  gv$sql_monitor m
where m.sid = :sid and m.session_serial# = :serial# and m.inst_id = :inst_id
order by 1

SQL Explain Plan detail (new query for Explain Plan of SQL for current session):

select
 child_number xms_child_number,
 case when access_predicates is not null then 'A' else ' ' end ||
   case when filter_predicates is not null then 'F' else ' ' end xms_pred,
 id xms_id,
 lpad(' ', depth * 1, ' ') || operation || ' '  || options xms_plan_step,
 object_name xms_object_name,
 cost xms_opt_cost,
 cardinality xms_opt_card,
 bytes xms_opt_bytes,
 optimizer xms_optimizer,
 access_predicates,
 filter_predicates
 from
  gv$sql_plan
 where hash_value = :sql_hash_value
 and address = hextoraw(:sql_address)
 and inst_id = :inst_id
 and child_number = :sql_child_number
order by
  xms_child_number,
  xms_id

DBMS XPlan detail (a different way to get and display the Explain Plan):

select * from table(dbms_xplan.display('g' || 'v$sql_plan_statistics_all', null, 'TYPICAL', 'inst_id = ' || :inst_id || ' and ' || ' sql_id = ''' || :sql_id || ''' and CHILD_NUMBER = ' || :sql_child_number))

Kill command detail (I added this because the Kill button doesn't support RAC, so this displays a kill command I can copy/paste into a Command Window):

select 'alter system kill session ''' || :sid || ', ' || :serial# || ', @' || :inst_id || ''';' from dual

MjH