SQL> conn /as sysdba Connected. SQL> set serveroutput on size 99999 SQL> @sql_profile_hints.sql Enter value for profile_name: PROF_0xy0uj562r893_1575588977 old 19: 'and name like (''&&profile_name'') '|| new 19: 'and name like (''PROF_0xy0uj562r893_1575588977'') '|| old 38: 'and p.name like (''&&profile_name'')) '|| new 38: 'and p.name like (''PROF_0xy0uj562r893_1575588977'')) '|| HINT ----------------------------------------------------------------------------------------------------------------------------- IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.1') DB_VERSION('11.2.0.1') OPT_PARAM('_optim_peek_user_binds' 'false') OPT_PARAM('_optimizer_null_aware_antijoin' 'false') OPT_PARAM('_bloom_filter_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing' 'none') OPT_PARAM('_gby_hash_aggregation_enabled' 'false') OPT_PARAM('_bloom_pruning_enabled' 'false') OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none') OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false') OPT_PARAM('_optimizer_use_feedback' 'false') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "DH_SQL"@"SEL$1") 15 rows selected.
三、使用create_sql_profile_awr脚本来还原AWR里面保存的SQL语句的执行计划（暂时没环境测试）...
四、将提示集手工移入到另外一条SQL语句的sql profile中（通过move_sql_profile.sql脚本实现）；方法如下： 1、执行一条与需要固定执行计划的SQL语句结构一致的语句 2、通过各种方法来实现将第一步运行的SQL语句，得到自己预期的执行计划（添加提示，修改参数等等） 3、通过这个语句运行产生的提示集合（v$sql_plan.other_xml列），来为需要固定的SQL语句创建sql profile从而固定执行计划 SQL> select * from table(dbms_xplan.display_cursor('bp7gpwq6w88nv','','')); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID bp7gpwq6w88nv, child number 0 ------------------------------------- select /* test2 */ * from dh_sql a where name=:"SYS_B_0" Plan hash value: 3828038811 ------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| DH_SQL | 2 | 76 | 4 (0)| 00:00:17 | |* 2 | INDEX RANGE SCAN | IND_DH_SQL2 | 2 | | 3 (0)| 00:00:13 | --需要固定的SQL的执行计划 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("NAME"=:SYS_B_0) 19 rows selected. SQL>
ID NAME TYPE ---------- ------------------------------ ------------------------------ 3167 DBA_TABLES VIEW 3168 DBA_TABLES SYNONYM 2 rows selected. SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%test2%' and sql_text not like '%v$sql%'; SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER ---------------------------------------------------------------------- ------------- ---------- ------------ select /* test2 */ * from dh_sql a where name=:"SYS_B_0" bp7gpwq6w88nv 2378441371 0 select /* test2 */ * from dh_sql where name=:"SYS_B_0" 0xy0uj562r893 1277927715 0 select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" 6vq4tjw38m8hk 109683218 0 3 rows