Thursday, February 11, 2010

Port/Move Sql Profiles in 10gR1

Today I was working on a migration project, where I had to port some sql profiles from 10gR1 to 10gR2 which was really critical to the performance of the application. Metalink has documentation to move the profiles from 10gR2 to 10gR2, but nothing for my requirement 10gR1.

Wihhout any documented information finally I was able to find some undocumented information.Thanks to Christian Antognini's papers which helped me to workaround my issue.Here is what I did to port the sql profiles:

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 => sqlprof_attr('FIRST_ROWS(1)','OPTIMIZER_FEATURES_ENABLE(default)'),replace => 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.

Note
-----
- SQL profile "SYS_SQLPROF_091112150758738" used for this statement


Note: You can follow metalink document ID 457531.1 for 10gR2 : [How To Move SQL Profiles From One Database To Another Database ] for 10gR2

For people who do not have access to metalink below are the steps for migrating the profiles in 10Gr2:

In the source database :

1. Create a staging table for the profiles

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'PROF_BR',schema_name=>'SYS');

2.Pack the profiles to the staging table

EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090806080427252');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_081029060743314');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090806054340984');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090812082621064');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_090819052716720');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_091112150758738');
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'PROF_BR',profile_name=>'SYS_SQLPROF_091206044219682');


3.Export the table

expdp directory=POC1 dumpfile=PROF_BR.dmp logfile=PROF_BR.log JOB_NAME=EXP_PROF_BR tables=SYS.PROF_BR


In the destination database :

4. Import the staging table

impdp directory=POC1 dumpfile=PROF_BR.dmp logfile=PROF_BR.imp JOB_NAME=IMP_PROF_BR tables=SYS.PROF_BR


5.Unpack the profiles

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(replace => TRUE,staging_table_name => 'PROF_BR');

6. Test the queries plan to see the usage of the sql profile.

No comments: