Re: dbms_stats.auto_invalidate

Hi Ric,

I understand the 9i/10g behavior but i'm interested to know how oracle decides whether to validate or no_validate cursors. In 10g, by default no_validate is set auto_invalidate.

I had a situation where the user had imported data on a fresh database, gathered stats using gather_schema_stats(ownname, estimate_percent=>dbms_stats.auto_sample_size, cascade=>true); However, this didn't gather histograms in one of the key table (even though
method_opt was set to default 'for all columns size auto'), which had performance issues after import. So i gathered table stats setting method_opt to 'for all indexed columns size skewonly') and i ran the problem sql in sqlplus and forced sql to hard parse, now it picked up right access path and the index. however, the application was still using the old sub-optimal plan. I had to flush shared pool in getting the application to pick the right plan.

Now, why didn't oracle invalidate the cursors automatically while i gathered table stats with skewonly.

Thanks,
Stalin

On 10/9/06, Ric Van Dyke <ric.van.dyke_at_hotsos.com> wrote:
> The default behavior in 9 and earlier is that when new stats are
> collected all dependent cursors are invalidated, meaning a hard parse
> will happen the next time the cursor is referenced.
>
> Starting in 10 the default is to NOT invalidate cursors when new stats
> are collected. Which means that after collecting new stats on an object
> dependent cursor will not be hard parsed the next time they are
> referenced.
>
> Ric Van Dyke
> Hotsos Enterprises
> -----------------------
> Hotsos Symposium March 4-8, 2007. Be there.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stalin
> Sent: Monday, October 09, 2006 6:39 PM
> To: Oracle Discussion List
> Subject: dbms_stats.auto_invalidate
>
> Hi,
>
> I couldn't find any references as to when oracle would decide to
> validate or not_validate dependent cursors upon gather stats. Any
> plausible explanation is appreicated.
>
> Thanks,
> Stalin
>
> 10g (10.1.0.4)
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l