Introduction to Oracle SQL Trace

Oracle's SQL trace facility allows an administrator to see what SQL is being run by an application, even if he has only limited access to the program. This knowledge can be used to optimise database performance or to suggest improvements to software developers, or sometimes just to understand what is going on.

The facility is excellent and well documented in the Oracle performance guides. However the author found the command line interface a little tricky, and so we include some scripts to help start tracing the SQL of a specific UNIX user.

Disclaimer

Please note all scripts are given "as is". Eighth Layer limited make no warranty as to the correctness of these scripts. You run them at your own risk. These scripts were written to assist in administering a particular Oracle instance, your environment may vary.

Conventions and Environment

These scripts were written to help administer an application running on a server running HP-UX 10.20, and Oracle 7.3.4. The application would start a number of Oracle sessions for each UNIX user, and it was hard to establish which session corresponded to which part of the application. To make life easier a set of scripts were written that would switch on SQL trace for a given UNIX user.

The utilities resided under a "/root" directory (Hence forth $DIR), and were divided into scripts and programs "$DIR/bin" and SQL "$DIR/sql".

UNIX environment variables required for Oracle are set in /etc/ORACLE

Red denotes filenames

"Quotation marks denote a UNIX command line"

Getting SQL run by UNIX scripts

First we need to be able to run SQL from UNIX scripts. To this end a simple set of functions are defined to minimise the number of locations in which Oracle passwords are stored in plain text in the UNIX system. You may not want to store Oracle passwords in plain text - if so don't use these scripts.

This script runs a file containing SQL in either sqlplus or in svrmgrl as user SYS

Note: HP-UX 10.20 uses the POSIX shell by default rather than the Bourne shell. Other Unix users may need to ensure these scripts are run under Korn shell (ksh) or bash.

$DIR/bin/functions

# Posix Shell Functions to assist in administration
#
. /etc/ORACLE

runassys() {

sqlplus -s sys/syspasswd @$*

}
# 
# Run using svrmgrl as timing stats etc must use svrmgrl
#
runinsvrmgr(){
svrmgrl<<EOF
connect sys/syspasswd
set echo on
@$1
EOF
}

Starting to Record Statistics

Oracle must be told to start timing how long each phase of processing an SQL statement takes. Since recording this information itself takes time we defined two scripts to easily switch it on and off (ostaton and ostatoff), with two related SQL files.

$DIR/bin/ostaton

#
# Switch Oracle timing stats on
DIR=/root 
. $DIR/bin/functions

runinsvrmgr $DIR/sql/timing.sql

$DIR/sql/timing.sql

/* Start Collecting timing statistics */
alter system set timed_statistics=true;

$DIR/bin/ostatoff

#
# Switch Oracle timing stats off
DIR=/root 
. $DIR/bin/functions

runinsvrmgr $DIR/sql/timingoff.sql

$DIR/sql/timingoff.sql

/* Start Collecting timing statistics */
alter system set timed_statistics=false;

Statistics for a specific UNIX user

The main issue with switching on statistics for a specific user is that whilst Oracle knows which sessions belong to which UNIX users, the commands for switching on statistics work on a per Oracle session basis, so we must retrieve all the sessions for a specific user, and switch on statistics for each of these Oracle sessions.

The above is achieved by issuing some SQL*PLUS commands that write another script ("/tmp/stats.sql") using the SPOOL command. This script will have one line for each session belonging to that user.

We then run the generated script "/tmp/stats.sql" in svrmgrl in order to enable the statistics for that users sessions.

$DIR/bin/osession

#
# Get Oracle session information for timings
#
if [ $# -ne 2 ]
then
        echo Usage osession username [true|false]
        exit 1
fi
USER=$1
STATS=$2

if [ $STATS = 'true' -o $STATS = 'false' ]
then 
 echo Changes statistics for $USER 
else
 echo "Second arguement must be [true|false]"
 exit 1
fi
DIR=/root 
. $DIR/bin/functions

# Make sure timing stats are on
# We don't switch it off at the end as we might run several times

ostaton

#
# Run svrmgrl and create the sql to start timings for all of a users sessions 
#
cd /tmp
sqlplus sys/syspasswd <<EOF
set echo off
set feedback off
set heading off
set sqlprompt #
spool stats.sql
select 'execute dbms_system.set_sql_trace_in_session(' || sid ||','||serial#||',$STATS);' from v\$session where osuser='$USER';
spool off
EOF

runinsvrmgr /tmp/stats.sql

A Worked Example

To trace the SQL of all current sessions of user daemon we issue the command

"osession daemon true".

Oracle starts to record data in trace files in the USER_DUMP_DEST (This has the same default destination for this is the same as the default location for the Oracle alert log).

After a few while we have a number of trace files, if batch work is going on we can collect a lot of data, be careful not to fill up an important file system!!!!

To stop recording data for user daemon.

"osession daemon false"

To stop tracking the time of statements

"ostatoff"

Don't forget this last one.

"ls -l ~oracle/rdbms/log" now shows

-rw-rw---- 1 oracle dba 5466 Sep 28 13:41 ora_19917.trc

-rw-rw---- 1 oracle dba 46273 Sep 28 13:41 ora_22246.trc

Amongst other files - alas the naming convention leaves a little to be desired. On my system the alert log and other trace files are sent to a different directory, so I know any trace files in ~oracle/rdbms/log are from performance measurements.

tkprof is the Oracle command for interpreting this trace files. It has various options to help you order the output sensibly.

"tkprof ora_22246.trc readable.prf"

"more readable.prf"

Then of course you have to understand the performance statistics you have produced, but that is for another article.

Comments

These scripts are meant as a simple aid to encouraging people to use Oracle SQL trace as well as introducing some simple scripting ideas for UNIX.

The scripts are intended to be simple, and relatively easy to follow, they are not meant to be portable, robust or complete.

The osession script has a number of glaring weaknesses. It doesn't check the first argument is a valid user name, it doesn't do anything to protect the /tmp/stats.sql files, or ensure the permissions are correct. None of the scripts specify under what shell they start, or any revision history or authorship information. If this bothers you, rewrite them or write your own.

This article was written by Simon Waters when he was working for Eighth Layer Limited.