Re: dbms_stats.auto_invalidate
- From: Stalin <stalinsk@xxxxxxxxx>
- To: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- Date: Mon, 9 Oct 2006 18:44:08 -0700
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@xxxxxxxxxx> 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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)
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
