本篇内容介绍了“手动类型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(*)
----------
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 : 4bh7sn1zvpgq7
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_profile怎么使用”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
