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
Post a Comment