Problem
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.
Solution
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)
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 https://support.oracle.com/epmos/main/downloadattachmentprocessor?parent=DOCUMENT&sourceId=215187.1&attachid=215187.1:SQLT_TOOL_12111&clickstream=yes
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.
Usage:
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:
coe_xfr_sql_profile_ap11rjm5q6c2w_627713330.sql
coe_xfr_sql_profile_ap11rjm5q6c2w_627713330.log
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;
SQL_ID SQL_PROFILE
------------- ----------------------------
ap11rjm5q6c2w coe_ap11rjm5q6c2w_627713330
Reference:
No comments:
Post a Comment