They say you learn more from your mistakes than your successes. I’ve been making a big mistake with a script I was using to tell how many logons had occurred between two AWR snapshots. I don’t want to lead anyone astray by posting the broken script so here is the one that works:
select to_char(sn1.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') first_snapshot_datetime, to_char(sn2.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI:SS') second_snapshot_datetime, after.value-before.value from DBA_HIST_SYSSTAT before, DBA_HIST_SYSSTAT after, dba_hist_snapshot sn1, dba_hist_snapshot sn2 where before.stat_name='logons cumulative' and before.stat_name=after.stat_name and after.snap_id =(select min(ss.snap_id) from dba_hist_snapshot ss where ss.snap_id > before.snap_id) and before.snap_id=sn1.snap_id and after.snap_id=sn2.snap_id order by before.snap_id;
This takes the total number of logons as recorded by the system statistic “logons cumulative” at the end of one interval and subtracts it from the same statistic as recorded at the end of the next interval. But my original script used BEGIN_INTERVAL_TIME and only on the first snapshot so I thought the interval was one hour earlier than it really was. So, I guess the message here is that if you want to get the difference between a system statistic value as recorded by two snapshots use END_INTERVAL_TIME of each snapshot to show you the time frame you are really examining.
– Bobby
P.S. Here is some output from the working script:
FIRST_SNAPSHOT_DATE SECOND_SNAPSHOT_DAT AFTER.VALUE-BEFORE.VALUE ------------------- ------------------- ------------------------ 2012-05-15 00:00:01 2012-05-15 01:00:19 286 2012-05-15 01:00:19 2012-05-15 02:00:33 186 2012-05-15 02:00:33 2012-05-15 03:00:46 184 2012-05-15 03:00:46 2012-05-15 04:00:59 179 2012-05-15 04:00:59 2012-05-15 05:00:12 201 2012-05-15 05:00:12 2012-05-15 06:00:26 294
