Oracle DBA Pipeline has a nice tip on how to generate execution plans directly from the Library Cache, a feature introduced in Oracle9i that brought with it the capability to view execution plan information for all parsed cursors stored in the library cache.

This information is exposed through the view V and is fairly similar in format to the well known PLAN_TABLE, which is used by diagnostic tools such as tkprof and SQL*Plus autotrace. PLAN_TABLE is created by running the script /rdbms/admin/utlxplan.sql

The problem with using tkprof and autotrace has always been that they generate the execution plan at the point in time that they are run. Up until now, there has never been a way to retrospectively generate an execution plan for the point in time that the SQL was originally parsed.

This was fine in the days of the rule-based optimizer when plans were generated by following an ordered set of rules (and were therefore predictable and consistent) that you could test in synthetic performance test scenarios you created with a lot of time ..

Nowadays, most databases are configured to use the cost-based optimizer, so Oracle must rely on statistics in the data dictionary being up to date. Out of date statistics can lead to Oracle generating sub-optimal execution plans so it is important to know the execution plan that Oracle chose at the time the statement was parsed when performance tuning slow-running SQL statements.

To improve the display of execution plans, Oracle provides a neat PL/SQL table function packaged as DBMS_XPLAN.DISPLAY.

Read on at Oracle DBA Pipeline