DBA_HIST_SNAPSHOT BEGIN and END_INTERVAL_TIME

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

About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply