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’ ) )

Oracle gathers a wealth of information in the Automatic Workload Repository (AWR). We can query this data to get very important IO access time information.

• Create a working table because depending of the size of the AWR, the queries could take a long time and cause load on the database.

drop table mdecker.filestatistics;
create table mdecker.filestatistics as select t.snap_id, t.begin_interval_time, filename, file#, tsname, phyrds, singleblkrds, readtim, singleblkrdtim, phyblkrd from
dba_hist_filestatxs f ,
dba_hist_snapshot t
where
t.snap_id = f.snap_id and (
begin_interval_time between to_date('05.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('09.11.2007 17:00','DD.MM.YYYY HH24:MI')
OR
begin_interval_time between to_date('12.11.2007 09:00','DD.MM.YYYY HH24:MI') and
to_date('16.11.2007 17:00','DD.MM.YYYY HH24:MI') );

• Create a summary table

create table mdecker.filestatistics_summary as
select snap_id, begin_interval_time, sum(phyrds) phyrds, sum(singleblkrds) singleblkrds from mdecker.filestatistics
group by snap_id, begin_interval_time
order by snap_id

• Query the summary table

select snap_id,
y.begin_interval_time,
phyrds as prev_phyrds,
lead(phyrds) over(order by snap_id) current_physrds,
lead(phyrds) over(order by snap_id) - phyrds,
begin_interval_time as prev_interval_time,
lead(begin_interval_time) over(order by snap_id) current_time,
lead(begin_interval_time) over(order by snap_id) - begin_interval_time,
extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as sec,
extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time) as min,
(extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time),
(lead(phyrds) over(order by snap_id) - phyrds)/
((extract(minute from lead(begin_interval_time) over(order by snap_id) - begin_interval_time)*60)+extract(second from lead(begin_interval_time) over(order by snap_id) - begin_interval_time))
from filestatistics_summary y