Re: stupid question on FTS
Date: Fri, 24 Aug 2012 07:21:24 -0700 (PDT)
Message-ID: <1345818084.6688.YahooMailNeo_at_web121605.mail.ne1.yahoo.com>
You're running 11.2.0.3 and you haven't considered dynamic sampling? By default the optimizer_dynamic_sampling parameter is set to 2 and if you have stats available (current or not) then the optimizer won't try to improve on those stats by sampling. Setting optimizer_dynamic_sampling to a higher value (say 4 or 5) will cause the optimizer to use dynamic sampling to obtain better estimates for the statistics and can improve query plans by providing a better path. optimizer_dynamic_sampling can be set at the session or system level so if you're running this query in a script you could alter the session to set optimizer_dynamic_sampling to 4 and then run the query. Oracle will tell you in the plan output if dynamic sampling is being used and the level of the sampling:
Note
- dynamic sampling used for this statement (level=4)
David Fitzjarrell
From: Vasu <vasudevanr_at_gmail.com>
To: "Uzzell, Stephan" <SUzzell_at_micros.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, August 24, 2012 7:30 AM
Subject: Re: stupid question on FTS
I think I am in a similar situation , as the stats became stale after
adding 50% more rows to the table .
I got the stats done by referring to the article by Doug.. and the list of
useful pointers are here.
http://jonathanlewis.wordpress.com/2010/03/17/partition-stats/
I am able to get the desired access path through hints, but not so lucky at times. Though I don't expect a FTS on that table, its a close call..and optimizer may still be correct, as the specific SQL JOINing that table is inspecting less than 10% rows. I have all the necessary Indexes and the Rule-Based optimizer would have chosen the expected path (Favoring the index..than FTS ).
Will do additional analysis based on the inputs and share the results.
Thanks
On Fri, Aug 24, 2012 at 8:09 AM, Uzzell, Stephan <SUzzell_at_micros.com> wrote:
> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Vasu
> Sent: Thursday, 23 August, 2012 20:35
> To: oracle-l_at_freelists.org
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- -Vasu -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 24 2012 - 09:21:24 CDT