SQLProfile总结（四）--使用示例 - 数据库编程

ry (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 ro

SQLProfile总结（四）--使用示例(二)