Best Oracle Performance Tools

September 13th, 2013


 

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.

For the past 4 blog posts I’ve reviewed some tools I like such as Method-R tools, Confio, Lab128 and EMlite.

There are other tools that I especially like and are totally free such as Tanel Poder’s Moats and snapper which are two important tools for a DBA to know about.

Here is my list of tools that I’m aware of (what other tools are out there?)

 

price would I use it ? db perf multi-db sql tune notes
Specialty
10046.pl free yes x sql trace  10046 parser
orasrp free yes x sql trace analyzer
10053 Viewer free yes x replace .doc with .zip and unzip
sqltxplain free yes x 215187.1: SQLT (SQLTXPLAIN) – Enhanced Explain Plan and related diagnostic information for one SQL
xplan free yes x extend explain plan info from Alberto Dell Era
xtrace free yes x java trace file viewer from Alberto Dell Era
latchprof free yes x Tanel’s latch collection query
waitprof free yes x Tanel’s wait collection query
tvdxstat free yes x Trivadis Extended Tracefile Analysis Tool
spviewer free yes x this looks interesting for statspack/AWR viewing and comparison
awr formatter free yes x chrome plugin  for AWR browsing – seescreencast
perfmon.sh free yes x script to combine top with top session in same output
snapper free yes x Tanel’s command line performance analysis
moats free yes x character mode top activity screen !
Method-R tools (Cary Millsap) ?? yes x sql trace analyzer, other stuff,mrkewmrkew examplesI’ve never had access to these tools but from what I can gather they should be awesome
DBA
emlite free/$256 yes x super cool lite weight tool that replace OEM – ie DBA info in a web browser
Ashviewer free yes x collects ASH data itself or uses v$active_session_history – pretty cool
ASHMon free yes x reads ASH data (requires license) unlessSASH is installed and pointed toSee BASH as well
MyOra free x x Never tried it. Looks fast and covers broad functionalitybut UI looks busy
Mumbai free yes x integrates snapper and orasrp
tora free yes x seems like a cool tool – haven’t really used it myself but it’s been around a while
Richmon free x
Lab128 $500/seat yes x x
DB Optimizer $1500/seat yes x x visual sql tuning diagrams
Quest Spotlight $1000/cpu x x x
Quest SQL Optimizer $1690/seat x
Enterprise
QuestFoglight /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 x
Precise Indepth I3 $2000/cpu? x x
Monicle ??
other stuff
fourth elephant
DBtuna

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 !  Plus diag pack comes with v$active_session_history and all of AWR which I can query directly with OEM.

On Enterprise tools I like this quote form http://www.infoworld.com/d/data-management/can-quest-foglight-hang-big-boys-601. It’s 5 years old but not a lot has changed since then:

The lack of decent tools to manage your databases on an enterprise level is just staggering. It’s hard to believe that nobody can cross the finish line on delivering a monitoring solution.


This looks interesting :http://windows.podnova.com/trends/oracle_performance_trace.html

Tracefile analyzer tools: http://ba6.us/node/177

this looks quite cool: http://jagjeet.wordpress.com/2012/09/20/sql-dashboard-v2/

another tool to check out http://dbspeed.com/product.html

Link from Yong Huang on sql command tools like Toad: http://yong321.freeshell.org/oranotes/ToadAndAlternatives.html

Tools that come with Oracle support:  http://blogs.oracle.com/Support/entry/support_tools_for_performance

  • Tools – LTOM, OS Watcher, & HangFG
  • Scripts – SlqTExplain, TracAnalyzer, PL/SQL Profiler

Login to My Oracle Support, Click the Knowledge Tab, On the left, Tools/training > Diagnostics

Image from 

BUR1mfgCcAAny-n.png-large

Horse Racing at Track


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. Austin Hackett
    September 14th, 2013 at 12:53 | #1

    Hi Kyle

    “oratop” might be one to add to the list of Oracle Support provided tools.

    See “oratop – utility for near real-time monitoring of databases, RAC and Single Instance (Doc ID 1500864.1)”

  3. khailey
    September 19th, 2013 at 22:05 | #2

    I’m excited about trying this at some point, a redo log walker!!
    http://www.databaseforensics.com/redowalker_for_oracle.htm

  4. Fred Pétillot
    October 23rd, 2013 at 07:21 | #3

    Hi Kyle,
    I stumbled yesterday on ASH viewer (http://sourceforge.net/projects/ashv/) and I was quite impressed. Open source Java with an OEM/Precise reporting look.
    Cheers
    Fred

  5. khailey
    October 24th, 2013 at 22:04 | #4

    Hi Fred,
    Thanks for the link on ASH Viewer. Looks cool!
    - Kyle

  6. Arpan
    March 18th, 2014 at 08:28 | #5

    Is there any tool, where I will write an SQL query and it will automatically return me the most optimized query based on my time/ IO cost requirement ?
    I am not asking about explain plan and all where I have to make a trial and error to get the best time saving query written by me own.
    Please reply and forgive my unawareness regarding this.
    - Arpan

  7. Tony


7 + = ten