SET ECHO ON TERM ON SERVEROUT ON SIZE 1000000 SQLP SQL>;
SPO sqltcompare.log;
REM
REM $Header: 215187.1 sqltcompare.sql 11.2.1 2008/09/21 csierra $
REM
REM Copyright (c) 2008, Oracle Corporation.  All rights reserved.
REM
REM SCRIPT
REM   sqltcompare.sql
REM
REM DESCRIPTION
REM   This script compares initialization parameters and CBO
REM   statistics FROM two executions of the SQLTXPLAIN over one SQL
REM   on same or different systems. Those init.ora parameters and CBO
REM   stats are associated to each SQL for which the SQLTXPLAIN is
REM   executed. The SQLTXPLAIN takes snapshots of this information.
REM
REM PRE-REQUISITES
REM   Steps 1 to 5 are necessary if SQLs to compare are FROM
REM   different systems.
REM   1. Install SQLTXPLAIN tool in both sources and in destination
REM      systems as per instructions.txt provided
REM   2. Execute SQLTXPLAIN (any method) in source systems for the
REM      one SQL statement that will be compared
REM   3. Export tables FROM both source systems using command below:
REM      # exp sqltxplain/[pwd] tables=sqlt% file=sqlt.dmp
REM   4. Transfer BINARY file sqlt.dmp FROM sources to destination
REM      system
REM   5. Import these tables into destination system using commands:
REM      # imp sqltxplain/[pwd] tables='sqlt$_statement' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_plan_table' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_stattab' file=sqlt.dmp ignore=y
REM      # imp sqltxplain/[pwd] tables='sqlt$_parameter2' file=sqlt.dmp ignore=y
REM   6. The user that executes this script can be SQLTXPLAIN or the
REM      application user
REM
REM PARAMETERS
REM   1. Statement ID 1 as per SQLTXPLAIN for SQL in source system
REM      (required). A list of statement ids is presented to the user
REM      executing this script.
REM   2. Statement ID 2 as per SQLTXPLAIN for SQL in source system
REM      (required). A list of statement ids is presented to the user
REM      executing this script.
REM
REM EXECUTION
REM   1. Navigate to sqlt/run directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN or application user
REM   3. Execute script sqltcompare.sql passing statement id and
REM      schema (parameters can be passed inline or until requested)
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus sqltxplain/[sqltxplain pwd]
REM   SQL> start sqltcompare.sql [statement id 1] [statement id 2];
REM   SQL> start sqltcompare.sql s2263_olc510_apperf02 s2597_scmx7st;
REM   SQL> start sqltcompare.sql s2263 s2597;
REM   SQL> start sqltcompare.sql;
REM
REM NOTES
REM   1. For possible errors see sqltcompare.log
REM
SET ECHO OFF;
EXEC sqltxplain.sqlt$r.initialization;
SELECT statement_id
  FROM sqltxplain.sqlt$_parameter2
 UNION
SELECT statid statement_id
  FROM sqltxplain.sqlt$_stattab
 WHERE statid LIKE 's%';
PRO
PRO Parameter 1: Statement id 1 to be compared (required)
PRO Parameter 2: Statement id 2 to be compared (required)
PRO
DEF statement1 = '&1';
DEF statement2 = '&2';
PRO
PRO Value passed to sqltcompare.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO STATEMENT 1: &&statement1
PRO STATEMENT 2: &&statement2
PRO
SET TERM OFF;
COL connected_as_user NEW_V connected_as_user FOR A32;
SELECT USER connected_as_user FROM DUAL;
SET TERM ON;
PRO
PRO ... generating sqlt compare report for &&statement1 and &&statement2
PRO
SET TERM OFF;
VAR v_output_filename VARCHAR2(256);
BEGIN
  sqltxplain.sqlt$r.compare_report (
    p_statement_id1   => '&&statement1',
    p_statement_id2   => '&&statement2',
    x_output_filename => :v_output_filename );
END;
/
CL COL;
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 ... sqlt_&©_file_name._compare.html file has been created into server directory:
PRO ... &&server_directory
PRO ... copying now generated file 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>;
SPO OFF;
SPO sqlt_&©_file_name._compare.html;
SELECT column_value FROM TABLE(sqltxplain.sqlt$r.display_file('COMPARE', :v_output_filename));
SPO OFF;
SET TERM ON;
CL COL;
UNDEFINE 1 2;