running 11.2.0.3.0 on Solaris 10/Unix Sparc server (2 cpus 16 cores) 12 terabytes, 300 tables. Before partitioning plans used range scan with occasional fast full index scan, query times ok.
Now after reference partitioning we are seeing range scans replaced by fast full index scans all over the place. run times went from minutes to hours. Not just on partitioned tables but more often on non partitioned tables that join to them. we have run full stats, both complete and auto sample. we have done histograms. nothing changes it from doing fast full or full index scans. we have had to restore to hints but we are talking dozens and dozens of queries. Its killing us.
Does anybody have a clue as to why the optimizer would start picking fast full scans instead of range scans? I know this is not much info, but we can't explain why it would pick them (of course I know its becuase it determines the cost if lower but still dont understand why since stats are up to date)