Re: Help interpreting dba_hist

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 19 Nov 2007 20:57:04 -0000
Message-ID: <xOOdnZgVqY0AZdzanZ2dnUVZ8uSdnZ2d@bt.com>

"Chuck" <skilover_nospam_at_bluebottle.com> wrote in message news:zBl0j.689$oL5.48_at_trnddc05...

> 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.

AWR collection is engineered to avoid working too hard if possible - which includes limits on the amount of data dumped to the tables. For the 'typical' level, only the top 30 statements are flushed at each snapshot.

Looking at your results I think this means that there were some statements (more than 30) which were busier than the one are interested in (sql_id = 'c8afnp575a327') from 1am to 7am, so the statistics were increasing in memory, but not fast enough to get into the dump - then from 7:00 am to 8:00 this statement started working harder (or the others dropped back) so that this was came back into the top 30.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Received on Mon Nov 19 2007 - 14:57:04 CST

Re: Help interpreting dba_hist_sqlstat