SQL Profile 总结（四）--使用示例

前提：sql profile工具的相关视图 dba_sql_profile 10g: sys.sqlprof$attr  &  sqlprof$ 11g: sys.sqlobj$data  &  sys.sqlobj$
1、主要完成四个示例，如下

• 使用dbms_sqltune.import_sql_profile过程手工指定提示的方式，这种方式要求非常高（查询块名等），一般不会使用
• 使用create_sql_profile.sql脚本固定内存中已经有的SQL的执行计划，通过指定sql_id
• 使用create_sql_profile_awr脚本来还原AWR里面保存的SQL语句的执行计划（暂时没环境测试）...
• 将提示集手工移入到另外一条SQL语句的sql profile中（通过move_sql_profile.sql脚本实现）；

示例： 一、使用dbms_sqltune.import_sql_profile过程手工指定提示的方式，这种方式要求非常高（查询块名等），一般不会使用 [oracle@192oracle ~]$ sqlplus dbmon/dbmon_123 SQL*Plus: Release 11.2.0.1.0 Production on Wed Jun 11 16:37:06 2014 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> create table dh_sql as select rownum id,object_name name ,object_type type from dba_objects; Table created. SQL> create index ind_dh_sql on dh_sql(id); Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>‘dbmon‘,TABNAME=>‘dh_sql‘,ESTIMATE_PERCENT=>30,METHOD_OPT=>‘FOR ALL COLUMNS SIZE 1‘,NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);  PL/SQL procedure successfully completed.
SQL> set linesize 200 pagesize 9999 SQL> select /* test1 */ id,name from dh_sql where id=771;
        ID NAME ---------- --------------------------------------------------------------------------------------------------------------------------------        771 RULESET$
SQL> @sql_profiles.sql               --该脚本查看当前使用sql profile的语句 Enter value for sql_text:  old   3: where sql_text like nvl(‘&sql_text‘,‘%‘) new   3: where sql_text like nvl(‘‘,‘%‘) Enter value for name:  old   4: and name like nvl(‘&name‘,name) new   4: and name like nvl(‘‘,name) no rows selected
SQL> col name format a30  SQL> col type format a30 SQL> col sql_text format a40 SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like ‘%test1%‘ and sql_text not like ‘%v$sql%‘; SQL_TEXT                                 SQL_ID        HASH_VALUE CHILD_NUMBER ---------------------------------------- ------------- ---------- ------------ select /* test1 */ id,name from dh_sql w 90nh2m7a3gsvf 3560432494            0
SQL> select * from table(dbms_xplan.display_cursor(‘90nh2m7a3gsvf‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  90nh2m7a3gsvf, child number 0 ------------------------------------- select /* test1 */ id,name from dh_sql where id=:"SYS_B_0" Plan hash value: 1731829956 ------------------------------------------------------------------------------------------ | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT            |            |       |       |     2 (100)|          | |   1 |  TABLE ACCESS BY INDEX ROWID| DH_SQL     |     1 |    30 |     2   (0)| 00:00:09 | |*  2 |   INDEX RANGE SCAN          | IND_DH_SQL |     1 |       |     1   (0)| 00:00:05 | ------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("ID"=:SYS_B_0) 19 rows selected.
SQL> @create_1_hint_sql_profile.sql Enter value for sql_id: 90nh2m7a3gsvf Enter value for profile_name (PROFILE_sqlid_MANUAL):  Enter value for category (DEFAULT):  Enter value for force_matching (false): true old  16: sql_id = ‘&&sql_id‘; new  16: sql_id = ‘90nh2m7a3gsvf‘; old  18: select decode(‘&&profile_name‘,‘X0X0X0X0‘,‘PROFILE_‘||‘&&sql_id‘||‘_MANUAL‘,‘&&profile_name‘) new  18: select decode(‘X0X0X0X0‘,‘X0X0X0X0‘,‘PROFILE_‘||‘90nh2m7a3gsvf‘||‘_MANUAL‘,‘X0X0X0X0‘) Enter value for hint: FULL(DH_SQL@SEL$1)           --手工输入指定提示 old  24: profile => sqlprof_attr(‘&hint‘), new  24: profile => sqlprof_attr(‘FULL(DH_SQL@SEL$1)‘), old  25: category => ‘&&category‘, new  25: category => ‘DEFAULT‘, old  31: force_match => &&force_matching new  31: force_match => true PL/SQL procedure successfully completed.
SQL> select /* test1 */ id,name from dh_sql where id=771;                           ID NAME ---------- ------------------------------        771 RULESET$ SQL> select * from table(dbms_xplan.display_cursor(‘90nh2m7a3gsvf‘,‘‘,‘‘));
PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  90nh2m7a3gsvf, child number 0 ------------------------------------- select /* test1 */ id,name from dh_sql where id=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     1 |    30 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("ID"=:SYS_B_0) Note -----    - SQL profile PROFILE_90nh2m7a3gsvf_MANUAL used for this statement 22 rows selected.
SQL> @sql_profiles.sql   --可以看到确实已经使用！ Enter value for sql_text:  old   3: where sql_text like nvl(‘&sql_text‘,‘%‘) new   3: where sql_text like nvl(‘‘,‘%‘) Enter value for name:  old   4: and name like nvl(‘&name‘,name) new   4: and name like nvl(‘‘,name) NAME                           CATEGORY        STATUS   SQL_TEXT                                                               FOR ------------------------------ --------------- -------- --------------------------------------------------------------------- PROFILE_90nh2m7a3gsvf_MANUAL   DEFAULT         ENABLED  select /* test1 */ id,name from dh_sql where id=:"SYS_B_0"             YES

SQL> conn /as sysdba Connected. SQL> set serveroutput on size 9999 SQL> @profile_hint.sql Enter value for profile_name: PROFILE_90nh2m7a3gsvf_MANUAL old  19:    ‘and name like (‘‘&&profile_name‘‘) ‘|| new  19:    ‘and name like (‘‘PROFILE_90nh2m7a3gsvf_MANUAL‘‘) ‘|| old  38:    ‘and p.name like (‘‘&&profile_name‘‘)) ‘|| new  38:    ‘and p.name like (‘‘PROFILE_90nh2m7a3gsvf_MANUAL‘‘)) ‘|| HINT ----------------------------------------------------------------------------------------------------------------------------- FULL(DH_SQL@SEL$1)               --可以看到sql profile的基表里面保存了我们指定的提示




二、使用create_sql_profile.sql脚本固定内存中已经有的SQL的执行计划，通过指定sql_id SQL> conn dbmon/dbmon_123  Connected. SQL> select * from dh_sql where name=‘DBA_TABLES‘;         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select /* test2 */ * from dh_sql where name=‘DBA_TABLES‘;         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 where name=:"SYS_B_0"                 0xy0uj562r893 1277927715            0 1 row selected. SQL> select * from table(dbms_xplan.display_cursor(‘0xy0uj562r893‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) 18 rows selected.
SQL> @create_sql_profile.sql    --使用这个脚本固定内存中指定SQL_ID的执行计划 Enter value for sql_id: 0xy0uj562r893 Enter value for child_no (0):  Enter value for profile_name (PROF_sqlid_planhash):  Enter value for category (DEFAULT):  Enter value for force_matching (FALSE): TRUE old  19: sql_id = ‘&&sql_id‘ new  19: sql_id = ‘0xy0uj562r893‘ old  20: and child_number = &&child_no new  20: and child_number = 0 old  27: decode(‘&&profile_name‘,‘X0X0X0X0‘,‘PROF_&&sql_id‘||‘_‘||plan_hash_value,‘&&profile_name‘) new  27: decode(‘X0X0X0X0‘,‘X0X0X0X0‘,‘PROF_0xy0uj562r893‘||‘_‘||plan_hash_value,‘X0X0X0X0‘) old  33: sql_id = ‘&&sql_id‘ new  33: sql_id = ‘0xy0uj562r893‘ old  34: and child_number = &&child_no; new  34: and child_number = 0; old  39: category => ‘&&category‘, new  39: category => ‘DEFAULT‘, old  41: force_match => &&force_matching new  41: force_match => TRUE old  52:   dbms_output.put_line(‘ERROR: sql_id: ‘||‘&&sql_id‘||‘ Child: ‘||‘&&child_no‘||‘ not found in v$sql.‘); new  52:   dbms_output.put_line(‘ERROR: sql_id: ‘||‘0xy0uj562r893‘||‘ Child: ‘||‘0‘||‘ not found in v$sql.‘); SQL>  SQL> select /* test2 */ * from dh_sql where name=‘DBA_TABLES‘;
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor(‘0xy0uj562r893‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_0xy0uj562r893_1575588977 used for this statement   --可以看到sql profile确实已经生效 22 rows selected. --新建一个索引，确认已经固定执行计划的语句不会因为访问路径而改变执行计划 SQL> create index ind_dh_sql2 on dh_sql(name) compute statistics;    Index created. SQL> select /* test2 */ * from dh_sql a where name=‘DBA_TABLES‘;         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 2 rows selected. 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 |  --没有固定的语句使用索引计划 ------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access("NAME"=:SYS_B_0) 19 rows selected.
SQL> select /* test2 */ * from dh_sql where name=‘DBA_TABLES‘;
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor(‘0xy0uj562r893‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  0xy0uj562r893, child number 0 ------------------------------------- select /* test2 */ * from dh_sql where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 |   --已经固定的执行计划还是使用全表扫描 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_0xy0uj562r893_1575588977 used for this statement 22 rows selected.
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> select /* test2 */ /*+ full(a) */ *from dh_sql a where name=‘DBA_TABLES‘;
        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 selected. SQL> select * from table(dbms_xplan.display_cursor(‘6vq4tjw38m8hk‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  6vq4tjw38m8hk, child number 0 ------------------------------------- select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" Plan hash value: 1575588977
---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 |   --我们指定的预期执行计划 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) 18 rows selected.
SQL> @create_sql_profile.sql     ---为这个预期的SQL执行计划创建sql profile Enter value for sql_id: 6vq4tjw38m8hk Enter value for child_no (0):  Enter value for profile_name (PROF_sqlid_planhash):  Enter value for category (DEFAULT):  Enter value for force_matching (FALSE): TRUE old  19: sql_id = ‘&&sql_id‘ new  19: sql_id = ‘6vq4tjw38m8hk‘ old  20: and child_number = &&child_no new  20: and child_number = 0 old  27: decode(‘&&profile_name‘,‘X0X0X0X0‘,‘PROF_&&sql_id‘||‘_‘||plan_hash_value,‘&&profile_name‘) new  27: decode(‘X0X0X0X0‘,‘X0X0X0X0‘,‘PROF_6vq4tjw38m8hk‘||‘_‘||plan_hash_value,‘X0X0X0X0‘) old  33: sql_id = ‘&&sql_id‘ new  33: sql_id = ‘6vq4tjw38m8hk‘ old  34: and child_number = &&child_no; new  34: and child_number = 0; old  39: category => ‘&&category‘, new  39: category => ‘DEFAULT‘, old  41: force_match => &&force_matching new  41: force_match => TRUE old  52:   dbms_output.put_line(‘ERROR: sql_id: ‘||‘&&sql_id‘||‘ Child: ‘||‘&&child_no‘||‘ not found in v$sql.‘); new  52:   dbms_output.put_line(‘ERROR: sql_id: ‘||‘6vq4tjw38m8hk‘||‘ Child: ‘||‘0‘||‘ not found in v$sql.‘); SQL> select /* test2 */ /*+ full(a) */ *from dh_sql a where name=‘DBA_TABLES‘;         ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. SQL> select * from table(dbms_xplan.display_cursor(‘6vq4tjw38m8hk‘,‘‘,‘‘)); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------------------------- SQL_ID  6vq4tjw38m8hk, child number 0 ------------------------------------- select /* test2 */ /*+ full(a) */ *from dh_sql a where name=:"SYS_B_0" Plan hash value: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROF_6vq4tjw38m8hk_1575588977 used for this statement 22 rows selected.
SQL> @move_sql_profile.sql           --需要一些权限，建议用SYS用户执行 Enter value for profile_name: PROF_6vq4tjw38m8hk_1575588977 Enter value for sql_id: bp7gpwq6w88nv Enter value for category (DEFAULT):  Enter value for force_matching (false): TRUE old  18:    ‘and name like (‘‘&&profile_name‘‘) ‘|| new  18:    ‘and name like (‘‘PROF_6vq4tjw38m8hk_1575588977‘‘) ‘|| old  36:    ‘and p.name like (‘‘&&profile_name‘‘)) ‘|| new  36:    ‘and p.name like (‘‘PROF_6vq4tjw38m8hk_1575588977‘‘)) ‘|| old  55: and name like (‘&&profile_name‘) new  55: and name like (‘PROF_6vq4tjw38m8hk_1575588977‘) old  66: sql_id = ‘&&sql_id‘; new  66: sql_id = ‘bp7gpwq6w88nv‘; old  71: , category => ‘&&category‘ new  71: , category => ‘DEFAULT‘ old  72: , name => ‘PROFILE_‘||‘&&sql_id‘||‘_moved‘ new  72: , name => ‘PROFILE_‘||‘bp7gpwq6w88nv‘||‘_moved‘ old  77: , force_match => &&force_matching new  77: , force_match => TRUE declare * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at line 26

SQL> conn /as sysdba Connected. SQL> set verify off SQL> @move_sql_profile.sql Enter value for profile_name: PROF_6vq4tjw38m8hk_1575588977 Enter value for sql_id: bp7gpwq6w88nv Enter value for category (DEFAULT):  Enter value for force_matching (false):  PL/SQL procedure successfully completed. SQL> conn dbmon/dbmon_123 Connected. SQL> select /* test2 */ * from dh_sql a where name=‘DBA_TABLES‘;
        ID NAME                           TYPE ---------- ------------------------------ ------------------------------       3167 DBA_TABLES                     VIEW       3168 DBA_TABLES                     SYNONYM 2 rows selected. 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: 1575588977 ---------------------------------------------------------------------------- | Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |        |       |       |   112 (100)|          | |*  1 |  TABLE ACCESS FULL| DH_SQL |     2 |    76 |   112   (0)| 00:07:50 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("NAME"=:SYS_B_0) Note -----    - SQL profile PROFILE_bp7gpwq6w88nv_moved used for this statement   --目的达成！ 22 rows selected.