手动类型sql_profile怎么使用

本篇内容介绍了“手动类型sql_profile怎么使用”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

一、自动类型的sql_profile(运用dbms_sqltune)实验如下：SQL> create table t1 (n number);SQL> create table t1 (n number);Table created.SQL> declare     begin     for i in 1..10000 loop     insert into t1 values(i);commit;     end loop;     end;     /PL/SQL procedure successfully completed.SQL> select count(*) from t1;  COUNT(*)----------     10000SQL>  create index idx_t1 on t1(n);Index created.SQL> analyze table t1 compute statistics;Table analyzed.SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;         N----------         1SQL> set lines 200SQL> select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT-----------------------------------------------------------------SQL_ID  1kg76709mx29d, child number 0-------------------------------------select /*+ no_index(t1 idx_t1) */ * from t1 where n=1Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          ||*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     7   (0)| 00:00:01 |PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------------      BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "T1"@"SEL$1")      END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT---------------------------------------------   1 - filter("N"=1)Column Projection Information (identified by operation id):-----------------------------------------------------------1 - "N"[NUMBER,22]42 rows selected.SQL> --使用sql tunning 优化（dbms_sqltune)declare my_task_name VARCHAR2(30);my_sqltext   CLOB;BEGIN my_sqltext := select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(         sql_text    => my_sqltext,         user_name   => SYS,scope       => COMPREHENSIVE,         time_limit  => 60,         task_name   => my_sql_tuning_task_2,description => Task to tune a query on t1);END;/PL/SQL procedure successfully completed.--执行自动tune任务：exec dbms_sqltune.execute_tuning_task(my_sql_tuning_task_2);--查看自动调整任务的调整结果set long 9000set longchunksize 1000set linesize 100select dbms_sqltune.report_tuning_task(my_sql_tuning_task_2) from dual;DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------GENERAL INFORMATION SECTION-------------------------------------------------------------------------------Tuning Task Name   : my_sql_tuning_task_2Tuning Task Owner  : SYSWorkload Type      : Single SQL StatementScope              : COMPREHENSIVETime Limit(seconds): 60Completion Status  : COMPLETEDStarted at         : 09/03/2017 12:22:50Completed at       : 09/03/2017 12:22:51DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------Schema Name: SYSSQL ID     : 4bh7sn1zvpgq7SQL Text   : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1-------------------------------------------------------------------------------FINDINGS SECTION (1 finding)-------------------------------------------------------------------------------1- SQL Profile Finding (see explain plans section below)--------------------------------------------------------DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------A potentially better execution plan was found for this statement.Recommendation (estimated benefit: 90.91%)  ------------------------------------------  - Consider accepting the recommended SQL profile.execute dbms_sqltune.accept_sql_profile(task_name =>my_sql_tuning_task_2, task_owner => SYS, replace => TRUE);  Validation results  ------------------The SQL profile was tested by executing both its plan and the original planDBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------and measuring their respective execution statistics. A plan may have beenonly partially executed if the other could be run to completion in less time.                           Original Plan  With SQL Profile  % Improved-------------  ----------------  ----------  Completion Status:            COMPLETE          COMPLETEElapsed Time (s):             .000136           .000017       87.5 %  CPU Time (s):                   .0001                 0        100 %User I/O Time (s):                  0                 0Buffer Gets:                       22                 2       90.9 %  Physical Read Requests:             0                 0DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------  Physical Write Requests:            0                 0Physical Read Bytes:                0                 0  Physical Write Bytes:               0                 0Rows Processed:                     1                 1Fetches:                            1                 1  Executions:                         1                 1  Notes  -----1. Statistics for the original plan were averaged over 10 executions.2. Statistics for the SQL profile plan were averaged over 10 executions.DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------EXPLAIN PLANS SECTION-------------------------------------------------------------------------------1- Original With Adjusted Cost------------------------------Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |     1 |     3 |     7   (0)| 00:00:01 ||*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     7   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("N"=1)2- Using SQL ProfileDBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)---------------------------------------------------------------------------------------------Plan hash value: 1369807930---------------------------------------------------------------------------| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT |        |     1 |     3 |     1   (0)| 00:00:01 ||*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------Predicate Information (identified by operation id):DBMS_SQLTUNE.REPORT_TUNING_TASK(MY_SQL_TUNING_TASK_2)----------------------------------------------------------------------------------------------------   1 - access("N"=1)---------------------------------------------------------------------------------接受 sql_profile：execute dbms_sqltune.accept_sql_profile(task_name =>my_sql_tuning_task_2, task_owner => SYS, replace => TRUE);PL/SQL procedure successfully completed.--删除tuning_task，这里不执行exec dbms_sqltune.drop_tuning_task(my_sql_tuning_task_2);--验证（执行sql,并查看执行计划）SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;         N----------         1SQL> select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  1kg76709mx29d, child number 0-------------------------------------select /*+ no_index(t1 idx_t1) */ * from t1 where n=1Plan hash value: 1369807930---------------------------------------------------------------------------| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          ||*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |PLAN_TABLE_OUTPUT------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------      BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))      END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------   1 - access("N"=1)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "N"[NUMBER,22]Note-----PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------- SQL profile SYS_SQLPROF_015e45fbfb7e0001 used for this statement46 rows selected.---现在试着将原sql的where调整的n=1改成n=2；SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;         N----------         2SQL>  select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  c4j6hxkqudj1s, child number 0-------------------------------------select /*+ no_index(t1 idx_t1) */ * from t1 where n=2Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          ||*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     7   (0)| 00:00:01 |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      FULL(@"SEL$1" "T1"@"SEL$1")      END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------   1 - filter("N"=2)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "N"[NUMBER,22]42 rows selected.---想让原sql profile还生效，需加入force_match=>true，再重新实行一下dbms_sqltune.accept_sql_profileexecute dbms_sqltune.accept_sql_profile(task_name =>my_sql_tuning_task_2, task_owner => SYS, replace => TRUE,force_match=>true);注意force_match参数，相当于将sql的where条件中的输入值用绑定变量替换.SQL> execute dbms_sqltune.accept_sql_profile(task_name =>my_sql_tuning_task_2, task_owner => SYS, replace => TRUE,force_match=>true);PL/SQL procedure successfully completed.SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;         N----------         2SQL> select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  c4j6hxkqudj1s, child number 0-------------------------------------select /*+ no_index(t1 idx_t1) */ * from t1 where n=2Plan hash value: 1369807930---------------------------------------------------------------------------| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          ||*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)      ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------   1 - access("N"=2)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "N"[NUMBER,22]Note-----PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------- SQL profile SYS_SQLPROF_015e462e462e0002 used for this statement46 rows selected.二、手动类型的sql_profile(使用coe_xfr_sql_profile.sql)--沿用上述sql,则先需删除sql_profileexec DBMS_SQLTUNE.DROP_SQL_PROFILE (SYS_SQLPROF_015e45fbfb7e0001);--验证，重新执行原sqlSQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;         N----------         2SQL> select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------SQL_ID  c4j6hxkqudj1s, child number 0-------------------------------------select /*+ no_index(t1 idx_t1) */ * from t1 where n=1Plan hash value: 3617692013--------------------------------------------------------------------------| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  |      |       |       |     7 (100)|          ||*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     7   (0)| 00:00:01 |PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------      BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)      ALL_ROWSOUTLINE_LEAF(@"SEL$1")      FULL(@"SEL$1" "T1"@"SEL$1")      END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------   1 - filter("N"=1)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "N"[NUMBER,22]42 rows selected.---生成原sql的manual类型的sql_profile;SQL> @coe_xfr_sql_profile.sqlParameter 1:SQL_ID (required)Enter value for 1: c4j6hxkqudj1sPLAN_HASH_VALUE AVG_ET_SECS--------------- -----------     3617692013        .002Parameter 2:PLAN_HASH_VALUE (required)Enter value for 2: 3617692013Values passed to coe_xfr_sql_profile:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL_ID         : "c4j6hxkqudj1s"PLAN_HASH_VALUE: "3617692013"SQL>BEGIN  2    IF :sql_text IS NULL THEN3      RAISE_APPLICATION_ERROR(-20100, SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).);  4    END IF;  5  END;6  /SQL>SET TERM OFF;SQL>BEGIN  2    IF :other_xml IS NULL THEN3      RAISE_APPLICATION_ERROR(-20101, PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).);  4    END IF;  5  END;  6  /SQL>SET TERM OFF;Execute coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sqlon TARGET system in order to create a custom SQL Profilewith plan 3617692013 linked to adjusted sql_text.COE_XFR_SQL_PROFILE completed.SQL>!ls coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sqlcoe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql--优化sql(加hint走索引)SQL>select /*+ index(t1 idx_t1) */ * from t1 where n=2;         N----------         2SQL>select * from table(dbms_xplan.display_cursor(null,null,advanced));PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------SQL_ID  81hhdnr1waru8, child number 0-------------------------------------select /*+ index(t1 idx_t1) */ * from t1 where n=2Plan hash value: 1369807930---------------------------------------------------------------------------| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |---------------------------------------------------------------------------|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          ||*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     3 |     1   (0)| 00:00:01 |PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$1 / T1@SEL$1Outline Data-------------  /*+PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE(11.2.0.4)      DB_VERSION(11.2.0.4)ALL_ROWS      OUTLINE_LEAF(@"SEL$1")      INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))      END_OUTLINE_DATA  */Predicate Information (identified by operation id):PLAN_TABLE_OUTPUT--------------------------------------------------------------------------------   1 - access("N"=2)Column Projection Information (identified by operation id):-----------------------------------------------------------   1 - "N"[NUMBER,22]42 rows selected.---生成改写后sql的manual类型的sql_profile;SQL>@coe_xfr_sql_profileParameter 1:SQL_ID (required)Enter value for 1: 81hhdnr1waru8PLAN_HASH_VALUE AVG_ET_SECS--------------- -----------     1369807930        .001Parameter 2:PLAN_HASH_VALUE (required)Enter value for 2: 1369807930Values passed to coe_xfr_sql_profile:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SQL_ID         : "81hhdnr1waru8"PLAN_HASH_VALUE: "1369807930"SQL>BEGIN  2    IF :sql_text IS NULL THEN3      RAISE_APPLICATION_ERROR(-20100, SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).);  4    END IF;5  END;  6  /SQL>SET TERM OFF;SQL>BEGIN  2    IF :other_xml IS NULL THEN3      RAISE_APPLICATION_ERROR(-20101, PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).);  4    END IF;  5  END;  6  /SQL>SET TERM OFF;Execute coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sqlon TARGET system in order to create a custom SQL Profilewith plan 1369807930 linked to adjusted sql_text.COE_XFR_SQL_PROFILE completed.SQL>SQL>!ls coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sqlcoe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql

---调整计划先看原sql的sql_profile

“手动类型sql_profile怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！