My Oracle Support Banner

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 AUGUST 17, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

Goal

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

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

To view full details, 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 a vibrant support community of peers and Oracle experts.