Best Oracle Peformance Tools?

January 12th, 2011

What tools do you use? What are your favorites and why? As someone who has been working  Oracle performance tools for the last 15 years, I’m always interested in what the best tools are.  Here is my list of tools that I’m aware of (what other tools are out there?)

price db perf multi-db sql tune notes
orasrp free yes sql trace analyzer
snapper free yes x command line performance  analysis
hotsos tools ?? sql trace analyzer, other stuff
ASHMon free yes reads ASH data (requires license) unless SASH is installed and pointed to
Mumbai free yes x integrates snapper and orasrp
Richmon free x
Lab128 $500/seat yes x x
DB Optimizer $1500/seat yes x visual sql tuning diagrams
Quest Spotlight $1000/cpu x x x
Quest SQL Optimizer $1690/seat x
Quest Performance Analyzer $3000/cpu yes x x
Oracle Diag Pack $5000/cpu yes x x
Oracle SQL Tuning Pack $5000/cpu x x
Confio ignite $1500/core? x x
Precise Indepth I3 $2000/cpu? x x
other stuff
fourth elephant

(some of the prices I got off of programmers paradise, others are word of mouth, so any clarifications on prices would be appreciated)

The tools marked “yes” are ones that I use or would use.  I would use Quest’s Performance Analyzer given the chance. I’ve never used it but from the demos of the tool, I like what I see.  I have never used Mumbai, but plan to and like again what I see in the demos.

All the other tools marked “yes” I use.

All the tools except “other stuff”, I consider reasonable tools for one job or another. Some are better than others in different ways. The “other stuff” tools I don’t see much point in.

I generally consider OEM with diagnostic pack too expensive and slow, but hey, if customers have it, I’ll use it ! (I better like it as I designed the performance page and top activity page ) Plus diag pack comes with v$active_session_history and all of AWR which I can query directly with OEM.

I tried to design “DB Optimizer” to be simple to install on my laptop and just point at databases and immediately start monitoring and tuning.  The biggest advantage of “DB Optimizer” over other tools, for me, is the  Visual SQL Tuning diagrams.  Other nice features are being able to load test code by multiple concurrent sessions.  With concurrent sessions running code examples, I can easily demonstrate issues that arise in the database with the profiling page.

But for absolute ease of install and speed, I have to hand it to Lab128. Lab128 runs fast, runs it’s own ASH collection (which doesn’t require diag pack) and has the option to read v$active_session_history as well.

Though Lab128 is fast, collects ASH info and can monitor multiple databases simultaneously for days on end, it still doesn’t allow me a real easily accessible centralized database of performance data. That’s why I created “S-ASH” which can collect ASH data from multiple databases into a central repository where I can run analytic queries or visualize it with ASHMon.

ASHMon and  S-ASH are still rough pieces of code as I spent the last 3 years working solely on DB-Optimizer.

Script libraries

Karl Arao
Tim Gorman

I’m not so much addressing alerting systems or general NOC montioring systems but those are of interest as well.
Big Brother
Big Sister


Other stuff

10053 Viewer (replace .doc with .zip and unzip)

xplan – extend explain plan info from Alberto Dell Era

latchprof - Tanel’s latch collection query

waitprof - Tanel’s wait collection query

tvdxstat - Trivadis Extended Tracefile Analysis Tool

spviewer this looks interesting for statspack/AWR viewing and comparison

enteros does some statspack/AWR trending and other monitoring

I wonder how many smaller companies are out there with Oracle monitoring tools.

Question – how do you graph data from Grid Control and DB Control? Some nice links from Karl Arao:[[Performance%20Formulas]]

Kyle Hailey

  1. Trackbacks

  2. No trackbacks yet.

  2. Craig
    January 12th, 2011 at 22:21 | #1

    A few more that I use:
    A couple of Tanel’s other scripts: latchprof and waitprof
    A Hotsos script for quickly reporting on data selectivity: hds
    Christian Antognini’s TKPROF replacement: TVD$XTAT

    I am not sure those count as true “Performance Tools”, but they are definitely helpful when working through performance issues.

    Very interested to see what other people have found useful.

  3. Kyle Hailey
    January 12th, 2011 at 22:32 | #2

    Hey Craig -
    good info. Latchprof is awesome. I’ll have to go back and modify the chart with people’s input.
    That reminds me, there is Alberto Dell’era xplan,
    which is pretty cool.
    - Kyle