Tales From A Lazy Fat DBA

Hi All,

Have you used coe_load_sql_profile.sql before? I mean a lot of people uses coe_xfr_sql_profile.sql from SQLT and these two scripts deals with SQL profiles in Oracle, but their purposes and use cases differ. coe_xfr_sql_profile.sql is used to export and migrate an existing SQL Profile from one system to another, ensuring performance stability across environments. coe_load_sql_profile.sql is used to create a new SQL Profile by capturing the execution plan from a modified SQL query and applying it to the original query, forcing it to use the optimized plan.

Let me first explain a little bit more of the toolkit – Oracle SQLT (SQLTXPLAIN) which is a powerful tool designed to help DBAs analyze and troubleshoot SQL performance issues and all above mentioned scripts are part of the kit provided by Oracle and written by none other than Carlos Sierra.

A common question DBAs encounter is: Can we plug the execution plan of one SQL ID into another SQL ID? …. The answer is YES! This can be accomplished using the SQLT script coe_load_sql_profile.sql. In this blog, we will explore how to use this script to achieve plan stability by enforcing a preferred execution plan across different SQL IDs. It examines the memory and AWR both to look text of the SQL IDs you passed and then it queries GV$SQL_PLAN and DBA_HIST_SQL_PLAN to extract the execution plan hash value from the modified SQL. Once it’s done collecting that information, it performs a loop to extract optimizer hints of the modified SQL’s execution plan. Finally it creates a SQL Profile using DBMS_SQLTUNE.IMPORT_SQL_PROFILE.

Let’s give a quick demo … assume we have two SQL statements:

SQL ID 1: 78a1nbdabcba (Original SQL) …. SQL ID 2: 9na182nn2bnn (Modified SQL)
Both queries are logically similar but produce different execution plans.
Our goal is to take the execution plan from SQL ID 1 and apply it to SQL ID 2.

connect system/monkey123
SQL> @coe_load_sql_profile.sql 
or 
SQL> START coe_load_sql_profile.sql <ORIGINAL_SQL_ID> <MODIFIED_SQL_ID>


Parameter 1:
ORIGINAL_SQL_ID (required)

Enter value for 1: 78a1nbdabcba

Parameter 2:
MODIFIED_SQL_ID (required)

Enter value for 2: 9na182nn2bnn


     PLAN_HASH_VALUE          AVG_ET_SECS
-------------------- --------------------
          1181381381                 .003

Parameter 3:
PLAN_HASH_VALUE (required)

Enter value for 3: 1181381381

Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "78a1nbdabcba"
MODIFIED_SQL_ID: "9na182nn2bnn"
PLAN_HASH_VALUE: "1181381381"

.
.
.

ORIGINAL:78a1nbdabcba MODIFIED:9na182nn2bnn PHV:1181381381 SIGNATURE:16731003137917309319 CREATED BY COE_LOAD_SQL_PROFILE.SQL
SQL>SET ECHO OFF;

****************************************************************************
* Enter password to export staging table STGTAB_SQLPROF_78a1nbdabcba
****************************************************************************

Export: Release 19.0.0- Production on Sun Mar 08 14:45:47 2012

Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.

Password:
.
.
.

coe_load_sql_profile completed.



Run original query
SQL> select ename from DIXIT where ename='Name';

Plan hash value: 1181381381

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| 
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |     1 |     6 |     3   (0)|
|*  1 |  TABLE ACCESS FULL| DIXIT  |     1 |     6 |     3   (0)|
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME"='Name')

Note
-----
   - SQL profile "78a1nbdabcba_1181381381" used for this statement
   

What are your experiences with enforcing execution plans in Oracle?
Let me know in the comments!

Hope It Helped!
Prashant Dixit