Doug's Oracle Blog

Note - features in this post require the Diagnostics Pack license

[I originally had the first section at the end of the blog post, but then realised I might as well get the bad news out of the way to save you wasting your time if you're not interested]

A small section of the course covers moving AWR data between instances to run the AWR comparison report against different environments.

The Bad News

As I mentioned before, one of Oracle's local Pre-Sales Technical guys was on the course and he approached me at a break and said that he thought that these scripts were only to be used by Oracle Support. When I got home I re-ran the awrextr.sql script and, sure enough :-

sys@GP06PROD> @?/rdbms/admin/awrextr 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
Disclaimer: This SQL/Plus script should only be called under 
the guidance of Oracle Support. 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

So an interesting demo, but only for home experimentation or if Oracle Support are involved and I certainly wouldn't expect to see the script converted to use old exp/imp utilities rather than the Data Pump equivalent, as I was asked during the course. In retrospect, I imagine Oracle might request you run awrextr.sql and send the output dump file to them so they can use awrload.sql to load it for further analysis.

How to move AWR data to another repository

Connect to Source Instance as SYS

@?/rdbms/admin/awrextr

The script will prompt for

1. dbid
2. Number of days worth of snapshots to display for selection (just like awrrpt.sql)
3. Begin Snap
4. End Snap
5. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
6. Dump File Name

Connect to Target Instance as SYS

@?/rdbms/admin/awrload

The script will prompt for

1. Directory Object Name for Data Pump (e.g. DATA_PUMP_DIR)
2. Dump File Name
3. Schema Name for staging schema that data will be imported into (the default of AWR_STAGE is fine)
4. Default and Temporary tablespaces for the staging schema

@?/rdbms/admin/awrddrpi (Note the 'i' which allows you to specify which database/instances to report against)

The script will prompt for

1. HTML or Text report format
   
2. First dbid and instance number
   
3. Number of days snapshots to display for selection from first repository
   
4. Begin and end snapshot for first repository
5. Second dbid and instance number
   
6. Number of days snapshots to display for selection from second repository
   
7. Begin and end snapshot for second repository
8. Report file name
   

When I was working on the course, I noticed this White Paper (PDF) on Oracle 10g Self-Management Framework Internals: Exploring the Automatic Workload Repository. The paper describes the way that MMON selects 1 in 10 of the ASH samples for storage in DBA_HIST_ACTIVE_SESS_HISTORY (I suppose it would be more correct to say WRH$_ACTIVE_SESSION_HISTORY_BL) :-

"The in-memory data that is selected for writing is randomly chosen (sampling of the samples)."

This phrase confused me but, rather than digging around to resolve it (it was late), I took the paper at face value and repeated the phrase during the class the next day. Someone questioned it (as I said before, they were a smart bunch) and I found myself unable to justify it, particularly as it didn't really make sense to me either. So that evening I sent a mail to a couple of people who would know and, as well as getting a quick answer, was able to sit back and enjoy a debate about whether the selection could be described as 'random' or not. The answer first :-

Not random, think MOD(sample_id, 10)

Initially I thought that meant that MMON selects every tenth sample from the ASH buffers^*. That would be the case if the sample_id was incremented every time there was data in a sample but it's incremented even if there are no active sessions. For example, here is a selection of ASH samples from an active system. (Note that a 'sample' includes all of the sessions that were Active at the sample point, so the volume of data and number of sessions in each sample will vary.)

SYS@TEST1020> SELECT sample_id, TO_CHAR(sample_time, 'HH24:MI:SS'), COUNT(*)
  2  FROM v$active_session_history
  3  WHERE sample_id > 1238065
  4  GROUP BY sample_id, TO_CHAR(sample_time, 'HH24:MI:SS')
  5  ORDER BY 1
  6  /

 SAMPLE_ID TO_CHAR(   COUNT(*)
---------- -------- ----------
   1238073 20:57:17          1
   1238074 20:57:18          2

Straightforward so far; there was one active session at 20:57:17 and two at 20:57:18. So let's look at the next bunch of samples that appear in the output.

   1238162 20:58:47          1
   1238242 21:00:08          1
   1238245 21:00:11          1
   1238266 21:00:32          1
   1238278 21:00:45          1
   1238286 21:00:53          1
   1238358 21:02:06          1
   1238371 21:02:19          2
   1238483 21:04:12          1
   1238510 21:04:40          1
   1238527 21:04:57          1
   1238554 21:05:24          1
   1238560 21:05:31          1
   1238563 21:05:34          1
   1238575 21:05:46          1
   1238667 21:07:19          1

Because there were no active sessions between 20:57:19 and 20:58:46, there's no ASH data (as expected), but SAMPLE_ID is incremented every second. If we apply MOD(SAMPLE_ID) to determine which samples should be written to the workload repository, we'd end up with something like this.

   1238073 20:57:17          1
   1238074 20:57:18          2
   1238162 20:58:47          1
   1238242 21:00:08          1
   1238245 21:00:11          1
   1238266 21:00:32          1
   1238278 21:00:45          1
   1238286 21:00:53          1
   1238358 21:02:06          1
   1238371 21:02:19          2
   1238483 21:04:12          1
   1238510 21:04:40          1 << IN AWR   
   1238527 21:04:57          1
   1238554 21:05:24          1
   1238560 21:05:31          1 << IN AWR   
   1238563 21:05:34          1
   1238575 21:05:46          1
   1238667 21:07:19          1

I can check this by forcing a manual AWR snapshot.

SYS@TEST1020> exec dbms_workload_repository.create_snapshot

PL/SQL procedure successfully completed.

SYS@TEST1020> SELECT ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),
  2     DECODE(awr.sample_id, NULL, 'NO', 'YES') in_awr
  3  FROM v$active_session_history ash, dba_hist_active_sess_history awr
  4  WHERE ash.sample_id=awr.sample_id  (+)
  5  AND ash.sample_id > 1238065
  6  GROUP BY ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),
  7     DECODE(awr.sample_id, NULL, 'NO', 'YES')
  8  ORDER BY 2;

 SAMPLE_ID TO_CHAR( IN_
---------- -------- ---
   1238073 20:57:17 NO
   1238074 20:57:18 NO
   1238162 20:58:47 NO
   1238242 21:00:08 NO
   1238245 21:00:11 NO
   1238266 21:00:32 NO
   1238278 21:00:45 NO
   1238286 21:00:53 NO
   1238358 21:02:06 NO
   1238371 21:02:19 NO
   1238483 21:04:12 NO
   1238510 21:04:40 YES
   1238527 21:04:57 NO
   1238554 21:05:24 NO
   1238560 21:05:31 YES
   1238563 21:05:34 NO
   1238575 21:05:46 NO
   1238667 21:07:19 NO

Bear in mind that this is on a very quiet instance running on my laptop so it's possible to get large holes in the AWR data. The reality is that any reasonably busy system is likely to have ASH data for every second and so will have an AWR sample for every ten seconds.

As for the debate, I'm no mathematician, but although the sample time gaps in DBA_HIST_ACTIVE_SESS_HISTORY might appear random in isolation, it seems clear to me that they aren't. I think the term 'sampling of the samples' is perfect, though.

* I suppose it does select every tenth sample really, but there might be no data there.

There have been a couple of very interesting blog postings over the past few weeks from Daniel Fink and Alex Gorbachev, prompted by a panel discussion at last week's RMOUG conference. Well, I suspect the panel was prompted by an earlier blog posting and many late night conversations. I've just noticed that it looks like it's spilled over into subsequent email conversations, too. I'd bet they were interesting!

The subject up for discussion is 'How useful are diagnostic/optimisation tools' (Sorry, Dan, I'll revert to true English spelling for this one ) As Dan says,

"If you do not have a method you can explain to another person, that you can repeat multiple times and reach the same conclusion with the same data, that accurately identifies the root cause and recommends the correct plan of action ... you really do not have a method ... you have a bunch of guesses."

I'd suggest that you have some assertions and a bunch of theories (or educated guesses) that can then be proved right or wrong, but I know where Dan (and Alex-BAAG-Gorby) are coming from. The fact of the matter is that regardless of the currently available tools that you use, there's still a degree of individual skill required to analyse the results and even if you have those skills, you'll still come up against problems that you've never seen before and your skills will grow.

Dan goes on to discuss software as the implementation of a method or process in his next paragraph and concludes that if a tool can't come up with a correct optimisation method, then it's not a complete optimisation tool. He's right, of course, complete optimisation tools for Oracle systems simply don't exist yet. I'm in the midst of writing a course that discusses ADDM (among other things). Now it's a clever tool and I think it helps reduce time to problem resolution (possibly even more so when you understand a lot about performance already!) but the fact is that it often gives utterly stupid recommendations. It has a desire to have you add memory to the SGA constantly, in the absence of any other solution to a sick application, and (so far) I've rarely seen it suggest that CPU is a bottleneck. That "CPU is not a bottleneck" at the end of every ADDM report I've seen so far is enormously reassuring

So if all of these tools are so flawed, what's the point of them? Well, as Dan says in the last sentence of that paragraph ....

"It certainly provides invaluable data, but that data still needs interpretation"

Both parts of that sentence are true. Without wanting to come across as a pompous, be-suited, middle-aged, "this stuff is really hard, Sonny" consultant, the fact remains that the behaviour of entire systems is inherently complex or maybe a better way to say this is that applications are so varied and the parameters governing their behaviour are so wide and sometimes dynamic as to appear unique. Any automated tool or method for diagnosing performance would have to be extremely smart.

As it happens, I'm a bit of an amateur AI fan. I've always had a lurking interest in it and have read a few academic and more populist texts on the subject. What strikes me is that developing software to automatically solve problems in even very simple domains has proved time consuming and fairly unsuccessful to date. That's not to say we won't get there, but it's a long road ahead.

I'm not suggesting that software doesn't have it's place here. Everyone who reads this blog is using a pretty intelligent automated method every time they use Oracle. The CBO is a piece of software that applies an optimisation method automatically and is largely successful, but the CBO can't and could never tell you that the SQL statement that it's analysing is part of a batch report that no-one ever reads. A tool can't know whether what you're doing is sensible - that question is too high-level and abstract. How can any number of lines of code understand that actually, nobody really gives a monkey's about that stupid report that destroys system performance?

That's the problem with performance tuning - not only do you have to solve the narrow technical problem, you have to take a step back and look at human and business issues too. Humans are still much better at that

But let me take a step away from that ultra-wide (but crucial) view and look again at whether, for something such as a Statspack report or a bunch of trace files, a tool could analyse them for us. Well, Anjo Kolk has already demonstrated some attempts at this at oraperf.com a long time ago and they were pretty impressive. (I'd say "are", but I just tried to post a link and I don't think the site's available any more.) ADDM is another impressive tool, as I've mentioned already and I'm more impressed by several of the recent Oracle-supplied tools every time I use them (which is a lot at the moment).

So I'm not saying we shouldn't discuss the problem and take steps towards a solution, but to expect a complete solution to come along any time soon is like expecting a car-driving robot around the corner. Actually, I think I'd rather not come across a car-driving robot around the corner! (Seriously - just think about it. It seems reasonable and I've seen cars park, accelerate and brake themselves. I've seen an automated BMW(?) tear around a track, but what on earth would it make of bikes, children, ice ....)

People like Dan and Alex are gold-dust for the wider Oracle community, because these subjects need to be discussed so everyone can move forward and having a blog is gold-dust for me, because I can ramble away for ages when the mood takes me

I think the best we can hope for at the moment is :-

1) Gather High Quality Information
2) Produce first-pass automatic recommendations.
3) Analyse those recommendations to see if they're any good.
4) Analyse the available information for other causes and develop possible solutions (partly method-based, partly experience and intuition-based)
5) Test each of the theories developed at steps 3 and 4

But, as I said, I'm probably just rambling ....

I thought I'd wait for a few days to see how the open letter to Larry Ellison on the subject of AWR licencing panned out.

I love AWR, ASH and I even have an increasing, slightly grudging and cynical respect for ADDM. In fact, I'm in the course of putting together a two-hour training/technical mind-share session on their use at my current site because I have the joy of using them! Nothing would make me happier (in the Oracle technical arena) than for Oracle to include these features in the standard Enterprise licence so I support the letter and hats off to Mark Brinsmead for adding to Niall's (and others) earlier work. But it would be boring for me to just join the band-wagon and repeat what's already been said, so let me focus on some minor areas of difference.

1) The implication in Mark's blog is that adoption levels are almost zero, but that's not my experience. Of the last three companies I've worked with in the past three years, two of them have Diagnostics Pack licences and the other was Pythian. So, whilst Pythian's many customers might not pay for Diagnostics, the UK enterprises I've come across recently do - in fact that's a 100% adoption rate . Of course, it's wrapped up in a site licence, but maybe that's what this is really about? A negotiating tool when dealing with big customers. Suffice to say that adoption is not nearly zero, although it may be among the active blogging and forum communities.

2) I agree with Howard Rogers' assertion that Standard Edition customers should be allowed to licence the Diagnostics pack if they wish. In fact, Howard's written very perceptively about this over the course of the past year or so, if I recall correctly? However, if I had to make the choice personally, my stronger feeling is that this should be included in Enterprise Edition. If the product is truly an Enterprise product, then AWR is far more significant than any SOA/XML/Java/AQ blah, blah, blah feature. What do Oracle think that Enterprise DBAs *do*? Don't they think this stuff is central to our role?

3) In the end, I wouldn't mind so much if Oracle didn't give away so much software for free - SQL Developer and the rest. To restrict access to the workload repository just makes people suspicious - for what? It just makes the company look small-minded. When the licence restrictions become the target for humour - the law is an ass - it's time to reconsider.

I hope those minor differences don't detract from the central argument because I do worry that, the more fragmented the argument, the less likely it is to be won. I even accept the argument might be too emotional but never under-estimate the emotions of a bunch of geeks Wow, think about that! People who are emotional about your product, made up of a stream of hex! Of course Oracle can decide to charge whatever they want for whatever features their customers want and then let the customers decide. However, think about what a positive message a change in stance would send to their customers. I don't pretend to know how much money this might cost Oracle in the long run but the warm, fuzzy glow would resonate around the Oracle community and make die-hard fans be able to sing their praises of the product without having to be embarassed about what I still believe to be a licensing anomaly.

Here's the thing, Larry. Wouldn't it be nice to be the good guy for once?

(Yeah, I know, it's business, blah, blah ... but make some fans happy and who knows how powerful that might be ...)

(Yep, time for another lie down ....)