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