Learning about parameters

We are kicking off an effort to analyze and hopefully standardize parameters across some 200 databases. Fortunately, we have a history of parameters which is made possible by an in-house job that scrapes them from each database every night.

So this post is more about my own little path of discovery; I am sure most of you know this already.

Lesson 1: Pay attention to what the documentation says

For instance, db_cache_size has this little blurb in the 10gR2 docs:

The value must be at least 4M * number of cpus * granule size (smaller values are automatically rounded up to this value).

Let us assume the documentation means to say 4*1024*1024, as opposed to 4 megabytes; 4 megabytes * granule size (which is also in bytes) would give you bytes squared, which makes no sense. We will also assume the granule size = 8KB, but in order for the numbers to be realistic, we will say 8 bytes. That first 4M is really throwing things off. 4M * 8 bytes = 33,554,432 bytes, or 32 megabytes.

So let's say we have a generic setting of, say 200M (bear with me). If you upgrade your host from using 5 CPUs (4M * 5 cpus * 8K = 32MB * 5 = 160MB) to 10 CPUs (160MB * 2 = 320MB), the setting for db_cache_size will automatically jump up to 320MB, despite your explicit setting of 200M. If you had budgeted your databases to max out memory, you might want to be a tad careful. =)

Lesson 2: Comments in the init file are stored in x$ksppcv.ksppstcmnt (and thus v$parameter.UPDATE_COMMENT)

Of course, one has to put the comments in the right place. We pretty much discovered this by accident; if you put a # followed by text after the parameter=value, you are all set. Of course, if you ever look at spfiles, they have the same thing. Just something we have missed for a very long time. =) We have found this to be very handy in identifying parameters that deviate from a given "standard" or are set as a result of a bug. Or an upgrade (which are just really massive bug fixes, as well as new bug introductions, right? *grin*).

Lesson 3: Undocumented/unpublished x$ tables really sucks

I really wish Oracle would document the x$ tables for us. So I am looking at X$KSPPI.ksppity; all the online blogs/white papers/articles that I can find decode this value into 4 groups (boolean, string, number, file). But wait, in 10gR2 I have 5 distinct types (1-4, 6). 6 seems to correspond to "Big Integer", that little convention of using [K|M|G] after a number to distinguish a shorthand for its size. Not sure why 5 was skipped - we do not have any values for 5.

Overdramatic? Saying that such a small thing sucks? This is merely the tip of the iceberg - there is so much out there that is not available for the general public, and the experts end up making guesses (really good and quite often very accurate guesses, but guesses none-the-less).

Well that is it for now. Still exploring, learning... seeing what's out there.