前提：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.