First page Back Continue Last page Overview Graphics
Runtime Plan
Query V$SQL_PLAN to see run time plan
Actual plan
- EXPLAIN output is predicted plan
A new window to the shared pool
Plan is eventually aged out
Notes:
In the past, we have always used the EXPLAIN command (or trace) to see the plan (access path) for a query. However, EXPLAIN will show us what the plan will probably be - it can actually be different when the query is run. Why? Perhaps the environment is different when the query is executed, e.g. different indexes are available. Perhaps the sort_area_size parameter is different. Perhaps cursor sharing is in effect or stored outlines are being used. All of these can have an impact on plan choice.
Oracle9i addresses this issue with V$SQL_PLAN, which was introduced in Oracle9i Release 1. V$SQL_PLAN is the actual historical look at the plan used to execute the query. The plan in the shared pool is not new; what is new is the ability to see it.
V$SQL_PLAN is a dynamic performance view - it will be reset (i.e. your query will no longer be seen by querying the view) - if the query is aged out of the shared pool, the database is restarted or you execute the ALTER SYSTEM FLUSH SHARED_POOL command.