Best Oracle Peformance Tools?

January 12th, 2011

Haven’t had time to check this link out but looks like some interesting tools: http://windows.podnova.com/trends/oracle_performance_trace.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


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 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 – see screencast
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,mrkew, mrkew 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) unless SASH is installed and pointed to

See 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
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 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 ! (I had better like it as I designed the performance page and top activity page , though much of what I wanted didn’t make it in) 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. ASHViewer  is a tool that will also read ASH data from Oracle and/or collect ASH on it’s own, which is awesome. ASHviewer can also be hooked up to S-ASH repository with some minor changes. Here are the minor changes (thanks to Marcin Przepiorowski) 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.


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. zabbix Big Brother Big Sister Xymon Nagios ground work zenoss Ganglia


Question – how do you graph data from Grid Control and DB Control? Some nice links from Karl Arao:


Check out Charles Hooper‘s  blogs on connecting to Oracle and charting data:

Excel part starts about 1/2 way though the slides):

Examples from my Charles’  blog:
Web based graphical statistics monitor – driven by a VBS script, but can also be driven by Excel – simulates part of a program that I wrote:
Another web based statistics example that works with the time model statistics and wait events – driven by a VBS script, but can also be driven Excel:
Example of building Excel charts on demand using data from Oracle:
Auto-scrolling charts in Excel:
Shows how to create a UserForm in Excel and display that UserForm using a VBS script:
Windows Vista and Windows 7 gadget:


Uncategorized

  1. Trackbacks

  1. Comments

  2. 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, http://www.adellera.it/blog/2009/08/07/xplan-20/
    which is pretty cool.
    - Kyle

  4. Sedinho
    January 20th, 2011 at 11:46 | #3

    Kyle, please add rss feed for new entries on your new wp site. Now it’s possible only to subscribe comments feed :(

  5. Kyle Hailey
    January 20th, 2011 at 15:14 | #4

    AFAIK rss feed should be working fine. I have it linked into my facebook account.
    I have aggregated into the oaktable:
    http://www.oaktable.net/feed/blog-rss.xml
    as well.
    You can see it here
    http://dboptimizer.com/?feed=rss
    let me know what kind of issue you are seeing.
    - Kyle

  6. Sedinho
    January 21st, 2011 at 14:26 | #5

    @Kyle Hailey
    Thanks for link. Works great. I had old: http://db-optimizer.blogspot.com/feeds/posts/default and couldn’t find new on this site (except comments feed at the bottom of page).

  7. April 6th, 2011 at 13:15 | #6

    Hello Kyle,

    during the last years I worked in parallel to my project work on a GUIbased trace analysis tool for Oracle named QueryAdvisor. Maybe you could be so kind and take a look independent it is far from beeing complete. So many stuf could and will be added in the near future. In addition maybe you are interested beeing part of the betatest group I’m searching dbas for?

    Kind regards
    Frank

  8. Yong Huang
    July 16th, 2011 at 15:31 | #7

    Kyle, I run my simple perfmon.sh script on my database servers:
    http://yong321.freeshell.org/computer/Perfmon.html
    One advantage is to correlate `top’ with Oracle sessions. One log per day and auto rotates per month “(log file named xxx day of month.log)”..

  9. July 16th, 2011 at 16:12 | #8

    Hi Yong – thanks for the pointer. I added perfmon.sh into this list

  10. July 18th, 2011 at 14:08 | #9

    Here’s an article to the Performance Tuning Tools that are included with a customer’s Premier Support investment.
    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

  11. July 18th, 2011 at 14:33 | #10

    Hi Chris, thanks for the link. I included it as well at the beginning of the post. I’ll have to go through these as some point.

  12. July 19th, 2011 at 11:25 | #11

    Hi Kyle,
    thank you for this collection! I really appreciate the time you took for it!
    Unfortunately, one of the links (at least) leads to an
    404 not found error
    waitprof.sql
    http://www.tanelpoder.com/files/scripts/waitprof.sql

  13. July 19th, 2011 at 14:28 | #12

    Thanks Uwe – looks like Tanel changed the location of the script. I don’t see a direct link to the script anymore on his site, so I created a local copy using a version I had. The link should work now.

  14. October 21st, 2011 at 19:25 | #13

    Hi Kyle

    Please look at some more below and consider adding them in your list:

    * AutoFocus Analytics from tierdata.com
    * MoreVRP from morevrp.com
    * Performance Analysis of Quest
    * Insider for Oracle from fourthelephant.com
    * DBArtisan Performance Analyst from embarcadero.com
    * Teamquest software from teamquest.com
    The last one is used to monitor servers but it has (free) plugins for the most popular enterprise software (Weblogic, DB2, Oracle, etc). Teamquest will not show you which SQL query is at the top at the moment. It rather shows diagrams/charts of some values and its trends.

    – Kirill Loifman, dadbm.com

  15. October 21st, 2011 at 21:25 | #14

    Hi Kirill,

    Some of these tools are in the list some are not. For these tools could you give any feedback on why they are good, different or what you like in them?
    MoreVRP has some good visualizations but lacks an overarching performance approach as in OEM, DB Optimizer or Performance Analyzer from Quest. You mention Perf Anal from Quest and it is already in the list. It’s a neat tool with the main drawback being it requires binaries for the target databases, and Quest seems to have taken much of the info off the web. Maybe they have rolled Perf Anal into Foglight? For Insider from fourthelephant it’s also already on the page above, but frankly I don’t see it as adding anything in the market place. It seems to be a Quest Spotlight rip off and the Spotlight approach, though good 10 years ago, is now outdated and the efficient approach is more visualization of system load as in OEM 10g, and as Quest has done in Perf Anal. DB Artisan from Embarcadero is a DBA tool. The Embarcadero performance tool is DB Optimizer which is already on the list above.
    Autofocus looks interesting but didn’t see enough on their web site to see what differentiators it may have. For Teamquest I couldn’t get enough info form the web site either. Any info you have would be appreciated.
    Thanks for adding to the list!

  16. November 4th, 2011 at 15:59 | #15

    My update.
    - see a brief info about Teamquest:
    http://www.quintica.co.uk/web/main/download/oracle.pdf
    - I mentioned Insider since they have a free plugin for Oracle SQL Developer that gives you a nice overview on page (similar to spotlight)
    - another tool is HP OpenView Interconnect (OVI) database minitoring with their Oracle plugin DB-SPI (see some info here: http://sysdoc.doors.ch/HP/oracle_ref.pdf

    – Kirill Loifman, dadbm.com

  17. November 4th, 2011 at 16:08 | #16

    My main question is the following. Ok, We’ve got a long list of monitoring/PT soft.
    But we have to prioritize probably. Imagine, I have several DB Oracle instances running on diff. platforms (Linux, HP-UX, etc.) and I want a centralized monitoring solution that do 24×7 monitoring & alerting & graphs, etc. for me. Additionally I want a nice GUI for DB performance tasks. So, would you be able to suggest a reliable tool or a set of tools from your list to accomplish my goal:
    a) best free-ware tools
    b) best commercial but not expansive (best quality/price)
    c) Ideally it should manage MS SQL Server also

    If not, I would implement the same with Oracle Grid Control :)

    – Kirill

  18. November 4th, 2011 at 21:24 | #17

    This would make a great poll.

    For Performance tools:
    Free AshViewer, ASHmon/SASH
    For pay for tools cross database platform tuning (Ora,SQLS, DB2, Syb) : DB Optimizer
    For low end Oracle multi-database performance monitoring, and it’s fast: LAB128
    For enterprise monitoring there is Confio and Quest
    (or OEM for Oracle only and yes I know they “have” a SQL Server plugin)

    For admin tools (with minimal performance monitoring)
    Free Mumbai, MyOra, SQL Developer, tora
    Pay for : Toad

    I’d love to hear what others have experienced.

  19. September 7th, 2012 at 20:30 | #18

    another tool to check out http://dbspeed.com/product.html
    haven’t tried it. Might be nice to look at the ASH queries the do.
    The point with DB Optimizer was to have a click and drill down graphical approach which is much more ergonomic than having to track a bunch of SQL statements

  20. Gerrit Haase
    January 24th, 2013 at 14:02 | #19

    Hello Kyle,

    nice collection here!

    I have a question, after some investigation, I found that Foglight offers a lot, but it seems too expensive with its “per host * cores” licensing model, and also the comment by Sean McCown about Foglight not crossing the finish line makes no good feelings about this tool.

    I like the approach of Lab128 better, to pay one license and have a multi-database (real-time-only?) monitor. It seems that Lab128 doesn’t store the performance data.

    How is it with DB Optimizer? AFAIU, it stores the data in the database itself, it is able to help with Tuning as well. Is this also a multi-database solution, so I would need 1 license for me and I am able to monitor all my databases (like 50 or 100 Oracle instances)? Unfortunately, the sales office in Germany was not able to answer this simple question ;)

    Regards,
    Gerrit

  21. January 25th, 2013 at 00:10 | #20

    Hi Gerrit,

    Neither DB Optimizer nor Lab 128 are enterprise monitors. Lab 128 will monitor multiple databases ( at least 10 maybe many more) and store the data in flat file that AFAIK, only readable by Lab128.

    DB Optimizer only monitors 1 database at a time. It too will store the data but in an Oracle database of your choice. To monitor multiple databases you’d have to start up multiple copies of DB Optimizer. My plans at Embarcadero were to make it monitor multiple databases but I wasn’t given the resources to do this.

    So, as I said above, for enterprise database monitoring, Confio seems to be the best, cheapest and most sincere company.
    For a super cool fast light weight desktop tool that can monitor a dozen or more databases at once, Lab128 is awesome
    If you want to do some serious performance tuning drill downs into one database and do SQL tuning, DB Optimizer is the way to go. Of the tools DB Optimizer is the only one to have power SQL tuning features. It’s the only tool in the industry with the Visual SQL Tuning (VST) diagrams.

    - Kyle

  22. July 19th, 2013 at 18:21 | #21


9 + one =