I Love ADDM

Some features in this post require a Diagnostics Pack license.

I'll get back to adaptive thresholds at some point but something's been bugging me.

I'm not just trying to be controversial but I've a feeling I'm about to be, given that ADDM is the probably the most infamous of the 10g 'Automatic Tuning' features. What should be the beautiful swan of those features has somehow become the unloved, barely-mentioned ugly duckling.

One of my favourite running lines on a mailing list is that whenever a performance problem crops up, one of the participants will always say 'So what did ADDM say in this case?' or something similar. It's funny because ADDM is barely mentioned and people seem to need reminding that it exists or maybe they just don't like it? I suspect it's the latter. Maybe there are psychological factors at play here? Even the people who turn up at the courses I teach, who are obviously interested in the subject, or those good people at the good sites like the one I've working at at the moment all seemed focussed on AWR. ASH and ADDM don't get much of a look in, 5 years after they were released. Maybe people like AWR because it's so similar to Statspack and they're used to Statspack?

However, I keep coming across different examples where Automatic Database Diagnostic Monitor has identified what's causing a performance problem. That's not the same as fixing it, of course, but I've watched people flailing around making a variety of guesses, looking at ASH data or debating AWR reports before getting round to thinking - 'Why don't we just see what ADDM has to say. Just out of interest.' (The truth is I often prompt that thought ) This has happened many times in recent months and lo-and-behold, ADDM either formed the same correct conclusions in a fraction of the time people had been 'analysing' things or found things that the initial analysis hadn't.

It stands to reason. If you think about how you analyse performance, don't you find where sessions are spending their time and identify the causes? Don't you see which SQL statements are running for long periods of time or are consuming most resources? Don't you identify those segments which are most active? I could go on. The fact is that every sensible performance analysis method I'm personally aware of could be reduced to looking at where time is being spent and focussing on the most significant contributing factors. Which is all that ADDM does, too.

In case I seem like some deluded fan-boy, let me highlight some of the things I don't like about ADDM.

1) I haven't seen this problem for a while and I suspect it's because of improvements on the earlier releases, but ADDM used to suggest frequently that CPU was not a bottleneck when it clearly was.

2) What is it with it's constant suggestions to increase memory areas? You could say maybe that's because I'm configuring too little memory all the time, but I've talked to lots of people about this and worked on lot of systems and everyone I know experiences the same. Somewhere down the bottom of those recommendations it'll tell you to increase SGA and PGA memory. So you do. So it asks again. So you do ... I'll save you weeks and months of tedium and just point out that this can go on for a very long time and in very small increments. Rare is the ADDM report that doesn't suggest increasing some memory area or other.

I don't think that ADDM's recommendations should be followed slavishly though. I'm much more interested in using it as a tool that will analyse where time is being spent and show me the big-hitters. I'll always be interested in it's advice, too, but I'd be extremely unlikely to click to implement any of it's recommendations without some serious thought. I usually confirm what it's telling me through my own experience and ASH and AWR data. In fact, one of my favourite games when I have the time is to leave the ADDM analysis until after I've analysed the problem myself. Believe me, my ego is big enough to cope with the large percentage of times that ADDM ends up reporting what it took me 30-100 times as long to work out (If that makes me sound slow, remember that ADDM has already done the job and maybe took a minute)

For example, at work last week there were some serious performance problems with the overnight batch. Unfortunately we didn't find out about them until the next day so we needed tools suited to analysing problems in the past. There are quite a few options, most of which I had no access to, so I requested the privileges and that the DBAs run some reports for me while I was waiting. I found myself looking at ASH reports and comparing AWR reports for nights where performance was acceptable and unacceptable. I could identify a couple of things in the AWR reports - the auto stats gather job seemed to be running during the second night and the single block read time averages were double what they usually are. But it took a little while to spot these anomalies because the reports looked very similar even though they were for identical time periods but completely different workload (maybe more on that another time, just to prove I understand that all tools have some weakness or other). Eventually the privileges came through and I was able to look at the ADDM runs for the periods covering the batch schedule. ADDM spotted the same couple of issues in a fraction of the time and picked out a few SQL statements that I could focus on, one of which was part of a new release.

OK, so then some serious tuning started, but if I'd used ADDM in the first place, there was probably an hour or two that I could have used for tuning instead of analysing. Of course, it often highlights a bunch of SQL statements to focus on, so how has that helped me with solving the problem? Let me turn that around. How often is a performance problem down to a few SQL statements that need tuning? Even in the case of the application I'm working on at the moment, where the design is all wrong, what I'm really interested in is the impact of that design on system performance. ADDM won't help me redesign it, but I can assure you that it's highlighting the negative impact of the original design decisions.

What really gets me about this is that you're paying the overhead anyway and if you have Diagnostics Pack licences (which I'm finding more people do these days) why on earth would you choose to ignore further confirmation of your own analysis, if nothing else. Is there something about using ADDM's analysis which somehow undermines our performance egos? LOL

If you're in the 10g/Diagnostics situation, just do me a favour and at least just look at the relevant ADDM runs when you've experienced a performance problem in the past. It's not hard and, with my restricted privileges and limited server access at my current site, it comes down to this. (You don't even need Grid or DB Control.)

1) Get the ADVISOR privilege.

2) Look in DBA_ADVISOR_TASKS and the ADDM runs will be there. The work's already been done.

select task_name, execution_end 
from dba_advisor_tasks 
where advisor_name='ADDM' 
and status='COMPLETED' 
and owner='SYS' 
order by execution_end desc;

3) View the report.

select DBMS_ADVISOR.GET_TASK_REPORT('<task_name from previous query>', 'TEXT', 
	'TYPICAL', 'ALL', 'SYS') 
from dual;

If the recommendations turn out to be complete rubbish then I promise I'll be very keen to hear about that. If not, then maybe people will start taking ADDM a little more seriously and look at it on it's merits without starting off from the biased view-point that an Oracle tool couldn't possibly be as good at this stuff as you are

Really, if you're paying for it, why aren't you looking at it?