Oracle Session Resource Profiler

Introduction

In short, OraSRP is a profiler. It parses extended SQL trace files and creates report which allows you to learn where your session spent its time and why. OraSRP may generate reports either in html or in text format.

Features

1. Builds complete and detailed session profile (which includes such pseudo-events like 'PARSE/EXEC/FETCH calls' and 'unaccounted-for time').
2. Display results either in html or text format.
3. Display graph of statements calls.
4. Display events histograms.
5. Display various session statistics like 'blocks read', 'datafiles read', 'parallel execution' etc.
6. Group similar statements' statistics into one.
7. Display values of bind variables.
8. Able to parse trace files from Oracle version 7.2 upto 11g.
   

Installation

OraSRP comes in several forms:

1. Installer for Windows (orasrp-setup.exe). Just run it and follow usual instructions.
2. Binary for Windows (orasrp-windows.zip). Just unzip anywhere you want.
3. Binary for Linux (orasrp-linux.zip). Just unzip anywhere you want.
   

Usage

OraSRP is a command-line utility. Pass trace file name as a first parameter and output report name as a second. Like this:

$ orasrp trace.trc report.html

That's it.

Command-line options

Brief explanation:

$ orasrp -h
usage: orasrp [options] file [outfile]

options:
-h, --help show this help message and exit
--version show program's version number and exit
-t, --text output in text format
--aggregate=YES/no aggregate similar statements
-bn, --binds=n how many unique bind-sets to display (1 by defaut)
--maxbinds=n how many unique bind-sets to analyze (all by default)
--recognize-idle-events=YES/no recognize or not idle wait events
--sys=YES/no print sys statements
--sort how to sort statements in output (values like in tkprof)
--display-sections what sections of report to display (all by default)
--skip-sections what sections of report to skip
--sessionid analyze data only for the specified session id
--threshold omit statements which spend less than threshold % from total time (not used by defaut)
--google-charts display charts using Google Chart API

Downloads

Current version is 3.2.0 beta released 20 Dec 2007.

• Binary distribution for Windows.
  
• Binary installer for Windows.
  
• Binary distribution for Linux.

Samples

• Sample One (excessive parsing; demonstration of grouping similar statements' statistics)
• Sample Two (slow delete because of row lock contention)
• Sample Three (text output; display values of bind variables)

To find the location of Tracle File :

-bash-3.00$ sqlplus / as sysdba

SQL> show parameter dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/bdump
core_dump_dest string /backup2/Core_Dump
user_dump_dest string /backup2/User_dump

So the trace files are stored in /backup2/User_dump.Here you find lot of trace files, to identify which trace file has created for current session :

select d.value || '/ora_' || p.spid || '.trc' trace_file_name
from
( select p.spid from sys.v_$mystat m,
sys.v_$session s, sys.v_$process p
where m.statistic# = 1 and
s.sid = m.sid and p.addr = s.paddr
) p,
( select value from sys.v_$parameter
where name = 'user_dump_dest'
) d;

To Enable SQL TRACE:

alter session set sql_trace=true;
alter system set sql_trace=true scope=spfile;