SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqltprofile.log;
REM
REM $Header: 215187.1 sqltprofile.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqltprofile.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.
REM   An execution of SQLT on the SOURCE system extracts and
REM   preserves all available plans for a given SQL (out of memory
REM   and/or AWR). Then, this script uses the hints associated to
REM   the preserved plans in order to build the custom profile.
REM
REM PRE-REQUISITES
REM   1. Execute SQLT (XTRACT or XECUTE) in SOURCE systems for the
REM      one SQL statement for which a good plan is known.
REM   2. The user that executes this script can be SQLTXPLAIN, the
REM      application user or SYSDBA
REM   3. If SOURCE is not easily available but its dmp file is, then
REM      import 2 tables before using this script:
REM      # imp sqltxplain/[pwd] tables='sqlt$_other_xml_hints' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_other_xml' file=sqlt.dmp ignore=y
REM
REM PARAMETERS
REM   1. Statement ID as per SQLT for SQL in source system (required)
REM      A list of statement ids is presented to the user executing
REM      this script.
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 sqlt/utl directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM      or SYSDBA
REM   3. Execute script sqltprofile.sql passing statement id and
REM      plan hash value (parameters can be passed inline or until
REM      requested)
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus sqltxplain/[pwd]
REM   SQL> start sqltprofile.sql [statement id] [plan hash value];
REM   SQL> start sqltprofile.sql s2263_olc510_apperf02 923669362;
REM   SQL> start sqltprofile.sql s2263 923669362;
REM   SQL> start sqltprofile.sql;
REM
REM NOTES
REM   1. For possible errors see sqltprofile.log
REM   2. Use only on 10g and 11g
REM   3. If SQLT is not installed, use
REM      sqlt/utl/coe_xfr_sql_profile.sql instead.
REM
SET ECHO OFF FEED OFF HEA ON LIN 200 PAGES 100 TRIMS ON TIM OFF;
PRO
SELECT DISTINCT statid,
       SUBSTR(REPLACE(TRIM(sql_text), CHR(10), ' '), 1, 50) sql_text
  FROM sqltxplain.sqlt$_other_xml
 ORDER BY 1, 2;
PRO
PRO Enter partial or complete statid (required)
PRO
DEF statid = '&1';
PRO
SELECT plan_hash_value, cost
  FROM sqltxplain.sqlt$_other_xml
 WHERE UPPER(statid) LIKE UPPER('%'||TRIM('&&statid.')||'%')
 ORDER BY 1;
PRO
PRO Enter plan hash value (required)
PRO
DEF phv = '&2';
PRO
PRO Values passed to sqltprofile.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO STATID:          &&statid
PRO PLAN HASH VALUE: &&phv
PRO
SET TERM OFF HEA ON LIN 2000 PAGES 1000 TRIMS ON TIM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PRO
PRO NOTE:
PRO If you get an ORA-06550 followed by PLS-00302: component 'INITIALIZATION' must be declared,
PRO then review *.lis files created during installation and fix errors reported.
PRO
EXEC sqltxplain.sqlt$r.initialization;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') current_time FROM DUAL;
SELECT * FROM v$version;
COL library FOR A64 HEA 'Libraries';
SELECT object_type||' '||object_name||' ('||status||')' library
  FROM all_objects
 WHERE owner = 'SQLTXPLAIN'
   AND object_type IN ('PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
 ORDER BY
       object_type, object_name;
COL tool_version FOR A16 HEA 'Tool Version';
SELECT sqltxplain.sqlt$d.get_param('tool_version', 'I') tool_version FROM DUAL;
COL install_date FOR A16 HEA 'Install Date';
SELECT sqltxplain.sqlt$d.get_param('install_date', 'I') install_date FROM DUAL;
COL host_name FOR A128 HEA 'Host Name';
SELECT sqltxplain.sqlt$d.get_param('host_name_short', 'I') host_name FROM DUAL;
COL column_value FOR A128 HEA 'Directories';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.directories);
COL column_value FOR A128 HEA 'Libraries';
SELECT column_value FROM TABLE(sqltxplain.sqlt$d.packages);
EXEC sqltxplain.sqlt$d.validate_tool_version('11.2.9.2');
SET TERM ON;
PRO
PRO ... generating manual SQL Profile script for &&statid and &&phv
PRO
SET TERM OFF;
VAR v_output_filename VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$r.gen_manual_sql_profile_script (
    p_statid          => '&&statid',
    p_plan_hash_value => '&&phv',
    x_output_filename => :v_output_filename );
END;
/
COL server_directory NOPRI NEW_V server_directory FOR A512;
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
SELECT :v_output_filename copy_file_name, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... &&copy_file_name. file has been created into server directory:
PRO ... &&server_directory
PRO ... copying now generated file into local directory
PRO
SET TERM OFF ECHO OFF DEF ON FEED OFF FLU OFF HEA OFF LIN 2000 NEWP NONE PAGES 0 SHOW OFF SQLC MIX TAB OFF TRIMS ON VER OFF TIM OFF ARRAY 100 SQLP SQL> BLO . RECSEP OFF SERVEROUT ON SIZE 1000000 FOR TRU;
COL column_value FOR A2000;
WHENEVER SQLERROR CONTINUE;
SPO OFF;
SPO &&copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('SQLPROF', NULL, :v_output_filename));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SET TERM ON;
CL COL;
UNDEFINE 1 2 statid phv server_directory copy_file_name;
PRO SQLTPROFILE completed.
