2009/10/09

DBMS_XPLAN.DISPLAY_CURSOR for RAC database.

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')
return sys.dbms_xplan_type_table
pipelined is
l_line sys.dbms_xplan_type := sys.dbms_xplan_type(null);
begin
for i in (select distinct inst_id, CHILD_NUMBER
from gv$sql_plan_statistics_all
where sql_id = statement_id
order by 1, 2) loop
l_line.plan_table_output := 'INSTANCE#=' || i.inst_id ||
'; CHILD_NUMBER=' || i.CHILD_NUMBER;
pipe row(l_line);
for j in (select *
from table(dbms_xplan.display('gv$sql_plan_statistics_all',
null,
format,
'inst_id=' || i.inst_id ||
' and sql_id=''' ||
statement_id ||
''' and CHILD_NUMBER=' ||
i.CHILD_NUMBER))) loop
l_line.plan_table_output := j.plan_table_output;
pipe row(l_line);
end loop;
l_line.plan_table_output := '================================================';
pipe row(l_line);
end loop;
return;
end;

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