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

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)

This is where the one second sampling is specified. I'm tempted to try to reduce this to a silly level and watch the server fall on it's back-side. I vaguely recall Graham Wood saying during a presentation that it had been tested with double-digit values.

_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.