Help interpreting dba_hist

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Fri, 16 Nov 2007 18:51:03 GMT
Message-ID: <rEl%i.311$oL5.34@trnddc05>

Oracle version 10.2.0.2

I need a little help with the dba_hist_sqlstat view in determining why executions_total - executions_delta doesn't always equal the previous snapshot's executions_total. Below I've queried the view and show for a single SQL three different successive snapshots where that sql was in the snapshot. Between lines 2 and 3 everything balances out but between 1 and 2 it does not. Is this because the sql was flush from the library cache between the hourly snapshots (notice the gap in snap_id's). If so, why doesn't the executions_total get reset to 0 since the manual defines the column as "Cumulative number of executions that took place on this object *since it was brought into the library cache*".

Thanks.

SQL> SELECT
2 to_char(b.begin_interval_time,'dd-MON-yy hh24:mi') begin_interval_time, 3 --to_char(b.end_interval_time,'dd-MON-yy hh24:mi') end_interval_time,

  4     a.snap_id,
  5     a.executions_total as total,
  6     a.executions_delta as delta,
  7     a.executions_total - a.executions_delta diff
  8  FROM dba_hist_sqlstat a,
  9     dba_hist_snapshot b

10 WHERE a.snap_id BETWEEN 5483
11 AND 5491
12 AND a.sql_id = 'c8afnp575a327'
13 AND a.snap_id = b.snap_id
14 ORDER BY a.snap_id;

BEGIN_INTERVAL_ SNAP_ID TOTAL DELTA DIFF

--------------- ---------- -------- -------- --------
12-NOV-07 01:00       5483  481,227    6,957  474,270
12-NOV-07 08:00       5490  517,201    6,458  510,743
12-NOV-07 09:00       5491  536,625   19,424  517,201

Received on Fri Nov 16 2007 - 12:51:03 CST

Help interpreting dba_hist_sqlstat