Oracle 执行计划

执行计划必须要在内存或缓存中才能绑定，否则执行没效果；在内存里直接拿来使用，不在内存里需要加载AWR旧快照到内存中，再进行绑定

 一、内存中有好的执行计划plan_hash_value 

 1、查找SQL_ID 及PLAN_HASH（内存中可以查询到）

select sql_id,plan_hash_value,executions from v$sql where sql_ID='';

2、创建固化执行计划

declare
u int;
begin
u:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'4atz1zf39vycr',plan_hash_value=>'2616822811',fixed=>'YES'，enabled => 'YES');
DBMS_OUTPUT.put_line ('');
end;
/

3、检查创建是否成功

select * from dba_sql_plan_baselines;

4、修改属性

set serveroutput on;
declare
  plans_alter pls_integer;
begin
  plans_alter := dbms_spm.alter_sql_plan_baseline(sql_handle => 'sql_60fea6835db2e913',plan_name => 'sql_plan_61zp6hdfv5u8mb860bcf2',attribute_name => 'fixed',attribute_value => 'YES');
  dbms_output.put_line('plan:'|| plans_alter);
end;
/

5、验证好的执行计划

select sql_id,plan_hash_value,executions from v$sql where sql_ID='';

6、删除坏的执行计划

select address,hash_value from v$sqlarea where sql_id='&sql_id';
exec dbms_shared_pool.purge ('&address,&hash_value','C');

二、内存中没有好的执行计划plan_hash_value

1、查找SQL_ID 及PLAN_HASH（内存中查询不到）

select sql_id,plan_hash_value,executions from v$sql where sql_ID='';

2、从AWR中找出执行计划

select sql_id, plan_hash_value, count(*)
  from (select distinct sql_id, plan_hash_value
          from dba_hist_sql_plan
         where sql_id = '&sql_id')
 group by sql_id, plan_hash_value
 order by 3 desc;

或

select distinct plan_hash_value, sql_id
  from dba_hist_sqlstat
 where sql_id = '&sql_id';

3、选择snap_id中cost最少最好的plan

select ss.snap_id,
       ss.instance_number,
       begin_interval_time,
       sql_id,
       plan_hash_value,
       optimizer_cost,
       disk_reads_total,
       buffer_gets_total,
       rows_processed_total,
       cpu_time_total,
       elapsed_time_total,
       iowait_total,
       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
  from dba_hist_sqlstat s, dba_hist_snapshot ss
 where sql_id = '&sql_id'
   and ss.snap_id = s.snap_id
   and ss.instance_number = s.instance_number
   and executions_delta > 0
 order by 1, 2, 3;

4、创建STS

BEGIN
 dbms_sqltune.create_sqlset(
 sqlset_name => 'STS_$sid_id',
 description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;
/

5、从snap_id中Load STS

declare
 cur sys_refcursor;
begin
 open cur for
 select value(p)
 from table(
 dbms_sqltune.select_workload_repository(begin_snap=>6819, end_snap=>6820,basic_filter=>'sql_id =''&sql_id''',attribute_list=>'ALL')) p;
 dbms_sqltune.load_sqlset( sqlset_name=> 'STS_【sid_id】', populate_cursor=>cur);
 close cur;
end;
/

或

declare
 cur sys_refcursor;
begin
 open cur for 
 select value(p)
 from table(
 dbms_sqltune.select_workload_repository
 (begin_snap=>6819, --老执行计划起始的snap id
 end_snap=>6820, --老执行计划结束的snap id
 basic_filter=>'sql_id = ''4atz1zf39vycr''', --老执行计划起始的sql_id
 attribute_li