查看oracle歷史執(zhí)行計(jì)劃:
set pagesize 500
col plan_hash_value format 9999999999
col id format 999999
col operation format a30
col options format a15
col object_owner format a15
col object_name format a20
col optimizer format a15
col cost format 9999999999
col access_predicates format a15
col filter_predicates format a15
SELECT plan_hash_value,id,
LPAD(' ', DEPTH) || operation operation,
options,
object_owner,
object_name,
optimizer,
cost,
access_predicates,
filter_predicates
FROM dba_hist_sql_plan
WHERE sql_id = '23g91gw32rz3s'
ORDER BY plan_hash_value,id;
>=11g之后，大部分使用spm
SPM綁定：
--好的執(zhí)行計(jì)劃，查詢SQL ID
SQL> select sql_text ,sql_id from v$sql where sql_text like '%2021_10_19_test_sql_text%';
5yv7w368z62bz
--查詢好的執(zhí)行計(jì)劃SQL 對(duì)應(yīng)的hash value
select * from table(dbms_xplan.display_awr('&sql',format=>'PEEKED_BINDS'));
select * from table(dbms_xplan.display_cursor('&sql'));
select * from table(dbms_xplan.display_awr('c7nnn789abukk',format=>'PEEKED_BINDS'));
select * from table(dbms_xplan.display_cursor('c7nnn789abukk'));
--綁定執(zhí)行計(jì)劃
declare
m_clob clob;
begin
select sql_fullteXt
into m_clob
from v$sql
where sql_id = 'bcq5f5sd2k5wu' --需要綁定的SQL ID
and child_number = 0; --需要綁定的SQL ID對(duì)應(yīng)的子游標(biāo)編號(hào)
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '5yv7w368z62bz', --參考需要的執(zhí)行計(jì)劃SQL ID及好的執(zhí)行計(jì)劃對(duì)應(yīng)的SQL ID
plan_hash_value => 3270942279, --參考需要的執(zhí)行計(jì)劃 Hash value
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));
end;
/
--如下實(shí)際執(zhí)行
declare
m_clob clob;
begin
select sql_fullteXt
into m_clob
from v$sql
where sql_id = 'bcq5f5sd2k5wu'
and child_number = 0;
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '5yv7w368z62bz',
plan_hash_value => 3270942279,
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));
end;
/
查詢是否綁定:
select sql_handle, plan_name, accepted, fixed,optimizer_cost from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ACC FIX OPTIMIZER_COST
------------------------------ ------------------------------ --- --- --------------
SQL_916244ba197a1647 SQL_PLAN_92sk4r8crn5k7f0218608 YES YES 1
刪除SPM綁定的執(zhí)行計(jì)劃:
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_916244ba197a1647',plan_name=>null);
END;
/
