SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 SQLP SQL>;
SPO sqltxecute.log;
REM
REM $Header: 215187.1 sqltxecute.sql 11.2.1 2008/09/21 csierra $
REM
REM Copyright (c) 2008, Oracle Corporation.  All rights reserved.
REM
REM SCRIPT
REM   sqltxecute.sql
REM
REM DESCRIPTION
REM   This sqltxecute.sql takes as input the name of a SCRIPT file
REM   and proceeds to execute the one SQL contained in it, then it
REM   generates a set of comprehensive reports with query tuning
REM   diagnostic details.
REM
REM   The SCRIPT file whose name is provided as an inline execution
REM   parameter to sqltxecute.sql, is a custom script similar to
REM   example provided script1.txt. Besides the one SQL that will be
REM   executed and explained, it also contains the bind variables
REM   referenced by the SQL (declaration and value assignment).
REM
REM   The SQL text provided with the SCRIPT file must include a tag
REM   /* &&unique_id */ within a comment in any place. Please see
REM   script1.txt provided as an example.
REM
REM PRE-REQUISITES
REM   1. Install SQLTXPLAIN tool as per instructions.txt provided
REM   2. Create a custom script very similar to script1.txt.
REM        o Read instructions included in example file script1.txt
REM        o Put in your custom script file the one SQL you want to
REM          analyze. Include your environment setup if needed.
REM   3. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted the following list (installation script
REM           created all these grants for main application schema)
REM           GRANT SELECT_CATALOG_ROLE TO 
REM           GRANT EXECUTE ON SYS.DBMS_METADATA TO 
REM           GRANT ADVISOR TO  (10g or 11g)
REM           GRANT ADMINISTER SQL TUNING SET TO  (10g or 11g)
REM   4. Install Trace Analyzer (Note:224270.1) Highly recommended
REM      but not mandatory
REM
REM PARAMETERS
REM   1. Name of SCRIPT that has the SQL to be executed and analyzed
REM      (required)
REM
REM EXECUTION
REM   1. Place your file with one SQL into sqlt/run server directory
REM   2. Navigate to sqlt/run server directory
REM   3. Start SQL*Plus in server connecting as application user
REM   4. Execute script sqltxecute.sql passing name of script with
REM      one SQL and its bind variables
REM   5. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxecute.sql [name of script with one SQL]
REM   SQL> start sqltxecute.sql script1.txt  <== script file
REM
REM NOTES
REM   1. It invokes Trace Analyzer (Note:224270.1) if previously
REM      installed (recommended)
REM   2. The SQL text provided with the SCRIPT file must include a tag
REM      /* &&unique_id */ within a comment in any place. Please see
REM      script1.txt provided as an example.
REM   3. If requested by Support provide a BINARY export:
REM      # exp sqltxplain/ tables=sqlt% file=sqlt.dmp
REM   4. For possible errors see sqltxecute.log
REM
SET ECHO OFF;
PRO
PRO Parameter 1:
PRO Name of SCRIPT file that contains SQL to be executed (required)
DEF script_with_sql = '&1';
PRO
PRO Value passed to sqltxecute.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO SCRIPT_WITH_SQL: &&script_with_sql
PRO
SET TERM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
PRO
COL library FOR A64 HEA 'Libraries';
SELECT object_type||' '||object_name||' ('||status||')' library
  FROM all_objects
 WHERE owner = 'SQLTXPLAIN'
   AND object_type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
 ORDER BY
       object_type, object_name;
COL connected_as_user NEW_V connected_as_user FOR A32;
SELECT USER connected_as_user FROM DUAL;
SET TERM ON;
PRO
PRO NOTE:
PRO You must be executing this script connected as
PRO the application user that executed original SQL.
PRO You are now connected as &&connected_as_user
PRO
SET TERM OFF;
VAR v_execution_id VARCHAR2(8);
VAR v_unique_id    VARCHAR2(32);
BEGIN
  sqltxplain.sqlt$i.sqltxecute_begin (
    x_execution_id => :v_execution_id,
    x_unique_id    => :v_unique_id );
END;
/
COL unique_id NEW_V unique_id FOR A32;
SELECT :v_unique_id unique_id FROM DUAL;
@&&script_with_sql
VAR v_statement_id VARCHAR2(16);
BEGIN
  sqltxplain.sqlt$i.sqltxecute_end (
    p_unique_id    => :v_unique_id,
    p_execution_id => :v_execution_id,
    x_statement_id => :v_statement_id );
END;
/
CL COL;
COL statement_id NEW_V statement_id FOR A16;
COL server_directory NOPRI NEW_V server_directory FOR A512;
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
SELECT :v_statement_id statement_id, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... sqlt_s&&statement_id._* files have been created into server directory:
PRO ... &&server_directory
PRO ... copying now generated files into local directory
PRO
SET TERM OFF ECHO OFF BLO ON DEF ON FEED OFF FLU OFF HEA OFF LIN 32767 NEWP NONE PAGES 0 RECSEP OFF SHOW OFF SQLBL ON SQLC MIX TAB OFF TRIMS ON VER OFF SERVEROUT ON SIZE 1000000 FOR TRU TIM OFF ARRAY 100 SQLP SQL>;
WHENEVER OSERROR CONTINUE;
WHENEVER SQLERROR CONTINUE;
SPO OFF;
SELECT NVL(file_sqlt_main, 'sqlt_s&&statement_id._main.html') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('MAIN', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_sqlt_frames, 'sqlt_s&&statement_id._frames.html') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('FRAMES', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_sqlt_metadata, 'sqlt_s&&statement_id._metadata.sql') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('METADATA', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_sqlt_trace, 'sqlt_s&&statement_id._trace.html') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRACE', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_sqlt_lite, 'sqlt_s&&statement_id._lite.txt') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('LITE', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_trcanlzr_html, 'sqlt_s&&statement_id._trcanlzr.log') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCAHTML', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_trcanlzr_txt, 'sqlt_s&&statement_id._trcanlzr.log') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCATXT', NULL, :v_statement_id));
SPO OFF;
SELECT NVL(file_trcanlzr_log, 'sqlt_s&&statement_id._trcanlzr.log') copy_file_name FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO &©_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCALOG', NULL, :v_statement_id));
SPO OFF;
SET TERM ON
PRO
PRO ... exporting SQLT tables needed to create a test case
ACC sqltxplain_password PROMPT '...please enter SQLTXPLAIN password: ' hide;
HO exp sqltxplain/&sqltxplain_password tables=sqlt% file=sqlt_s&&statement_id..dmp statistics=none
UNDEFINE SQLTXPLAIN_PASSWORD
PRO
PRO ... if sqlt_s&&statement_id..dmp was not created, please execute manually FROM server:
PRO ... # exp sqltxplain/ tables=sqlt% file=sqlt_s&&statement_id..dmp statistics=none
PRO
PRO ... generating now a zip with all generated files
HO zip sqlt_s&&statement_id. sqlt_s&&statement_id.*
PRO
PRO ... if sqlt_s&&statement_id..zip was not created, please execute manually:
PRO ... # zip sqlt_s&&statement_id. sqlt_s&&statement_id.*
PRO
CL COL;
UNDEFINE 1;