绑定运行计划sql_plan_baseline[通俗易懂]

详细步骤：
 –1、找到问题sql，假设查询sql的运行计划，假设有合适的运行计划。直接进行绑定
 –查询sql运行计划相应的PLAN_HASH_VALUE
 SELECT DISTINCT(PLAN_HASH_VALUE) FROM V$SQL_PLAN t WHERE SQL_ID = ‘010cv4dvf6swv’ and child_number=’0′
 –绑定好的运行计划：
 declare
   l_pls number;
 begin
   l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id  => ‘524wzct86gu1d’,
                                                  plan_hash_value => 2554538542,
                                                  enabled         => ‘YES’);
 end;
 /
 2、假设没有合适的运行计划，就要通过自己分析，运用一些hint让sql产生比較好的运行计划
 –须要绑定的sql
 –oldSQL(id PLAN_HASH_VALUE)
 524wzct86gu1d
 2554538542
–新的sql
 –newSQL(id PLAN_HASH_VALUE)
 010cv4dvf6swv
 756701203
 –查询新的运行计划的sql_id
 select * from v$sql where sql_text like ‘%zhruoyu%’ –通过在hint中加一下特殊字符来查找
—新建制定SQLID的BASELINE依据old_sql id,PLAN_HASH_VALUE
 declare
   l_pls number;
 begin
   l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id    => ‘524wzct86gu1d’,
                                                  plan_hash_value => 2554538542,
                                                  enabled         => ‘NO’);  –注意这里是no
 end;
 /
—确定原始运行计划的 sql_handle
 select sql_handle, plan_name, origin, enabled, accepted,fixed,creator,optimizer_cost,sql_text 
 from dba_sql_plan_baselines where origin = ‘MANUAL-LOAD’ order by created desc
 
 SQL_HANDLE:SQL_66108ad9595208fc                                      
 PLAN_NAME:SQL_PLAN_6c44av5cp427w65e519aa
 —与正确的运行计划做关联
 declare
   l_pls number;
 begin
   l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id          => ‘010cv4dvf6swv’, — new_SQL_ID’
                                                  plan_hash_value => 756701203, –new_plan_hash_value
                                                  sql_handle      => ‘SQL_66108ad9595208fc’ –OLD_handle
                                                  );
 end;
 /
—删除错误的运行计划
 declare
   l_pls number;
 begin
   l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => ‘SQL_66108ad9595208fc’, –sql_handle_for_original
                                            plan_name  => ‘SQL_PLAN_6c44av5cp427w65e519aa’ –sql_plan_name_for_original                                        
                                            );
 end;
 /
–检查一下
 select sql_handle, plan_name, origin, enabled, accepted,fixed,creator,optimizer_cost,sql_text 
 from dba_sql_plan_baselines where origin = ‘MANUAL-LOAD’ and sql_handle=’SQL_66108ad9595208fc’
–完毕