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.
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center