TRACE made easy - Source Code

From: <rzx2122_at_mcvax2.d48.lilly.com>
Date: 14 Apr 93 13:38:26 EST
Message-ID: <1993Apr14.133826.1_at_mcvax2.d48.lilly.com>

Two files follow: TRACE.SQL and TRACE.COM. Please share this utility with others. It helped me. It would be nice if someone could elaborate on the output of TKPROF. Tom

-------------------------------- cut here ------------------------------------

/*
TRACE.SQL - March 1, 1993

                                     .     ,------+
Tom Harleman                         .     |      |
INOUG Steering Committee Member      .     |      |
Paradigm Consulting, Inc.            .     | Indiana Oracle Users Group
11080 Willowmere Drive               .     |      |
Indianapolis, IN  46280              .     |      | 
                                     .     /   _,'
                                     .    /_,-'

A SQL*Plus utility for the VAX/VMS platform to analyze SQL statements.

While working on a SQL statement in SQL*Plus, you may execute this SQL script via:

                SQL> START TRACE

(1) The TRACE feature of SQL*Plus will be turned on, 
(2) your SQL script will be executed and statistics stored in a .TRC file,
(3) the statistics will be TKPROFed and stored in a file called TRACE.TXT, 
(4) and you will be returned to SQL*Plus ready to modify your SQL script.

Then, at the SQL prompt, edit the TRACE.TXT file to view the results.

Placed in the public domain 01-MAR-1993 without restriction or warranty.

Note:
This utility does not delete or purge the .TRC, TRACE1.SQL, or TRACE.TXT files. If you want this feature, add these lines after the $ _at_SYS$LOGIN:TRACE line.
$ DELETE/NOLOG SYS$LOGIN:*.TRC;*
$ DELETE/NOCONFIRM/NOLOG SYS$LOGIN:TRACE1.SQL;*
$ PURGE/NOCONFIRM/NOLOG SYS$LOGIN:TRACE.TXT;*
Requirements:
XPLAINPL.SQL - a utility supplied by Oracle Corporation and

               required to build the PLAN_TABLE table.

TRACE.COM - a DCL script that must be placed in the logical SYS$LOGIN.

*/
SAVE SYS$LOGIN:TRACE1.SQL REPLACE
$ _at_SYS$LOGIN:TRACE

GET SYS$LOGIN:TRACE1.SQL
PROMPT EDIT SYS$LOGIN:TRACE.TXT

-------------------------------- cut here ------------------------------------
$!
$! TRACE.COM - March 1, 1993
$! 
$! when called from SQLPLUS, this DCL will 
$! (1) store the SQL in the SQL*Plus buffer,
$! (2) set up the TRACE/TKPROF facility,
$! (3) execute the SQL and generate statistics to a .TRC file,
$! (4) use TKPROF to translate the .TRC file into a readable TRACE.TXT file,
$! (5) and reload the original SQL into the SQL*Plus buffer.
$!
$!                                      .     ,------+
$! Tom Harleman                         .     |      |
$! INOUG Steering Committee Member      .     |      |
$! Paradigm Consulting, Inc.            .     | Indiana Oracle Users Group
$! 11080 Willowmere Drive               .     |      |
$! Indianapolis, IN  46280              .     |      |
$!                                      .     /   _,'
$!                                      .    /_,-'
$!
$!Turn off VMS messaging.

$ SET NOVERIFY
$ SET MESSAGE/NOFAC/NOID/NOSEV/NOTEXT

$!
$!Direct the SQL_TRACE output to your HOME directory.

$ DEFINE ORA_DUMP "SYS$LOGIN"

$!
$!Turn on VMS messaging.

$ SET MESSAGE/FAC/ID/SEV/TEXT

$!
$ SQLPLUS /

SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
ALTER SESSION SET SQL_TRACE TRUE;
START ORA_DUMP:TRACE1
EXIT

$!
$!/* Execute the Oracle TKPROF utility. */

$ FILE1 = F$SEARCH("ORA_DUMP:*.TRC")
$ IF FILE1 .EQS. "" THEN GOTO FINISH
$ TKPROF 'FILE1 ORA_DUMP:TRACE.TXT SORT=(EXECPU,FCHCPU) EXPLAIN=/

$!

$ FINISH:
$ EXIT

-------------------------------- cut here ------------------------------------

Received on Wed Apr 14 1993 - 20:38:26 CEST