使错误的执行计划失效：
调用PURGE函数影响最小的方式：
select s.sql_text,s.address,s.hash_value from v$sqlarea s where s.sql_id=''; ----找到地址，HASH_VALUE
exec sys.dbms_shared_pool.purge('','','c');
绑定正确的执行计划：
DECLARE
ar_profile_hints sys.sqlprof_attr;
clsql_text clob;
BEGIN
select EXTRACTVALUE(value(d),'/hint') as outline_hints
bulk COLLECT
into ar_profile_hints
from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval from dba_hist_sql_plan
where sql_id=''
and plan_hash_value=
and other_xml is not null)) d;
select sql_text into clsql_text from dba_hist_sqltext where sql_id='';
dbms_sqltune.import_sql_profile
(
sql_text =>clsql_text,
profile =>ar_profile_hints,
name => 'PROFILE_SQLID值',
force_match =>TURE,
REPLACE =>TRUE);
end;
/
select * from dba_sql_profiles; ----查询绑定的情况
SPM:
SMB:SQL管理基线，SQL计划历史和SQL计划基线都存储在SMB中。
select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
SPM绑定案例：
加载差的执行计划到SPB:
DECLARE
cnt number;
begin
cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID =>'',PLAN_HASH_VALUE => );
end;
/
select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines order by last_modified;
用HIT构建一个好的执行计划
将好的执行计划加载到SPB中：
DECLARE
cnt number;
begin
cnt :=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(SQL_ID =>'',PLAN_HASH_VALUE => sql_handle =>'' );
end;
/
select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines order by last_modified;
绑定想使用的PLAN_NAME:
DECLARE
x number;
BEGIN
x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE(
sql_handle =>'',
plan_name =>'',
attribute_name =>'FIXED',
attribute_value =>'YES');
end;
/
借助自动优化任务来绑定：
var tuning_task varchar2(100);
DECLARE
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task VARCHAR2(30);
BEGIN
l_sql_id :='';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id =>l_sql_id);
:tuning_task :=l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end;
/
print tuning_task;
select dbms_sqltune.report_tuning_task(:tuning_task) from dual;
EXECUTE dbms_sqltune.accept_sql_profile(task_name => :tuning_task,REPLACE =>true,force_match =>true);
