Migrating Oracle SQL Profiles
I am moving my database and I want to be able to export and migrate
my SQL profiles. How do your
export/import migration of SQL profiles.
SQL Profiles can be exported and imported just like stored outlines,
but with a different procedure. There
is a different procedure for migrating SQL profiles in Oracle 10g release
one, and the migration procedure changes in Oracle 10g release two.
Migrating SQL Profiles in Oracle 10 release 1
Prior to Oracle 10g release 2, you can migrate SQL profiles with the
procedure. Incidentally, the
import_sql_profile procedure is used to swap SQL profiles so that you can
execution plans without touching the SQL source code.
Balaji R offers these steps to migrate SQL profiles in Oracle 10g
SQL Profile consists of auxiliary statistics specific to that statement and
are stored as profile attributes in the data dictionary, which can be
retrieved from the two tables SQLPROF$ and SQLPROF$ATTR.
In the source I have a profile called 'SYS_SQLPROF_091112150758738',
which can be retrieved as below:
select sp.sp_name, sa.attr#, sa.attr_val
from SQLPROF$ sp, SQLPROF$ATTR sa
where sp.signature = sa.signature
and sp.category = sp.category
and sp.sp_name = 'SYS_SQLPROF_091112150758738'
order by sp.sp_name, sa.attr#
SP_NAME ATTR# ATTR_VAL
SYS_SQLPROF_091112150758738 1 FIRST_ROWS(1)
SYS_SQLPROF_091112150758738 2 OPTIMIZER_FEATURES_ENABLE(default)
Now I have the attributes from the source, which can be imported as a
profile into the destination using the import_sql_profile procedure of the
dbms_sqltune package as below:
exec dbms_sqltune.import_sql_profile(name => 'SYS_SQLPROF_091112150758738',description
=> 'SQL profile created for porting the profile from 10gR1',category => 'DEFAULT',sql_text
=> 'select XXXXXXXXX from XXXXXXX where XXXXXXXXX and XXXXXXXXXXX',profile
=> FALSE,force_match => FALSE);
After the import I wanted to make sure of two things :
1. The profile is used in the statement.
2. I have the optimal plan as I had in the source DB.
Both can be checked with the explain plan as below:
explain plan for
select XXXXXXXXX from XXXXXXX where XXXXXXXXX and XXXXXXXXXXX;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
I had a similar plan and also could see the below in the explain plan which
says the profile is used. I am
not sure if this is supported but did work for me.
Migrating SQL Profiles in Oracle 10 release 2 and beyond
10g release 2 and beyond, the MOSC document ID 457531.1 provides complete
steps for migrating SQL profiles.
The documentation notes that you export SQL profiles with the
dbms_sqltune package, using these
Import/Export SQL Tuning Sets and SQL Profiles
You use DBMS_SQLTUNE subprograms to move SQL Profiles and SQL Tuning Sets
from one system to another using a common programmatic model. In both cases,
you create a staging table on the source system and populate that staging
table with the relevant data.
You then move that staging table to the destination system following the
method of your choice (such as datapump, import/export, or database link),
where it is used to reconstitute the objects in their original form.
In a nutshell, here are the steps to migrate SQL profiles in 10gr2 and
create_stgtab_sqlprof = Create a SQL Profile storage table
pack_stgtab_sqlprof = Populate the storage table with SQL
Step 3: expdp/impdb = Next, you
migrate the table anywhere you like, using standard Oracle utilities or CTAS
over a database link.
unpack_stgtab_sqlset = Unpacks the SQL profiles on the
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.