Manual类型的SQL Profile
实验目的：Manual类型稳定执行计划，相对AUTOMATIC类型更稳定
关键词： dbms_stats.gather_table_stats、 DBMS_SQLTUNE.DROP_SQL_PROFILE、 coe_xfr_sql_profile.sql
一、创建表，收集表统计信息
SQL> create table t1( n number);
表已创建。
SQL> declare
2 begin
3 for i in 1 .. 10000
4 loop
5 insert into t1 values(i);
6 commit;
7 end loop;
8 end;
9 /
PL/SQL 过程已成功完成。
SQL> select count(*) from t1;
COUNT(*)
----------
10000
SQL> create index idx_t1 on t1(n);
索引已创建。
SQL> exec dbms_stats.gather_table_stats( ownname =>'TEST' , tabname =>'T1' , method_opt =>'for all columns size 1', CASCADE => TRUE);
PL/SQL 过程已成功完成。
二是查找到SQL_PROFILE,并将相应的SQL_PROFILE删除
SQL> SET LONG 9000
SQL> SET LONGCHUNKSIZE 1000
SQL> SET LINESIZE 2000
SQL> SELECT NAME ,SQL_TEXT, TYPE,STATUS,FORCE_MATCHING FROM DBA_SQL_PROFILES WHERE SQL_TEXT LIKE 'SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE%';
NAME SQL_TEXT
TYPE STATUS FORCE_
------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------- ---------------- ------
SYS_SQLPROF_0162663bdb700000 SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1
MANUAL ENABLED NO
SYS_SQLPROF_01626643a6130001 SELECT /*+ NO_INDEX(T1 IDX_T1) */ * FROM T1 WHERE N=1
MANUAL ENABLED YES
SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_01626643a6130001');
PL/SQL 过程已成功完成。
SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('SYS_SQLPROF_0162663bdb700000');
PL/SQL 过程已成功完成。
三是调用coe_xfr_sql_profile.sql，产生Manual类型的SQL PROFILE脚本
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 gn8zuq00kd86g, 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 | 4 | 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行。
SQL> SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3;
Nse
----------
3
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 866w0nx37z5kg, child number 0
-------------------------------------
SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 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"=3)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "N"[NUMBER,22]
已选择42行。
SQL> SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%';
SQL_TEXT
--------------------------------------------------------------------------------
SQL_ID VERSION_COUNT
-------------------------- -------------
SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE '%N=3%'
9bvng6dz8ct9z 1
SELECT /*+ INDEX(T1 IDX_T1) */* FROM T1 WHERE N=3
866w0nx37z5kg 1
SQL> SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg';
PLAN_HASH_VALUE
---------------
1369807930
SQL> @F:\oracle\脚本\coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
输入 1 的值: 866w0nx37z5kg --条件为 N=3 的SQL_ID
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
1369807930 .001
Parameter 2:
PLAN_HASH_VALUE (required)
输入 2 的值: 1369807930
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "866w0nx37z5kg"
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_866w0nx37z5kg_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> @F:\oracle\脚本\coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
输入 1 的值: gn8zuq00kd86g --条件为 N=1的SQL_ID
PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
3617692013 .002
Parameter 2:
PLAN_HASH_VALUE (required)
输入 2 的值: 3617692013
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID : "gn8zuq00kd86g"
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_gn8zuq00kd86g_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.
四、将coe_xfr_sql_profile_866w0nx37z5kg_1369807930.sql中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]');
替换 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql
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);
并将 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql中的参数 FORCE_MATCH 的值由 FALSE 替换 TRUE
五、执行脚本，调整执行计划
SQL> @C:\Users\YX\coe_xfr_sql_profile_gn8zuq00kd86g_3617692013（修改后）.sql
SQL>REM
SQL>REM $Header: 215187.1 coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql 11.4.3.5 2018/03/28 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_gn8zuq00kd86g_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 gn8zuq00kd86g 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_gn8zuq00kd86g_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_gn8zuq00kd86g_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=1
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 DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
19 sql_text => sql_txt,
20 profile => h,
21 name => 'coe_gn8zuq00kd86g_3617692013',
22 description => 'coe gn8zuq00kd86g 3617692013 '||:signature||'',
23 category => 'DEFAULT',
24 validate => TRUE,
25 replace => TRUE,
26 force_match => TRUE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
27 END;
28 /
PL/SQL 过程已成功完成。
SQL>WHENEVER SQLERROR CONTIN UE
SQL>SET ECHO OFF;
SIGNATURE
---------------------
... manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed
六、查看执行计划
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 gn8zuq00kd86g, 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 | 4 | 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 coe_gn8zuq00kd86g_3617692013 used for this statement
已选择46行。
