Re: Help interpreting dba_hist_sqlstat

On Nov 22, 5:15 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
>
> 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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

Are you talking about the case where you do a DDL on the table referenced in the SQL therefore invalidating the cursor body? I think it happens in 9i only. In 10gR2 (not sure about R1), DDL will remove the entire cursor, not just heap 6.

The DDL in this case includes analyze (special because it doesn't update last_ddl_time), and even dbms_stats.gather_table_stats unless no_invalidate is true. (In 10g, gather_table_stats doesn't clean out the cursor stats regardless the no_invalidate setting.)

Yong Huang Received on Sat Nov 24 2007 - 13:33:51 CST