Running DBMS_XPLAN against V$SQL_PLAN

If you haven't used it, DBMS_XPLAN gives a much more complete output from PLAN_TABLE that some of the older, more common queries used. V$SQL_PLAN contains the real and actual query plan being used for a query (rather than the one EXPLAIN might give for the same statement). Getting one to work with the other is tricky.

Tom Kyte's Effective Oracle by Design (and probably asktom.oracle.com) gives a mechanism for this, but it requires a view in a schema with an explicit grant on SYS.V_$SQL_PLAN.

If you've got a nice DBA who will set that up, great.
If not, but you still have access to V$SQL_PLAN, and privileges for creating types and functions, here's another mechanism.

First it creates a record type matching the plan_table, then a collection of that record type.

create or replace type rec_plan_table is object (
Statement_Id VARCHAR2(30) ,
Timestamp DATE ,
Remarks VARCHAR2(80) ,
Operation VARCHAR2(30) ,
Options VARCHAR2(30) ,
Object_Node VARCHAR2(128) ,
Object_Owner VARCHAR2(30) ,
Object_Name VARCHAR2(30) ,
Object_Instance NUMBER(38) ,
Object_Type VARCHAR2(30) ,
Optimizer VARCHAR2(255) ,
Search_Columns NUMBER(38) ,
Id NUMBER(38) ,
Parent_Id NUMBER(38) ,
Position NUMBER(38) ,
Cost NUMBER(38) ,
Cardinality NUMBER(38) ,
Bytes NUMBER(38) ,
Other_Tag VARCHAR2(255) ,
Partition_Start VARCHAR2(255) ,
Partition_Stop VARCHAR2(255) ,
Partition_Id NUMBER(38) ,
Other VARCHAR2(2000) ,
Distribution VARCHAR2(30) ,
Cpu_Cost NUMBER(38) ,
Io_Cost NUMBER(38) ,
Temp_Space NUMBER(38) ,
Access_Predicates VARCHAR2(4000) ,
Filter_Predicates VARCHAR2(4000) )
/

create or replace type tab_plan_table is table of rec_plan_table
/

Then there's a function which, using invoker rights and dynamic SQL to avoid the need for the explicit grant. This reads from v$sql_plan and returns the collection type. I've hardcoded the SCOTT user here. Replace that with the user owning the REC_PLAN_TYPE created above.
You can omit that if you are only ever going to run it from the user owning that object, but why restrict yourself ?

create or replace function f_pt (p_sql_hash in number) return tab_plan_table
authid current_user is
c_1 sys_refcursor;
v_return tab_plan_table;
begin
-- YOU WILL NEEED TO PUT YOUR SCHEMA OWNER FOR THE REC_PLAN_TABLE OBJECT
-- IN PLACE OF SCOTT.
open c_1 for
'select '||
' SCOTT.rec_plan_table(''STATEMENT'', '||
' sysdate, to_char(hash_value)||''_''||child_number, '||
' operation, options, object_node, '||
' object_owner, object_name, 0 , '||
' '' '' , optimizer, search_columns, id, parent_id, position, '||
' cost, cardinality, bytes, other_tag, partition_start, '||
' partition_stop, partition_id, other, distribution, '||
' cpu_cost, io_cost, temp_space, access_predicates, '||
' filter_predicates) '||
' from v$sql_plan '||
' where hash_value = :b1 ' using p_sql_hash;
fetch c_1 bulk collect into v_return;
close c_1;
return v_return;
end;
.
/

You'll probably want to make public grants on the function and object type, and possibly a public synonym too.
You can then query as follows :

SELECT plan_table_output
from TABLE( dbms_xplan.display
( 'table(f_pt(2782527334)),dual','STATEMENT','serial' ) )


The number is the hash_value for the SQL owning the plan, which you can get from V$SQL.
The reason DUAL is in there is that I couldn't get it to work without it.
The query which DBMS_XPLAN runs behind the scenes involves a CONNECT BY. Unfortunately, as I tried with this very simple test, 9.2.0.4 wouldn't give me any rows back.
select column_value from table(sys.DBMS_DEBUG_VC2COLL('a','b','c'))
start with column_value = 'a'
connect by (1=1)

Adding the dual seems to materialize the results from the function call. It may not be needed in later patches or releases. I've only tested this in Oracle 9.2.0.4. I believe that it is definately not usable pre-9i.

The TABLE_NAME parameter to DBMS_XPLAN.DISPLAY is size limited to 32 characters.
To get the hash_value in, plus the TABLE function and DUAL, the name of the function doing the work has to be 8 characters or less. In most cases, you won't be able to use a SCHEMA.F_PT (or even make the F_PT a more meaningful name). As such to make this available to multiple users, you'll want either synonyms or to use
ALTER SESSION SET CURRENT_SCHEMA....