使用sql profile固定执行计划实验
已有表TACCOINFO
create table TACCOINFO
(
vc_tradeacco VARCHAR2(17) not null
);
create unique index IACCOINFO_TRADEACCO on TACCOINFO (VC_TRADEACCO);
下面是实验过程
1. 原sql执行计划 select * from taccoinfo t where t.vc_tradeacco = '0001';会走唯一性索引扫描
2.新sql执行计划select /*+ full(t)*/* from taccoinfo t where t.vc_tradeacco = '0001'; 全表扫描
3.获取新sql的sql_id 3p621f6k5pdqt
4.获取新sql的outline
5.创建sql profile(SQLPROFILE_001)
6.查看是否使用sql profile
select * from taccoinfo t where t.vc_tradeacco = '1002';
7.删除sql proflie
8.执行计划变为唯一性索引扫描
创建sql profile
DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => 'FULL QUERY TEXT',
profile => sqlprof_attr('HINT SPECIFICATION WITH FULL OBJECT ALIASES'),
name => 'PROFILE NAME',
force_match => TRUE/FALSE,
replace=> TRUE/FALSE);
sql_text用于指定sql的全文本,可查询V$SQLAREA.SQL_FULLTEXT或DBA_HIST_SQLTEXT.SQL_TEXT获得
profile => sqlprof_attr 指定执行计划
sql profile相关视图
SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;
SELECT sql_attr.attr_val outline_hints
FROM dba_sql_profiles sql_profiles, sys.SQLPROF$ATTR sql_attr
WHERE sql_profiles.signature = sql_attr.signature
AND sql_profiles.name = 'SQLPROFILE_001'
ORDER BY sql_attr.attr# ASC;