SPO coe_xfr_sql_profile.log;
SET DEF ^ ECHO ON TERM ON VER OFF SERVEROUT ON;
REM
REM $Header: 215187.1 coe_xfr_sql_profile.sql 11.2.9.7 2009/08/17 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_xfr_sql_profile.sql
REM
REM DESCRIPTION
REM   This script generates another that contains the commands to
REM   create a manual custom SQL Profile out of a known plan from
REM   memory or AWR. The custom manual profile can be implemented
REM   into the same system where the plan was retrieved, or into
REM   another similar system that has same schema objects referenced
REM   by the SQL that generated the known plan. In other words,
REM   this script allows you to extract and transfer a desired plan
REM   from a source system into a similar target system.
REM
REM PRE-REQUISITES
REM   1. Execute this script connected as SYSDBA.
REM
REM PARAMETERS
REM   1. SQL_ID for SQL in source system (required)
REM   2. PLAN_HASH_VALUE for which a manual custom SQL Profile is
REM      needed (required). A list of known plan ids is presented to
REM      the user executing this script.
REM
REM EXECUTION
REM   1. Navigate to directory where this script resides.
REM   2. Start SQL*Plus connecting as SYSDBA.
REM   3. Execute script coe_xfr_sql_profile.sql passing SQL_ID and
REM      PLAN_HASH_VALUE (parameters can be passed inline or until
REM      requested)
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus system/[pwd]
REM   SQL> start coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
REM   SQL> start coe_xfr_sql_profile.sql 5pb8pd95rynrs 1464560987;
REM   SQL> start coe_xfr_sql_profile.sql;
REM
REM NOTES
REM   1. For possible errors see coe_xfr_sql_profile.log
REM   2. Use only on 10g and 11g
REM   3. Be aware that using DBMS_SQLTUNE requires a license for the
REM      Oracle Tuning Pack
REM
SET ECHO OFF;
PRO
PRO Enter parameter 1: SQL_ID
PRO
DEF sql_id = '^1';
PRO
SELECT plan_hash_value
  FROM gv$sql_plan
 WHERE sql_id = '^^sql_id'
   AND other_xml IS NOT NULL
 UNION
SELECT plan_hash_value
  FROM dba_hist_sql_plan
 WHERE sql_id = '^^sql_id'
   AND other_xml IS NOT NULL;
PRO
PRO Enter parameter 2: PLAN_HASH_VALUE
PRO
DEF plan_hash_value = '^2';
PRO
PRO Values passed to coe_xfr_sql_profile.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SQL_ID:          ^^sql_id
PRO PLAN_HASH_VALUE: ^^plan_hash_value
PRO
SET TERM OFF ECHO ON;

VAR other_xml CLOB;
VAR sql_text CLOB;
VAR hints_count NUMBER;
EXEC :other_xml := NULL; :sql_text := NULL; :hints_count := 0;

-- get other_xml from memory
BEGIN
  IF :other_xml IS NULL THEN
    SELECT other_xml
      INTO :other_xml
      FROM gv$sql_plan
     WHERE sql_id = '^^sql_id'
       AND plan_hash_value = TO_NUMBER('^^plan_hash_value')
       AND other_xml IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :other_xml := NULL;
END;
/

-- get other_xml from awr
BEGIN
  IF :other_xml IS NULL THEN
    SELECT other_xml
      INTO :other_xml
      FROM dba_hist_sql_plan
     WHERE sql_id = '^^sql_id'
       AND plan_hash_value = TO_NUMBER('^^plan_hash_value')
       AND other_xml IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :other_xml := NULL;
END;
/

-- get sql_text from memory
BEGIN
  IF :sql_text IS NULL THEN
    SELECT REPLACE(sql_fulltext, CHR(00), ' ')
      INTO :sql_text
      FROM gv$sqlarea
     WHERE sql_id = '^^sql_id'
       AND sql_fulltext IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :sql_text := NULL;
END;
/

-- get sql_text from awr
BEGIN
  IF :sql_text IS NULL THEN
    SELECT REPLACE(sql_text, CHR(00), ' ')
      INTO :sql_text
      FROM dba_hist_sqltext
     WHERE sql_id = '^^sql_id'
       AND sql_text IS NOT NULL
       AND ROWNUM = 1;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :sql_text := NULL;
END;
/

-- counts hints in other_xml
BEGIN
  IF :other_xml IS NOT NULL THEN
    SELECT COUNT(*)
      INTO :hints_count
      FROM XMLTABLE('/other_xml/outline_data/hint' PASSING XMLTYPE(:other_xml)) hint;
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :hints_count := 0;
END;
/

PRINT sql_text;
PRINT other_xml;
PRINT hints_count;

-- generates script that creates sql profile in target system:
SET ECHO OFF;
PRO coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql.
SET FEED OFF LIN 2000 TRIMS ON TIM OFF SERVEROUT ON FOR WOR;
SPO OFF;
SPO coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql;
DECLARE
  my_count2 INTEGER;
BEGIN
  IF :hints_count > 0 AND :sql_text IS NOT NULL THEN
    DBMS_OUTPUT.PUT_LINE('SPO coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..log;');
    DBMS_OUTPUT.PUT_LINE('SET DEF ~ ECHO ON TERM ON SERVEROUT ON;');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM $Header: 215187.1 coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql 11.2.9.6 '||TO_CHAR(SYSDATE, 'YYYY/MM/DD')||' csierra $');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM SCRIPT');
    DBMS_OUTPUT.PUT_LINE('REM   coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM DESCRIPTION');
    DBMS_OUTPUT.PUT_LINE('REM   This script was generated by coe_xfr_sql_profile.sql and it');
    DBMS_OUTPUT.PUT_LINE('REM   contains the commands to create a manual custom SQL Profile');
    DBMS_OUTPUT.PUT_LINE('REM   based on SQL_ID ^^sql_id. AND PLAN_HASH_VALUE ^^plan_hash_value..');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM PARAMETERS');
    DBMS_OUTPUT.PUT_LINE('REM   None.');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM EXAMPLE');
    DBMS_OUTPUT.PUT_LINE('REM   SQL> start coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql;');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('REM NOTES');
    DBMS_OUTPUT.PUT_LINE('REM   1. SOURCE and TARGET systems can be the same or different.');
    DBMS_OUTPUT.PUT_LINE('REM   2. Should be run as SYSTEM, SYSDBA or SQLTXPLAIN. User must have');
    DBMS_OUTPUT.PUT_LINE('REM      CREATE ANY SQL PROFILE privilege.');
    DBMS_OUTPUT.PUT_LINE('REM   3. If you later need to drop this manual SQL Profile:');
    DBMS_OUTPUT.PUT_LINE('REM      SQL> EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(''COE_^^sql_id._^^plan_hash_value.'');');
    DBMS_OUTPUT.PUT_LINE('REM');
    DBMS_OUTPUT.PUT_LINE('SET TERM OFF;');
    DBMS_OUTPUT.PUT_LINE('DECLARE');
    DBMS_OUTPUT.PUT_LINE('sql_txt VARCHAR2(32767);');
    DBMS_OUTPUT.PUT_LINE('h       SYS.SQLPROF_ATTR;');
    DBMS_OUTPUT.PUT_LINE('BEGIN');
    DBMS_OUTPUT.PUT_LINE('sql_txt := q''['||:sql_text||']'';');
    my_count2 := 0;
    FOR i IN (SELECT ROWNUM line, EXTRACTVALUE(VALUE(hint), '/hint') hint
                FROM XMLTABLE('/other_xml/outline_data/hint' PASSING XMLTYPE(:other_xml)) hint)
    LOOP
      my_count2 := my_count2 + 1;
      IF my_count2 = 1 THEN
        DBMS_OUTPUT.PUT_LINE('h := SYS.SQLPROF_ATTR(');
      END IF;
      IF my_count2 = :hints_count THEN
        DBMS_OUTPUT.PUT_LINE('q''['||i.hint||']''');
        DBMS_OUTPUT.PUT_LINE(');');
      ELSIF my_count2 < :hints_count THEN
        DBMS_OUTPUT.PUT_LINE('q''['||i.hint||']'',');
      END IF;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('DBMS_SQLTUNE.IMPORT_SQL_PROFILE (');
    DBMS_OUTPUT.PUT_LINE('sql_text    => sql_txt,');
    DBMS_OUTPUT.PUT_LINE('profile     => h,');
    DBMS_OUTPUT.PUT_LINE('name        => ''COE_^^sql_id._^^plan_hash_value.'',');
    DBMS_OUTPUT.PUT_LINE('description => ''COE sql_id:^^sql_id. phv:^^plan_hash_value.'',');
    DBMS_OUTPUT.PUT_LINE('category    => ''DEFAULT'',');
    DBMS_OUTPUT.PUT_LINE('validate    => TRUE,');
    DBMS_OUTPUT.PUT_LINE('replace     => TRUE,');
    DBMS_OUTPUT.PUT_LINE('force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );');
    DBMS_OUTPUT.PUT_LINE('END;');
    DBMS_OUTPUT.PUT_LINE('/');
    DBMS_OUTPUT.PUT_LINE('SET TERM ON ECHO OFF;');
    DBMS_OUTPUT.PUT_LINE('PRO');
    DBMS_OUTPUT.PUT_LINE('PRO Plan ^^plan_hash_value has been linked to SQL ^^sql_id..');
    DBMS_OUTPUT.PUT_LINE('PRO Review log coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..log.');
    DBMS_OUTPUT.PUT_LINE('PRO');
    DBMS_OUTPUT.PUT_LINE('SET DEF ON;');
    DBMS_OUTPUT.PUT_LINE('SPO OFF;');
  END IF;
END;
/
SPO OFF;
SET TERM ON ECHO OFF;
PRO
PRO Execute coe_xfr_sql_profile_^^sql_id._^^plan_hash_value..sql
PRO on target system in order to transfer plan ^^plan_hash_value
PRO for sql ^^sql_id..
PRO
SET DEF ON;
