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

ws 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 | -----------------------------------------------------

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