Previously I wrote about how to view a plan of a sql. Today I will tell you about a good feature DBMS_XPLAN.DISPLAY_AWR function comes with Oracle 10G which helps you to view plan of an old sql. . If you have license for tuning pack and diagnostics pack you can get historical information about the old SQLs which ran on your database. For more info about licensing feature of these packs refer to the Oracle® Database Licensing Information 10g Release 1 (10.1) manual
DBMS_XPLAN.DISPLAY_AWR displays the contents of an execution plan stored in the AWR.
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER DEFAULT NULL,
db_id IN NUMBER DEFAULT NULL,
format IN VARCHAR2 DEFAULT TYPICAL);
If db_id paramater is not specified the function will use the id of the local db.
If you don’t specify plan_hash_value parameter, function will bring all the stored execution plans for the given sql_id
Format parameter have so many capabilities you can get the list from the manual.
Simple demonstration ; (all tests are done with 10.2.0.1 Express Edition)
You can also use DBA_HIST_SQL_PLAN table for viewing the historic plan info.
References Used ;