hpuxrac wrote:
> 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 ...
No, there were no snapshots that included sql_id c8afnp575a327 from
02:00 through 07:00.
Received on Mon Nov 19 2007 - 13:37:03 CST
