I had the two-node Oracle RAC till recently. And it was sufficient to use the dbms_xplan.display_cursor function to examine the query execution plans with run-time statistics with connection to required instance.
However, recently my oracle databases have migrated onto new hardware with eight nodes. It is become inconveniently to use dbms_xplan.display_cursor for examination of all instances and all child cursors plans.
For this purposes I have written wrapper for dbms_xplan.display function:
create or replace function sys.gdisplay(statement_id varchar2,
format varchar2 default 'TYPICAL')
l_line sys.dbms_xplan_type := sys.dbms_xplan_type(null);
for i in (select distinct inst_id, CHILD_NUMBER
where sql_id = statement_id
order by 1, 2) loop
l_line.plan_table_output := 'INSTANCE#=' || i.inst_id ||
'; CHILD_NUMBER=' || i.CHILD_NUMBER;
for j in (select *
'inst_id=' || i.inst_id ||
' and sql_id=''' ||
''' and CHILD_NUMBER=' ||
l_line.plan_table_output := j.plan_table_output;
l_line.plan_table_output := '================================================';
Now I can see execution plans and run-time statistics for certain query for all instances and child cursors without big effort.
But I was obliged to include the "sql_id=<...>" filter into filter_preds parameter, because if I use the statement_id parameter to point at certain cursor, I receive the error on my 10.2.0.4 SUSE 10 sp2 ia64 box:
ORA-12805: parallel query server died unexpectedly