你的位置:首页 > 数据库

[数据库]固定执行计划


 

固定(稳定)执行计划

你的应用的功能时快时慢,变化比较大,功能的性能能够保持一种稳定的状态,ORACLE 固定执行计划,采用以下这几种方式

  • oracle 9i使用 Outline
  • oracle 10g采用 sql profile
  • oracle 11g增加了sql plan manage

oracle 10g采用 sql profile :两种模式

  • 从SQL语句历史的执行计划,找到一个合理的,进行绑定
  • 还有一种无法从历史的执行计划找到合理的,只能手工构造进行绑定

提供脚本

create_sql_profile

提供绑定shared pool中已有的执行计划中,找一个绑定或自己构造一个绑定

-------------------------------------------------------------------------------------------- File name:  create_sql_profile.sql---- Purpose:   Create SQL Profile based on Outline hints in V$SQL.OTHER_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 offset sqlblanklines onaccept 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'declarear_profile_hints sys.sqlprof_attr;cl_sql_text clob;l_profile_name varchar2(30);beginselectextractvalue(value(d), '/hint') as outline_hintsbulk collectintoar_profile_hintsfrom/*/outline_data/hint'passing (selectas is not null)) d;selectsql_fulltext,decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||plan_hash_value,'&&profile_name')intocl_sql_text, l_profile_namefromv$sqlareawheresql_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(' ');exceptionwhen 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_idundef new_sql_idundef child_noundef profile_nameundef categoryundef force_matchingset sqlblanklines offset feedback on

create_sql_profile

CREATE_SQL_PROFILE_AWR

绑定AWR中历史的计划中其他一个

-------------------------------------------------------------------------------------------- File name:  create_sql_profile_awr.sql---- Purpose:   Create SQL Profile based on Outline hints in V$SQL.OTHER_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 offset sqlblanklines onaccept 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'declarear_profile_hints sys.sqlprof_attr;cl_sql_text clob;l_profile_name varchar2(30);beginselectextractvalue(value(d), '/hint') as outline_hintsbulk collectintoar_profile_hintsfrom/*/outline_data/hint'passing (selectas is not null)) d;selectsql_text,decode('&&profile_name','X0X0X0X0','PROF_&&sql_id'||'_'||'&&plan_hash_value','&&profile_name')intocl_sql_text, l_profile_namefromdba_hist_sqltextwheresql_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(' ');exceptionwhen 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_idundef plan_hash_valueundef profile_nameundef categoryundef force_matchingset sqlblanklines offset feedback on

create_sql_profile_awr

sql_profile_hints

显示sql profile中的HINT信息

-------------------------------------------------------------------------------------------- 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 onset feedback offaccept profile_name -    prompt 'Enter value for profile_name: ' -    default 'X0X0X0X0'declarear_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(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_nameset feedback on

sql_profile_hints

 

一、SQL 绑定现有执行计划

一个SQL存在多个执行计划,选择其中一个固定

select * from scott.emp where deptno=30select * 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_profileEnter value for sql_id: 4hpk08j31nm7yEnter value for child_no (0): 0Enter value for new_sql_id: 4hpk08j31nm7yEnter 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=30select * 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=30select * from table(dbms_xplan.display_cursor('4hpk08j31nm7y',null))alter session set optimizer_index_cost_adj=500select * from scott.emp where deptno=30select * 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 onSQL> @create_sql_profile_awr.sqlEnter value for sql_id: 4hpk08j31nm7yEnter value for plan_hash_value: 1404472509Enter 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=30select * 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=30select * 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=30select * 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_profileEnter value for sql_id: 2hdyvqk9b09vaEnter value for child_no (0): 0Enter value for new_sql_id: 4hpk08j31nm7yEnter 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=30select * 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.sqlEnter 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_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.3')DB_VERSION('11.2.0.3')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."DEPTNO"))6 rows selected.

下一篇讲解一些用coe_xfr_sql_profile脚本去绑定执行计划