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

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 pro

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