SET DEF ^ ECHO ON TERM ON SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SPO sqltxplite.log;
REM
REM $Header: 215187.1 sqltxplite.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   sqltxplite.sql
REM
REM DESCRIPTION
REM   This script generates a lite report with query tuning
REM   diagnostic details. It requires as input the filename of a
REM   plain text file that contains one valid SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Create a text file that contains the ONE valid SQL statement
REM      to be explained. The SQL in this file must comply with the
REM      following:
REM        o  SQL must be valid (semantics and syntax)
REM        o  Must end with one semicolon ";" (optional)
REM        o  Should not have empty lines (lines with no text at all)
REM        o  It can contain bind variables (like :b1)
REM        o  Should be the same SQL that performs poorly
REM           (no changes other than fixing implicit type conversion)
REM        o  The created file must contain ONE and only ONE SQL
REM        o  The file must be placed into local SQL*Plus directory
REM        o  A sample file with one SQL is file run/sample_sql1.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
REM PARAMETERS
REM   1. Name of file that contains SQL to be explained (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 sqltxplain.sql passing directory path and
REM      name of file with SQL
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 sqltxplite.sql [name of your file with one SQL]
REM   SQL> start sqltxplite.sql sample_sql1.sql  <== your file
REM
REM NOTES
REM   1. This script can only be used on 10g and higher
REM   2. For possible errors see sqltxplite.log
REM
SET ECHO OFF;
PRO
PRO Parameter 1: Name of file that contains SQL to be explained (required)
DEF file_with_one_sql = '^1';
PRO
PRO Value passed to sqltxplite.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO FILE_WITH_ONE_SQL: ^^file_with_one_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');
VAR v_statement_id VARCHAR2(256);
EXEC :v_statement_id := sqltxplain.sqlt$r.next_statement_id_char;
COL statement_id1 NEW_V statement_id1 FOR A32;
SELECT :v_statement_id statement_id1 FROM DUAL;
SELECT '>>> If you get "SP2-0160: unable to open [filename]", please fix and retry <<<' verify_file FROM DUAL;
SET SUF '';
SET TERM ON;
PRO
PRO
PRO ... reading file ^^file_with_one_sql
PRO
GET ^^file_with_one_sql
.
SET TERM OFF;
SET SUF 'sql';
PRO >>> If you get "SP2-0023: String not found" warning, please ignore <<<
C/;/
PRO
0 EXPLAIN PLAN SET statement_id = '^^statement_id1.' INTO sqltxplain.sqlt$_plan_table FOR
/
SET TERM ON;
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 ^^file_with_one_sql exists and it contains one valid DML statement.
PRO
PRO ... please wait
PRO
SET TERM OFF;
VAR v_stat_id VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$i.sqltxplite_end (
    p_statement_id => '^^statement_id1.',
    p_sql_filename => TRIM('^^file_with_one_sql') );
END;
/
COL statement_id1 NEW_V statement_id1 FOR A32;
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_id1, sqltxplain.sqlt$d.get_param('output_directory', 'I') server_directory FROM DUAL;
SET TERM ON;
PRO
PRO ... sqlt_s^^statement_id1._sqltxplite.txt file has been created into server directory:
PRO ... ^^server_directory
PRO ... copying now generated file 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 SQLERROR CONTINUE;
SPO OFF;
SELECT NVL(REPLACE(file_sqlt_lite, '.', '_c.'), 'sqlt_s^^statement_id1._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;
SET TERM ON;
CL COL;
UNDEFINE 1 file_with_one_sql server_directory copy_file_name;
SET DEF ON
PRO SQLTXPLITE completed.
