Re: Help interpreting dba_hist_sqlstat

On Nov 25, 3:33 am, Yong Huang <yong..._at_yahoo.com> wrote:
> 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

I should add that, for the last part, my 10gR2 database has the default no_invalidate which is auto_invalidate. My simple test happened to not cause Oracle to invalidate the cursor.

Yong Huang Received on Sat Nov 24 2007 - 13:59:54 CST