Re: dbms_stats.auto_invalidate

First of all, if you need dependent plan(s) to be invalidated then do set no_invalidate=false rather than rely on auto_invalidate.

I could be completely mistaken here, but I believe I read somewhere ( can't remember where and thus I'm mot sure it was the right context ) that auto_invalidate was introduced to avoid the performance shock of all cursors to be invalidated by e.g. a gather_schema_stats, import_schema_stats, or simply the gather_table_stats of a central table that is referenced in a lot of plans. IIRC, auto_invalidate will invalidate plans gradually over time, so it is not a decision of whether to invalidate a plan or not but a decision which ones to invalidate sooner and which ones later.
But as I said, I could be totally wrong in that.

At 07:44 PM 10/9/2006, Stalin wrote:
>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.

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

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