fix bad query plan using sql profile created by coe_xfr_sql_profile.sql

After database upgrade from 10g to 12c, some of queries have bad performance, elapsed time are changed from less than 1 second to a few minutes, the immediately fix is to force the application to use 10g optimizer by either change database parameter or use database logon trigger to force specific application to choose the 10g optimizer. I used the database trigger.

CREATE OR REPLACE TRIGGER sys.triggername

   AFTER LOGON ON DATABASE WHEN (upper(sys_context('USERENV','MODULE')) like '%APP_UPPERCASE%' or upper(sys_context('USERENV','MODULE')) like '%APP_UPPERCASE%')

BEGIN

/* some functions of application running slow after db upgrade from 10g to 12c, this trigger helps to force them to use 10g optimizer and get their normal performance*/

   EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_features_enable=''10.2.0.5''';

END triggername;

  /

I engaged oracle support on troubleshooting this, sent MOS the 10053 trace, but MOS did not analyze it, instead, MOS suggest to use the sql_profile to fix problem on individual query.

This is possible solution because the good query plan resides in RAM or AWR because 10g optimizer has been turned on.

Correcting Optimizer Cost Estimates to Encourage Good Execution Plans Using the COE XFR SQL Profile Script (Doc ID 1955195.1)

To Bottom

They key is to run this:

SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>

1. Unzip sqlt.zip and navigate to the sqlt directory
2. Navigate to the subdirectory utl
3. Find the SQL_ID and the Plan Hash Value (PHV) for the plan you want to base the profile on. You can get the SQL_ID from SQLT in the "SQL Identification" section and the PHV from the "Execution Plans" section:
   
   
   
   The SQL_ID is as follows:
   
   
   
   The PHV is shown against the plans in the "Execution Plans" section:
   
4. Run the script coe_xfr_sql_profile.sql as SYSDBA user providing the sql_id and the good Plan Hash Value (PHV) :
   
   
   SQL> START coe_xfr_sql_profile.sql <sql_id> or <plan hash value for good plan>
   Example:
   
   SQL> START coe_xfr_sql_profile.sql 2qknbzqt0aoxb 365331166
   
   where "2qknbzqt0aoxb" is the SQL ID for the problem query and "365331166"  is the PHV (plan hash value) for the good plan
5. Step 4 generates a script named in the format: "coe_xfr_sql_profile_SQL_ID_PLAN_HASH_VALUE.sql" i.e. including the sql_id and plan hash value.
   
   For example, with "2qknbzqt0aoxb" as the SQL ID and "365331166"  as the PHV (as above)  it will generate a script named: "coe_xfr_sql_profile_2qknbzqt0aoxb_365331166.sql"
6. If the query uses literals, but you would like to use the profile for all literals, you can modify the force_match parameter in the generated script from false to true:
   
   
   force_match => TRUE
   
   This will ensure the profile will be enabled even when different literals are used in the SQL query.
7. Run the generated script as SYSDBA user in order to correct the optimizer cost estimates used in the execution plan for the sql_id and encourage it to use the desired plan obtained from the stated plan hash value.
8. You can verify that the new execution plan is being used as followed:
   
   
   select SQL_ID, SQL_PROFILE,PLAN_HASH_VALUE from V$SQL where SQL_ID='2qknbzqt0aoxb';
   
    
9. The profile can be disabled or dropped as follows using the the SQL_PROFILE name returned from the query above:
   
   To disable the profile:
   
   
   EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(Name => '<SQL PROFILE>', Attribute_Name => 'STATUS', Value => 'DISABLED');
   
   To drop the profile:
   
   
   EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(Name => '<SQL PROFILE>');

coe_xfr_sql_profile.sql code can be found here:

https://github.com/carlos-sierra/cscripts/blob/master/sql/spm/coe_xfr_sql_profile.sql

The other article talks about how to use the query plan generated by hint.

http://www.bobbydurrettdba.com/2014/03/19/using-hints-with-coe_xfr_sql_profile-sql/