使錯(cuò)誤的執(zhí)行計(jì)劃失效：
調(diào)用PURGE函數(shù)影響最小的方式：
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');
綁定正確的執(zhí)行計(jì)劃：
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計(jì)劃歷史和SQL計(jì)劃基線都存儲(chǔ)在SMB中驯鳖。
select plan_name,sql_handle,enabled,accepted,fixed,module,sql_text from dba_sql_plan_baselines;
SPM綁定案例：
加載差的執(zhí)行計(jì)劃到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構(gòu)建一個(gè)好的執(zhí)行計(jì)劃
將好的執(zhí)行計(jì)劃加載到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;
/
借助自動(dòng)優(yōu)化任務(wù)來綁定：
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);
