固定（稳定）执行计划
你的应用的功能时快时慢，变化比较大，功能的性能能够保持一种稳定的状态，ORACLE 固定执行计划，采用以下这几种方式
- oracle 9i使用 Outline
- oracle 10g采用 sql profile
- oracle 11g增加了sql plan manage
oracle 10g采用 sql profile ：两种模式
- 从SQL语句历史的执行计划，找到一个合理的，进行绑定
- 还有一种无法从历史的执行计划找到合理的，只能手工构造进行绑定
提供脚本
create_sql_profile
提供绑定shared pool中已有的执行计划中，找一个绑定或自己构造一个绑定
----------------------------------------------------------------------------------------create_sql_profile
--
-- File name: create_sql_profile.sql
--
-- Purpose: Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for four values.
--
-- sql_id: the sql_id of the statement to attach the profile to (must be in the shared pool),if sql_id is not shared pool,must be bulid sql plan
--
-- child_no: the child_no of the statement from v$sql
--
-- new_sql_id:需要绑定的SQL语句
--
-- profile_name: the name of the profile to be generated
--
-- category: the name of the category for the profile
--
-- force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
-- Based on a script by Randolf Giest.
--
-- Mods: This is the 2nd version of this script which removes dependency on rg_sqlprof1.sql.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
-- @rg_sqlprof1 '&&sql_id' &&child_no '&&new_sql_id' '&&category' '&force_matching'
set feedback off
set sqlblanklines on
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
accept child_no -
prompt 'Enter value for child_no (0): ' -
default '0'
accept new_sql_id -
prompt 'Enter value for new_sql_id: ' -
default '0'
accept profile_name -
prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
default 'X0X0X0X0'
accept category -
prompt 'Enter value for category (DEFAULT): ' -
default 'DEFAULT'
accept force_matching -
prompt 'Enter value for force_matching (TRUE): ' -
default 'TRUE'
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
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 = '&&sql_id'
and child_number = &&child_no
and other_xml is not null
)
) d;
select
sql_fulltext,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
into
cl_sql_text, l_profile_name
from
v$sqlarea
where
sql_id = '&&new_sql_id';
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);
dbms_output.put_line(' ');
dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
dbms_output.put_line(' ');
exception
when NO_DATA_FOUND then
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
dbms_output.put_line(' ');
end;
/
undef sql_id
undef new_sql_id
undef child_no
undef profile_name
undef category
undef force_matching
set sqlblanklines off
set feedback on
CREATE_SQL_PROFILE_AWR
绑定AWR中历史的计划中其他一个
----------------------------------------------------------------------------------------create_sql_profile_awr
--
-- File name: create_sql_profile_awr.sql
--
-- Purpose: Create SQL Profile based on Outline hints in V$SQL.OTHER_XML.
--
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for five values.
--
-- sql_id: the sql_id of the statement to attach the profile to
-- (must be in the shared pool and in AWR history)
--
-- plan_hash_value: the plan_hash_value of the statement in AWR history
--
-- profile_name: the name of the profile to be generated
--
-- category: the name of the category for the profile
--
-- force_macthing: a toggle to turn on or off the force_matching feature
--
-- Description:
--
-- Based on a script by Randolf Giest.
--
-- Mods: This is the 2nd version of this script which removes dependency on rg_sqlprof2.sql.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
-- @rg_sqlprof1 '&&sql_id' &&child_no '&&category' '&force_matching'
set feedback off
set sqlblanklines on
accept sql_id -
prompt 'Enter value for sql_id: ' -
default 'X0X0X0X0'
accept plan_hash_value -
prompt 'Enter value for plan_hash_value: '
accept profile_name -
prompt 'Enter value for profile_name (PROF_sqlid_planhash): ' -
default 'X0X0X0X0'
accept category -
prompt 'Enter value for category (DEFAULT): ' -
default 'DEFAULT'
accept force_matching -
prompt 'Enter value for force_matching (FALSE): ' -
default 'false'
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
l_profile_name varchar2(30);
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 = '&&sql_id'
and plan_hash_value = &&plan_hash_value
and other_xml is not null
)
) d;
select
sql_text,
decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
into
cl_sql_text, l_profile_name
from
dba_hist_sqltext
where
sql_id = '&&sql_id';
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text,
profile => ar_profile_hints,
category => '&&category',
name => l_profile_name,
force_match => &&force_matching
-- replace => true
);
dbms_output.put_line(' ');
dbms_output.put_line('SQL Profile '||l_profile_name||' created.');
dbms_output.put_line(' ');
exception
when NO_DATA_FOUND then
dbms_output.put_line(' ');
dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
dbms_output.put_line(' ');
end;
/
undef sql_id
undef plan_hash_value
undef profile_name
undef category
undef force_matching
set sqlblanklines off
set feedback on
sql_profile_hints
显示sql profile中的HINT信息
----------------------------------------------------------------------------------------sql_profile_hints
--
-- File name: profile_hints.sql
--
-- Purpose: Show hints associated with a SQL Profile.
-
-- Author: Kerry Osborne
--
-- Usage: This scripts prompts for one value.
--
-- profile_name: the name of the profile to be modified
--
-- Description: This script pulls the hints associated with a SQL Profile.
--
-- Mods: Modified to check for 10g or 11g as the hint structure changed.
-- Modified to join on category as well as signature.
--
-- See kerryosborne.oracle-guy.com for additional information.
---------------------------------------------------------------------------------------
--
set sqlblanklines on
set feedback off
accept profile_name -
prompt 'Enter value for profile_name: ' -
default 'X0X0X0X0'
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
version varchar2(3);
l_category varchar2(30);
l_force_matching varchar2(3);
b_force_matching boolean;
begin
select regexp_replace(version,'\..*') into version from v$instance;
if version = '10' then
-- dbms_output.put_line('version: '||version);
execute immediate -- to avoid 942 error
'select attr_val as outline_hints '||
'from dba_sql_profiles p, sqlprof$attr h '||
'where p.signature = h.signature '||
'and p.category = h.category '||
'and name like (''&&profile_name'') '||
'order by attr#'
bulk collect
into ar_profile_hints;
elsif version = '11' then
-- dbms_output.put_line('version: '||version);
execute immediate -- to avoid 942 error
'select hint as outline_hints '||
'from (select p.name, p.signature, p.category, row_number() '||
' over (partition by sd.signature, sd.category order by sd.signature) row_num, '||
' extractValue(value(t), ''/hint'') hint '||
'from sqlobj$data sd, dba_sql_profiles p, '||
' table(xmlsequence(extract(xmltype(sd.comp_data), '||
' ''/outline_data/hint''))) t '||
'where sd.obj_type = 1 '||
'and p.signature = sd.signature '||
'and p.category = sd.category '||
'and p.name like (''&&profile_name'')) '||
'order by row_num'
bulk collect
into ar_profile_hints;
end if;
dbms_output.put_line(' ');
dbms_output.put_line('HINT');
dbms_output.put_line('------------------------------------------------------------------------------------------------------------------------------------------------------');
for i in 1..ar_profile_hints.count loop
dbms_output.put_line(ar_profile_hints(i));
end loop;
dbms_output.put_line(' ');
dbms_output.put_line(ar_profile_hints.count||' rows selected.');
dbms_output.put_line(' ');
end;
/
undef profile_name
set feedback on
一、SQL 绑定现有执行计划
一个SQL存在多个执行计划，选择其中一个固定
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=30)
SQL_ID 4hpk08j31nm7y, child number 2
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=30)
语句绑定第一个子游标为固定的执行计划：全表扫描
sys@GULL> @create_sql_profile
Enter value for sql_id: 4hpk08j31nm7y
Enter value for child_no (0): 0
Enter value for new_sql_id: 4hpk08j31nm7y
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (TRUE):
原值 19: sql_id = '&&sql_id'
新值 19: sql_id = '4hpk08j31nm7y'
原值 20: and child_number = &&child_no
新值 20: and child_number = 0
原值 27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
新值 27: decode('X0X0X0X0','X0X0X0X0','PROF_4hpk08j31nm7y'||'_'||plan_hash_value,'X0X0X0X0')
原值 33: sql_id = '&&new_sql_id';
新值 33: sql_id = '4hpk08j31nm7y';
原值 38: category => '&&category',
新值 38: category => 'DEFAULT',
原值 40: force_match => &&force_matching
新值 40: force_match => TRUE
原值 51: dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
新值 51: dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' Child: '||'0'||' not found in v$sql.');
原值 52: dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
新值 52: dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' not found in v$sqlarea.');
SQL Profile PROF_4hpk08j31nm7y_1404472509 created.
执行相同的SQL语句运行
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=30)
Note
-----
- SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement
在Note信息中可以看到sql profile的信息， - SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement，说明已经强制使用了手工绑定的执行计划，之后这个语句就一直采用全表扫描了，不会再走索引的访问方式
二、SQL绑定AWR中的执行计划
shared pool中之前没有合适的执行计划，你可以在awr（DBMS_XPLAN.DISPLAY_AWR）中查找历史的执行计划，查询到了，采用create_sql_profile_awr这个过程来绑定
构造一个SQL语句两个执行计划，保存到AWR中
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
alter session set optimizer_index_cost_adj=500
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
execute DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
查看AWR中的执行计划
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
SQL_ID 4hpk08j31nm7y
--------------------
select * from scott.emp where deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 522 | 2 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
SQL_ID 4hpk08j31nm7y
--------------------
select * from scott.emp where deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 6 | 522 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
使SQL语句固定走索引的处理模式
SQL> set serveroutput on
SQL> @create_sql_profile_awr.sql
Enter value for sql_id: 4hpk08j31nm7y
Enter value for plan_hash_value: 1404472509
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE): TRUE
原值 19: sql_id = '&&sql_id'
新值 19: sql_id = '4hpk08j31nm7y'
原值 20: and plan_hash_value = &&plan_hash_value
新值 20: and plan_hash_value = 1404472509
原值 27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')
新值 27: decode('X0X0X0X0','X0X0X0X0','PROF_4hpk08j31nm7y'||'_'||'1404472509','X0X0X0X0')
原值 33: sql_id = '&&sql_id';
新值 33: sql_id = '4hpk08j31nm7y';
原值 38: category => '&&category',
新值 38: category => 'DEFAULT',
原值 40: force_match => &&force_matching
新值 40: force_match => TRUE
原值 51: dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Plan: '||'&&plan_hash_value'||' not found in AWR.');
新值 51: dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' Plan: '||'1404472509'||' not found in AWR.');
SQL Profile PROF_4hpk08j31nm7y_1404472509 create
重新执行SQL语句并查看执行计划
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor(null,null))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 4 | 348 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 2 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=30)
Note
-----
- SQL profile PROF_4hpk08j31nm7y_1404472509 used for this statement
note 信息中已经使用了sql profile，而且语句也是走索引
三、SQL绑定构造的执行计划
shared pool和awr中没有一个合适的，需要自己构造这个sql语句的执行计划，进行偷梁换柱
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
|* 1 | TABLE ACCESS FULL| EMP | 6 | 228 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DEPTNO"=30)
可以构造一个走deptno索引的，在走索引的执行计划去替换全表
select /*+index(emp index_emp_deptno)*/ * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor(null,null))
SQL_ID 2hdyvqk9b09va, child number 0
-------------------------------------
select /*+index(emp index_emp_deptno)*/ * from scott.emp where
deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=30)
可以使用SQL_ID 2hdyvqk9b09va, child number 0，来替换之前SQL_ID 4hpk08j31nm7y, child number 0的执行计划
sys@GULL> @create_sql_profile
Enter value for sql_id: 2hdyvqk9b09va
Enter value for child_no (0): 0
Enter value for new_sql_id: 4hpk08j31nm7y
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (TRUE):
原值 19: sql_id = '&&sql_id'
新值 19: sql_id = '2hdyvqk9b09va'
原值 20: and child_number = &&child_no
新值 20: and child_number = 0
原值 27: decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')
新值 27: decode('X0X0X0X0','X0X0X0X0','PROF_2hdyvqk9b09va'||'_'||plan_hash_value,'X0X0X0X0')
原值 33: sql_id = '&&new_sql_id';
新值 33: sql_id = '4hpk08j31nm7y';
原值 38: category => '&&category',
新值 38: category => 'DEFAULT',
原值 40: force_match => &&force_matching
新值 40: force_match => TRUE
原值 51: dbms_output.put_line('ERROR: sql_id: '||'&&sql_id'||' Child: '||'&&child_no'||' not found in v$sql.');
新值 51: dbms_output.put_line('ERROR: sql_id: '||'2hdyvqk9b09va'||' Child: '||'0'||' not found in v$sql.');
原值 52: dbms_output.put_line('ERROR: sql_id: '||'&&new_sql_id'||' not found in v$sqlarea.');
新值 52: dbms_output.put_line('ERROR: sql_id: '||'4hpk08j31nm7y'||' not found in v$sqlarea.');
SQL Profile PROF_2hdyvqk9b09va_3956160932 created.
再次查看原始语句的执行计划
select * from scott.emp where deptno=30
select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))
SQL_ID 4hpk08j31nm7y, child number 0
-------------------------------------
select * from scott.emp where deptno=30
Plan hash value: 1404472509
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 6 | 228 | 10 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_EMP_DEPTNO | 6 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"=30)
Note
-----
- SQL profile PROF_2hdyvqk9b09va_3956160932 used for this statement
偷梁换柱完成，操作起来也是很方便。
四、查看sql profile hint信息
SQL> @sql_profile_hints.sql
Enter value for profile_name: PROF_4hpk08j31nm7y_1404472509
原值 19: 'and name like (''&&profile_name'') '||
新值 19: 'and name like (''PROF_4hpk08j31nm7y_1404472509'') '||
原值 38: 'and p.name like (''&&profile_name'')) '||
新值 38: 'and p.name like (''PROF_4hpk08j31nm7y_1404472509'')) '||
HINT
--------------------------------------------------------------------------------
----------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))
6 rows selected.
下一篇讲解一些用coe_xfr_sql_profile脚本去绑定执行计划