How to identify the timing when database parameters exceeding predefined threshold value

The following powerful script can be used to identify the timing when the oracle database is exceeding some
threshold value [which can be provided in this script as a input parameter] of some 500 db parameters which can be provided as another input.
This script use the table dba_hist_sysstat table. In this table there are 500 statistics  which can be analysed. [One can get entire list using "select distinct(stat_name) from dba_hist_sysstat;" query ].

This script is very useful for performance analysis of the database, to understand when db is experiencing bottleneck.

The script is as follows.

Here we have analyzed the db parameter "physical writes" and providing a threshold value of "155,000,000". Though we are analyzing entire months snapshots but finding shows that threshold value is exceeding at the end of the month during 28, 29. Based on this, we can take important decision to tune the db writes on each month.

SQL> col snap_time format a20
SQL> col value format 999,999,999

 SQL>       select to_char(begin_interval_time, 'dd-mm-yyyy hh24:mi') snap_time
,value
    from dba_hist_sysstat
    natural join dba_hist_snapshot
    where stat_name='&statname'
    and value > &statvalue
    order by to_char(begin_interval_time, 'dd-mm-yyyy hh24:mi') ;

Enter value for statname: physical writes
old   5:     where stat_name='&statname'
new   5:     where stat_name='physical writes'
Enter value for statvalue: 155000000
old   6:     and value > &statvalue
new   6:     and value > 155000000

SNAP_TIME                   VALUE
-------------------- ------------
28-08-2014 02:00      155,090,096
28-08-2014 03:00      155,147,807
28-08-2014 04:00      155,563,483
28-08-2014 05:00      155,612,245
28-08-2014 06:00      155,621,123
28-08-2014 07:00      155,626,703
28-08-2014 08:00      155,631,441
28-08-2014 09:00      155,636,134
28-08-2014 10:00      155,677,538
28-08-2014 11:00      155,903,716
28-08-2014 12:00      155,946,107

SNAP_TIME                   VALUE
-------------------- ------------
28-08-2014 13:00      155,991,519
28-08-2014 14:00      155,999,646
28-08-2014 15:00      156,005,142
28-08-2014 16:00      156,009,834
28-08-2014 17:00      156,014,457
28-08-2014 18:00      156,061,890
28-08-2014 19:00      156,102,167
28-08-2014 20:00      156,120,367
28-08-2014 21:00      156,125,586
28-08-2014 22:00      156,130,561
28-08-2014 23:00      156,135,594

SNAP_TIME                   VALUE
-------------------- ------------
29-08-2014 00:00      156,155,661
29-08-2014 01:00      156,209,887
29-08-2014 02:00      156,593,186
29-08-2014 03:00      156,597,672

26 rows selected.