SQL Profile of type Manual
Purpose: Manual type stable execution plan, which is more stable than AUTOMATIC type
Keywords: dbms_stats.gather_table_stats, DBMS_SQLTUNE.DROP_SQL_PROFILE, coe_xfr_sql_profile.sql
First, create tables and collect table statistics
SQL > create table T1 (n number)
The table has been created.
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 /
The PL/SQL process completed successfully.
SQL > select count (*) from T1
COUNT (*)
-
10000
SQL > create index idx_t1 on T1 (n)
The index has been created.
SQL > exec dbms_stats.gather_table_stats (ownname = > 'TEST', tabname = >' T1', method_opt = > 'for all columns size 1', CASCADE = > TRUE)
The PL/SQL process completed successfully.
Second, find the SQL_PROFILE and delete the corresponding 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 No.1
MANUAL ENABLED NO
SYS_SQLPROF_01626643a6130001 SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1
MANUAL ENABLED YES
SQL > EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_01626643a6130001')
The PL/SQL process completed successfully.
SQL > EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_0162663bdb700000')
The PL/SQL process completed successfully.
The third is to call coe_xfr_sql_profile.sql to generate a SQL PROFILE script of type Manual
SQL > SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1
N
-
one
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 No.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]
Selected 42 rows.
SQL > SELECT / * + INDEX (T1 IDX_T1) * / * FROM T1 WHERE Numb3
Nse
-
three
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 Niss3
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]
Selected 42 rows.
SQL > SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE'% Niss3%'
SQL_TEXT
SQL_ID VERSION_COUNT
SELECT SQL_TEXT,SQL_ID,VERSION_COUNT FROM V$SQLAREA WHERE SQL_TEXT LIKE'% Niss3%'
9bvng6dz8ct9z 1
SELECT / * + INDEX (T1 IDX_T1) * / * FROM T1 WHERE Niss3
866w0nx37z5kg 1
SQL > SELECT PLAN_HASH_VALUE FROM V$SQL WHERE SQL_ID='866w0nx37z5kg'
PLAN_HASH_VALUE
-
1369807930
SQL > @ F:\ oracle\ script\ coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter a value of 1: 866w0nx37z5kg-SQL_ID with the condition Niss3
PLAN_HASH_VALUE AVG_ET_SECS
--
1369807930. 001
Parameter 2:
PLAN_HASH_VALUE (required)
Enter a value of 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\ script\ coe_xfr_sql_profile.sql
Parameter 1:
SQL_ID (required)
Enter a value of 1: gn8zuq00kd86g-SQL_ID with the condition Numb1
PLAN_HASH_VALUE AVG_ET_SECS
--
3617692013. 002
Parameter 2:
PLAN_HASH_VALUE (required)
Enter a value of 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.
4. Combine HINT in coe_xfr_sql_profile_866w0nx37z5kg_1369807930.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'[INDEX (@ "SEL$1"T1" @ "SEL$1" ("T1". "N"))]'
Q'[END_OUTLINE_DATA]')
Replace 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)
And replace the value of parameter FORCE_MATCH in coe_xfr_sql_profile_gn8zuq00kd86g_3617692013.sql with FALSE instead of TRUE
Execute the script and adjust the execution plan
SQL > @ C:\ Users\ YX\ coe_xfr_sql_profile_gn8zuq00kd86g_3617692013 (modified) .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 Numb1
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 /
The PL/SQL process completed successfully.
SQL > WHENEVER SQLERROR CONTIN UE
SQL > SET ECHO OFF
SIGNATURE
-
... Manual custom SQL Profile has been created
COE_XFR_SQL_PROFILE_gn8zuq00kd86g_3617692013 completed
Check the implementation plan
SQL > SELECT / * + NO_INDEX (T1 IDX_T1) * / * FROM T1 WHERE No.1
N
-
one
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 No.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 rows have been selected.
