Thursday, November 6, 2014

Oracle Database 10g R2 -- Using SQL Profiles for Plan Stability


Your query uses suboptimal execution plan which causes performance degradation and/or generates system errors.

You have a better execution plan and want to bind the optimal plan to the query.


On Oracle 10g you have different options to bind an execution plan to a query. If using hints against the source code is not an option, then the Stored Outlines or SQL Profiles features can be used.

SQL Profiles is preferable than Stored Outlines because managing Stored Outlines can become cumbersome and sometimes Stored Outlines do not work as expected.

There are two types of SQL Profiles:
  • One that consists of auxiliary information, "hints" as scaling factors which allows the optimize to generate a new plan if conditions change, like more data. This type is generated by SQL Tuning Advisor
  • Another type of SQL Profiles "fixes" the plan and produces exactly the same time (like old Stored Outlines feature)
SQLTXPLAIN, also known as SQLT, is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE.

SQLT provides the script coe_xfr_sql_profile.sql which helps to easily create a SQL Profile against a statement.

First of all download SQLT from

Once SQLT has been downloaded unzip the archive into a working directory of your choice. coe_xfr_sql_profile.sql script is located under the utl/ subdirectory

To use the script you need to know the SQL_ID of your statement and PLAN_HASH_VALUE of the plan you want to bind to the statement. 


sqlplus / as sysdba

SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];

For example:

SQL> START coe_xfr_sql_profile.sql ap11rjm5q6c2w 627713330

This will generate a script and a log files in your current working directory:


Now execute this script (coe_xfr_sql_profile_ap11rjm5q6c2w_627713330.sql) to generate an SQL Profile which will instruct the optimizer to use the specified plan

SQL> select sql_id, sql_profile from V$SQL
  2  where sql_id='ap11rjm5q6c2w'
  3  and plan_hash_value=627713330;

------------- ----------------------------
ap11rjm5q6c2w coe_ap11rjm5q6c2w_627713330


No comments: