执行计划状态及绑定

某个SQL_ID的执行计划状态及绑定
select SQL_PLAN_HASH_VALUE,round(avg(RUN_MINS),2) as avg_run_mins,count(*) from
(
SELECT T.SQL_ID,T.SQL_EXEC_ID,CAST(MAX(T.SAMPLE_TIME) AS DATE) EXEC_END_TIME,
T.SQL_EXEC_START as EXEC_START_TIME,ROUND((CAST(MAX(T.SAMPLE_TIME) AS DATE) - T.SQL_EXEC_START) * 1440，2) RUN_MINS,T.SQL_PLAN_HASH_VALUE, T.MODULE
from dba_hist_active_sess_history T
WHERE T.SQL_ID ='5w91hk4nmcmrx' AND T.SAMPLE_TIME > SYSDATE - 36
GROUP BY T.SQL_ID,T.SQL_EXEC_ID, T.SQL_EXEC_START, T.SQL_PLAN_HASH_VALUE, T.MODULE
ORDER BY EXEC_END_TIME DESC )
group by SQL_PLAN_HASH_VALUE
order by 2 desc;

--计划是否在内存
select sql_text,sql_id,hash_value,child_number,plan_hash_value,to_char(LAST_ACTIVE_TIME，'hh24:mi:ss') time
from v$sql a where sql_id='26kqp5puukbh8' and plan_hash_value='3059001790';

--①从库缓存载入
declare
l_plans_loaded pls_integer;
begin
l_plans_loaded:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'26kqp5puukbh8' ,plan_hase_value=>'3059001790');
end;
/

select * from dba_sql_plan_baseline where origin=’MANUAL-LOAD’ order by created desc;

--固定执行计划，将该基线转为fixed
declare
i natural;
begin
i:=dbms_spm.alter_sql_plan_baseline(
‘sql_handle的值’,’plan_name的值’,attribute_name=>’FIXED’,attribute_value=>’YES’);
dbms_output.putline(i);
end;
/

select * from dba_sql_plan_baseline where origin=’MANUAL-LOAD’ order by created desc;--看fixed、accepted
select * from table(dbms_xplan.displany_sql_plan_baseline(‘sql_handle的值’,plan_name=>’plan_name的值’,format=>’ADVANCED’))

--删除方法
declare
v_text pls_integer;
begin
v_text:=dbms_spm.drop_sql_plan_baseline(sql_id=>'26kqp5puukbh8' ,plan_hase_value=>null);
dbms_output.putline(v_text);
end;
/

--②从awr载入
select * from dba_hist_sql_plan a where sql_id='26kqp5puukbh8' and plan_hash_value='3059001790' order by timestamp desc;--看期望的执行计划在什么时间
select * from dba_hist_ash_snapshot where begin_interval_time='时间值' order by begin_interval_time desc;--依据时间找快照id
select * owner,name,id,created,statement_count from dba_sqlnet order by created;--看已有的dba_sqlset，没有时创建
begin
dbms_sqltune.create_sqlset(‘sqlset名’,’SQL Tuning Set for loading plan into SQL Plan Baseline’);
end;/--创建sqlset

--指定快照号从awr中将执行计划load到sqlset，再select看dba_sqlset
declare
cur sys_refcursor;
begin
open cur for
select vaue(P) from table(dbms_sqltune.select_workload_repository(begin_snap=>起始snap_id, end_snap=>结束snap_id, base_filter=>’sql_id=''老执行计划的起始sql_id’’’,attribute_list=>’ALL’)) P;
dbms_sqltune.load_sqlset(sqlset_name=>’sqlset名’,populate_cursor=>cur);
close cur;
end;/

select * from table(dbms_sqltune.select_sqlset(sqlset_name=>’sqlset名’));--load后看sqlset值

--从sqlset中载入基线
declare
i pls_integer;
begin
i:=dbms_spm.load_plans_from_sqlset(
sqlset_name=>‘sqlset_name的值’,basic_filter=>’plan_hash_vale=’’plan_hash_value的值’’’);
end;
/

select * from dba_sql_plan_baseline where origin=’MANUAL-LOAD’ order by created desc;--创建后的信息

--固定执行计划，将该基线转为fixed
declare
i natural;
begin
i:=dbms_spm.alter_sql_plan_baseline(
‘sql_handle的值’,’plan_name的值’,attribute_name=>’FIXED’,attribute_value=>’YES’);
dbms_output.putline(i);
end;
/

select * from dba_sql_plan_baseline where origin=’MANUAL-LOAD’ order by created desc;--看fixed、accepted
select * from table(dbms_xplan.displany_sql_plan_baseline(‘sql_handle的值’,plan_name=>’plan_name的值’,format=>’ADVANCED’))

--清除缓存中的执行计划，让db重新解析生成
select sql_text,address,hash_value from v$sqlarea where sql_text like ‘sql语句’;
exec sys.dbms_shared_pool.purge(‘address的值,hash_value的值’,’c’); --清除缓存中sql执行计划