Re: autotrace issue

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