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)
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]
Step 2: Run COE_XFR_SQL_PROFILE
[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>
Step 3: Create Custome SQL Profile
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 carlos.sierra@oracle.com 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>
Step 4: Verif is it using SQL_PROFILE
-- 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;
Step 5: Flushing a Single SQL Statement from Library Cache (Only if required)
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';
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.
Thank you,
Rajasekhar Amudala