Tracking Parameter Changes
I was in a meeting yesterday and a guy asked if there was a way to track changes to database parameters (sometimes called init.ora parameters by us old guys). I thought for a second and said I didn’t think there was any sort of built in mechanism for doing that, no history table that I was aware of. Then one of the other guys in the meeting said, “oh yeah, we do that by looking at the AWR table that has the list of non-default parameter settings” (that table is WRH$_PARAMETER by the way).
Hmmm, interesting approach. AWR runs every hour by default. Not a bad idea. They had a script that prompted for a parameter name and did a dump of all entries for the specified parameter. So you could easily see where a change had occurred.
Looks like this:
SYS@LAB1024> @parm_hist
Enter value for pname: star_transformation_enabled
SNAP_ID TIME PARAMETER_NAME VALUE
---------- --------------- -------------------------------------------------- --------------------
2232 25-SEP-09 00 star_transformation_enabled FALSE
2233 25-SEP-09 01 star_transformation_enabled FALSE
2234 25-SEP-09 02 star_transformation_enabled FALSE
2235 25-SEP-09 03 star_transformation_enabled FALSE
2376 01-OCT-09 00 star_transformation_enabled FALSE
2377 01-OCT-09 01 star_transformation_enabled FALSE
2378 01-OCT-09 02 star_transformation_enabled FALSE
2379 01-OCT-09 03 star_transformation_enabled FALSE
2380 01-OCT-09 04 star_transformation_enabled FALSE
2381 01-OCT-09 05 star_transformation_enabled FALSE
2382 01-OCT-09 06 star_transformation_enabled FALSE
2383 01-OCT-09 07 star_transformation_enabled FALSE
2384 01-OCT-09 08 star_transformation_enabled FALSE
2385 01-OCT-09 09 star_transformation_enabled FALSE
2386 01-OCT-09 10 star_transformation_enabled FALSE
2387 01-OCT-09 11 star_transformation_enabled FALSE
3900 02-DEC-09 23 star_transformation_enabled TRUE
3901 03-DEC-09 00 star_transformation_enabled TRUE
3902 03-DEC-09 01 star_transformation_enabled TRUE
3903 03-DEC-09 02 star_transformation_enabled TRUE
3904 03-DEC-09 03 star_transformation_enabled TRUE
3905 03-DEC-09 04 star_transformation_enabled TRUE
3906 03-DEC-09 05 star_transformation_enabled TRUE
3907 03-DEC-09 06 star_transformation_enabled TRUE
... |
Of course my first thought was “that’s nice, but most systems only have a few weeks of history in AWR”. I’ll come back to that issue in a minute, but for now let’s go on to the part where my brain started working on ways to make use of this idea and to maybe improve on it a little bit.
To begin with, I didn’t want to see a record for every snapshot if nothing had changed. I would prefer to just see a single record with both the old and new value when there was actually a change. Easy enough to do with the an analytic query using the lag function. I also thought I’d like to be able to wild card the parameter - no problem there. Then I decided I wanted it to be RAC aware and let me specify a single instance (since some of the parameters have different values depending on the instance). And finally, I found the calculated hidden parameters to be annoying (the ones that start with 2 underscores like “__shared_pool_size”). Several of them get reset on a regular basis, and I am not usually all that interested in those. So I added a switch to turn them off (or not). I called the script parm_mods.sql.
Here’s a quick example:
SYS@LAB1024> @parm_mods
Enter value for parameter_name:
Enter value for instance_number:
Enter value for show_calculated:
INSTANCE SNAP_ID TIME PARAMETER_NAME OLD_VALUE NEW_VALUE
---------- ---------- --------------- ----------------------------------- -------------------- --------------------
1 2376 01-OCT-09 00:00 db_recovery_file_dest_size 26843545600 42949672960
3900 02-DEC-09 23:00 db_file_multiblock_read_count 16 128
3900 02-DEC-09 23:00 parallel_execution_message_size 2148 8192
3900 02-DEC-09 23:00 hash_area_size 131072 200000000
3900 02-DEC-09 23:00 large_pool_size 0 536870912
3900 02-DEC-09 23:00 workarea_size_policy AUTO MANUAL
3900 02-DEC-09 23:00 shared_pool_reserved_size 12582912 24326963
3900 02-DEC-09 23:00 shared_pool_size 0 419430400
3900 02-DEC-09 23:00 sort_area_size 65536 100000000
3900 02-DEC-09 23:00 star_transformation_enabled FALSE TRUE
4085 10-DEC-09 15:02 _spin_count 2001 2002
11 rows selected.
SYS@LAB1024> /
Enter value for parameter_name:
Enter value for instance_number:
Enter value for show_calculated: Y
INSTANCE SNAP_ID TIME PARAMETER_NAME OLD_VALUE NEW_VALUE
---------- ---------- --------------- ----------------------------------- -------------------- --------------------
1 2376 01-OCT-09 00:00 __shared_pool_size 251658240 285212672
2376 01-OCT-09 00:00 db_recovery_file_dest_size 26843545600 42949672960
2376 01-OCT-09 00:00 __db_cache_size 1275068416 1241513984
3900 02-DEC-09 23:00 db_file_multiblock_read_count 16 128
3900 02-DEC-09 23:00 __shared_pool_size 285212672 486539264
3900 02-DEC-09 23:00 __large_pool_size 16777216 536870912
3900 02-DEC-09 23:00 workarea_size_policy AUTO MANUAL
3900 02-DEC-09 23:00 __db_cache_size 1241513984 520093696
3900 02-DEC-09 23:00 hash_area_size 131072 200000000
3900 02-DEC-09 23:00 large_pool_size 0 536870912
3900 02-DEC-09 23:00 parallel_execution_message_size 2148 8192
3900 02-DEC-09 23:00 shared_pool_reserved_size 12582912 24326963
3900 02-DEC-09 23:00 shared_pool_size 0 419430400
3900 02-DEC-09 23:00 sort_area_size 65536 100000000
3900 02-DEC-09 23:00 star_transformation_enabled FALSE TRUE
4085 10-DEC-09 15:02 _spin_count 2001 2002
16 rows selected. |
So back to the issue of AWR retention…
