Thursday, November 19, 2009

OEM Oracle Enterprise manager graphs are great

The title says it all.

I am really coming round to Oracle Enterprise Manager, OEM, for use in monitoring and troubleshooting performance. I'm with Doug Burns here, who once did a presentation on how he learned to love pictures.

OEM is maturing
into a
and indispensible

I cannot express my appreciation for OEM enough.

OEM is the best improvement in performance monitoring in the last decade. The link goes to a jubilant pressrelease by Oracle. And I really hope the search engines will pick this up. I also need to be seen to praise oracle to keep my status in the sponsored community...

the blog-aggregator

*PdV peeks over the edge, then looks over shoulder - twice*

This will not get me credit with Vendors...

Oracle-OEM, Quest, and Symantec all have competing commercial products,
but today, I am plugging ... :

And to a lesser extent, Lab128 (which is effectively free as well).

(If those two are old news to you, I apologize, just close the tab and go click elsewhere)

I will admit that the commercially licensed products are much better.
They are richer in functionality (feature bloat!) then any open-source, free-to-give Tool could hope to be (or are they?). I should also mention that the commercial products often will offer all the goodies, gizmos, spotlights and the key-hangers that my kids like so much. I would never pass up on the free parties, eh, salesevents Seminars with food and refreshments. In that area, commercially licensed product are unbeatable.

But for Sheer Simplicity, for Acutal "Value" I have used TORA for nearly 10 (ten!) years now. My trusty 1.3.8 version dates back to 2001. I always used the free version worked fine on oracle 8, 9 and 10. It still works on 11, and I'm sure that when an 11.2 "problem" pops up at one of our customers, that even the old 1.3.8 version will show my the wait-stats.

Since Quest (bless them, they are the makers of Toad), claimed to have bought TORA in eh... 2003, I had not checked for TORA updates.

But now Timo Raitalaakso (Rafu on OTN, blog can be found Here) from Finland told me TORA is still around.
Version 2.0.0 is available.

Go and check it out at sourceforge, and go find a few references on google...

Then download it and Test It (it is totally for free - Wayhey!). If TORA is right for you, you will become addicted quite easily.

My main reason for having TORA is the server-tuning screen called "wait-events". It offers a free, and quick-to-use alternative to the OEM waitevent graphic.

I can run TORA without need for permissions on OEM (acutally, without the OEM period), and it gives me roughly the same screen. All I need is "PERFSTAT" or any user with sufficient privs (the "advisor" role is fine). If I can run spreport or awrrpt, I can generally also run TORA.

It just takes a tns-entry, and Oracle-Net connection, and then TORA is up and running. Mine runs from my company-windhoze. But there are Linux and Mac builds too.

I combine TORA with some servertool sar, vmstat (unix), lparstat (AIX) or taskmgr (windhoze) and with statspack (AWR for the licensed-lucky).
With those tools, I have solved just about every performance-problem I was given.
(admittedly, my problems tend to be simple - but I'm waiting for the first SOA/SOAP system to overload).

Inside TORA, I run the "server tuning" screen at 10 sec interval (I only use the 3rd tab with the graph and the pie-chart of the wait-events). I tend to disable the other screens, not just to save on sqlnet traffic, but also to keep my windows-lappy running happy.

Click to get larger pic of my favorite screen...

And Voila, that screen (which predates and resembles the OEM graph...) tells me what is happening right now, and in the last hour. In using it, I have a few dislikes and a wishlist too, but I'll keep that for another time.

Tip: I often configure 12 gridlines and 360 datapoints to have a 5 min grid on 1 hr of data.

I am still of the opinion that real DBA's use SQL*Plus, common sense, and not much more. But if you are into troubleshooting databases, you could do worse then look into these tools.


Rafu said...

Thank you for pointing out the monitoring possibilities. I did not notice those at the point when I used the tool, before Quest purchased. As at the time, I was mainly dealing with empty development databases. This is just the thing I will need in the near future. Not all have diagnostics pack purchased. Be sure to have enough privileges going to those pages, missing select privilege to v$tables result to an error loop. After noticing the new version I have tried some sql writing capabilities and been a bit disappointed as the tool has crashed on a merge statement and explaining some not so trivial queries. 2.0.0 TOra is drawing the wait events graphs nicely from 11.2 database. 2.1.1 version seems to be available also to MAC. It might worth getting familiar with Qt and try to compile the new version also to some other platform. Tanel Poder’s PerfSheet does not have the automatic sliding window ability or I have not found that yet, but it is also a nice tool for visualizing self made query data.

PdV said...

Hi Rafu,

Thanks for an Interesting comment.

The monitoring screen is about the only screen I have used on all those years of TORA.

Only in emergencies have I used the sql-windows or the object browser, as TOAD and recently
sql-developer are OK-capable in that area. And all my sql-code goes into notepad + SQL*plus before passing Quality-Control.

For Explain-plans: nothing beats SQL*Plus for two reasons:
- it is (nearly) always there (and free).
- it gives you the v$sqlplan view (and since v10: the dbms_xplan.display_cursor).

The explain-plan-for and the plans from tkprof have bitten me a few times with wrong plans, as recently re-documented by a few bloggers. I thought that was relatively well known quirk as plans from tkprof and explain-plan necesarily come from different sessions then the original problem-causing connection, but apparently not many ppl kept that in mind.
Hence: Trust but Verify, and verify especially if some tool tries to explain-plan your query. The truth is in v$sql_plan, and nowhere else (and verify even that..).

Doug Burns said...

Ha! I thought at first you were just going to agree with me without any further comment, but that would have been boring ;-)

For the record, I'm finding OEM's performance pages *more* useful every week although, like all software, it does have some failings. But thanks very much for highlighting the non-Oracle alternatives. I've used TOra on and off for a while too, but it seemed to be dying a slow death. I'm glad to hear otherwise and will check it out soon. The thing I always liked about TOra was that it wasn't Windows-only.

As for TOAD ... much as I like a lot of what it does and use it every day on-site, I keep hearing people making claims about performance problems that are completely wrong when they've been looking at TOAD. It's started to become my first question - 'are you looking at this in TOAD?'. Of course, the problem is a combination of the tool and the person using it, but I've heard too many misleading claims now to think it represents the information in the right way.

P.S. I'm *still* doing that presentation ;-) Unfortunately it seems to be different every time but I'll be doing it next Tuesday at UKOUG in Birmingham.

mwidlake said...

Until recently I thought OEM had an advantage that it was always available at any decent site. {Some sites specifically don't allow the likes of toad or tora}.

That belief has recently been shot to pieces as I have been struggling at a big site that does not use OEM. Or toad. Or in fact any decent database administration package...
SO it was back to SQL*Plus, my trusty scripts and statspack. That should please you Piet :-)

However, I am forced to agree with Doug (it's painful) that life with AWR/ASH and all those graphs and links in OEMS diagnostics pack can do in minutes what it takes me hours to do with SQL*Plus and scripts.

My painful site has seen the light, however, OEM is on it's way...

PdV said...

Hello Doug and Martin.

You are both correct of course, and even I am warming to OEM. Of course I am an avid fan of AWR, with or without OEM. But I will plug the alternatives, TOra and Lab128, for several reasons:
- keep some pressure on Oracle. OEM and Diagnostics should be freely available, and Oracle need to keep up on the ease-of-use and features.
- Sometimes OEM isnt available for reasons of license or local-politics. In that case, I revert to TOra or whatever is available to me.

And, as Doug points out re-TOAD, you need to know what you are looking at. A good tool in the wrong hands is generally risky (topic worthy of a blog too).

I might re-visit the TOra-tool with some suggestions (and of course, OEM or Quest may pick up some of those).

PS to Doug: I know that presentation of you is good!
Myself, I will give a critical expose of the merits of CBO.
Never thought I'd do that, but felt compelled to get my simple view on CBO on air - Wednesday 02 Dec, Birmingham (mental note: bang some drums, blow some trumpets, and write/blag/twat more plugs for my prezzo - I'm standing next to two hotshot-presentors for that one)

Danielle Felder said...

Great article! Your readers may also find real user reviews for Oracle Enterprise Manager, TOAD and all the other major database management tools on IT Central Station to be helpful:

The IT Central Station user community ranked OEM the #1 tool in Database Development and Management. This Lead DBA writes, "We checked Quest software (Spotlight and Toad) however we found OEM to be more productive." You can read the rest of his review, as well as explore what others have to say about OEM, here:


This is the footer...and this should be small text for disclaimers and the like. and some small stuff

Locations of visitors to this page And this text is placed next to the map. we could possibly hide some stuff here too for the benefit of the search-engines and if it is at color ffffff cam we put all sort of rubbish that we do not want readers to see. travel itinirary reservation ticket agent flight plane boarding attendant train connection rail ticket wait time booking flight boardingtime taxi ramp luggage suitcase trolley wheely laptop bagpack corpaorate wifi connection oracle. it will also be interesting to see what happens when this wrap around. or even if we put in spherus and worwood as additional word.