固定执行计划3.1 之 dbms_spm

说明由于生产环境执行的sql变化较快，版本发布比较频繁，造成sql的执行计划不是很稳定，经常会有一些性能很差的sql出现

对于这些sql，我们可以使用sql_plan_baseline对执行计划进行绑定，从而使执行计划固定下来

 

注意：前提是sql最好使用绑定变量，就算有的没有绑定变量，确定字段的值不会改变才行，因为是针对sql_id进行的绑定，

        如果sql文本改变，绑定也就无意义了  

 

解决办法： a. 通过  alter system set cursor_sharing=FORCE;   让系统自动绑定变量

                   (在11.2.0.3版本中测试，经绑定后v$SQL_PLAN执行计划变成了新的执行计划，但是通过set autot on 查看执行计划未改变)

                 b.文本方式，可能通过 sqlprofile 的方式，如自动方式就可以用 force_match=>true

 

方案一：如果查询的SQL有多个执行计划，其中有合适的执行计划，则可以直接绑定

1、查询每个执行计划消耗的资源情况

set lines 150 pages 999;
 col p_user for 99999 ;
 col execs for 9999999 ;
 select a.INSTANCE_NUMBER inst_id,to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24:mi:ss') time,plan_hash_value,buffer_gets_delta/executions_delta get_exec, disk_reads_delta/executions_delta read_exec,cpu_time_delta/executions_delta/1000 cpu_exec_ms ,elapsed_time_delta/executions_delta/1000 elaps_exec_ms ,parsing_schema_id p_user,ROWS_PROCESSED_delta/executions_delta rows_exec,EXECUTIONS_DELTA execs
    from dba_hist_sqlstat a, dba_hist_snapshot b
    where a.sql_id='&sql_id'
    and a.snap_id = b.snap_id
    and a.instance_number = b.instance_number
    and b.END_INTERVAL_TIME between sysdate - 3 and sysdate
    and executions_delta>0 order by 2,1;
​
SELECT DISTINCT(PLAN_HASH_VALUE) FROM V$SQL_PLAN t WHERE SQL_ID = '&sql_id';

　　

2、固定执行计划为 

SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;

　

declare
 l_pls number;
begin
 l_pls :=  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'&sql_id',plan_hash_value=>&plan_hash,fixed =>'YES',enabled=>'YES');
end;
/

　

​
SQL> select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines
​
SQL_HANDLE PLAN_NAME ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
QL_368f6240d3b95ad6 SQL_PLAN_3d3v2839vkqqqa3c44420 YES YES 34

　　

　

 

3、删除固定执行计划

declare 
xx PLS_INTEGER; 
BEGIN 
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_9ca68721a7101b8e',plan_name=>null); 
END; 
/

　　

 

方案二：如果没有合适的执行计划，就要通过自己分析，运用一些hint让sql产生比较好的执行计划

              或者其它类似SQL 有好的执行计划

 

1、让原来执行效率低的SQL 生成 HANDLE 

declare
  l_pls number;
begin
  l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id    => '&bad_sql_id',
                                                 plan_hash_value => &bad_sql_plan_hash_value,
                                                 enabled         => 'NO');           # 注意这里是NO 
end;
/

　　

 

2.  查询刚刚生成的 handle 

select sql_handle, plan_name, origin, enabled, accepted,fixed,creator,optimizer_cost,sql_text 
from dba_sql_plan_baselines where origin = 'MANUAL-LOAD' and ENABLED='NO' order by created desc ;

　　

3.  用新的SQL_ID  和 新的  sql_plan_hash_value ，绑定老的  sql_handle ，以固定旧的SQL的执行计划

 

declare
  l_pls number;
begin
  l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id          => '5qp9tjw81a5un',  -- new_SQL_ID'
                                                 plan_hash_value => 1175071277, --new_plan_hash_value
                                                 sql_handle      => 'SQL_9ca68721a7101b8e', --OLD_handle
                                                 fixed =>'YES'
                                                 );
end;
/

　　

 

以上是从内存中获得较优的执行计划 ,当然也可以从AWR  和 sqlset 优化集中获取 ：例从SQLSET中获取

DECLARE
my_plans PLS_INTEGER;
BEGIN
my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET( sqlset_name >'STS_PS',basic_filter=>'sql_id=''bxajng3zk2vn1''');
END;
/