DBA Hub

📋Steps in this guide1/4

How to Create Custom SQL Profile

How to Create Custom SQL Profile Step 1: Review the mail.html Step 2: Run COE_XFR_SQL_PROFILE Step 3: Create Custome SQL Profile Step 4: Verif is it using SQL_PROFILE Step 5: Flushing a Single SQL Statement from Library Cache (Only if required) Step 1: Review the mail.html This mail.html file would have all the previous and … Continue reading How to Create Custom SQL Profile →

oracle clusteringintermediate
by OracleDba
16 views
1

Overview

How to Create Custom SQL Profile Step 1: Review the mail.html The plan hash value for the best execution plan would have suffix [B] Then plan hash value for the worst exection plan would have a suffix[W] 3253233075 [W] 3302976337 [B]

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
This mail.html file would have all the previous and current execution plans used by the sql along with timestamp when they were used.
The plan hash value for the best execution plan would have suffix [B]
Then plan hash value for the worst exection plan would have a suffix[W]
open sqlt_s41092_main.html
--->
plans
--->
Execution Plans
SQLID:  dkz7v96ym42c6

Plan Hash Value:
3253233075 [W]
3302976337 [B]
2

Section 2

Step 2: Run COE_XFR_SQL_PROFILE Step 3: Create Custome SQL Profile

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
[oracle@rac1 utl]$ pwd
/home/oracle/sqlt/utl
[oracle@rac1 utl]$ sqlplus / as sysdba
@coe_xfr_sql_profile.sql <sqlid> <Best Plan Hash Value from SQLT report>
SQL>
@coe_xfr_sql_profile.sql dkz7v96ym42c6 3302976337 <---
output:
=======

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "dkz7v96ym42c6"
PLAN_HASH_VALUE: "3302976337"

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_dkz7v96ym42c6_3302976337.sql
on TARGET system in order to create a custom SQL Profile
with plan 3302976337 linked to adjusted sql_text.
COE_XFR_SQL_PROFILE completed. <----
SQL>

The coe_xfr_sql_profile.sql script would create another sql file, which should be run to create manual sql profile for the sql 
The new sql file created.
[oracle@rac1 utl]$ pwd
/home/oracle/sqlt/utl
[oracle@rac1 utl]$ ls -ltr
coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.sql
-rw-r--r-- 1 oracle oinstall 3498 Oct 17 15:09 coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.sql
[oracle@rac1 utl]$
[oracle@rac1 utl]$
sqlplus / as sysdba
Execute the script and it would create a manual sql profile for the sql, which would help the sql to use the execution plan, for which plan hash value was passed

SQL>
@coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.sql
<----
SQL> REM
SQL> REM $Header: 215187.1 coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.sql 11.4.4.4 2016/10/17 carlos.sierra $
SQL> REM
SQL> REM Copyright (c) 2000-2012, Oracle Corporation. All rights reserved.
SQL> REM
SQL> REM AUTHOR
SQL> REM   [email protected]
SQL> REM
SQL> REM SCRIPT
SQL> REM   coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.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 dkz7v96ym42c6 based on plan hash
SQL> REM   value 3302976337.
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_dkz7v96ym42c6_3302976337.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_dkz7v96ym42c6_3302976337');
SQL> REM   5. Be aware that using DBMS_SQLTUNE requires a license
SQL> REM         for the Oracle Tuning Pack.
SQL> REM   6. If you modified a SQL putting Hints in order to produce a desired
SQL> REM         Plan, you can remove the artifical Hints from SQL Text pieces below.
SQL> REM         By doing so you can create a custom SQL Profile for the original
SQL> REM         SQL but with the Plan captured from the modified SQL (with Hints).
SQL> REM
SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE;
SQL> REM
SQL> VAR signature NUMBER;
SQL> VAR signaturef NUMBER;
SQL> REM
SQL> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  PROCEDURE wa (p_line IN VARCHAR2) IS
  5  BEGIN
  6  DBMS_LOB.WRITEAPPEND(sql_txt, LENGTH(p_line), p_line);
  7  END wa;
  8  BEGIN
  9  DBMS_LOB.CREATETEMPORARY(sql_txt, TRUE);
 10  DBMS_LOB.OPEN(sql_txt, DBMS_LOB.LOB_READWRITE);
 11  -- SQL Text pieces below do not have to be of same length.
 12  -- So if you edit SQL Text (i.e. removing temporary Hints),
 13  -- there is no need to edit or re-align unmodified pieces.
 14  wa(q'[select count(*) from test t1, test t2 where t1.c=t2.c and t1.c=1]');
 15  DBMS_LOB.CLOSE(sql_txt);
 16  h := SYS.SQLPROF_ATTR(
 17  q'[BEGIN_OUTLINE_DATA]',
 18  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 19  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]',
 20  q'[DB_VERSION('11.2.0.3')]',
 21  q'[ALL_ROWS]',
 22  q'[OUTLINE_LEAF(@"SEL$1")]',
 23  q'[FULL(@"SEL$1" "T1"@"SEL$1")]',
 24  q'[FULL(@"SEL$1" "T2"@"SEL$1")]',
 25  q'[LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")]',
 26  q'[USE_HASH(@"SEL$1" "T2"@"SEL$1")]',
 27  q'[END_OUTLINE_DATA]');
 28  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 29  :signaturef := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt, TRUE);
 30  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 31  sql_text    => sql_txt,
 32  profile     => h,
 33  name        => 'coe_dkz7v96ym42c6_3302976337',
 34  description => 'coe dkz7v96ym42c6 3302976337 '||:signature||' '||:signaturef||'',
 35  category    => 'DEFAULT',
 36  validate    => TRUE,
 37  replace     => TRUE,
 38  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 39  DBMS_LOB.FREETEMPORARY(sql_txt);
 40  END;
 41  /

PL/SQL procedure successfully completed.

SQL> WHENEVER SQLERROR CONTINUE
SQL> SET ECHO OFF;

            SIGNATURE
---------------------
  1141952350901668161


           SIGNATUREF
---------------------
 13148683388389418960


... manual custom SQL Profile has been created
<---
COE_XFR_SQL_PROFILE_dkz7v96ym42c6_3302976337 completed
SQL>
3

Section 3

@coe_xfr_sql_profile_dkz7v96ym42c6_3302976337.sql Step 4: Verif is it using SQL_PROFILE select name from dba_sql_profiles; Step 5: Flushing a Single SQL Statement from Library Cache (Only if required)

Code/Command (click line numbers to comment):

1
2
3
4
5
-- Run the problem SQL and below command parllel
select SQL_ID, CHILD_NUMBER, PLAN_HASH_VALUE, SQL_PROFILE FROM V$SQL where sql_id='&SQL_ID';
select * from table(dbms_xplan.display_cursor('dkz7v96ym42c6',null,'ADVANCED ALLSTATS LAST'));
Please verify is it using new PLAN_HASH_VALUE. Please note if new plan is not using, then flush the HASH VALUE to ensure the new plan is used, then have the apps team restart the process.
select name from dba_sql_profiles;
4

Section 4

Step 5: Flushing a Single SQL Statement from Library Cache (Only if required) Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'dkz7v96ym42c6';

ADDRESS          HASH_VALUE
---------------- ----------
00000000A2D6DEE8 3174173062

SQL> exec DBMS_SHARED_POOL.PURGE ('00000000A2D6DEE8, 3174173062', 'C');

PL/SQL procedure successfully completed.

SQL>

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like 'dkz7v96ym42c6';

Comments (0)

Please to add comments

No comments yet. Be the first to comment!