top of page
  • Writer's picturekyle Hailey

Best Oracle Peformance Tools?

Haven’t had time to check this link out but looks like some interesting tools:

Link from Yong Huang on sql command tools like Toad:

  1. Tools – LTOM, OS Watcher, & HangFG

  2. 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?) pricewould I use it ?db perfmulti-dbsql tunenotesSpecialty10046.plfreeyesxsql trace  10046 parserorasrpfreeyesxsql trace analyzer10053 Viewer freeyesxreplace .doc with .zip and unzipsqltxplainfreeyesx215187.1: SQLT (SQLTXPLAIN) – Enhanced Explain Plan and related diagnostic information for one SQLxplan freeyesxextend explain plan info from Alberto Dell Eraxtracefreeyesxjava trace file viewer from Alberto Dell Eralatchprof freeyesxTanel’s latch collection querywaitprof freeyesxTanel’s wait collection querytvdxstat freeyesxTrivadis Extended Tracefile Analysis Toolspviewerfreeyesxthis looks interesting for statspack/AWR viewing and comparisonawr formatterfreeyesxchrome plugin  for AWR browsing – see screencast perfmon.shfreeyesxscript to combine top with top session in same outputsnapperfreeyesxTanel’s command line performance analysismoatsfreeyesxcharacter mode top activity screen !Method-R tools (Cary Millsap)??yesxsql trace analyzer, other stuff,mrkew, mrkew examplesI’ve never had access to these tools but from what I can gather they should be awesomeDBAemlitefree/$256yesxsuper cool lite weight tool that replace OEM – ie DBA info in a web browserAshviewerfreeyesxcollects ASH data itself or uses v$active_session_history – pretty coolASHMonfreeyesxreads ASH data (requires license) unless SASH is installed and pointed to

See BASH as wellMyOrafreexxNever tried it. Looks fast and covers broad functionalitybut UI looks busyMumbaifreeyesxintegrates snapper and orasrptorafreeyesxseems like a cool tool – haven’t really used it myself but it’s been around a whileRichmonfreexLab128$500/seatyesxxDB Optimizer$1500/seatyesxxvisual sql tuning diagramsQuest Spotlight$1000/cpuxxxQuest SQL Optimizer$1690/seatxEnterpriseQuest Performance Analyzer$3000/cpuyesxxOracle Diag Pack$5000/cpuyesxxOracle SQL Tuning Pack$5000/cpuxxConfio ignite$1500/core? xxxPrecise Indepth I3$2000/cpu?xxMonicle??other stufffourth elephantDBtuna

(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 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:

12 views0 comments


bottom of page