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 can read data from stding, regular file or directly from remote machine via TCP socket.
It may generate reports either in html or in text format.
OraSRP comes in several forms:
- Installer for Windows (orasrp-setup.exe). Just run it and follow usual instructions.
- Binary for Windows (orasrp-windows.zip). Just unzip anywhere you want.
- Binary for Linux (orasrp-linux.zip). Just unzip anywhere you want.
- Binary for Mac OS X (orasrp-macosx.zip). Just unzip anywhere you want.
OraSRP is a command-line utility (like tkprof, for example). 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. More detailed explanation of various command-line options is in the next paragraph.
Brief explanation:
$ orasrp -h
usage: orasrp [options] trcfile [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)
-c, --config specify config file
--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)
-s, --server run in httpd mode
-p, --port listen on alternate port (in httpd mode)
-r, --remote connect to remote machine:port
--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
Note that you can use reduced form of options. I.e. instead of '--display-sections' you can use '--display' or just '--d'.
More detailed explanation:
- On
trcfile
, outfile
naming convention:
If trcfile
is specified as -
then OraSRP will read data from stdin
. If outfile
is not specified then OraSRP will
print results to stdout
. For example:
$ gzip -cd trace.trc.gz | orasrp - > report.html
- --aggregate (default: yes)
When OraSRP meet similar statements it aggregates statistics of such statements into one.
For example the following two statements are similar for OraSRP
(i.e. they differ from each other only by literal values):
select * from table1 where field1 = 'foo';
select * from table1 where field1 = 'bar';
Set '--aggregate=no' and OraSRP will treat such statements separately.
- --binds (default: 1)
Although OraSRP analyzes every bind-set it displays only one
(the one for which statement spent most of its elapsed time).
Note that if you specify '--binds=0' or just '-b0' OraSRP
will not analyze any bind-sets at all (which results to speed-up in trace file processing).
- --maxbinds (default: all)
By default OraSRP analyzes every bind-set for every statement in trace file.
For big files (f.e. above 1Gb) with a lot of binds it may be a very memory consuming operation.
In that case you can specify exactly how many unique bind-sets per statement to analyze.
- --config /path/to/alternate.conf
Starting from OraSRP version 4 you can specify options not only on command-line but in config-file too.
Default config file is .orasrp.conf at the same directory where OraSRP resides. If you want to use alternate
config file you can specify it with --config flag.
- --recognize-idle-events (default: yes)
There are some wait events called 'idle wait events' (or in terms of 10g and above, events from class 'Idle events').
It's such events like 'SQL*Net message from client' or 'SQL*Net message from dblink'. But in terms of OraSRP
they are not always 'idle'. They are 'idle' when they happens 'between-call'. For example, the following
'SQL*Net message from client' is considered 'idle':
FETCH #1:c=1000,e=1000,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1096584406938289
WAIT #1: nam='SQL*Net message to client' ela= 60 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 300 p1=1952673792 p2=1 p3=0
====
PARSING IN CURSOR #1 len=16 dep=0 uid=1 oct=3 lid=0 tim=1096584406938294 hv=2000000 ad='5ff9ad80'
and the following ('within-call') is not:
FETCH #1:c=1000,e=1000,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1096584406938289
WAIT #1: nam='SQL*Net message to client' ela= 60 p1=1952673792 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 300 p1=1952673792 p2=1 p3=0
FETCH #1:c=1000,e=1000,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1096584406940289
By specifying '--recognize-idle-events=no' you tell OraSRP not to distinguish such cases.
- --sys (default: yes)
Like in tkprof, this option specifies whether or not to display internal statements (statements with userid=sys).
- --sort (default: totela)
How to sort statements in report. Values are similar to tkprof's ones. One difference is that you cannot
simultaneously specify several values (yet?).
- prscnt -- number of times parse was called
- prscpu -- cpu time parsing
- prsela -- elapsed time parsed
- prsdsk -- number of disk reads during parse
- prsqry -- number of buffers for consistent read during parse
- prscur -- number of buffers for current read during parse
- prslio -- number of logical reads (consistent reads + current reads) during parse
- prsmis -- number of misses in library cache during parse
- execnt -- number of execute was called
- execpu -- cpu time spent executing
- exeela -- elapsed time executing
- exedsk -- number of disk reads during execute
- exeqry -- number of buffers for consistent read during execute
- execur -- number of buffers for current read during execute
- exerow -- number of rows processed during execute
- exelio -- number of logical reads (consistent reads + current reads) during execute
- exemis -- number of library cache misses during execute
- fchcnt -- number of times fetch was called
- fchcpu -- cpu time spent fetching
- fchela -- elapsed time fetching
- fchdsk -- number of disk reads during fetch
- fchqry -- number of buffers for consistent read during fetch
- fchcur -- number of buffers for current read during fetch
- fchrow -- number of rows processed during fetch
- fchlio -- number of logical reads (consistent reads + current reads) during fetch
- totcnt -- number of times parse+exec+fetch were called
- totcpu -- total cpu time spent
- totela -- total elapsed time spent (default)
- totdsk -- total number of disk reads
- totqry -- total number of buffers for consistent read
- totcur -- total number of buffers for current read
- totrow -- total number of rows processed
- totlio -- total number of logical reads (consistent reads + current reads)
- totmis -- total number of library cache misses
- crsid -- display statements as they arrear in trace file
- --server
It means that OraSRP will run in httpd mode listening on default port 2502
(can be changed by specifying --port ). It's kind of interactive mode --
in your browser you select which trace (from all traces in current dir) to analyze.
- --remote
OraSRP can read data via socket from remote machine (proxysrp.pl script should be launched on remote machine).
- --display-sections (default: all)
OraSRP's report is divided into several sections. These sections are: 'Summary', 'Flat Profile',
'Top 5 Statements per Event', 'Call Graph', 'Events Histograms' and 'Statements'. Corresponding
values for '--display-section' are: summary, flatprofile, top5, callgraph, histograms and statements.
So, if you want to see just a call graph, run OraSRP with
'--display-sections=callgraph' option. Note that you can specify several sections separated by comma.
- --skip-sections (not used by default)
You can use this option when you want to skip just one section from report (say, histograms). I.e. instead of
specifying '--display-sections=summary,flatprofile,top5,callgraph,statements' you can specify just
'--skip-sections=histograms'.
- --sessionid (not used by default)
One trace file can contain data from several sessions. Unlike tkprof, OraSRP by default analyzes data only from
the first encountered session.
When '--sessionid=all' is specified then OraSRP analyzes data from all
sessions in trace file. Pay attention that in that case OraSRP sum up 'session time' and various statistics
from all sessions. So if you have trace file with two unrelated sessions then report would be meaningless.
On the other side, if you have, for example, trace file combined from parallel execution slaves then
'--sessionid=all' would be helpful.
You can also specify exactly from what session to analyze data. SessionID is just a
'sid.seriail#' (as they displayed in trace file).
- --threshold (not used by default)
Trace file may contain several hundreds statements each of them with a tiny impact to total session time.
Sometimes it's just annoying to see all of these unimportant statements in report. By using '--threshold'
option you tell OraSRP to omit statements not reached the threshold.
OraSRP exists only on Windows/Linux/MacOSX. What if you want to analyze trace files on Solaris (or HP-UX, or AIX)?
Previously you had to download tracefile to the machine with OraSRP and then analyze it there.
Starting from OraSRP version 4, you can use another method. Launch small perl script called proxysrp.pl on your Solaris box
and leave it running. After that execute OraSRP with --remote flag and specify path to trace file as it's on a remote machine.
For example: let's say we have /path/on/solaris/to/trace.trc on solarisbox and orasrp on linuxbox. Run on solaris:
solarisbox$ perl proxysrp.pl
and on linux:
linuxbox$ orasrp --remote solarisbox /path/on/solaris/to/trace.trc report.html
OraSRP will connect to solarisbox and with help of proxysrp.pl will read trace.trc directly via TCP socket.
Analyze trace1.trc file and generate report report1.html
$ orasrp trace1.trc report1.html
Analyze trace2.trc file and generate report report2.html without sys statements in it
$ orasrp --sys=no trace2.trc report2.html
Analyze ora_1234.trc trace file. Display only statements section. For every statement display 2
distinct bind-sets in maximum. Sort statements by total CPU time spent. Omit statements with CPU
time spent value less than 5% of total session CPU time spent. Output results in text format into
ora_1234.txt file:
$ orasrp --disp=statements --binds=2 --sort=totcpu --thresh=5 -t ora_1234.trc ora_1234.txt
Analyze data from compressed trace3.trc.gz
$ gzip -cd trace3.trc.gz | orasrp - report3.html
Analyze trace4.trc which is located on remotehost.
remotehost$ perl proxysrp.pl
localhost$ orasrp -r remotehost trace4.trc report4.html
In short (list not complete):
- Written in D programming language.
- Works faster (about two times) and spends less memory.
- Display events histograms.
- Recognize idle 'SQL*Net message from client' events.
- Display 'datafiles read statistics' and 'parallel execution statistics'.