Spotlight on Oracle does not show currently executing SQL plan (4267488)

In Oracle 10g, the cost based optimizer can change the exeuction plan---even with no recent changes to statistics--based on 'bind variable peeking'. Basically, your sql execution plan 'changes, out of the blue' for no apparent reason. See Metalink doc: 387394.1 for more info.

In Spotlight, if you go into the top sessions screen, see a 'high cpu utilization" user, or a 'high logical I/O' user, pull up the currently executing sql, and then select the explain plan, you see a 'just generated plan'....not necessarily the plan that the sql is executing. Not helpful, since the new plan may be fine, but the executing plan may be horrid.

The only way to tell what the sql is executing is to look at v$sql_plan_statistics_all, using the SQL_ID and SQL_HASH_VALUE from v$session.

Identifying these processes today is very problematic. Having the top sessions screen pull the execution plan from the v$sql_plan_statistics_all, and maybe even indicating that there is a decscrepancy between the executing plan, and straight explain plan would be very, very helpful.

TopSessions does indeed show the execution plan as shown by EXPLAIN PLAN and as noted, it is not necessarily the plan that is executing.

From Spotlight on Oracle 5.0 and later, we show the actual cached SQL Execution Plan in TopSQL. Use the TopSQL wizard to find the SQL of interest, then navigate to the "SQL Plan" tab in the sub-drilldown.

We have a high-priority requirement in our requirements database to link to the SQL Details screen from wherever SQL is shown in the product. This will assist users in navigating to find the actual cached plan. This would be for a future version of Spotlight on Oracle.