Tom Kyte has posted a way to display the SQL Explain Plan from v$sql_plan in a formatted way in Oracle 9i on AskTom. Whereas this is easily possible in 10g with DBMS_XPLAN, in 9i this poses a bit of a challenge:

• Step 1: Create a view dynamic_plan_table

create or replace view dynamic_plan_table
as
select
rawtohex(address) || ‘_’ || child_number statement_id,
sysdate timestamp, operation, options, object_node,
object_owner, object_name, 0 object_instance,
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;

• Step 2: Extract SQL child_number, rawtohex(address) from v$sql for a known SQL hash value:

SQL> select rawtohex(address) , child_number from v$sql where hash_value = ‘4163478529’;

• Step 3: Query DBMS_XPLAN with created view

SQL> r select plan_table_output from TABLE( dbms_xplan.display ( ‘dynamic_plan_table’, (select rawtohex(address)||’_’||child_number x from v$sql where hash_value = ‘3471874038’ AND rawtohex(ADDRESS) = ‘00000003A924BBD0’ AND child_number = 1 ), ‘ALL’ ) )