jump to navigation

I am Neo off the Matrix (apparently) March 30, 2011

Posted by mwidlake in AWR, performance.
Tags: , ,
trackback

I know I have mentioned it before, but I am a big fan of the OEM performance screens that are derived from the ASH/AWR information. One of the things I really like about it is the immediate information it gives you, in one glance, that things are “not normal”. Once you notice that things are not normal you can then, within a few seconds, get a feel for what is still probably OK and where you have something that has changed.

As an example of the immediate information, I recently came back to my desk and glanced at my OEM performance screen. It was showing the below:

This will not be an interesting picture to you, but to me it tells me a lot about my system

“data load has just ran” I said to my comrade-in-arms. “which one?” he asked. “The Delta – It ran the quick plan. But it started a bit late, 12:15. Oh, and looks like the transaction view code has swapped back to the full table scan plan and the summary code is not playing up at the moment.”

“you’re turning into Neo you are – can you see a lady in a red dress???” he asked.

That was of course a reference to the “Matrix” films where at times you see the virtual world displayed on a screen as a stream of characters running down the screen – but once you get used to it you can apparently “see” what is going.

The screen shot above is not even actually a very good example of what the performance screens can show you. One of my minor complaints about the performance screens is that it scales to show the greatest of the largest peak or a number of sessions to match the number of CPUs (real or fake) that are available to you. So if you have more CPU available than you need, you can’t see much detail in the graph. And if you have had a nasty peak of activity, again, all detail is squeezed out. In my case, the box is sized to cope in 12 months and the system is new, so activity is scuttling along the bottom of the graph.

However, “poor” though the example is, it told me what was going on across my system at a glance, something about the major tasks we are running, that one problem is currently occurring and that several of the other issues I need to keep an eye out for are not occurring.

That is why I love these screens – I recognise “my” activity patterns from the graph, I now recognise the SQL IDs for my key statements. If I see a pattern in the graph I don’t recognise, I need to check things out immediately. Three or four times over the last 2 weeks I have spotted an issues, started investigating and found out the cause before the Operations desk has even noticed an issue.

Oh, and what is SQL type 189? It is a merge statement. Our implementation of OEM is a little old, it does not correctly interpret that SQL command type. It might be a little old, it is still a lot useful.

About these ads

Comments»

1. rnm1978 - March 30, 2011

Hi Martin,

Completely agree about the scale problem – check out this illustration of it: http://rnm1978.files.wordpress.com/2011/03/snag-2011-03-30-11-53-50-0000.png
Without those two spikes of 140 sessions with concurrency waits, it would be a lot clearer that CPU usage is actually pretty high at some points, rather than the fraction that it appears on the picture.

But apart from that, OEM graphs are teh awesome :) Particularly drilling through to Monitored SQL Executions and looking at the SQL active reports from there.

Cheers, Robin.

2. coskan - March 30, 2011

Martin,

which version is this ?

I remember for merge I saw “upsert” on 11GR2 but it maybe related with how the merge is defined.

mwidlake - March 30, 2011

Hi Coskan,

The underlying DB is 10.2.0.4, I’ll have to check the OEM version (away from the office today) but the age of the DB version indicates how cautious this client is in moving forward – nicely behind the curve but not by 6 years.

3. Neil Chandler - March 31, 2011

Martin,

We have a 50″ LCD on the wall with that page from the 8 most important databases on it (displaying 4 at a time, on 2 refreshing screens). It’s invaluable.

Underlying infrastructure issues show up as simultaneous problems.

You learn how the systems generally look, and the absence of peaks can show as much of an issue as an abundance. The warehouse not going “big blue mountains” at 7pm would worry me greatly. :o)

mwidlake - March 31, 2011

We had the same at a site I was at a few months ago and it was what I looked at when I arrived, what I looked at before I went and what I looked at way too often during the day. It works wonders for allowing the DBAs to respond quickly. Managers also like it as it gives them a nice feeling of everything is being managed, but boy they get upset if there is some red on the screen.

Don’t you just love the fact that in Oracle RAC world, the blue mountains over the Green sea have nice pink snow on top? :-)

4. Dom Brooks - March 31, 2011

I don’t remember Neo being quite that vertically challenged…

mwidlake - March 31, 2011

OI! Watch it, else I’ll try my Kung Fu on your ankles! (or just add extra “foam” to your pint next time in the pub)

5. Log Buffer #215, A Carnival of the Vanities for DBAs | The Pythian Blog - April 1, 2011

[...] and more experts are highlighting the importance of having immediate performance information from OEM, and Martin Widlake posts another blog about [...]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 166 other followers

%d bloggers like this: