Re: autotrace issue

Thanks Wolfgang. Will give that a try.
Thanks all for your time and help.

sanjay

On 10/17/05, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
>
> With "method_opt => 'FOR ALL COLUMNS SIZE AUTO'" you get histograms on
> all columns that have been used in predicates. That will explain why you
> get different plans for different predicate values.
> Try to NOT collect a histogram for that column and see what plan you
> get. If the cardinality is not high enough for the optimizer to use an
> index access - and you KNOW that an index access is the way to go always
> - then raise the cardinality until the optimizer does use the index.
>
> Sanjay Madan wrote:
> > Chris/ Wolfgang,
> >
> > Thanks for your replies.
> >
> > Chris,
> > U are right .. It isnt a FTS, its a Full Partition Scan.
> > The autotrace output is:
> > TABLE ACCESS (FULL) OF 'PART_TAB' (Cost=8412 Card
> > =19 Bytes=2109)
> >
> > The plan_table clearly shows thats it will scan just one partition and
> > not the full table.
> >
> > Still that behaviour is not as expected. The table access(full) run
> > takes about 1:56 mins .. whereas if i give the index hint, it takes
> > about 0:35 mins. ( I had initially thought that this happens only when
> > the input value is a non-existent one.. maybe thats forcing a
> > full-partn-scan. But thats not the case. It happens randomly even for
> > values that do exist in the table.).
> >
> > Wolfgang,
> >
> > user_at_DB1 > show parameter cursor
> >
> > NAME TYPE VALUE
> > ------------------------------------ ----------- ------
> > cursor_sharing string EXACT
> > cursor_space_for_time boolean FALSE
> > open_cursors integer 1000
> > session_cached_cursors integer 0
> >
> >
> > I collect statistics using the following ( once a week) :
> >
> > dbms_stats.gather_schema_stats(ownname=> 'DBOWNER',
> > estimate_percent =>
> > DBMS_STATS.AUTO_SAMPLE_SIZE,
> > method_opt => 'FOR ALL COLUMNS SIZE AUTO',
> > cascade => TRUE);
> >
> >
>
>
> --
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com <http://www.centrexcc.com>
>

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