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 <return> 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 <return> 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 <return> 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 <return> 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 <return> 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 <return> to use the current value:    274.0
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables: 
 
**   Value for 'Number of Tables': 274
 
|
| For 'Number of Partitions',
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions: 
 
**   Value for 'Number of Partitions': 0
 
|
| For 'Statistics Retention',
|   Press <return> to use the current value:     31.0 days
|   otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention: 
 
**   Value for 'Statistics Retention': 31
 
|
| For 'DML Activity',
|   Press <return> to use the current value:        2 <medium>
|   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!

18 Comments

  1. 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…

  2. [...] This post was Twitted by gvwoods [...]

  3. coskan says:

    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

  4. osborne says:

    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

  5. Gord Irish says:

    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.

  6. [...] Post discussing bind variable peeking issue and the uses of AWR/ASH data to help resolve them [...]

  7. osborne says:

    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

  8. Gord Irish says:

    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

  9. Fred Habash says:

    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.

  10. osborne says:

    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

  11. Fred Habash says:

    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.

  12. osborne says:

    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

  13. Fred Habash says:

    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.

  14. osborne says:

    Interesting. Thanks for your comments.

    Kerry

  15. osborne says:

    By the way, you can see what the current retention is set to (in 11g) like so:

    SYS@dbm1> !cat get_awr_retention.sql
    col a for a20 head "Snapshot Interval" 
    col b for a20 head "Retention Interval" 
    col c for a20 head "TopNSQL"
    select
    to_char(extract( day from snap_interval) *24*60+
    extract( hour from snap_interval) *60 + 
    extract( minute from snap_interval ))||' Minutes' a ,
    to_char((extract( day from retention) *24*60+ 
    extract( hour from retention) *60+ 
    extract( minute from retention ))/(24*60))||' Days' b,
    ltrim(topnsql) c
    from dba_hist_wr_control
    /
    

    And set the retention like so:

    SYS@dbm1> !cat set_awr_retention.sql
    exec dbms_workload_repository.modify_snapshot_settings( retention => &days*24*60 );
    
  16. Sunil says:

    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

Leave a Reply