Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT)
Last updated on OCTOBER 25, 2016
Applies to:Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Information in this document applies to any platform.
This document explains the steps necessary to use coe_load_sql_baseline.sql / coe_load_sql_profile.sql to apply a custom SQL Baseline (in 11g and higher) or a custom SQL Profile (in 10g and higher).
One application of this is to prompt the optimizer to use a plan that could only be achieved through adding hints. For example: When you cannot modify the original SQL for whatever reason and you would like to "capture" the plan from a modified version of your SQL (the one with CBO Hints) and associate this "good" plan to the original un-modified SQL.
coe_load_sql_baseline.sql / coe_load_sql_profile.sql are scripts provided with the SQLT tool in the "utl" folder.
Both the coe_load_sql_baseline.sql and coe_load_sql_profile.sql scripts may require licenses depending on how they are used.
The coe_load_sql_profile.sql script calls DBMS_SQLTUNE (which is a Licensed Command-Line API) requiring the Tuning Pack license (for which Oracle Diagnostics Pack is a prerequisite).
If the coe_load_sql_baseline.sql script finds the required plans in memory then it does not require any license. However, if it retrieves plans from the Automatic Workload Repository (AWR), a Diagnostic Pack license would be required.
SQLT is a tool provided by Oracle Server Technologies Center of Expertise - ST CoE. SQLT inputs one SQL statement and outputs a set of diagnostics files that are commonly used to diagnose SQL statements performing poorly.You can download SQLT from:
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms