4 Steps To Move SQL Profiles Between Databases – Part 2

Welcome back for another week on SQL Profiles. If you don’t feel comfortable around SQL Profiles, or you just need a refresher on SQL Profiles, check out last week’s post 3 Basic Things DBAs Need To Know About SQL Profiles – Part 1.

You have upgraded your database, or you have moved a specific schema from one database to another, and all of a sudden SQL queries started running slow.
You spent time investigating, and it turns out, the execution plans for some of the queries have changed.
You realize the SQL Profiles that exist in the old environment, are totally MIA (missing in action). The executions plans have changed due to the missing profiles in the new environment.

The big question now is:

What do you do?

First, you panic.

Second, you wish SQL Profiles were never invented, then you wouldn’t have this problem.

Third, you wish the person who created these SQL Profiles was there, so this could be his problem, not yours.

Fourth, you wish you knew more about SQL Profiles, and how to move them quickly between databases, so you can come clean and look like a hero!

What if I share with you the method of moving profiles from one database to another, so when you are faced with a problem like this, instead of panicking and going through these negative emotions, you can jump in and quickly implement the solution.

What if I share with you a mind map, that explains in detail how SQL Profiles can be moved between databases, so finally, you will understand the process, and you won’t be afraid anymore?

Wouldn’t that be awesome?

Guess what? That is what I’ll do today!

Disclaimer: SQL Profiles require Diagnostic and Tuning Pack License.

Step 1 – Creating A Staging Table
Step 2 – Copy SQL Profiles Into The Staging Table
Step 3 – Move Staging Table Between Databases
Step 4 – Copy SQL Profiles From The Staging Table Into Data Dictionary
Putting It All Together

Step 1 – Creating A Staging Table

This is not a random table you create. This is a table that needs to be created with the dbms_sqltune package: dbms_sqltune.create_stgtab_sqlprof.

SQL> EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name => 'SQL_PROF_STGTAB');

This table gets created in the schema you are logged in. You could also provide the owner for the table.

Step 2 – Copy SQL Profiles Into The Staging Table

This step is also called Packing the SQL Profiles into the staging tables. The SQL Profiles are copied from the data dictionary into the staging table, in order to be ready for transport.

SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF 
          (staging_table_name => 'SQL_PROF_STGTAB',profile_name=>'SYS_SQLPROF1');
SQL> EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF 
          (staging_table_name => 'SQL_PROF_STGTAB',profile_name=>'SYS_SQLPROF2');

As you can see above, you can use the same table for multiple profiles. You need to invoke the same statement for each profile you need to transport.
You can get a list of the profiles you want to move, from DBA_SQL_PROFILES.

SELECT name FROM dba_sql_profiles where name like 'SYS_%';

Step 3 – Move Staging Table Between Databases

It is up to you what method you want to use. Is the old export/import still your friend? Then use that, to export the staging table from the source database, and import it into your new database.
If you prefer export datapump/import datapump, then use that method. It really doesn’t matter. It is only one table to be moved across databases.

Step 4 – Copy SQL Profiles From The Staging Table Into Data Dictionary

This step is also called Unpacking the SQL Profiles from the staging table, into the data dictionary. What happens now? Oracle copies the definition of the profiles, all those hints, into the data dictionary, and makes them available to you in the new environment.

SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROF_STGTAB');

Putting It All Together

No more fears of SQL Profiles! Now you understand how it works, and what you need to do! To better remember this all, here’s a mind map for you! Print it, use it!

Stay tuned for even more SQL Profiles AWESOMENESS next week!

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