Directing Plans with Baselines/Profiles Using coe_load_sql_baseline.sql / coe_load_sql_profile.sql (shipped with SQLT) (Doc ID 1400903.1)

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.

Goal

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.

NOTE: Care should be taken to ensure that the 2 queries are syntactically identical in order to obtain the desired result. If for example the aliases used or predicates in the query are different,   the optimizer may not be able to follow the directives. In this case, the profile may be ignored by the optimizer or a different non-optimal plan obtained.

coe_load_sql_baseline.sql / coe_load_sql_profile.sql are scripts provided with the SQLT tool in the "utl" folder.

NOTE: Licensing considerations:

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:

<Document: 215187.1> SQLT (SQLTXPLAIN) - Tool That Helps To Diagnose SQL Statements Performing Poorly

 

Solution

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