Recording Oracle System Stats for historical analysis...

If you are experimenting with gathering system statistics it might be helpful to archive your current settings and any intermediate settings you come up with along the way.

There is a way to save stats to a table using DBMS_STATS.CREATE_STAT_TABLE and gathering with DBMS_STATS.GATHER into that table, but the format is cryptic and it is nice to have the descriptive parameter names tagging along with the data. (In a future post I will cover format of the CREATE_STAT_TABLE format).

The current system stats info is held in the sys.aux_stats$ table. Since the format is a little wacky, I came up with the following table to hold stats and the following insert statement to populate it after every gathering of system stats.

Now you can easily query the values of old stats in the SYSTEM_STATS_HISTORY table:

-- Create a table to hold system stats info
create table SYSTEM_STATS_HISTORY as 
(SELECT PVAL2 as STATUS,
        SYSDATE as DSTART,
        SYSDATE as DSTOP,
        PVAL1 as FLAGS, 
        aux_stats$.* FROM sys.aux_stats$ WHERE 0=1);

To record the current system statistics, run this statement:

-- Record the current system statistics, run this statement
INSERT INTO SYSTEM_STATS_HISTORY 
    SELECT (SELECT PVAL2 FROM sys.aux_stats$ where PNAME='STATUS') as STATUS,
           (SELECT PVAL2 FROM sys.aux_stats$ WHERE PNAME='DSTART') as DSTART,
           (SELECT PVAL2 FROM sys.aux_stats$ WHERE PNAME='DSTOP') as DSTOP,
           (SELECT PVAL1 FROM sys.aux_stats$ WHERE PNAME='FLAGS') as FLAGS,
           aux_stats$.*
      FROM sys.aux_stats$
     WHERE sname='SYSSTATS_MAIN'; 

And here is my favorite way to gather system stats. Specify an interval so you don't have to wait around to run a 'Stop'. Specify the interval in Minutes:

exec dbms_stats.gather_system_stats(gathering_mode => 'interval',interval => 30);