Re: Help interpreting dba_hist_sqlstat

"Chuck" <skilover_nospam_at_bluebottle.com> wrote in message news:EJE0j.12$Xg.9_at_trnddc06...
> Jonathan Lewis wrote:
>
>>
>> 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.
>
> Thanks Jonathan.
>
> One more question. If the sql_id I'm interested in where *completely*
> flushed from the shared pool (not just from the top 30), does Oracle
> still maintain the statistic counters for it somewhere? Or will they be
> reset to 0's.
>
> What I'm trying to accomplish is seeing how much elasped time that
> sql_id consumed over the past week. If the counters ever get reset to 0,
> I want to know that and account for it in my query.

If a cursor is completely flushed from the library cache, it's gone, and the statistics are gone with it - there isn't even a place to show a zero.

I think there may be odd cases where a cursor can lose its sub-heaps for a while (kicked out of memory) and show zero counts - then - some time later, a re-execution causes a new optimisation and some of the statistics appear from somewhere. I've never tracked down how this can happen, though - but in these cases some part of the cursor (including the text) was still in memory all the time.

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