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';
SQLProfile总结（四）--使用示例(三)
file_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
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.