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

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

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