A comment on my previous posting about dbms_xplan pointed out that in 10g you also get the function dbms_xplan.display_awr that allows you to report historical execution plans from the AWR (automatic workload repository).
This is true, and there is yet another function dbms_xplan.display_sqlset which allows you to report execution plans from SQL Tuning Sets if you have been using the Automatic SQL Tuning Tools. But to use these functions you do need to purchase the Tuning Pack licence and the Diagnostic Pack licence.
There is also an enhancement to the basic dbms_xplan.display function itself, which can now take a fourth parameter called filter_preds. This could be quite useful if you decide to build a permanent library of statements with their executions plans – perhaps using a clone of the plan_table to hold the plans, using something like the hash_value combined with the plan_hash_value as the statement_id.
You could even consider using it to query the perfstat table stats$sql_plan if you regularly take statspack snapshots at level 6 – which capture the plans from v$sql_plan for the sql statements captured by level 5. For example, the following call would report the execution plan of a statement with a plan_hash_value of 3992920156:
select * from table( dbms_xplan.display( 'perfstat.stats$sql_plan', null, null, 'plan_hashvalue=3992920156' ) ) ;
This isn’t really an ideal way of reporting execution plans from the statspack tables though. For a start, you have to find a convenient way of collecting the plan_hash_value before you get to it.
If you want to make best use of snapshots at level 6, then you really need to know the script sprepsql.sql (and for users of the AWR (automatic workload repository) there is the awrsqrpt.sql script) in the $ORACLE_HOME/rbdbms/admin subdirectory.
This script takes as its inputs the snapshot ids for a start and end snapshot, plus a hash_value - which you would have picked up from the “Top SQL” sections of a normal statspack report.
The output is a summary of the cost of the statement for the period covered by the snapshot, and a simplified listing of any execution plans for the statement that existed in v$sql_plan in that period. [Following a comment by Mathew Butler below - for "cost" read "activity and resource consumption"]
Footnote: I have recently heard of a bug in v$sql_plan in earlier versions of 9.2 that causes queries against v$sql_plan to hang the database (jamming it on one of the library cache latches) with an instance restart being the only recovery option. Essentially this happens if you have a sufficiently complex statement in v$sql_plan when you query it. I haven’t checked the details yet, but “sufficiently complex” may mean something like “more than 255 predicates”. This is fixed, apparently by 22.214.171.124.
Footnote 2: Statspack doesn’t collect the filter_predicates and access_predicates (or other_xml) columns from v$sql_plan. So it does lose some critical detail.
Footnote 3: The 10g version of the script that creates the dbms_xplan package contains a warning that the filter_preds parameter allows “SQL Injection” to take place. (See Pete Finnigan’s website for everything you need to know about this, and other, Oracle security topics). In this case, I don’t think it’s a really a threat since the package is created with authid current_user, so you won’t be able to use SQL injection through the base package to do anything that your account couldn’t normally do. However, if you are a DBA and wrap your own dbms_xplan package around the supplied package – to hide the display_awr, display_sqlset and display_cursor calls, for example – then you must remember to create your own package as authid current_user, or it will be possible for anyone you allow to call your package to do almost anything that your DBA privileges allow you to do.