Many times we have been called for the poor performance of a database and it has been narrowed down to a SQL statement. Subsequent analysis have shown that the execution plan has been changed and a wrong execution plan was being used.
Resolution normally, is to fix the execution plan in 11g by running
variable x number begin :x := dbms_spm.load_plans_from_cursor_cache( sql_id=>'&sql_id', plan_hash_value=>&plan_hash, fixed=>'YES'); end; /
or for 10g, SQL_PROFILE is created as mentioned in Carlos Sierra’s blog .
A pro-active approach can be to mine AWR data for any SQL execution plan changes.
Following query from dba_hist_sqlstat can retrieve the list of SQL IDs whose plans have changed. It orders the SQL IDs,so that those SQL IDs for which maximum gains can be achieved by fixing plan, are listed first.
spool sql_with_more_than_1plan.txt set lines 220 pages 9999 trimspool on set numformat 999,999,999 column plan_hash_value format 99999999999999 column min_snap format 999999 column max_snap format 999999 column min_avg_ela format 999,999,999,999,999 column avg_ela format 999,999,999,999,999 column ela_gain format 999,999,999,999,999 select sql_id, min(min_snap_id) min_snap, max(max_snap_id) max_snap, max(decode(rw_num,1,plan_hash_value)) plan_hash_value, max(decode(rw_num,1,avg_ela)) min_avg_ela, avg(avg_ela) avg_ela, avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain, -- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets, -- avg(avg_buffer_gets) avg_buf_gets, max(decode(rw_num,1,sum_exec))-1 min_exec, avg(sum_exec)-1 avg_exec from ( select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec, row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id from ( select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets, sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec, min(snap_id) min_snap_id, max(snap_id) max_snap_id from dba_hist_sqlstat a where exists ( select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id and a.plan_hash_value != b.plan_hash_value and b.plan_hash_value > 0) and plan_hash_value > 0 group by sql_id, plan_hash_value order by sql_id, avg_ela ) order by sql_id, avg_ela ) group by sql_id having max(decode(rw_num,1,sum_exec)) > 1 order by 7 desc / spool off clear columns set numformat 9999999999
The sample output for this query will look like
SQL_ID MIN_SNAP MAX_SNAP PLAN_HASH_VALUE MIN_AVG_ELA AVG_ELA ELA_GAIN MIN_EXEC AVG_EXEC ------------- -------- -------- --------------- -------------------- -------------------- -------------------- ------------ ------------ ba42qdzhu5jb0 65017 67129 2819751536 11,055,899,019 90,136,403,552 79,080,504,532 12 4 2zm7y3tvqygx5 65024 67132 362220407 14,438,575,143 34,350,482,006 19,911,906,864 1 3 74j7px7k16p6q 65029 67134 1695658241 24,049,644,247 30,035,372,306 5,985,728,059 14 7 dz243qq1wft49 65030 67134 3498253836 1,703,657,774 7,249,309,870 5,545,652,097 1 2
MIN_SNAP and MAX_SNAP are the minimum/maximum snap id where the SQL statement occurs
PLAN_HASH_VALUE is the hash_value of the plan with the best elapsed time
ELA_GAIN is the estimated improvement in elapsed time by using this plan compared to the average execution time.
Using the output of the above query, sql execution plans can be fixed, after proper testing. This method can help DBAs pin-point and resolve problems with SQL execution plans, faster.
Looks interesting. Just want to make sure I understand. You take the PLAN_HASH_VALUE in the query and use this in the dbms_spm.load_plans_from_cursor_cache function?
That’s correct. Have done this many times, when there is a report of a query performing badly.
just to follow the example:
variable x number
and it would fix ba42qdzhu5jb0?
Yes that should fix the plan, if the plan is available in the cursor cache. If the plan is stored in AWR tables, you can use DBMS_SPM.LOAD_PLANS_FROM_SQLSET to store sql baselines. The sql statements with the correct plan can be put in the sqlset using DBMS_SQLTUNE.LOAD_SQLSET.