--查看自动调整任务的调整结果 set long 9000 set longchunksize 1000 set linesize 100 select port_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_2 Tuning Task Owner : SYS Workload Type : Single SQL Statement Scope : COMPREHENSIVE Time Limit(seconds): 60 Completion Status : COMPLETED Started at : 09/03/2017 12:22:50 Completed at : 09/03/2017 12:22:51
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') ---------------------------------------------------------------------------------------------------- Schema Name: SYS SQL ID : 4bh6sn1zvpgq7 SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
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.
Validation results ------------------ The SQL profile was tested by executing both its plan and the original plan
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2') ---------------------------------------------------------------------------------------------------- and measuring their respective execution statistics. A plan may have been only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved ------------- ---------------- ---------- Completion Status: COMPLETE COMPLETE Elapsed Time (s): .000136 .000017 87.5 % CPU Time (s): .0001 0 100 % User I/O Time (s): 0 0 Buffer Gets: 22 2 90.9 % Physical Read Requests: 0 0
Notes ----- 1. Statistics for the original plan were averaged over 10 executions. 2. Statistics for the SQL profile plan were averaged over 10 executions.
--验证（执行sql,并查看执行计划） SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N ---------- 1
SQL> 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=1
Plan 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): -------------------------------------------------------------
Column Projection Information (identified by operation id): -----------------------------------------------------------
1 - "N"[NUMBER,22]
Note -----
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- - SQL profile SYS_SQLPROF_015e45fbfb7e0001 used for this statement
46 rows selected.
---现在试着将原sql的where调整的n=1改成n=2； SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N ---------- 2
SQL> 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=2
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------
Column Projection Information (identified by operation id): -----------------------------------------------------------
1 - "N"[NUMBER,22]
Note -----
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- - SQL profile SYS_SQLPROF_015e462e462e0002 used for this statement
PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): -------------------------------------------------------------
Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "c4j6hxkqudj1s" PLAN_HASH_VALUE: "3617692013"
SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 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 THEN 3 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.sql on TARGET system in order to create a custom SQL Profile with plan 3617692013 linked to adjusted sql_text.
--优化sql(加hint走索引) SQL>select /*+ index(t1 idx_t1) */ * from t1 where n=2;
N ---------- 2
SQL>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=2
Plan 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): -------------------------------------------------------------
Values passed to coe_xfr_sql_profile: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SQL_ID : "81hhdnr1waru8" PLAN_HASH_VALUE: "1369807930"
SQL>BEGIN 2 IF :sql_text IS NULL THEN 3 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 THEN 3 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.sql on TARGET system in order to create a custom SQL Profile with plan 1369807930 linked to adjusted sql_text.
---调整计划先看原sql的sql_profile 即coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql [oracle@slient ~]$ more coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql SPO coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.log; SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999; 省略......................................
WHENEVER SQLERROR EXIT SQL.SQLCODE; REM VAR signature NUMBER; REM DECLARE sql_txt CLOB; h SYS.SQLPROF_ATTR; BEGIN sql_txt := q'[ select /*+ no_index(t1 idx_t1) */ * from t1 where n=2 ]'; =============注意hint=================== h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', q'[DB_VERSION('11.2.0.4')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[FULL(@"SEL$1" "T1"@"SEL$1")]', q'[END_OUTLINE_DATA]'); ============================================ :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( sql_text => sql_txt, profile => h, name => 'coe_c4j6hxkqudj1s_3617692013', description => 'coe c4j6hxkqudj1s 3617692013 '||:signature||'', category => 'DEFAULT', validate => TRUE, replace => TRUE, force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); END; / WHENEVER SQLERROR CONTINUE SET ECHO OFF; PRINT signature PRO PRO ... manual custom SQL Profile has been created PRO SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""; SPO OFF; PRO PRO COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed [oracle@slient ~]$
--查看改写后sql的sql_profile 即coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql [oracle@slient ~]$ more coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql SPO coe_xfr_sql_profile_81hhdnr1waru8_1369807930.log; SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999; 省略。。。。。。。。。。。。。。。。。。。。。。。。。。。。 WHENEVER SQLERROR EXIT SQL.SQLCODE; REM VAR signature NUMBER; REM DECLARE sql_txt CLOB; h SYS.SQLPROF_ATTR; BEGIN sql_txt := q'[ select /*+ index(t1 idx_t1) */ * from t1 where n=2 ]'; ======================hint================ h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', q'[DB_VERSION('11.2.0.4')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]', q'[END_OUTLINE_DATA]'); ============================================= :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( sql_text => sql_txt, profile => h, name => 'coe_81hhdnr1waru8_1369807930', description => 'coe 81hhdnr1waru8 1369807930 '||:signature||'', category => 'DEFAULT', validate => TRUE, replace => TRUE, force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); END; / WHENEVER SQLERROR CONTINUE SET ECHO OFF; PRINT signature PRO PRO ... manual custom SQL Profile has been created PRO SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""; SPO OFF; PRO PRO COE_XFR_SQL_PROFILE_81hhdnr1waru8_1369807930 completed [oracle@slient ~]$
--同时将原sql的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) 中的参数force_match的值由false改为ture; 查看替换后的原sql的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql) [oracle@slient ~]$ cat coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql SPO coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.log; SET ECHO ON TERM ON LIN 2000 TRIMS ON NUMF 99999999999999999999; REM REM $Header: 215187.1 coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql 11.4.3.5 2017/09/03 carlos.sierra $ REM REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved. REM REM AUTHOR REM carlos.sierra@oracle REM REM SCRIPT REM coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql REM REM DESCRIPTION REM This script is generated by coe_xfr_sql_profile.sql REM It contains the SQL*Plus commands to create a custom REM SQL Profile for SQL_ID c4j6hxkqudj1s based on plan hash REM value 3617692013. REM The custom SQL Profile to be created by this script REM will affect plans for SQL commands with signature REM matching the one for SQL Text below. REM Review SQL Text and adjust accordingly. REM REM PARAMETERS REM None. REM REM EXAMPLE REM SQL> START coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql; REM REM NOTES REM 1. Should be run as SYSTEM or SYSDBA. REM 2. User must have CREATE ANY SQL PROFILE privilege. REM 3. SOURCE and TARGET systems can be the same or similar. REM 4. To drop this custom SQL Profile after it has been created: REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_c4j6hxkqudj1s_3617692013'); REM 5. Be aware that using DBMS_SQLTUNE requires a license REM for the Oracle Tuning Pack. REM WHENEVER SQLERROR EXIT SQL.SQLCODE; REM VAR signature NUMBER; REM DECLARE sql_txt CLOB; h SYS.SQLPROF_ATTR; BEGIN sql_txt := q'[ select /*+ no_index(t1 idx_t1) */ * from t1 where n=2 ]'; h := SYS.SQLPROF_ATTR( q'[BEGIN_OUTLINE_DATA]', q'[IGNORE_OPTIM_EMBEDDED_HINTS]', q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', q'[DB_VERSION('11.2.0.4')]', q'[ALL_ROWS]', q'[OUTLINE_LEAF(@"SEL$1")]', q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]', q'[END_OUTLINE_DATA]'); :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( sql_text => sql_txt, profile => h, name => 'coe_c4j6hxkqudj1s_3617692013', description => 'coe c4j6hxkqudj1s 3617692013 '||:signature||'', category => 'DEFAULT', validate => TRUE, replace => TRUE, force_match => true /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); END; / WHENEVER SQLERROR CONTINUE SET ECHO OFF; PRINT signature PRO PRO ... manual custom SQL Profile has been created PRO SET TERM ON ECHO OFF LIN 80 TRIMS OFF NUMF ""; SPO OFF; PRO PRO COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed
SQL>@coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql SQL>REM SQL>REM $Header: 215187.1 coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql 11.4.3.5 2017/09/03 carlos.sierra $ SQL>REM SQL>REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved. SQL>REM SQL>REM AUTHOR SQL>REM carlos.sierra@oracle SQL>REM SQL>REM SCRIPT SQL>REM coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql SQL>REM SQL>REM DESCRIPTION SQL>REM This script is generated by coe_xfr_sql_profile.sql SQL>REM It contains the SQL*Plus commands to create a custom SQL>REM SQL Profile for SQL_ID c4j6hxkqudj1s based on plan hash SQL>REM value 3617692013. SQL>REM The custom SQL Profile to be created by this script SQL>REM will affect plans for SQL commands with signature SQL>REM matching the one for SQL Text below. SQL>REM Review SQL Text and adjust accordingly. SQL>REM SQL>REM PARAMETERS SQL>REM None. SQL>REM SQL>REM EXAMPLE SQL>REM SQL> START coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql; SQL>REM SQL>REM NOTES SQL>REM 1. Should be run as SYSTEM or SYSDBA. SQL>REM 2. User must have CREATE ANY SQL PROFILE privilege. SQL>REM 3. SOURCE and TARGET systems can be the same or similar. SQL>REM 4. To drop this custom SQL Profile after it has been created: SQL>REM EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_c4j6hxkqudj1s_3617692013'); SQL>REM 5. Be aware that using DBMS_SQLTUNE requires a license SQL>REM for the Oracle Tuning Pack. SQL>REM SQL>WHENEVER SQLERROR EXIT SQL.SQLCODE; SQL>REM SQL>VAR signature NUMBER; SQL>REM SQL>DECLARE 2 sql_txt CLOB; 3 h SYS.SQLPROF_ATTR; 4 BEGIN 5 sql_txt := q'[ 6 select /*+ no_index(t1 idx_t1) 7 */ * from t1 where n=2 8 ]'; 9 h := SYS.SQLPROF_ATTR( 10 q'[BEGIN_OUTLINE_DATA]', 11 q'[IGNORE_OPTIM_EMBEDDED_HINTS]', 12 q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]', 13 q'[DB_VERSION('11.2.0.4')]', 14 q'[ALL_ROWS]', 15 q'[OUTLINE_LEAF(@"SEL$1")]', 16 q'[INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."N"))]', 17 q'[END_OUTLINE_DATA]'); 18 :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt); 19 DBMS_SQLTUNE.IMPORT_SQL_PROFILE ( 20 sql_text => sql_txt, 21 profile => h, 22 name => 'coe_c4j6hxkqudj1s_3617692013', 23 description => 'coe c4j6hxkqudj1s 3617692013 '||:signature||'', 24 category => 'DEFAULT', 25 validate => TRUE, 26 replace => TRUE, 27 force_match => true /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ ); 28 END; 29 /
--验证：即执行原sql,然后查看执行计划： SQL>select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N ---------- 2
SQL>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=2
Plan 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): -------------------------------------------------------------
Column Projection Information (identified by operation id): -----------------------------------------------------------
1 - "N"[NUMBER,22]
Note -----
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - SQL profile coe_c4j6hxkqudj1s_3617692013 used for this statement
46 rows selected.
--因为在改写原sql的sql_profile中将force_match值改为true，验证将where条件中的n=2改为n=4； SQL>select /*+ no_index(t1 idx_t1) */ * from t1 where n=4;
N ---------- 4
SQL>select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- SQL_ID fd5p89b5jz0ct, child number 0 ------------------------------------- select /*+ no_index(t1 idx_t1) */ * from t1 where n=4
Plan 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): -------------------------------------------------------------
Column Projection Information (identified by operation id): -----------------------------------------------------------
1 - "N"[NUMBER,22]
Note -----
PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- - SQL profile coe_c4j6hxkqudj1s_3617692013 used for this statement
