SET DEF ^ ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET ESC ON SQLBL ON;
SPO sqltxecute.log;
REM
REM $Header: 215187.1 sqltxecute.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   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   provided sample_script1.sql. 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 PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Create a custom script using sample_script1.sql.
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 those privileges shown in script
REM           sqlt/install/sqguser.sql
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/input 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 directory path and
REM      name of file with 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 input/sample_script1.sql  <== script
REM
REM NOTES
REM   1. It invokes Trace Analyzer (Note:224270.1) if previously
REM      installed (recommended)
REM   2. 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 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;
DEF _SQLPLUS_RELEASE
COL connected_user NEW_V connected_user FOR A30;
SELECT user connected_user FROM DUAL;
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.8');
COL prev_sql_id NEW_V prev_sql_id FOR A16;
COL prev_child_number NEW_V prev_child_number FOR 9999999999;
SELECT /* SQLT XECUTE */ prev_sql_id, prev_child_number FROM sqltxplain.sqlt$_v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND username IS NOT NULL AND prev_hash_value != 0;
VAR v_execution_id VARCHAR2(8);
VAR v_unique_id    VARCHAR2(32);
COL unique_id NEW_V unique_id FOR A32;
SELECT :v_unique_id unique_id FROM DUAL;
BEGIN
  sqltxplain.sqlt$i.sqltxecute_begin (
    x_execution_id => :v_execution_id,
    x_unique_id    => :v_unique_id );
END;
/
SELECT :v_unique_id unique_id FROM DUAL;
SET TERM ON;
PRO
PRO
PRO NOTE:
PRO You connected as ^^connected_user
PRO If you get an error please verify the following:
PRO 1. That you connected as the application user that executed original SQL.
PRO 2. That your SQL inside ^^script_with_sql contains an extra space in any valid place (to force a hard-parse).
PRO 3. That your SQL inside ^^script_with_sql contains token "\^\^unique_id" within a comment (to precisely identify your SQL).
PRO 4. That ^^script_with_sql executes well (without errors) stand alone i.e.: SQL> START ^^script_with_sql..
PRO 5. That your SQL inside ^^script_with_sql ends with a semicolon ";".
PRO
PRO ... please wait
PRO
SET ECHO ON SERVEROUT OFF;
@^^script_with_sql
SET TERM OFF;
PRO
L
PRO
SELECT /* SQLT XECUTE */ prev_sql_id, prev_child_number FROM sqltxplain.sqlt$_v$session WHERE audsid = SYS_CONTEXT('USERENV', 'SESSIONID') AND username IS NOT NULL AND prev_hash_value != 0;
SET SERVEROUT ON SIZE 1000000;
VAR v_statement_id VARCHAR2(16);
BEGIN
  sqltxplain.sqlt$i.sqltxecute_end (
    p_unique_id    => :v_unique_id,
    p_sql_filename => TRIM('^^script_with_sql'),
    p_sql_id       => TRIM('^^prev_sql_id'),
    p_child_number => TO_NUMBER(TRIM('^^prev_child_number')),
    p_execution_id => :v_execution_id,
    x_statement_id => :v_statement_id );
END;
/
SET ECHO OFF;
COL statement_id3 NEW_V statement_id3 FOR A16;
COL server_directory NOPRI NEW_V server_directory FOR A512;
COL copy_file_name NOPRI NEW_V copy_file_name FOR A256;
COL tc_builder_script_file_name NOPRI NEW_V tc_builder_script_file_name FOR A256;
COL trace_file_name NOPRI NEW_V trace_file_name FOR A256;
COL tkprof_file_name NOPRI NEW_V tkprof_file_name FOR A256;
SELECT :v_statement_id statement_id3, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... sqlt_s^^statement_id3._* files have been created into server directory:
PRO ... ^^server_directory
PRO ... copying now generated files into local directory
PRO
SET TERM OFF ECHO OFF 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 OSERROR CONTINUE;
WHENEVER SQLERROR CONTINUE;
PRO No fatal errors!
SPO OFF;
SELECT NVL(REPLACE(file_sqlt_main, '.', '_c.'), 'sqlt_s^^statement_id3._main.html') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('MAIN', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_frames, '.', '_c.'), 'sqlt_s^^statement_id3._frames.html') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('FRAMES', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_metadata, '.', '_c.'), 'sqlt_s^^statement_id3._metadata.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('METADATA', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_setenv, '.', '_c.'), 'sqlt_s^^statement_id3._setenv.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('SETENV', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_readme, '.', '_c.'), 'sqlt_s^^statement_id3._readme.txt') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('README', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_trace, '.', '_c.'), 'sqlt_s^^statement_id3._trace.trc') trace_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^trace_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRACE', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_lite, '.', '_c.'), 'sqlt_s^^statement_id3._lite.txt') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('LITE', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_xplore_script, '.', '_c.'), 'sqlt_s^^statement_id3._xplore.sql') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('XPLORE_SCRIPT', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_sqlt_tcbuilder, '.', '_c.'), 'sqlt_s^^statement_id3._tc_builder.sql') tc_builder_script_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^tc_builder_script_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TCBUILDER', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_trcanlzr_html, '.', '_c.'), 'sqlt_s^^statement_id3._trcanlzr.log') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCAHTML', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_trcanlzr_txt, '.', '_c.'), 'sqlt_s^^statement_id3._trcanlzr.log') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCATXT', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
SELECT NVL(REPLACE(file_trcanlzr_log, '.', '_c.'), 'sqlt_s^^statement_id3._trcanlzr.log') copy_file_name
FROM sqltxplain.sqlt$_statement WHERE statement_id = :v_statement_id;
SPO ^^copy_file_name.;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('TRCALOG', :v_statement_id));
SPO OFF;
EXEC sqltxplain.sqlt$r.store_file;
@^^tc_builder_script_file_name.
SELECT 'sqlt_s^^statement_id3._tkprof.txt' tkprof_file_name FROM DUAL;
SPO sqlt_s^^statement_id3._xecute.log;
GET sqltxecute.log
.
SET TERM ON
PRO
PRO ... exporting SQLT tables needed to create a test case
PRO
PRO ... if sqlt_s^^statement_id3..dmp is not created, please execute manually from server:
PRO ... # exp sqltxplain@host_string tables=sqlt% file=sqlt_s^^statement_id3..dmp statistics=none log=sqlt_s^^statement_id3._exp.log
PRO
ACC sqltxplain_password PROMPT '...please enter SQLTXPLAIN password: ' hide;
ACC host_string PROMPT'...please specify OPTIONAL Host String (i.e.: @PROD, or just hit enter): ';
HO exp sqltxplain/^^sqltxplain_password^^host_string tables=sqlt% file=sqlt_s^^statement_id3..dmp statistics=none log=sqlt_s^^statement_id3._exp.log
UNDEFINE SQLTXPLAIN_PASSWORD
PRO
PRO ... generating now a tkprof out of 10046 trace ^^trace_file_name
PRO
PRO ... if ^^tkprof_file_name is not created, please execute manually from server:
PRO ... # tkprof ^^trace_file_name ^^tkprof_file_name
PRO
HO tkprof ^^trace_file_name ^^tkprof_file_name
PRO
PRO ... generating now a zip with all generated files
PRO
PRO ... if sqlt_s^^statement_id3..zip is not created, please execute manually:
PRO ... # zip -mT sqlt_s^^statement_id3. sqlt_s^^statement_id3.* sqltxecute.log
PRO ... # zip -j sqlt_s^^statement_id3. ^^script_with_sql
PRO ... # zip -d sqlt_s^^statement_id3. sqltxecute.log
PRO
SPO OFF;
HO zip -mT sqlt_s^^statement_id3. sqlt_s^^statement_id3.* sqltxecute.log
HO zip -j sqlt_s^^statement_id3. ^^script_with_sql
HO zip -d sqlt_s^^statement_id3. sqltxecute.log
CL COL;
UNDEFINE 1 unique_id script_with_sql server_directory copy_file_name trace_file_name tkprof_file_name host_string;
SET DEF ON
PRO SQLTXECUTE completed.
