查看oracle历史执行计划:
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绑定：
----执行计划，查询SQLID
select sql_text,sql_id from v$sql where sql_text like '%2021_10_19_test_sql_text%';
5yv7w368z62bz
----查询好的执行计划SQL对应的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'));
--绑定执行计划
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对应的子游标编号
dbms_output.put_line(m_clob);
dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache(sql_id => '5yv7w368z62bz', --参考需要的执行计划SQLID及好的执行计划对应的SQLID
plan_hash_value => 3270942279, --参考需要的执行计划 Hash value
sql_text => m_clob,
fixed => 'YES',
enabled => 'YES'));
end;
/
--如下实际执行
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绑定的执行计划:
declare
xx PLS_INTEGER;
BEGIN
xx :=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_916244ba197a1647',plan_name=>null);
END;
/
