自動/手動型別sql_profile(dbms_sqltune/coe_xfr_sql_profile.sql)
一、自動型別的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(*)
----------
10000
SQL> 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
----------
1
SQL> set lines 200
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: 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$1
Outline 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"=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 9000
set longchunksize 1000
set linesize 100
select 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_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
-------------------------------------------------------------------------------
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 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
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 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 Profile
DBMS_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
----------
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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"=1)
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 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$1
Outline 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_profile
execute 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
----------
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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]
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
- SQL profile SYS_SQLPROF_015e462e462e0002 used for this statement
46 rows selected.
二、手動型別的sql_profile(使用coe_xfr_sql_profile.sql)
--沿用上述sql,則先需刪除sql_profile
exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_015e45fbfb7e0001');
--驗證，重新執行原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=1
Plan 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$1
Outline 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"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
42 rows selected.
---生成原sql的manual型別的sql_profile;
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: c4j6hxkqudj1s
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3617692013 .002
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3617692013
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.
COE_XFR_SQL_PROFILE completed.
SQL>!ls coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql
coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql
--最佳化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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: 81hhdnr1waru8
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1369807930 .001
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1369807930
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.
COE_XFR_SQL_PROFILE completed.
SQL>
SQL>!ls coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql
coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql
---調整計劃先看原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_81hhdnr1waru8_1369807930.sql)中的hints
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]');
替換原sql的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql)中的hints
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]');
--同時將原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.com
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的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql)
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.com
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 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
3990623997227762646
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed
--驗證：即執行原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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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]
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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"=4)
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> 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(*)
----------
10000
SQL> 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
----------
1
SQL> set lines 200
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: 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$1
Outline 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"=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 9000
set longchunksize 1000
set linesize 100
select 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_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
-------------------------------------------------------------------------------
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 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
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
----------------------------------------------------------------------------------------------------
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 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 Profile
DBMS_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
----------
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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"=1)
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 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$1
Outline 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_profile
execute 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
----------
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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]
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
- SQL profile SYS_SQLPROF_015e462e462e0002 used for this statement
46 rows selected.
二、手動型別的sql_profile(使用coe_xfr_sql_profile.sql)
--沿用上述sql,則先需刪除sql_profile
exec DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_015e45fbfb7e0001');
--驗證，重新執行原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=1
Plan 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$1
Outline 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"=1)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
42 rows selected.
---生成原sql的manual型別的sql_profile;
SQL> @coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter value for 1: c4j6hxkqudj1s
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3617692013 .002
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 3617692013
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.
COE_XFR_SQL_PROFILE completed.
SQL>!ls coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql
coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql
--最佳化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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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_profile
Parameter 1:
SQL_ID (required)
Enter value for 1: 81hhdnr1waru8
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1369807930 .001
Parameter 2:
PLAN_HASH_VALUE (required)
Enter value for 2: 1369807930
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.
COE_XFR_SQL_PROFILE completed.
SQL>
SQL>!ls coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql
coe_xfr_sql_profile_81hhdnr1waru8_1369807930.sql
---調整計劃先看原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_81hhdnr1waru8_1369807930.sql)中的hints
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]');
替換原sql的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql)中的hints
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]');
--同時將原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.com
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的sql_profile(coe_xfr_sql_profile_c4j6hxkqudj1s_3617692013.sql)
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.com
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 /
PL/SQL procedure successfully completed.
SQL>WHENEVER SQLERROR CONTINUE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
3990623997227762646
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_c4j6hxkqudj1s_3617692013 completed
--驗證：即執行原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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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]
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):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline 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"=4)
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.
經過驗證依然生效。。。。。。。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2144446/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- DBMS_SQLTUNE詳解(2)---sql_profileSQL
- 自動提升為int型別型別
- 遊戲自動化型別和方向遊戲型別
- Java資料型別自動轉換（++ ，+=）Java資料型別
- C語言中資料型別的自動型別轉換C語言資料型別
- Oracle SQL_Profile手動生成及繫結sql執行計劃OracleSQL
- jsp頁面number型別自動轉為String型別JS型別
- PostgreSQL自定義自動型別轉換(CAST)SQL型別AST
- php之資料型別自動轉換PHP資料型別
- Guru of the week:#19 自動型別轉換. (轉)型別
- java基本資料型別與自動轉換Java資料型別
- JS直譯器之自動型別轉換：[]==![]JS型別
- Jenkins：批次自動將 Maven 型別 Job 遷移到自由風格型別JenkinsMaven型別
- 轉載自SAPSKY 物料移動型別和後勤自動科目設定型別
- SAP筆記-物料移動型別和後勤自動科目設定筆記型別
- web前端仿手機左右滑動（手滑+自動滑動）Web前端
- 郵箱輸入實現型別自動提示功能型別
- 自動型別安全的.NET標準REST庫refit型別REST
- 深入解析C++的auto自動型別推導C++型別
- mysql啟動與關閉（手動與自動）MySql
- 【.NET】多執行緒：自動重置事件與手動重置事件的區別執行緒事件
- 元件化下EventBus的訊息型別自動編譯元件化型別編譯
- 設定IDEA自動忽略GIT提交檔案型別IdeaGit型別
- SAP UB型別的STO能自動建立交貨單？型別
- SAP MM 移動型別型別
- eclipse 手動/自動安裝外掛Eclipse
- 記憶體自動管理與手動管理記憶體
- golang 快速入門 [8.2]-自動型別推斷的祕密Golang型別
- Java基本型別自動裝箱的效能成本 -Coffee TalkJava型別
- TS 分析字串實現 Commander.js 自動強型別字串JS型別
- Protobuf自動反射訊息型別的網路傳輸方案反射型別
- c語言中資料型別的自動轉換原則C語言資料型別
- 商品新增-選擇商品型別自動載入商品屬性型別
- [python學習手冊-筆記]004.動態型別Python筆記型別
- 淺談程式語言型別的強型別，弱型別，動態型別，靜態型別型別
- drf☞jwt自動簽發與手動簽發JWT
- 使用fastlane match自動和手動管理證書AST
- 禁用Windows自動更新並允許手動更新Windows