SQL Profiles – The Secret That Only Few DBAs Are Aware Of – Part 3

What if I told you there is a way to move SQL Profiles between databases zooming fast, without using the export/import method, would you believe me?

Not many DBAs are aware of this method, and the reason is either they don’t work much with SQL Profiles, or they just don’t know this method even exists.

I had an a-ha moment, when I found out about this, and I am using this method ever since!

Starting now, you are part of the few DBAs that know this secret!!! Consider yourself fortunate!

What is all this buzz about?

Have you heard of a tool named SQLT (SQLTXPLAIN)?
This is a free tool provided by Oracle, more precisely by Oracle Server Technologies Center Of Expertise, to help you diagnose poorly performing SQL Statements.

This post will not cover the SQLT tool. That could be the subject of another article!

What I am going to talk about, is a script within the SQLT tool, that you can use to move SQL Profiles between databases very easily!
The script is called: coe_xfr_sql_profile.sql.

We can be thankful and grateful for the wonderful mind of Carlos Sierra, who is the mastermind behind this script.

This script is brilliant. It generates another script with commands to create a manual custom SQL Profile out of a known execution plan from either memory or AWR.
You can use this method, as long as your SQL Statement can be found in the shared pool or AWR.

Let me walk you through the steps. Before we begin, as a pre-requisite: You need to have Oracle Tuning Pack License.

Step 1 – Download SQLT from Oracle Support
Step 2 – Identify the SQL ID, and Plan Hash Value (PHV)
Step 3 – Extract The SQL Profile
Step 4 – Create SQL Profile Anywhere You Need

Step 1 – Download SQLT from Oracle Support.

If you connect to Oracle Support, look up the article: All About the SQLT Diagnostic Tool (Doc ID 215187.1). There is a link within the article, to download SQLT tool, to your desktop.
Unzip the file on your desktop, and go to sqlt>utl. The script coe_xfr_sql_profile.sql, can be found there.

In order to use this script, you DO NOT NEED to have SQLT installed. You can just add this script to your collection of scripts, and use it as needed.

Step 2 – Identify the SQL ID, and Plan Hash Value (PHV).

In order to extract the SQL Profile for a specific SQL statement, you need to determine the SQL ID and Plan Hash Value, for that statement.
There are many ways to get this information, from memory, from AWR, from Enterprise Manager and so on.

Step 3 – Extract The SQL Profile

Connect to the database as a user who has access to the data dictionary, and is not SYS.
Run coe_xfr_sql_profile.sql. The script will prompt you for SQL_ID, and if found in memory or AWR, it will provide a list of available Plan Hash Values. Select the Plan Hash Value that you want/need.
Then automagically another script is created that can be run anywhere else to create a SQL Profile. The beauty of it is that the new script will have the SQL ID, as part of its name.
If you run this script multiple times, for different SQL IDs, the output will not be overwritten.

sqlplus system/***

SQL>@coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 3ka73fwkff7tz


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      532210296      59.9

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 532210296

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "3ka73fwkff7tz"
PLAN_HASH_VALUE: "532210296"

...
...
...

SQL>-- generates script that creates sql profile in target system:
SQL>SET ECHO OFF;
coe_xfr_sql_profile_3ka73fwkff7tz_532210296.sql.

Step 4 – Create SQL Profile Anywhere You Need

Connect to the target database as system or sysdba, and run the script that was generated in Step 3. Please note, you can edit the script, to change the FORCE MATCHING SIGNATURE, if you want.

sqlplus / as sysdba
@coe_xfr_sql_profile_3ka73fwkff7tz_532210296.sql

This is it! It is that simple!
This is how you move SQL Profiles from one database to another!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana