Skip to content

Insight and analysis of technology and business strategy

Pro-active AWR Data Mining to find change in SQL Execution Plan

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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner