Entries tagged as ash
Mar 25: ASH and the psychology of Hidden Parameters
Time for a quick break from the final push to complete the course slides. I've (probably foolishly) decided to apply the 10.2.0.4 patch to my test database.
As I was confirming the details of when Oracle starts to flush information from the ASH Buffer to the workload repository, I thought I'd check the value as it's defined in a hidden parameter, _ash_eflush_trigger. The default is 66, which means that the flush begins whenever the circular buffer is 66% used, to stop it from wrapping around on itself, or filling up, whichever description you prefer.
It also set me thinking that I wonder whether Hidden Parameters will ever stop seeming 'cool' or 'sexy'. Despite the fact that there are very few occasions when they are used, the mere mention of them seems to elicit intense responses as though you're letting people in on some big secret! It made me question what people might want from a course - internals for the sake of it, because they're cool, or maybe just because everyone wants to learn something new - or hopefully they want information that might be useful in their day to day jobs. Frankly, if it's deliberately clever stuff people are looking for, they're going to be disappointed
It's very tempting to write a course to prove how much you know, but I think that's a mistake.
Where I think Hidden Parameters are very useful is in understanding how something works and ASH is no exception. Here are a few examples, with their default values on 10.2.0.3
_ash_enable=TRUE
_ash_sample_all = FALSE
Oooh, this one would be fun! Why not sample all sessions include those that aren't Active (Hint, you would have an enormous growth in the volume of data generated so, again, I'm kidding)
_ash_disk_write_enable = TRUE
Whether samples are flushed to the workload repository or not. Might initially seem a good idea to save space or improve performance a little but (just a guess) I think it would confuse the hell out of ADDM when it couldn't find any ASH samples to correlate with the AWR information.
_ash_disk_filter_ratio = 10
I've seen this question asked in a few Metalink forum postings - why is some of the information missing in DBA_HIST_ACTIVE_SESS_HISTORY? Because only one in 10 samples are written there. If you wanted one-second granularity in AWR as well as in memory, this would need to be set to 1. However, as well as 10 times the space used in AWR, that's ten times as much data to write there, Direct Path Inserts or not!
So the usual warning; The parameters are hidden for a reason and you shouldn't tamper with them unless someone in Oracle Support has suggested it. This post is just a bit of interesting fun and anyone who breaks their instance because they tried any of this stuff deserves everything they get.
Now my 10.2.0.4 upgrade has just finished, so back to work.
As I was confirming the details of when Oracle starts to flush information from the ASH Buffer to the workload repository, I thought I'd check the value as it's defined in a hidden parameter, _ash_eflush_trigger. The default is 66, which means that the flush begins whenever the circular buffer is 66% used, to stop it from wrapping around on itself, or filling up, whichever description you prefer.
It also set me thinking that I wonder whether Hidden Parameters will ever stop seeming 'cool' or 'sexy'. Despite the fact that there are very few occasions when they are used, the mere mention of them seems to elicit intense responses as though you're letting people in on some big secret! It made me question what people might want from a course - internals for the sake of it, because they're cool, or maybe just because everyone wants to learn something new - or hopefully they want information that might be useful in their day to day jobs. Frankly, if it's deliberately clever stuff people are looking for, they're going to be disappointed
It's very tempting to write a course to prove how much you know, but I think that's a mistake. Where I think Hidden Parameters are very useful is in understanding how something works and ASH is no exception. Here are a few examples, with their default values on 10.2.0.3
_ash_enable=TRUE
Self-explanatory, hopefully, but I wonder if anyone's ever tried using this to switch ASH off, rather than statistics_level=BASIC?
_ash_sampling_interval = 1000 (milliseconds)
_ash_sample_all = FALSE
Oooh, this one would be fun! Why not sample all sessions include those that aren't Active
(Hint, you would have an enormous growth in the volume of data generated so, again, I'm kidding)_ash_disk_write_enable = TRUE
Whether samples are flushed to the workload repository or not. Might initially seem a good idea to save space or improve performance a little but (just a guess) I think it would confuse the hell out of ADDM when it couldn't find any ASH samples to correlate with the AWR information.
_ash_disk_filter_ratio = 10
I've seen this question asked in a few Metalink forum postings - why is some of the information missing in DBA_HIST_ACTIVE_SESS_HISTORY? Because only one in 10 samples are written there. If you wanted one-second granularity in AWR as well as in memory, this would need to be set to 1. However, as well as 10 times the space used in AWR, that's ten times as much data to write there, Direct Path Inserts or not!
So the usual warning; The parameters are hidden for a reason and you shouldn't tamper with them unless someone in Oracle Support has suggested it. This post is just a bit of interesting fun and anyone who breaks their instance because they tried any of this stuff deserves everything they get.
Now my 10.2.0.4 upgrade has just finished, so back to work.
Feb 22: "How useful are diagnostic/optimization tools?" - Another View
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 ....
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 ....
Comments