执行计划绑定方法

执行计划突变属于每个数据库系统不可避免页难以预防的难题，处理执行计划突变带来的性能问题也是每个DBA应该具备的技能。除了开发时针对不同范围的结果集使用不同的sql，DBA可以通过查看索引状态、消除索引碎片、表重新分析，还可以使用执行计划固化来强制sql走正确的执行计划。

固化执行计划的方法包括profile、baseline、ouline的方式，常用的方式baseline、outline，可以使用sqlt工具中包含的sql脚本来绑定（coe_load_sql_profile.sql、coe_xfr_sql_profile.sql、coe_load_sql_baseline.sql）

1、情景1--不跨库绑定profile

同实例里不同/相同的sql_id绑定同一个执行计划（plan_hash_value），使用coe_load_sql_profile.sql脚本实现

（1）创建用户、构造表、创建索引、收集统计信息

create user test identified by test; grant dba to test; conn test/test create table test_plan as select * from dba_objects; create index idx_test_plan on test_plan(object_id); SQL> select count(*) from test_plan; COUNT(*) ---------- 211843 SQL> update test_plan set object_id=200 where object_id<200000; 210550 rows updated. SQL> commit; exec dbms_stats.gather_table_stats('TEST','TEST_PLAN',NO_INVALIDATE=>FALSE);

（2）构造测试sql

sqlplus test/test set autot traceo select object_name from test_plan where object_id=200; 执行计划全表扫描---由于object_id=200的记录数210550，总记录数 211843，结果集占据了全表的大多数，优化器评估走全表cost更小

（3）构造另一个sql

sqlplus test/test select /*+index(test_plan idx_test_plan)*/object_name from test_plan where object_id=200; 注意：构造加hint的sql时，注意不能给表加别名，否则绑定profile的时候会报hint unresolved，导致绑定不起效果，如： select /*+index(t idx_test_plan)*/object_name from test_plan t where object_id=200; 这种sql针对 select object_name from test_plan where object_id=200;无法绑定

（4）查看sql对应的sql_id和plan_hash_value

a、查看sql_id select sql_text,sql_id from v$sql where sql_text like '%object_name%object_id=200%'; select object_name from test_plan where object_id=200 1g9qpbgxysw2n select /*+index(test_plan idx_test_plan)*/object_name from test_plan where object_id=200 1dm5zr08f8q42 b、查看plan_hash_value SELECT PLAN_HASH_VALUE, version_count , substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE SQL_ID='1g9qpbgxysw2n'; 289916773 1 select object_name from test_plan where SELECT PLAN_HASH_VALUE, version_count , substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE SQL_ID='1dm5zr08f8q42'; 568013966 1 select /*+index(test_plan idx_test_plan) c、查看sql_id的执行计划： set lines 400 pages 9999 long 9999 select plan_table_output from table(dbms_xplan.display_cursor('1g9qpbgxysw2n',null,'ADVANCED')); select plan_table_output from table(dbms_xplan.display_cursor('1dm5zr08f8q42',null,'ADVANCED')); d、通过plan_hash_value查看v$sql_plan中对应的执行计划： insert into plan_table(statement_id,PLAN_ID,timestamp ,REMARKS ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,OBJECT_INSTANCE ,OBJECT_TYPE ,OPTIMIZER ,SEARCH_COLUMNS ,ID ,PARENT_ID ,DEPTH ,POSITION ,COST ,CARDINALITY ,BYTES ,OTHER_TAG ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,OTHER ,OTHER_XML ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME ) SELECT 'PLAN_'||PLAN_HASH_VALUE AS statement_id, 0 PLAN_ID,SYSDATE timestamp ,REMARKS ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,0 OBJECT_INSTANCE ,OBJECT_TYPE ,OPTIMIZER ,SEARCH_COLUMNS ,ID ,PARENT_ID ,DEPTH ,POSITION ,COST ,CARDINALITY ,BYTES ,OTHER_TAG ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,OTHER ,OTHER_XML ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME FROM v$sql_plan WHERE PLAN_HASH_VALUE='&plan_hash_value' AND ADDRESS=(SELECT MAX(ADDRESS) FROM V$SQL_PLAN WHERE PLAN_HASH_VALUE='&plan_hash_value'); SELECT * FROM TABLE(dbms_xplan.display('plan_table', 'PLAN_'||'&plan_hash_value'));

（5）使用profile进行执行计划绑定

@coe_load_sql_profile.sql 待优化的sql_id 正确执行计划sql_id 正确执行计划的plan_hash_value sqlplus test/test @coe_load_sql_profile.sql 1g9qpbgxysw2n 1dm5zr08f8q42 568013966 注意： 此脚本coe_load_sql_profile.sql只能使用普通用户执行，sys用户执行会报错 ORA-19381: cannot create staging table in SYS schema 如果同一个sql（即sql_id只有一个）有不同的执行计划（plan_hash_value），想对此sql绑定其中一个执行计划，也可以使用coe_xfr_sql_profile.sql 如： @coe_xfr_sql_profile.sql 待优化的sql_id 待优化的sql_id 正确执行计划的plan_hash_value sqlplus test/test @coe_load_sql_profile.sql 1g9qpbgxysw2n 1g9qpbgxysw2n 568013966

（6）查看profile绑定效果

select name,SQL_TEXT,STATUS from dba_sql_profiles; profile已创建，绿框的568013966是绑定的plan_hash_value，查看远sql的执行计划，发现profile已生效，执行计划由原来的全表走了索引

2、情景2--跨库绑定profile

如果某个库中的某sql执行计划不对，另一个类似的库的同一个sql执行计划正常，需要从正常库中导出正确的执行计划，绑定到问题库。（不同库不同用户sql完全相同，sql_id也会一样，可以使用此方法）

由于coe_load_sql_profile.sql会直接绑定，可能会对正常库有些影响，且比较麻烦，需要将dmp文件导入问题库，然后使用DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF

针对这种情况，可以使用coe_xfr_sql_profile.sql脚本，不会对正常库产生修改，只生成绑定脚本，然后在问题库执行即可（比如印尼shard1，shard2，shard3，业务都是用app_schema用户，且业务sql在3个库里一样）

（1）创建用户、构造表、创建索引、收集统计信息

create user test identified by test; grant dba to test; conn test/test create table test_plan as select * from dba_objects; create index idx_test_plan on test_plan(object_id); SQL> select count(*) from test_plan; COUNT(*) ---------- 84145 SQL> update test_plan set object_id=200 where object_id<80000; 72361 rows updated. SQL> commit; exec dbms_stats.gather_table_stats('TEST','TEST_PLAN',NO_INVALIDATE=>FALSE);

（2）构造测试sql

sqlplus test/test set autot traceo select object_name from test_plan where object_id=200; 执行计划全表扫描

（3）查看sql对应的sql_id和plan_hash_value

a、查看sql_id select sql_text,sql_id from v$sql where sql_text like '%object_name%object_id=200%'; select object_name from test_plan where object_id=200 1g9qpbgxysw2n b、查看plan_hash_value SELECT PLAN_HASH_VALUE, version_count , substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE SQL_ID='1g9qpbgxysw2n'; 289916773 1 select object_name from test_plan where c、查看sql_id的执行计划： set lines 400 pages 9999 long 9999 select plan_table_output from table(dbms_xplan.display_cursor('1g9qpbgxysw2n',null,'ADVANCED'));

（4）使用coe_xfr_sql_profile.sql生成正确的执行计划脚本

在执行计划正确的库中执行： @coe_xfr_sql_profile.sql 正确执行计划sql_id 正确执行计划的plan_hash_value sqlplus test/test @coe_xfr_sql_profile.sql 1g9qpbgxysw2n 568013966 此脚本coe_xfr_sql_profile.sql不会真正生成profile，需要手动执行生成的coe_xfr_sql_profile_1g9qpbgxysw2n_568013966.sql来生成profile绑定执行计划，将生成的脚本传到问题库并执行： sqlplus test/test @coe_xfr_sql_profile_1g9qpbgxysw2n_568013966.sql

（6）问题库查看profile绑定效果

select name,SQL_TEXT,STATUS from dba_sql_profiles; profile已创建，绿框的是绑定的sql_id和plan_hash_value，查看远sql的执行计划，发现profile已生效，执行计划由原来的全表走了索引

3、情景3--使用baseline绑定执行计划

（1）创建用户、构造表、创建索引、收集统计信息

create user test identified by test; grant dba to test; conn test/test create table test_plan as select * from dba_objects; create index idx_test_plan on test_plan(object_id); SQL> select count(*) from test_plan; COUNT(*) ---------- 211843 SQL> update test_plan set object_id=200 where object_id<200000; 210550 rows updated. SQL> commit; exec dbms_stats.gather_table_stats('TEST','TEST_PLAN',NO_INVALIDATE=>FALSE);

（2）构造测试sql

sqlplus test/test set autot traceo select object_name from test_plan where object_id=200; 执行计划全表扫描---由于object_id=200的记录数210550，总记录数 211843，结果集占据了全表的大多数，优化器评估走全表cost更小

（3）构造另一个sql

sqlplus test/test select /*+index(test_plan idx_test_plan)*/object_name from test_plan where object_id=200; 注意：构造加hint的sql时，注意不能给表加别名，否则绑定baseline的时候会报failed to use sql plan baseline for this statement，导致绑定不起效果，如： select /*+index(t idx_test_plan)*/object_name from test_plan t where object_id=200; 这种sql针对 select object_name from test_plan where object_id=200;无法绑定

（4）查看sql对应的sql_id和plan_hash_value

a、查看sql_id select sql_text,sql_id from v$sql where sql_text like '%object_name%object_id=200%'; select object_name from test_plan where object_id=200 1g9qpbgxysw2n select /*+index(test_plan idx_test_plan)*/object_name from test_plan where object_id=200 1dm5zr08f8q42 b、查看plan_hash_value SELECT PLAN_HASH_VALUE, version_count , substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE SQL_ID='1g9qpbgxysw2n'; 289916773 1 select object_name from test_plan where SELECT PLAN_HASH_VALUE, version_count , substr(sql_text,1,40) "SQL" FROM v$sqlarea WHERE SQL_ID='1dm5zr08f8q42'; 568013966 1 select /*+index(test_plan idx_test_plan) c、查看sql_id的执行计划： set lines 400 pages 9999 long 9999 select plan_table_output from table(dbms_xplan.display_cursor('1g9qpbgxysw2n',null,'ADVANCED')); select plan_table_output from table(dbms_xplan.display_cursor('1dm5zr08f8q42',null,'ADVANCED')); d、通过plan_hash_value查看v$sql_plan中对应的执行计划： insert into plan_table(statement_id,PLAN_ID,timestamp ,REMARKS ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,OBJECT_INSTANCE ,OBJECT_TYPE ,OPTIMIZER ,SEARCH_COLUMNS ,ID ,PARENT_ID ,DEPTH ,POSITION ,COST ,CARDINALITY ,BYTES ,OTHER_TAG ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,OTHER ,OTHER_XML ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME ) SELECT 'PLAN_'||PLAN_HASH_VALUE AS statement_id, 0 PLAN_ID,SYSDATE timestamp ,REMARKS ,OPERATION ,OPTIONS ,OBJECT_NODE ,OBJECT_OWNER ,OBJECT_NAME ,OBJECT_ALIAS ,0 OBJECT_INSTANCE ,OBJECT_TYPE ,OPTIMIZER ,SEARCH_COLUMNS ,ID ,PARENT_ID ,DEPTH ,POSITION ,COST ,CARDINALITY ,BYTES ,OTHER_TAG ,PARTITION_START ,PARTITION_STOP ,PARTITION_ID ,OTHER ,OTHER_XML ,DISTRIBUTION ,CPU_COST ,IO_COST ,TEMP_SPACE ,ACCESS_PREDICATES ,FILTER_PREDICATES ,PROJECTION ,TIME ,QBLOCK_NAME FROM v$sql_plan WHERE PLAN_HASH_VALUE='&plan_hash_value' AND ADDRESS=(SELECT MAX(ADDRESS) FROM V$SQL_PLAN WHERE PLAN_HASH_VALUE='&plan_hash_value'); SELECT * FROM TABLE(dbms_xplan.display('plan_table', 'PLAN_'||'&plan_hash_value'));

（5）使用baseline进行执行计划绑定

@coe_load_sql_baseline.sql 待优化的sql_id 正确执行计划sql_id 正确执行计划的plan_hash_value sqlplus test/test @coe_load_sql_baseline.sql 1g9qpbgxysw2n 1dm5zr08f8q42 568013966 注意： ORA-01403: no data found错误可以忽略 如果同一个sql（即sql_id只有一个）有不同的执行计划（plan_hash_value），想对此sql绑定其中一个执行计划，也可以使用coe_xfr_sql_profile.sql 如： @coe_load_sql_baseline.sql 待优化的sql_id 待优化的sql_id 正确执行计划的plan_hash_value sqlplus test/test @coe_load_sql_baseline.sql 1g9qpbgxysw2n 1g9qpbgxysw2n 568013966

（6）查看baseline绑定效果

set lines 400 pages 9999 long 999 col sql_handle for a30 col plan_name for a50 select sql_handle,plan_name, ENABLED, ACCEPTED, FIXED, EXECUTIONS,sql_text from dba_sql_plan_baselines; 查看原sql的执行计划，发现baseline已生效，执行计划由原来的全表走了索引 执行完此sql会生成一个accepted为no（不会被使用）的plan，如下： 可以使用dbms_xplan.display_sql_plan_baseline查看已有的baseline的执行计划： select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_a2fce0f552889dc6',plan_name=>'SQL_PLAN_a5z70yp98j7f623afb57b')); select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_a2fce0f552889dc6',plan_name=>'SQL_PLAN_a5z70yp98j7f6cfa72a87')); 一个SQL计划必须同时ENABLED和ACCEPTED为YES，否则CBO将忽略它

（7）观察baseline与profile的异同

使用以下存储过程，可以对同一个sql的多个plan进行比较，比如此场景中，比较SQL_PLAN_a5z70yp98j7f6cfa72a8和已经enable accepted的SQL_PLAN_a5z70yp98j7f623afb57b进行比较

set serveroutput on set long 10000 declare result_clob clob; begin result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_a2fce0f552889dc6',plan_name=>'SQL_PLAN_a5z70yp98j7f6cfa72a87',verify=>'YES',commit=>'YES'); dbms_output.put_line(result_clob); end; / 比较的结果是此plan并不比baseline绑定的plan好多少，没必要将其accepted改为yes，sql还是使用之前的绑定

总结：

• 在非跨库的执行计划绑定的情况下，统一使用coe_load_sql_profile.sql或者coe_load_sql_baseline.sql来绑定profile或baseline；
• 在跨库的执行计划绑定时，只能使用coe_xfr_sql_profile.sql绑定profile

baseline和profile的不同点：

1、profile是10g开始提供的功能，baseline是11g开始提供的功能

2、profile只能固定一个执行计划；baseline可以固定多个执行计划，如某sql有10个执行计划，可以使用baseline固定使用其中的2个，根据11g adaptive plan的特性，让优化器从这2个计划中自动选择，fixed属性为yes的执行计划优先被选择，可以设置多个计划的fixed=yes

3、profile的执行计划可以直接从历史数据（AWR）里面load，baseline需要先把awr的数据load到STS（SQL Tuning Set），才能再load到baseline。所以一般生产系统执行计划突然变差的应急处理，多使用profile。

4、profile可以对未使用绑定变量的SQL进行执行计划绑定，如:

select xx from t1 whereid=1;

select xx from t1 whereid=2等，

这样的sql一般只能使用profile绑定，且要使用coe_xfr_sql_profile.sql的方式，选择其中任意一个sql_id,将生成的sql脚本中force_match参数设置为true（默认是false），；

如上面生成的coe_xfr_sql_profile_1g9qpbgxysw2n_568013966.sql，将force_match => TRUE，则其他类似的sql如 select object_name from test_plan where object_id=100;也会走绑定的执行计划

而baseline不可以，如果要绑定，需要逐条sql_id进行绑定；如果将系统参数cursor_sharing改成FORCE，这时可以baseline绑定。

5、profile和baseline之间不是简单的优先级的问题，如果二者不冲突，则是合并使用；如果冲突，而且baseline可以reproduce（10053显示）时，使用baseline，否则使用profile。

6、sql_id不一样会不会有影响？

profile识别的不是sql_id，而是signature，如果几个sql 只是在大小写、空格数、回车数或tab键有区别，那么这些SQL在系统中的signature就是一样的，可以使用同一个profile。

📎coe_load_sql_profile.sql

📎coe_load_sql_baseline.sql

📎coe_xfr_sql_profile.sql