Best Oracle Peformance Tools?
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/
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/
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:
Excel part starts about 1/2 way though the slides):
Oracle L
Trackbacks
Comments
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.
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
Kyle, please add rss feed for new entries on your new wp site. Now it’s possible only to subscribe comments feed :(
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
@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).
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
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)”..
Hi Yong – thanks for the pointer. I added perfmon.sh into this list
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
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.
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
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.
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
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!
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
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
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.
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
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
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
this looks quite cool: http://jagjeet.wordpress.com/2012/09/20/sql-dashboard-v2/