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.

Linux high memory utilization

Today I was working on a project when the tester said the memory utilization was close to 100% always.First I was surprised as this was a pure DB box (RH 4) and has a single instance using only 50% of the memory for the Db.

As usual the first thing I did was to check the TOP/vmstat command, but this does not give the correct picture as Linux uses the spare memory available for caching the disk blocks (yes might be useful for a slow storage - extra cache) which is also accounted in the TOP command.


TOP:

Mem: 49433916k total, 47201932k used, 2231984k free, 903556k buffers
Swap: 70894792k total, 4k used, 70894788k free, 22948700k cached

VMSTAT:

vmstat 2 4
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 3 4 2250296 903688 22950204 0 0 1149 38 1 0 1 1 95 3 0
1 4 4 2249804 903688 22950204 0 0 39225 27 13506 35171 3 2 86 10 0
1 3 4 2250604 903688 22950204 0 0 42305 61 14293 35447 3 2 85 11 0
0 3 4 2250836 903688 22950204 0 0 29921 96 10224 25105 3 1 85 11 0



So in Linux we have to read the cached value carefully to calculate the actual memory used or the easier way is to cuse the free -m command as below.Here 22795 is the actual memory used and 25479 is the free memory.


free -m
total used free shared buffers cached
Mem: 48275 46090 2185 0 882 22412
-/+ buffers/cache: 22795 25479
Swap: 69233 0 69233