使用SQL PROFILE+IGNORE_OPTIM_EMBEDDED_HINTS改变带HINTS的SQL执行计划

  很多时候，开发喜欢直接在SQL里使用HINTS，这样限制了SQL可能走更好的执行计划，修改起来也麻烦，还需要改SQL，那么有没有什么方法能够在不改SQL语句的情况下，改变带HINTS的SQL执行计划呢，答案是有的。可以使用IGNORE_OPTIM_EMBEDDED_HINTS配合SQL PROFILE来实现。如下例所示：

  模拟需要SQL PROFILE绑定的情形，表a的object_id有索引，但是语句使用了full hints导致走不了索引，目前又不能改SQL，需要快速优化，让其走正确执行计划：

select/*+full(a)*/ * from a where object_id <100;
执行计划是全表扫描：

 

  不改SQL，且SQL可以走更好的执行计划，可以使用SQL PROFILE快速绑定，经常用于执行计划不稳定的情形，也可以使用coe_xfr_sql_profile脚本绑定。

declare

v_hints sys.sqlprof_attr;

v_sql_id clob;

begin

v_hints := sys.sqlprof_attr

(

      q'[BEGIN_OUTLINE_DATA                                      ]',

      q'[INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID"))    ]',

      q'[OUTLINE_LEAF(@"SEL$1")                                  ]',

      q'[ALL_ROWS                                                ]',

      q'[IGNORE_OPTIM_EMBEDDED_HINTS                             ]',

      q'[END_OUTLINE_DATA                                        ]'

);

select sql_text into v_sql_id from v$sql where sql_id='8c672gv64qvg3';

dbms_sqltune.import_sql_profile(v_sql_id,v_hints,'profile_8c672gv64qvg3',force_match => TRUE);

end;

/

 

  手写脚本来绑定，可以造个正确的执行计划，然后获取OUTLINE信息，这里主要是IGNORE_OPTIM_EMBEDDED_HINTS 忽略原有SQL中的HINTS，使用这个脚本里的HINTS，force_match => TRUE针对字面量SQL，使用不同值也有效。



  再看执行计划，虽然使用full hints,但是因为用上了SQL PROFILE走索引。ORACLE针对不改SQL来修改执行计划有很多手段，常用的有SQL PROFILE,SQL PLAN BASELINE、SPM、SPD等，所以一般不要在SQL里使用HINTS，以免影响生成正确的执行计划。