Encouraging CBO to Pickup a Better Execution Plan Using the COE XFR SQL Profile Script SQL TUNING (Doc ID 1955195.1)

 

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.2.0.2 and later
Information in this document applies to any platform.

GOAL

This Document helps in situations where a plan may have changed for some reason, but a good plan is still available and reproducible.
 

SOLUTION

Under certain circumstances the plan for an sql may unexpectedly change to a poorer plan (for example , when different binds are peeked at hard parse). If the good plan still exists in memory, it is possible to force the query to use the good execution plan by creating a SPM baseline.  If a SPM baseline is not usable, it is also possible to force a specific plan through a SQL profile created using the coe_xfr_sql_profile.sql script included in the SQLT Tool. Of course a rule of thumb to identify a good plan is to look at least CPU and/or elapsed time; unless you already know which plan suites you better on the long run!

For additional information on Baselines and Profiles, refer to:

Oracle Database Online Documentation 12c Release 1 (12.1)
Database Administration
Database SQL Tuning Guide
Chapter 23 Managing SQL Plan Baselines
23.1.1 Purpose of SQL Plan Management
23.1.1.2 Differences Between SQL Plan Baselines and SQL Profiles

http://docs.oracle.com/database/121/TGSQL/tgsql_spm.htm#TGSQL617

“SQL plan baselines reproduce a specific plan, whereas SQL profiles correct optimizer cost estimates.“

 

NOTE: In order to use SQL profiles an Oracle Tuning Pack licence is required. The Oracle Tuning Pack is available with Enterprise Edition ONLY.

For further details of pack licensing see:

Oracle Database Online Documentation 12c Release 1 (12.1)
Database Licensing Information

http://docs.oracle.com/database/121/DBLIC/editions.htm#DBLIC109

For information on SQLT see:

Document 215187.1 SQLT Diagnostic Tool

 

SQLT does not have to be installed in the database in order to use the "coe_xfr_sql_profile.sql" script. It can be run using the following steps:

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 (ignore the  'Do not use SYS' in script) providing the sql_id and the good Plan Hash Value (PHV) :
   
   
   SQL> START coe_xfr_sql_profile.sql <sql_id>  <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>');

NOTE: On RAC (Real Application Cluster) systems, the profile will be applicable on all instances.

  

REFERENCES

NOTE:215187.1 - All About the SQLT Diagnostic Tool