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…
I think this would be a very good way of keeping a record of parameter changes if the retention was long enough. The default is to keep only 7 days of history, although many systems keep a month or two. Turns out, the guys that are doing this keep 7 years of AWR history (actually they only have 2 years so far, but retention is set to 7 years). That made me wonder how much space 7 years would take and if performance would suffer on any of the standard AWR stuff. I checked a couple of systems and came up with an average of around 1 Meg per snapshot. So that’s about 168 Megs per week, 720 Megs per month, around 9 Gigs per year. They reported a much lower number, only about 4G for 2 years. I am not sure I believe that since they have a 3 node RAC environment (so 3X the data). The smallest number I found on any of the systems I checked was about 0.5 Megs per snap on a database that is virtually idle. The SQL Plans and the Active Session History take the most space. So very complex statements would tend to increase the size and obviously more activity would increase the size. See here:
SQL> select count(*) from dba_hist_snapshot;
COUNT(*)
----------
1841
SQL> @table_size
Enter value for owner:
Enter value for table_name: WRH%
OWNER SEGMENT_NAME TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ -------------- ------------------------------
SYS WRH$_FILESTATXS_BL .2 SYSAUX
SYS WRH$_OPTIMIZER_ENV .2 SYSAUX
SYS WRH$_BUFFER_POOL_STATISTICS .4 SYSAUX
SYS WRH$_SGASTAT_BL .3 SYSAUX
SYS WRH$_CURRENT_BLOCK_SERVER .3 SYSAUX
SYS WRH$_TABLESPACE_STAT_BL .2 SYSAUX
SYS WRH$_SERVICE_NAME .2 SYSAUX
SYS WRH$_WAITSTAT_BL .2 SYSAUX
SYS WRH$_SYSTEM_EVENT 29.1 SYSAUX
SYS WRH$_LATCH_MISSES_SUMMARY 43.1 SYSAUX
SYS WRH$_PARAMETER 34.1 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS 4.6 SYSAUX
SYS WRH$_TABLESPACE_STAT 3.4 SYSAUX
SYS WRH$_SQL_BIND_METADATA_PK 5.0 SYSAUX
SYS WRH$_WAITSTAT_BL_PK .1 SYSAUX
SYS WRH$_SGA_PK .6 SYSAUX
SYS WRH$_SHARED_POOL_ADVICE_PK 2.0 SYSAUX
SYS WRH$_SQL_WORKAREA_HIST_PK 3.0 SYSAUX
SYS WRH$_JAVA_POOL_ADVICE_PK .8 SYSAUX
SYS WRH$_LATCH_PK 49.1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_PK 3.1 SYSAUX
SYS WRH$_SGASTAT_U 4.0 SYSAUX
SYS WRH$_SYSSTAT_PK 45.1 SYSAUX
SYS WRH$_DLM_MISC_PK 9.3 SYSAUX
SYS WRH$_BUFFERED_SUBSCRIBERS .1 SYSAUX
SYS WRH$_SQL_PLAN 240.0 SYSAUX
SYS WRH$_LIBRARYCACHE 3.0 SYSAUX
SYS WRH$_SQL_WORKAREA_HISTOGRAM 3.0 SYSAUX
SYS WRH$_PARAMETER_NAME .1 SYSAUX
SYS WRH$_SESSMETRIC_HISTORY .1 SYSAUX
SYS WRH$_CR_BLOCK_SERVER .4 SYSAUX
SYS WRH$_FILESTATXS 5.8 SYSAUX
SYS WRH$_OSSTAT 2.6 SYSAUX
SYS WRH$_BUFFERED_SUBSCRIBERS_PK .1 SYSAUX
SYS WRH$_SQL_PLAN_PK 88.0 SYSAUX
SYS WRH$_SYSTEM_EVENT_BL_PK .6 SYSAUX
SYS WRH$_THREAD_PK .3 SYSAUX
SYS WRH$_CR_BLOCK_SERVER_PK .2 SYSAUX
SYS WRH$_ASH_BL_PK 2.0 SYSAUX
SYS WRH$_WAITCLASSMETRIC_HIST_IND 8.0 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY_PK 94.1 SYSAUX
SYS WRH$_SYS_TIME_MODEL_PK 2.7 SYSAUX
SYS WRH$_SQL_BIND_METADATA 8.0 SYSAUX
SYS WRH$_PROCESS_MEMORY_SUMMARY .9 SYSAUX
SYS WRH$_SQL_SUMMARY .3 SYSAUX
SYS WRH$_LATCH_BL 2.0 SYSAUX
SYS WRH$_SEG_STAT_BL 3.0 SYSAUX
SYS WRH$_METRIC_NAME .1 SYSAUX
SYS WRH$_FILEMETRIC_HISTORY .1 SYSAUX
SYS WRH$_SYSSTAT 34.1 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY 380.1 SYSAUX
SYS WRH$_STREAMS_CAPTURE_PK .1 SYSAUX
SYS WRH$_COMP_IOSTAT_PK .1 SYSAUX
SYS WRH$_SQLSTAT_BL_PK .3 SYSAUX
SYS WRH$_BG_EVENT_SUMMARY_PK 7.0 SYSAUX
SYS WRH$_ENQUEUE_STAT_PK 9.0 SYSAUX
SYS WRH$_LATCH_CHILDREN_BL_PK .1 SYSAUX
SYS WRH$_RESOURCE_LIMIT_PK 3.0 SYSAUX
SYS WRH$_LOG_PK 3.0 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_BL_PK .1 SYSAUX
SYS WRH$_SYSMETRIC_HISTORY_INDEX 10.0 SYSAUX
SYS WRH$_SGA_TARGET_ADVICE .8 SYSAUX
SYS WRH$_STREAMS_CAPTURE .1 SYSAUX
SYS WRH$_RESOURCE_LIMIT 3.0 SYSAUX
SYS WRH$_THREAD .3 SYSAUX
SYS WRH$_OSSTAT_BL .2 SYSAUX
SYS WRH$_MTTR_TARGET_ADVICE .1 SYSAUX
SYS WRH$_LATCH_CHILDREN .1 SYSAUX
SYS WRH$_SEG_STAT 43.1 SYSAUX
SYS WRH$_SERVICE_STAT 12.9 SYSAUX
SYS WRH$_BUFFERED_QUEUES_PK .1 SYSAUX
SYS WRH$_DATAFILE_PK .1 SYSAUX
SYS WRH$_SQLTEXT_PK 2.0 SYSAUX
SYS WRH$_SQL_SUMMARY_PK .2 SYSAUX
SYS WRH$_OPTIMIZER_ENV_PK .1 SYSAUX
SYS WRH$_LATCH_NAME_PK .1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_BL_PK .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_BL_PK .1 SYSAUX
SYS WRH$_PGA_TARGET_ADVICE_PK 3.0 SYSAUX
SYS WRH$_SYSSTAT_BL_PK .9 SYSAUX
SYS WRH$_SYS_TIME_MODEL_BL_PK .1 SYSAUX
SYS WRH$_OSSTAT_BL_PK .1 SYSAUX
SYS WRH$_OSSTAT_NAME_PK .1 SYSAUX
SYS WRH$_METRIC_NAME_PK .1 SYSAUX
SYS WRH$_CURRENT_BLOCK_SERVER_PK .1 SYSAUX
SYS WRH$_SGASTAT_BL_U .2 SYSAUX
SYS WRH$_SYSMETRIC_SUMMARY_INDEX 13.0 SYSAUX
SYS WRH$_SESSMETRIC_HISTORY_INDEX .1 SYSAUX
SYS WRH$_SYSTEM_EVENT_PK 33.1 SYSAUX
SYS WRH$_LATCH_PARENT_PK .1 SYSAUX
SYS WRH$_BUFFERED_QUEUES .1 SYSAUX
SYS WRH$_RULE_SET .3 SYSAUX
SYS WRH$_SESS_TIME_STATS .4 SYSAUX
SYS WRH$_COMP_IOSTAT .1 SYSAUX
SYS WRH$_SYSTEM_EVENT_BL .8 SYSAUX
SYS WRH$_BG_EVENT_SUMMARY 6.0 SYSAUX
SYS WRH$_LATCH_MISSES_SUMMARY_BL 2.0 SYSAUX
SYS WRH$_SYS_TIME_MODEL_BL .2 SYSAUX
SYS WRH$_PARAMETER_BL 1.0 SYSAUX
SYS WRH$_OSSTAT_NAME .1 SYSAUX
SYS WRH$_SQLSTAT 66.1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE 4.1 SYSAUX
SYS WRH$_SYS_TIME_MODEL 2.8 SYSAUX
SYS WRH$_DLM_MISC 13.0 SYSAUX
SYS WRH$_STREAMS_APPLY_SUM_PK .1 SYSAUX
SYS WRH$_PROCESS_MEM_SUMMARY_PK .6 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_BL_PK .1 SYSAUX
SYS WRH$_TEMPFILE_PK .1 SYSAUX
SYS WRH$_EVENT_NAME_PK .1 SYSAUX
SYS WRH$_LIBRARYCACHE_PK 2.0 SYSAUX
SYS WRH$_BUFFER_POOL_STATS_PK .2 SYSAUX
SYS WRH$_TABLESPACE_STAT_BL_PK .1 SYSAUX
SYS WRH$_TS_SPACE_USAGE_IND .6 SYSAUX
SYS WRH$_SEG_STAT_BL_PK .6 SYSAUX
SYS WRH$_SEG_STAT_OBJ_PK 6.0 SYSAUX
SYS WRH$_LATCH_CHILDREN_PK .1 SYSAUX
SYS WRH$_FILESTATXS_PK 3.4 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_BL .1 SYSAUX
SYS WRH$_SQLSTAT_BL 2.0 SYSAUX
SYS WRH$_SQLTEXT 16.0 SYSAUX
SYS WRH$_LATCH_NAME .1 SYSAUX
SYS WRH$_LATCH_PARENT_BL .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_BL .3 SYSAUX
SYS WRH$_PGASTAT 3.0 SYSAUX
SYS WRH$_STAT_NAME .1 SYSAUX
SYS WRH$_WAITCLASSMETRIC_HISTORY 9.0 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY_BL 8.0 SYSAUX
SYS WRH$_LOG 3.0 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_BL .3 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER 2.5 SYSAUX
SYS WRH$_RULE_SET_PK .3 SYSAUX
SYS WRH$_STREAMS_POOL_ADVICE_PK 2.0 SYSAUX
SYS WRH$_FILESTATXS_BL_PK .1 SYSAUX
SYS WRH$_LATCH_BL_PK .9 SYSAUX
SYS WRH$_LATCH_MISSES_SUMRY_BL_PK 2.0 SYSAUX
SYS WRH$_PGASTAT_PK 4.0 SYSAUX
SYS WRH$_INSTANCE_RECOVERY_PK .2 SYSAUX
SYS WRH$_STAT_NAME_PK .1 SYSAUX
SYS WRH$_PARAMETER_NAME_PK .1 SYSAUX
SYS WRH$_UNDOSTAT_PK 1.0 SYSAUX
SYS WRH$_DLM_MISC_BL_PK .2 SYSAUX
SYS WRH$_SERVICE_STAT_BL_PK .6 SYSAUX
SYS WRH$_SQLSTAT_BL_INDEX .3 SYSAUX
SYS WRH$_WAITSTAT_PK 2.9 SYSAUX
SYS WRH$_LATCH_MISSES_SUMMARY_PK 51.1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_PK 6.2 SYSAUX
SYS WRH$_SQLSTAT_PK 29.9 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_PK 1.3 SYSAUX
SYS WRH$_STREAMS_APPLY_SUM .1 SYSAUX
SYS WRH$_TEMPSTATXS .6 SYSAUX
SYS WRH$_ENQUEUE_STAT 10.0 SYSAUX
SYS WRH$_SYSSTAT_BL .9 SYSAUX
SYS WRH$_UNDOSTAT 3.0 SYSAUX
SYS WRH$_SEG_STAT_OBJ 11.0 SYSAUX
SYS WRH$_DLM_MISC_BL .4 SYSAUX
SYS WRH$_TABLESPACE_SPACE_USAGE 3.0 SYSAUX
SYS WRH$_LATCH 55.1 SYSAUX
SYS WRH$_LATCH_PARENT .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY 7.8 SYSAUX
SYS WRH$_LATCH_PARENT_BL_PK .1 SYSAUX
SYS WRH$_SERVICE_NAME_PK .1 SYSAUX
SYS WRH$_SERVICE_STAT_PK 28.1 SYSAUX
SYS WRH$_OSSTAT_PK 2.4 SYSAUX
SYS WRH$_SEG_STAT_PK 19.9 SYSAUX
SYS WRH$_STREAMS_POOL_ADVICE 3.0 SYSAUX
SYS WRH$_DATAFILE .1 SYSAUX
SYS WRH$_TEMPFILE .1 SYSAUX
SYS WRH$_EVENT_NAME .1 SYSAUX
SYS WRH$_LATCH_CHILDREN_BL .1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_BL .2 SYSAUX
SYS WRH$_SGA .4 SYSAUX
SYS WRH$_SHARED_POOL_ADVICE 4.0 SYSAUX
SYS WRH$_PGA_TARGET_ADVICE 3.0 SYSAUX
SYS WRH$_INSTANCE_RECOVERY .3 SYSAUX
SYS WRH$_JAVA_POOL_ADVICE .9 SYSAUX
SYS WRH$_SYSMETRIC_HISTORY 6.0 SYSAUX
SYS WRH$_SYSMETRIC_SUMMARY 22.0 SYSAUX
SYS WRH$_SERVICE_STAT_BL .4 SYSAUX
SYS WRH$_WAITSTAT 2.8 SYSAUX
SYS WRH$_SGASTAT 3.4 SYSAUX
SYS WRH$_SGA_TARGET_ADVICE_PK .9 SYSAUX
SYS WRH$_SESS_TIME_STATS_PK .3 SYSAUX
SYS WRH$_TEMPSTATXS_PK .3 SYSAUX
SYS WRH$_PARAMETER_BL_PK .7 SYSAUX
SYS WRH$_FILEMETRIC_HISTORY_INDEX .1 SYSAUX
SYS WRH$_PARAMETER_PK 39.1 SYSAUX
SYS WRH$_TABLESPACE_STAT_PK 2.4 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_PK 5.1 SYSAUX
SYS WRH$_SQLSTAT_INDEX 19.0 SYSAUX
--------------
sum 1,779.2
189 rows selected.
SQL> select 1779.2/1841 Megs from dual; -- Megs per Snapshot
MEGS
-----------
.966431287
SQL> select 7*24 Snaps from dual; -- Snaps in a week
SNAPS
----------
168
SQL> select 30*24 Snaps from dual; -- Snaps in a 30 days
SNAPS
----------
720
SQL> select 365*24 Snaps from dual; -- snaps in a year
SNAPS
----------
8760
SQL> -- With AWR Rentention set to 7 years. So that's
SQL> select (7*8760*.966431287)/1024 Gigs from dual;
GIGS
----------
57.8726236
SQL> select 57.87*3 Gigs from dual; -- and there are 3 nodes in his RAC environment
GIGS
----------
173.61
SQL> -- So that's 174G for AWR data for 7 years with 3 nodes and one hour snapshots!
So that’s a lot of space to keep a history of parameter changes (although having all that AWR history could be very nice for other uses as well). Another thought that crossed my mind was to create a trigger on the AWR table that holds the parameter information and copy just the changes off to another table. Seems like a very usable approach. Unfortunately, unlike its predecessor Statspack, AWR’s tables are owned by SYS, so no triggers allowed. I guess we could schedule a job to make a copy every hour, but I will leave that as an exercise for the reader. If you do that, you will probably want to do the analytics in the move so you’re not keeping much data in the history table, as opposed to moving it all and doing the lag function in the report. Anyway, that’s enough for one post. Let me know if you have any questions.
Update (12/18/09) : As Doug Burns mentions in his follow up post on his own blog (see his comment below), Oracle provides a nice script for estimating SYSAUX space usage by AWR. Here’s a sample run of the script (it reports current usage as well as an estimated size based on several variables that the user can input).
SYS@LAB1024> @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql
This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt. To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:
Using the report name utlsyxsz.txt
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Estimated at
16:01:00 on Dec 18, 2009 ( Friday ) in Timezone -06:00
DB_NAME HOST_PLATFORM INST STARTUP_TIME PAR
----------- ---------------------------------------- ----- ----------------- ---
* LAB1024 homer - Linux IA (32-bit) 1 11:31:19 (11/20) NO
~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size: 489.9 MB
|
| Total size of SM/AWR 108.0 MB ( 22.0% of SYSAUX )
| Total size of SM/OPTSTAT 79.9 MB ( 16.3% of SYSAUX )
| Total size of EM 48.8 MB ( 10.0% of SYSAUX )
| Total size of XDB 48.2 MB ( 9.8% of SYSAUX )
| Total size of SDO 32.9 MB ( 6.7% of SYSAUX )
| Total size of XSOQHIST 23.9 MB ( 4.9% of SYSAUX )
| Total size of AO 23.9 MB ( 4.9% of SYSAUX )
| Total size of XSAMD 15.6 MB ( 3.2% of SYSAUX )
| Total size of SM/ADVISOR 10.4 MB ( 2.1% of SYSAUX )
| Total size of WM 7.1 MB ( 1.5% of SYSAUX )
| Total size of LOGMNR 5.9 MB ( 1.2% of SYSAUX )
| Total size of SM/OTHER 4.9 MB ( 1.0% of SYSAUX )
| Total size of TEXT 4.6 MB ( 0.9% of SYSAUX )
| Total size of EXPRESSION_FILTER 3.6 MB ( 0.7% of SYSAUX )
| Total size of EM_MONITORING_USER 1.6 MB ( 0.3% of SYSAUX )
| Total size of LOGSTDBY 0.9 MB ( 0.2% of SYSAUX )
| Total size of JOB_SCHEDULER 0.8 MB ( 0.2% of SYSAUX )
| Total size of STREAMS 0.5 MB ( 0.1% of SYSAUX )
| Total size of ORDIM 0.5 MB ( 0.1% of SYSAUX )
| Total size of ODM 0.3 MB ( 0.1% of SYSAUX )
| Total size of TSM 0.3 MB ( 0.1% of SYSAUX )
| Total size of Others 67.6 MB ( 13.8% of SYSAUX )
|
~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~
| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
| - Interval Setting (minutes)
| - Retention Setting (days)
| - Number of Instances
| - Average Number of Active Sessions
| - Number of Datafiles
|
| For 'Interval Setting',
| Press to use the current value: 60.0 minutes
| otherwise enter an alternative
|
Enter value for interval:
** Value for 'Interval Setting': 60
|
| For 'Retention Setting',
| Press to use the current value: 7.00 days
| otherwise enter an alternative
|
Enter value for retention:
** Value for 'Retention Setting': 7
|
| For 'Number of Instances',
| Press to use the current value: 1.00
| otherwise enter an alternative
|
Enter value for num_instances:
** Value for 'Number of Instances': 1
|
| For 'Average Number of Active Sessions',
| Press to use the current value: 0.00
| otherwise enter an alternative
|
Enter value for active_sessions: 50
** Value for 'Average Number of Active Sessions': 50
| ***************************************************
| Estimated size of AWR: 830.2 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 50.00
| Datafiles - 5
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
| - Number of Tables in the Database
| - Number of Partitions in the Database
| - Statistics Retention Period (days)
| - DML Activity in the Database (level)
|
| For 'Number of Tables',
| Press to use the current value: 274.0
| otherwise enter an alternative
|
Enter value for number_of_tables:
** Value for 'Number of Tables': 274
|
| For 'Number of Partitions',
| Press to use the current value: 0.00
| otherwise enter an alternative
|
Enter value for number_of_partitions:
** Value for 'Number of Partitions': 0
|
| For 'Statistics Retention',
| Press to use the current value: 31.0 days
| otherwise enter an alternative
|
Enter value for stats_retention:
** Value for 'Statistics Retention': 31
|
| For 'DML Activity',
| Press to use the current value: 2
| otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:
** Value for 'DML Activity': 2
| ***************************************************
| Estimated size of Stats history 41.2 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 274
| Indexes - 615
| Columns - 4,126
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ***************************************************
~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR: 830.2 MB
|
| The AWR estimate was computed using
| the following values:
|
| Interval - 60 minutes
| Retention - 7.00 days
| Num Instances - 1
| Active Sessions - 50.00
| Datafiles - 5
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history 41.2 MB
|
| The space for Optimizer Statistics history was
| estimated using the following values:
|
| Tables - 274
| Indexes - 615
| Columns - 4,126
| Partitions - 0
| Indexes on Partitions - 0
| Columns in Partitions - 0
| Stats Retention in Days - 31
| Level of DML Activity - Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| For all the other components, the estimate
| is equal to the current space usage of
| the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************
| Est size of EM 48.8 MB
| Est size of XDB 48.2 MB
| Est size of SDO 32.9 MB
| Est size of XSOQHIST 23.9 MB
| Est size of AO 23.9 MB
| Est size of XSAMD 15.6 MB
| Est size of SM/ADVISOR 10.4 MB
| Est size of WM 7.1 MB
| Est size of LOGMNR 5.9 MB
| Est size of SM/OTHER 4.9 MB
| Est size of TEXT 4.6 MB
| Est size of EXPRESSION_FILTER 3.6 MB
| Est size of EM_MONITORING_USER 1.6 MB
| Est size of LOGSTDBY 0.9 MB
| Est size of JOB_SCHEDULER 0.8 MB
| Est size of STREAMS 0.5 MB
| Est size of ORDIM 0.5 MB
| Est size of ODM 0.3 MB
| Est size of TSM 0.3 MB
| Est size of Others 67.6 MB
| Est size of SM/AWR 830.2 MB
| Est size of SM/OPTSTAT 41.2 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size: 1,173.5 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************
End of Report
SYS@LAB1024>
SYS@LAB1024> -- Let's compare the scripts output to a sum of size for the underlying tables
SYS@LAB1024>
SYS@LAB1024> @table_size
Enter value for owner:
Enter value for table_name: WRH$%
OWNER SEGMENT_NAME TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ -------------- ------------------------------
SYS WRH$_LATCH_MISSES_SUMMARY_BL .1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_BL_PK .1 SYSAUX
SYS WRH$_SGASTAT .7 SYSAUX
SYS WRH$_PROCESS_MEMORY_SUMMARY .1 SYSAUX
SYS WRH$_STREAMS_POOL_ADVICE_PK .3 SYSAUX
SYS WRH$_SQL_WORKAREA_HISTOGRAM .3 SYSAUX
SYS WRH$_SQL_WORKAREA_HIST_PK .3 SYSAUX
SYS WRH$_PGA_TARGET_ADVICE .3 SYSAUX
SYS WRH$_PARAMETER_BL_PK .2 SYSAUX
SYS WRH$_UNDOSTAT .2 SYSAUX
SYS WRH$_SEG_STAT_BL_PK .1 SYSAUX
SYS WRH$_SYSMETRIC_SUMMARY_INDEX 2.0 SYSAUX
SYS WRH$_DLM_MISC_BL_PK .1 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY_BL .1 SYSAUX
SYS WRH$_LOG .1 SYSAUX
SYS WRH$_LOG_PK .1 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER .1 SYSAUX
SYS WRH$_SESS_TIME_STATS_PK .1 SYSAUX
SYS WRH$_STREAMS_CAPTURE_PK .1 SYSAUX
SYS WRH$_STREAMS_APPLY_SUM .1 SYSAUX
SYS WRH$_STREAMS_APPLY_SUM_PK .1 SYSAUX
SYS WRH$_BUFFERED_SUBSCRIBERS_PK .1 SYSAUX
SYS WRH$_TEMPSTATXS_PK .1 SYSAUX
SYS WRH$_FILESTATXS .6 SYSAUX
SYS WRH$_SQLSTAT_BL .4 SYSAUX
SYS WRH$_SQLTEXT_PK .1 SYSAUX
SYS WRH$_SQL_SUMMARY_PK .1 SYSAUX
SYS WRH$_SQL_PLAN_PK 2.0 SYSAUX
SYS WRH$_SQL_BIND_METADATA_PK .4 SYSAUX
SYS WRH$_LATCH_NAME .1 SYSAUX
SYS WRH$_ENQUEUE_STAT_PK .9 SYSAUX
SYS WRH$_DB_CACHE_ADVICE .7 SYSAUX
SYS WRH$_SGA_PK .1 SYSAUX
SYS WRH$_SYSSTAT_BL .3 SYSAUX
SYS WRH$_OSSTAT_BL .1 SYSAUX
SYS WRH$_OSSTAT_BL_PK .1 SYSAUX
SYS WRH$_SERVICE_STAT 1.5 SYSAUX
SYS WRH$_CURRENT_BLOCK_SERVER .1 SYSAUX
SYS WRH$_OSSTAT .6 SYSAUX
SYS WRH$_RULE_SET .1 SYSAUX
SYS WRH$_LATCH_CHILDREN_PK .1 SYSAUX
SYS WRH$_SERVICE_STAT_PK 2.8 SYSAUX
SYS WRH$_TEMPFILE .1 SYSAUX
SYS WRH$_COMP_IOSTAT_PK .1 SYSAUX
SYS WRH$_SQLSTAT_BL_PK .1 SYSAUX
SYS WRH$_SQLSTAT_BL_INDEX .1 SYSAUX
SYS WRH$_SQL_PLAN 6.0 SYSAUX
SYS WRH$_OPTIMIZER_ENV .1 SYSAUX
SYS WRH$_OPTIMIZER_ENV_PK .1 SYSAUX
SYS WRH$_WAITSTAT .7 SYSAUX
SYS WRH$_EVENT_NAME .1 SYSAUX
SYS WRH$_EVENT_NAME_PK .1 SYSAUX
SYS WRH$_BG_EVENT_SUMMARY .3 SYSAUX
SYS WRH$_LATCH 6.6 SYSAUX
SYS WRH$_WAITSTAT_BL .1 SYSAUX
SYS WRH$_SYSTEM_EVENT_PK 1.3 SYSAUX
SYS WRH$_LATCH_MISSES_SUMRY_BL_PK .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_BL_PK .1 SYSAUX
SYS WRH$_SGA .1 SYSAUX
SYS WRH$_SGA_TARGET_ADVICE .1 SYSAUX
SYS WRH$_PARAMETER_NAME .1 SYSAUX
SYS WRH$_METRIC_NAME_PK .1 SYSAUX
SYS WRH$_SYSMETRIC_SUMMARY 3.0 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_PK .7 SYSAUX
SYS WRH$_TABLESPACE_STAT_BL_PK .1 SYSAUX
SYS WRH$_LATCH_PARENT_PK .1 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY_PK .7 SYSAUX
SYS WRH$_FILESTATXS_BL .1 SYSAUX
SYS WRH$_DATAFILE .1 SYSAUX
SYS WRH$_TEMPFILE_PK .1 SYSAUX
SYS WRH$_SYSTEM_EVENT_BL .1 SYSAUX
SYS WRH$_SYSTEM_EVENT_BL_PK .1 SYSAUX
SYS WRH$_LIBRARYCACHE .3 SYSAUX
SYS WRH$_SGASTAT_BL .1 SYSAUX
SYS WRH$_PGASTAT_PK .4 SYSAUX
SYS WRH$_PGA_TARGET_ADVICE_PK .2 SYSAUX
SYS WRH$_SGA_TARGET_ADVICE_PK .2 SYSAUX
SYS WRH$_INSTANCE_RECOVERY_PK .1 SYSAUX
SYS WRH$_LATCH_PK 4.1 SYSAUX
SYS WRH$_SYS_TIME_MODEL_BL_PK .1 SYSAUX
SYS WRH$_UNDOSTAT_PK .1 SYSAUX
SYS WRH$_SYSMETRIC_HISTORY .1 SYSAUX
SYS WRH$_SESSMETRIC_HISTORY_INDEX .1 SYSAUX
SYS WRH$_STREAMS_CAPTURE .1 SYSAUX
SYS WRH$_RULE_SET_PK .1 SYSAUX
SYS WRH$_FILESTATXS_PK .6 SYSAUX
SYS WRH$_LATCH_NAME_PK .1 SYSAUX
SYS WRH$_BG_EVENT_SUMMARY_PK .3 SYSAUX
SYS WRH$_WAITSTAT_BL_PK .1 SYSAUX
SYS WRH$_LATCH_PARENT .1 SYSAUX
SYS WRH$_DB_CACHE_ADVICE_BL .1 SYSAUX
SYS WRH$_SGASTAT_BL_U .1 SYSAUX
SYS WRH$_RESOURCE_LIMIT .1 SYSAUX
SYS WRH$_SHARED_POOL_ADVICE .4 SYSAUX
SYS WRH$_STAT_NAME .1 SYSAUX
SYS WRH$_STAT_NAME_PK .1 SYSAUX
SYS WRH$_OSSTAT_NAME_PK .1 SYSAUX
SYS WRH$_PARAMETER_NAME_PK .1 SYSAUX
SYS WRH$_SEG_STAT_OBJ_PK .4 SYSAUX
SYS WRH$_METRIC_NAME .1 SYSAUX
SYS WRH$_FILEMETRIC_HISTORY .1 SYSAUX
SYS WRH$_CR_BLOCK_SERVER .1 SYSAUX
SYS WRH$_TABLESPACE_SPACE_USAGE .1 SYSAUX
SYS WRH$_TS_SPACE_USAGE_IND .1 SYSAUX
SYS WRH$_SERVICE_NAME .1 SYSAUX
SYS WRH$_SERVICE_NAME_PK .1 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_BL .1 SYSAUX
SYS WRH$_BUFFERED_QUEUES .1 SYSAUX
SYS WRH$_BUFFERED_QUEUES_PK .1 SYSAUX
SYS WRH$_SYS_TIME_MODEL_PK .7 SYSAUX
SYS WRH$_SQLTEXT 2.0 SYSAUX
SYS WRH$_SQL_SUMMARY .1 SYSAUX
SYS WRH$_LATCH_BL .4 SYSAUX
SYS WRH$_LATCH_BL_PK .3 SYSAUX
SYS WRH$_LATCH_CHILDREN_BL .1 SYSAUX
SYS WRH$_LATCH_CHILDREN_BL_PK .1 SYSAUX
SYS WRH$_LATCH_MISSES_SUMMARY 1.6 SYSAUX
SYS WRH$_LATCH_PARENT_BL_PK .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_BL .1 SYSAUX
SYS WRH$_JAVA_POOL_ADVICE .1 SYSAUX
SYS WRH$_JAVA_POOL_ADVICE_PK .1 SYSAUX
SYS WRH$_PARAMETER 3.6 SYSAUX
SYS WRH$_SYSSTAT_BL_PK .3 SYSAUX
SYS WRH$_SYS_TIME_MODEL_BL .1 SYSAUX
SYS WRH$_LATCH_MISSES_SUMMARY_PK 1.6 SYSAUX
SYS WRH$_SEG_STAT_OBJ .8 SYSAUX
SYS WRH$_WAITCLASSMETRIC_HIST_IND 2.0 SYSAUX
SYS WRH$_DLM_MISC_BL .1 SYSAUX
SYS WRH$_CURRENT_BLOCK_SERVER_PK .1 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_BL .1 SYSAUX
SYS WRH$_TABLESPACE_STAT .6 SYSAUX
SYS WRH$_MTTR_TARGET_ADVICE .1 SYSAUX
SYS WRH$_SERVICE_STAT_BL .1 SYSAUX
SYS WRH$_SESS_TIME_STATS .1 SYSAUX
SYS WRH$_BUFFERED_SUBSCRIBERS .1 SYSAUX
SYS WRH$_SGASTAT_U .7 SYSAUX
SYS WRH$_SYSSTAT_PK 4.0 SYSAUX
SYS WRH$_TABLESPACE_STAT_PK .6 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_PK .7 SYSAUX
SYS WRH$_FILESTATXS_BL_PK .1 SYSAUX
SYS WRH$_TEMPSTATXS .1 SYSAUX
SYS WRH$_COMP_IOSTAT .1 SYSAUX
SYS WRH$_SQL_BIND_METADATA .4 SYSAUX
SYS WRH$_LATCH_CHILDREN .1 SYSAUX
SYS WRH$_LATCH_PARENT_BL .1 SYSAUX
SYS WRH$_LIBRARYCACHE_PK .3 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY 1.3 SYSAUX
SYS WRH$_BUFFER_POOL_STATISTICS .1 SYSAUX
SYS WRH$_BUFFER_POOL_STATS_PK .1 SYSAUX
SYS WRH$_WAITSTAT_PK .7 SYSAUX
SYS WRH$_PROCESS_MEM_SUMMARY_PK .1 SYSAUX
SYS WRH$_SHARED_POOL_ADVICE_PK .2 SYSAUX
SYS WRH$_STREAMS_POOL_ADVICE .3 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS .7 SYSAUX
SYS WRH$_SYSSTAT 3.5 SYSAUX
SYS WRH$_PARAMETER_BL .3 SYSAUX
SYS WRH$_OSSTAT_NAME .1 SYSAUX
SYS WRH$_SEG_STAT_BL .2 SYSAUX
SYS WRH$_SESSMETRIC_HISTORY .1 SYSAUX
SYS WRH$_WAITCLASSMETRIC_HISTORY 2.0 SYSAUX
SYS WRH$_CR_BLOCK_SERVER_PK .1 SYSAUX
SYS WRH$_ASH_BL_PK .1 SYSAUX
SYS WRH$_TABLESPACE_STAT_BL .1 SYSAUX
SYS WRH$_ACTIVE_SESSION_HISTORY .9 SYSAUX
SYS WRH$_SERVICE_WAIT_CLASS_BL_PK .1 SYSAUX
SYS WRH$_SEG_STAT_PK 1.3 SYSAUX
SYS WRH$_OSSTAT_PK .6 SYSAUX
SYS WRH$_SQLSTAT 6.0 SYSAUX
SYS WRH$_DATAFILE_PK .1 SYSAUX
SYS WRH$_SYSTEM_EVENT 1.4 SYSAUX
SYS WRH$_SQLSTAT_PK 2.6 SYSAUX
SYS WRH$_SQLSTAT_INDEX 1.6 SYSAUX
SYS WRH$_ENQUEUE_STAT 2.0 SYSAUX
SYS WRH$_PGASTAT .3 SYSAUX
SYS WRH$_RESOURCE_LIMIT_PK .2 SYSAUX
SYS WRH$_INSTANCE_RECOVERY .1 SYSAUX
SYS WRH$_THREAD .1 SYSAUX
SYS WRH$_THREAD_PK .1 SYSAUX
SYS WRH$_SYS_TIME_MODEL .7 SYSAUX
SYS WRH$_SEG_STAT 2.6 SYSAUX
SYS WRH$_DLM_MISC .1 SYSAUX
SYS WRH$_SYSMETRIC_HISTORY_INDEX .1 SYSAUX
SYS WRH$_FILEMETRIC_HISTORY_INDEX .1 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_BL_PK .1 SYSAUX
SYS WRH$_ROWCACHE_SUMMARY_PK 1.3 SYSAUX
SYS WRH$_SERVICE_STAT_BL_PK .1 SYSAUX
SYS WRH$_PARAMETER_PK 2.8 SYSAUX
SYS WRH$_DLM_MISC_PK .1 SYSAUX
SYS WRH$_INST_CACHE_TRANSFER_PK .1 SYSAUX
--------------
sum 103.1
Note that the script reports 5M more than my sum of table and index sizes (108M vs 103M). Close enough for government work!
Hi Kerry,
Blogged about this here.
http://oracledoug.com/serendipity/index.php?/archives/1553-My-Favourite-Oracle-Blog.html
Cheers,
Doug
My Favourite Oracle Blog…
Some features in this post require a Diagnostics Pack license.I think there a quite a few decent Oracle blogs around. There are links to some of them over there on the right. But by far my favourite this year has been Kerry Osborne’s. I think there ar…
[…] This post was Twitted by gvwoods […]
I think it can be nice to add Tim Gorman’s sp_parm_changes for using with statspack for the ones who are looking for license free option of tracking parameter changes.
I also modified that one for calculated values
http://www.evdbt.com/sp_parm_changes.sql
[…] Kerry Osborne-Tracking Parameter Changes […]
Thanks for the comment Coskan.
And thanks for the link to Tim’s script. Statspack is 95% of AWR with 0% the price (at least up through 11g – who knows in the future).
Kerry
I was just working on something that involved AWR (or Statspack ) retention. I was given about 75 statspack reports and asked to work up some Excel graphs of anything I found significant. At first I thought that I would have to copy all that data off the reports and into Excel to do the analysis and graphng. Groan. Lucky for me, in this case, the statapack data was still on the database so I was able to run some queries to extract what I needed into Excel.
But what if I had been given this task for data that had aged out of the statspack tables? Then I would have been stuck. At the same time, I can see the value in the types of analysis that could be done with a few years of AWR/statspack data. It could be useful to track paramter changes over time, it could useful to compare performance this month with the same month last year, etc, etc. But a lot of us don’t want to store all that statspack data in our production systems.
Which leads me to wonder about using some data warehousing techniques to extract this data to a data warehouse. The data ought to be easy to extract as it is all keyed by snap_id. As you show, by data warehouse standards, it is also not a large amount of data so even a modest DW server could hold a few years worth. I believe all the AWR/statspack data is actually keyed by db_id/snap_id so it should be possible to store this data from multiple databases in one DW and so some cross-system analysis, which might also be interesting.
It also seems to me that someone else has already done this but so far I haven’t found any references to it.
[…] Post discussing bind variable peeking issue and the uses of AWR/ASH data to help resolve them […]
Gord,
After looking into how much space is required by the data, I am in favor of keeping a much larger window of data than I was previously. For the past few years I have pushed for 30-60 days of history. But I’m thinking now that a year is not at all unreasonable. I like your idea of combining the data from multiple databases into a centralized location. I have not seen anyone do that either.
Kerry
Kerry
I agree, something like a year would be reasonable. Right now, I’d like to be able to compare Dec 2009 with Dec 2008 to see what’s different but of course Dec 2008 is long gone. ( Unless I can convince a UNIX admin to pull some old backups and load them onto another system.)
Currently I only keep 30-60 days like you do. After the Christmas break I’m going to look into uping that to something like 365-400 days. For the big 100GB+ systems it should not be a big issue but some of these databases serve manufacturing processes and only hold a small amount of data in flight. For those I really can’t store any more statspack data but these are the ones with the most interesting performnace issues of course. I’ll have to try some sort of centralized scheme like I was talking about, I’ll have to get working on that…
Gord
One other option I’ve been exercising is keep awr data for 90 days. In addition to that, we generate weekly awr baselines which are excluded from awr purging. We keep these baselines for 3 years. This is a nice balance between allocating too much storage and wanting to keep a longer history.
Fred,
Baselines are a good idea, although they have been a bit flakey for me, at least in 10.2. I really think a year is not unreasonable for a large database though, maybe with quarterly baselines of a couple of days going back another year. I don’t think too many people are familiar with AWR Baselines as I rarely see them. Thanks for the input.
Kerry
I thought I can also add this remark based on our experience. Initially, we opted to go with the default of 1 hour, but we found out that it is not providing us the resolution we needed to zoom in on brief and sporadic performance issues. We now set our snapshots to occur every 15 minutes. This is another reason we decided to go with baselines since our snapshots are too granular as compared to the default.
Fred,
Ah, makes sense. One hour snapshots seem to work well for me during normal steady state operations. I have increased to 15 minutes when dealing with troublesome systems, but only for short periods of time. But it makes sense to use baselines with that volume. How many snaps do you include in the weekly baselines?
Kerry
How many snaps do you include in the weekly baselines?
Since baselines are primarily used for trending and capacity planning (vs. specific incident troublshooting), we wanted to represent a typical work day, so we chose Mondays between 7:00 and 17:00.
Interesting. Thanks for your comments.
Kerry
By the way, you can see what the current retention is set to (in 11g) like so:
And set the retention like so:
Hi Kery,
If we want to monitor these init parameter changes cont, its there any way we can exclude the values which are already reverted back to original.
Ex: If Job_queue_process has original value of 10 in snap 1 and it has changed to 0 in snap 2. But if that has changed back to 10 in snap 3 we dont want to get this notified in script. Is it a possibility ?
Thanks in Advance.
Sunil
Hi,
Please provide the parm_hist script
set linesize 155
col time for a20
col parameter_name format a50
col value for a20
col snap_id for 9999999
break on instance skip 3
select a.snap_id,to_char(end_interval_time,’DD-MON-YY HH24:MI’) TIME, parameter_name, value
from dba_hist_parameter a, dba_Hist_snapshot b, v$instance v
where a.snap_id=b.snap_id
and a.instance_number=b.instance_number
and parameter_name like nvl(‘¶meter_name’,parameter_name)
and v.instance_number = a.instance_number
order by 1,2
/
Hi Kerry,
Today we wanted to find out whether a parameter was there in the spfile a month back or not?
I guess AWR captures the values that are in use at that point of time. Is there a way to see the history of spfile values for a period?
Thanks,
Nehru.