Friday, August 19, 2005

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 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;
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;

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, 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 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

No comments: