CA Clarity Tuesday Tip: Tracking Oracle Execution Plan Changes

Discussion created by Shawn_Moore Employee on Jun 15, 2011
Latest reply on Jun 15, 2011 by Chris_Hackett
Title: CA Clarity Tuesday Tip: Tracking Oracle Execution Plan Changes

CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 6/14/2011

Occasionally Oracle will change execution plan in favor of a poor plan. This can be hard to track down manually, but with the following query, you can plug in the sql id of the poorly performing query (which you could retrieve from an AWR report) and then inspect the plan changes.

-- This script will show all the different plan changes over some time.
-- This is useful for determining when a plan changed occurred and how it impacted execution time.
-- Replace the "sql_id_here" with the sql_id obtained from an AWR report.

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
where sql_id = 'sql_id_here'
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3

See the attachment below for an example of the output.

The key things to note are changes in the plan_hash_value correlating with a change in avg_etime (execution time). In this case the average execution was around .017 before 12:30, but on the next snapshot, we see that the average execution jumped to 0.39 and higher. We also see that the plan has value changed from 1777660502 to 4233992792. This is our proof that Oracle changed the plan with one that was less efficient.

Now getting Oracle to use a more efficient plan as a whole article in itself, but typically gathering stats on the tables accessed in the query at a higher percentage can sometimes flip it back to a good plan. Using OEM's tuning advisor can also set it back to a good plan.