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
-- //www.freelists.org/webpage/oracle-l
