RE: stupid question on FTS
- From: "Uzzell, Stephan" <SUzzell@xxxxxxxxxx>
- To: "'vasudevanr@xxxxxxxxx'" <vasudevanr@xxxxxxxxx>, "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 24 Aug 2012 13:09:16 +0000
Are you seeing an FTS on the whole table? Or on a partition?
We've seen something similar - with a very large table with monthly partitions.
It *seems* to us that the 10g stats job that collects stale stats looks at the
table as a whole, not the partition. Because the new monthly partitions are so
small relative to the table, they don't trigger the collect stale stats job.
Therefore Oracle has no stats on the new partitions, thinks they are tiny,
thinks the FTS will be cheap, and chooses that over a more appropriate index
scan.
We're still working on how to best manage statistics for the new partitions...
Stephan Uzzell
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Vasu
Sent: Thursday, 23 August, 2012 20:35
To: oracle-l@xxxxxxxxxxxxx
Subject: stupid question on FTS
I have a huge table in my OLTP DB, that has 100 million+ rows (pls don't ask
why), and is partitioned.
I know it doesn't make sense for my App to ever do a FTS on it.. But Oracle at
times picks up FTS as the best access path (and our STATS is not at it best
yet).
It just wished for a setting..that "I never want to have a FTS on my
table..Unless otherwise explicitly told thru a Hint" .
yes, a Hint/setting can't compensate for lack of STATS.. but just that my
desparate situation/laziness forced me to think that way.
Any thoughts?
Thanks,
Vasu
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
