SQL Trace Access and Analysis: OraSRP

This page shortly describes a package to deploy and analyze SQL trace files for DBAs and developers. This package is a valid alternative to the simpler approach: sql trace + tkprof + mail results.

Package overview

• publish trace files for access via a web browsers
• process sql trace files (see event 10046 and/or DBMS_MONITOR for more info on how to activate sql tracing)
• advanced analysis of sql trace files (more advanced than tkprof, for example)

Sample Orasrp setup

1. Download the software (see below for binary and python source versions)
2. unzip under $HOME/scripts/orasrp
3. export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$HOME/scripts/orasrp; export PATH=$PATH:$HOME/scripts/orasrp
4. (optional) create password-authenticated user
   • cd to the UDUMP directory. Example: cd /ORA/dbs00/oracle/admin/DBNAME/udump
   • create user (optional) . Example: htpasswd -c .htpasswd luca
   • run orasrp listening on a chosen port. optionally define a keyword on the trace files. Example:

nohup orasrp -d $PWD --port 2502 --pattern=luca    &

• Note: on RAC orasrp should be run on multiple nodes
  Note: different port numbers can be used to run orasrp simultaneously for different users.
  Note: add --htpasswd to the command line enforce user/pass authentication

Instructions to get started:

• the session parameter tracefile_identifier is needed to add the pattern defined above to the trace files. Example: alter session set tracefile_identifier='luca';
• activate sql tracing trace. Example: exec dbms_monitor.SESSION_TRACE_ENABLE;
• point a browser to htp://webserver:port (2502 in the example above). For rac multiple sessions are needed, one for each node.

Download from these links:

• https://twiki.cern.ch/twiki/pub/PSSGroup/SQLTraceAnalysis/orasrp-src.zip: Preferred version ORASRP, SQL trace analyzer, python source
• https://twiki.cern.ch/twiki/pub/PSSGroup/SQLTraceAnalysis/orasrp-linux.zip: ORASRP, SQL trace analyzer, binary version for Linux
• https://twiki.cern.ch/twiki/pub/PSSGroup/SQLTraceAnalysis/orasrp-windows.zip: ORASRP, SQL trace analyzer, binary version for Windows