COpying sql profile from test environment to live environment


1)      First step is to capture the sql (and its plan) from the ‘working’ environment – you need to find the sql_id and  plan_hash_value for the sql and pass then into the  plsql below (you get these from v$sqlarea). There seems like there should be a simple bit of code to do this but not that we could find – at least in 10g.

DECLARE
cl_sql_text CLOB;
hint_spec sys.sqlprof_attr;
BEGIN
SELECT sql_fulltext INTO cl_sql_text FROM gv$sqlarea WHERE sql_id='dkp19rcc2w9yc';
SELECT
extractvalue(VALUE(d), '/hint') AS outline_hints
BULK COLLECT
INTO
hint_spec
FROM
xmltable('/*/outline_data/hint'
passing (
SELECT
xmltype(other_xml) AS xmlval
FROM
dba_hist_sql_plan
WHERE
sql_id = 'dkp19rcc2w9yc'
AND plan_hash_value = '3807542445'
AND other_xml IS NOT NULL
)
) d;

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
sql_text => cl_sql_text,
profile => hint_spec,
name => 'PROFILE NAME',
force_match => TRUE);

END;

/

2)  Create a staging table to dump the profile into for transfer out

BEGIN
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (
table_name => 'SQL_PROFILES',
schema_name=>'TP_INT_DEMO');
END;
/

3)  Extract the profiles you want into this staging table – the filter below just gets everything

BEGIN
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (
profile_category => '%',
staging_table_name => 'SQL_PROFILES',
staging_schema_owner=>'TP_INT_DEMO');
END;

4)  Datapump out and copy to other server then datapump back in
5)  Extract the profile from the newly imported staging table and make it active

BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(
staging_table_name => 'SQL_PROFILES',
staging_schema_owner=>'TP_PROD',
replace=>FALSE);
END;

Done…

Comments