Oracle-绑定执行计划
一、绑定执行计划
Oracle存在某个SQL多个执行计划的情况,那么如何快速将Oracle 好的执行计划,绑定到不好的SQL上呢?
由于版本的演进,绑定执行计划在10g 一般使用sql profile; >=11g之后,大部分使用spm就可以了。
本篇文档针对这两种绑定执行计划的方式进行了梳理。 有需要的小伙伴们可以直接使用。
二、实验测试
2.1 SPM绑定
--好的执行计划,查询SQL ID
SQL> 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'));
--绑定执行计划
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', --参考需要的执行计划SQL ID 及好的执行计划对应的SQL ID
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;
/
检查执行计划是否生效,手工可以执行的情况可以看到如下!
exec sql
select * from table(dbms_xplan.display_cursor(null,null,'advanced -PROJECTION -bytes iostats,last'));
- SQL plan baseline SQL_PLAN_92sk4r8crn5k7f0218608 used for this statement
2.2 SQL Profile绑定
使用sql profile绑定执行计划--参考
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 = 'SQLID 值' --好的执行计划对应的SQL ID
and plan_hash_value = value 值 --好的执行计划对应SQL ID 的hash value值
and other_xml is not null)) d;
SELECT sql_text INTO clsql_text
FROM dba_hist_sqltext
where sql_id = ' SQLID 值'; --需要绑定的SQL ID的值,通过视图查询该SQL 对应的文本
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
profile => ar_profile_hints,
name => 'PROFILE_ SQLID 值', --SQL Profile绑定的标记
force_match => TRUE,——true 表示对于谓词部分 具体值变化后的 SQL_ID 也能使用该 SQL profile
REPLACE => TRUE);
end;
/ 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 = '5yv7w368z62bz'
and plan_hash_value = '3270942279'
and other_xml is not null)) d;
SELECT sql_text INTO clsql_text
FROM dba_hist_sqltext
where sql_id = 'bcq5f5sd2k5wu';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
profile => ar_profile_hints,
name => 'PROFILE_ SQLID bcq5f5sd2k5wu',
force_match => TRUE,
REPLACE => TRUE);
end;
/
-----------------------------------------好的SQL执行计划,可能并不在历史的视图中记录,可以换成如下在memory cache的视图
select xmltype(other_xml) as xmlval
from v$sql_plan --dba_hist_sql_plan视图找不到记录!
where sql_id = '5yv7w368z62bz'
and plan_hash_value = '3270942279'
and other_xml is not null
--
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 v$sql_plan
where sql_id = '5yv7w368z62bz'
and plan_hash_value = '3270942279'
and other_xml is not null)) d;
SELECT sql_text INTO clsql_text
FROM dba_hist_sqltext
where sql_id = 'bcq5f5sd2k5wu';
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(sql_text => clsql_text,
profile => ar_profile_hints,
name => 'PROFILE_ SQLID bcq5f5sd2k5wu',
force_match => TRUE,
REPLACE => TRUE);
end;
/
--检查是否绑定成功
SQL> select * from dba_sql_profiles WHERE name ='PROFILE_ SQLID bcq5f5sd2k5wu';
--SQL 能执行的情况下
- SQL profile PROFILE_ SQLID bcq5f5sd2k5wu used for this statement
--删除SQL Profile
BEGIN
DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'PROFILE_ SQLID bcq5f5sd2k5wu');
END;
/
--验证SQL执行效率对比,可以检查SQL的执行效率
select sql_id,sql_profile,
executions,plan_hash_value,
elapsed_time / DECODE(executions, 0, 1, EXECUTIONS) / 1000 elasp_time_ms,
buffer_gets / DECODE(executions, 0, 1, EXECUTIONS),
disk_reads / DECODE(executions, 0, 1, EXECUTIONS),
cpu_time / DECODE(executions, 0, 1, EXECUTIONS)/1000 cpu_time_ms,
last_load_time,last_active_time,
sql_text,child_number
from v$sql
where SQL_ID IN ('&sql_id');
select *
from (select to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') begin_time,
a.instance_number,
module,
plan_hash_value,
EXECUTIONS_DELTA exec,
decode(EXECUTIONS_DELTA,
0,
buffer_gets_deltA,
round(BUFFER_GETS_DELTA / EXECUTIONS_DELTA)) per_get,
decode(EXECUTIONS_DELTA,
0,
ROWS_PROCESSED_DELTA,
round(ROWS_PROCESSED_DELTA / EXECUTIONS_DELTA, 3))
per_rows,
decode(EXECUTIONS_DELTA,
0,
ELAPSED_TIME_DELTA,
round(ELAPSED_TIME_DELTA / EXECUTIONS_DELTA / 1000,
2)) time_ms,
decode(EXECUTIONS_DELTA,
0,
DISK_READS_DELTA,
round(DISK_READS_DELTA / EXECUTIONS_DELTA, 2)) per_read
from dba_hist_sqlstat a, DBA_HIST_SNAPSHOT b
where a.snap_id = b.snap_id
and a.instance_number = b.instance_number
and a.sql_id = '&sql_id'
order by 1 desc)
where rownum < 100;
SPM非参考,SQL PROFILE ,SQL查询效率部分参考文档https://www.modb.pro/doc/287
最新文章
- POJ2239 Selecting Courses(二分图最大匹配)
- windows防火墙命令详解
- Javascript中的函数、this以及原型
- Android Studio 导入项目错误
- ecb, 找不到button
- sql for xml 输出结果带单引号出现转成&;apos的解决方案
- dedecms后台怎么添加发布软件?织梦后台软件内容管理
- 用Express、MySQL搭建项目(接口以及静态文件获取、文件上传等)
- webTest-----webUI自动化框架
- 多线程(三) java中线程的简单使用
- Kafka元数据缓存(metadata cache)
- 「造个轮子」——设计 HTTP 请求全局上下文
- 【 HDU 1538 】A Puzzle for Pirates (海盗博弈论)
- Linux上的文件查找工具之locate与find
- Java面试集合(三)
- iptables限制同一IP连接数,防防CC/DDOS攻击
- wvblk 把 xp、2003、win7(32位) 装入 VHD
- (转) Lua: 给 Redis 用户的入门指导
- Android UI开发第三十五篇——AppCompat实现Action Bar
- Notepad++的ftp远程编辑功能