Re: stupid question on FTS
Date: Fri, 24 Aug 2012 12:13:01 -0700 (PDT)
What I do with new partitions is to copy the stats from the previous partition to the newly created one. It helps keep the performance hits to a minimum when new partitions are added. The dbms_stats.copy_table_stats procedure does what I need to populate empty partition statistics.
From: "Stephens, Chris" <Chris.Stephens_at_adm.com> To: "vasudevanr_at_gmail.com" <vasudevanr_at_gmail.com>; "Uzzell, Stephan" <SUzzell_at_micros.com> Cc: "oracle-l_at_freelists.org" <oracle-l_at_freelists.org> Sent: Friday, August 24, 2012 12:20 PM
Subject: RE: stupid question on FTS
Maybe I'm missing something but if collecting statistics is considered too resource intensive, why not just manually set them to reasonable values any time a new partition is created so the optimizer is more likely to figure out that bad access paths are, in fact, bad access paths?
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vasu Sent: Friday, August 24, 2012 8:30 AM
To: Uzzell, Stephan
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.
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
> Stephan Uzzell
> -----Original Message-----
> From: 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?
-- -Vasu -- http://www.freelists.org/webpage/oracle-l CONFIDENTIALITY NOTICE: This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply. -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 24 2012 - 14:13:01 CDT