On Nov 16, 12:51 pm, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> 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
You say "three successive" snapshorts but that's not what the interval
and snap_id's are showing.
Looks like you are missing the interval's from 2:00 to 7:00 ...
Received on Sun Nov 18 2007 - 07:55:37 CST
