| Queries "break" after automatic statistic gathering. [message #620439] |
Fri, 01 August 2014 07:48  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
From another thread:
cookiemonster wrote on Thu, 31 July 2014 09:46So the query returns 2.7M/3.3M = 82% of the records.
Oracle is never going to use an index for that, it would be hideously inefficient for it to do so.
The cut off percentage of rows above which oracle stops using an index depends on a lot of factors but it's generally down around 10% or lower.
What is REALLY funny is that I have just run into this problem tonight "in reverse". Oracle IS using an index, despite it being horrible inefficient.
I export about 95% of a join of our article and stock tables, but only for branches and article groups that are in a "config table". For about 3 years it worked fine in ~10 minutes, Oracle doing a FTS of the article and stock tables then a hash join, then filtering against the config table last. Pretty straightforward execution plan. Three FTS, three hash joins.
Now THIS night it ran ~4 hours, because Oracle decided to first join the config table and the article table, THEN go over the article/branch index of the stock table (which also happens to be the primary key).
Haven't found the cause yet, only that the statistics for the article table where analysed two days ago, the ones for the stock table today by the 'auto optimizer stats collection' auto task, about 2 hours before that job ran. I wonder if anyone has ever seen something in that direction?
At the moment I decided to first gather the statistics for those three tables again manually, and see if that changes anything. I will post an update if I find anything interesting.
|
|
|
|
| Re: Queries "break" after automatic statistic gathering. [message #620440 is a reply to message #620439] |
Fri, 01 August 2014 07:55  |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
From the top of my head, a few things:
Any histograms in place?
What sample size is used?
Any possibility of the data being "wrong" (i.e. mid load) when they were gathered?
Have you checked they're not just goosed - I've seen that before. Correlate the blocks shown in dba_tables vs those shown in dba_segments - it's a good way to see if the stats are telling fibs.
|
|
|
|
| Re: Queries "break" after automatic statistic gathering. [message #620442 is a reply to message #620439] |
Fri, 01 August 2014 08:05 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Do any of the predicates involve more than one column, or a function on a column? If so, unless you have created extended stats (which so few people do) the cardinality estimates will be way off. I believe that lack of extended stats is responsible for the vast majority of the CBO's "mistakes" (in quotes, because it is actually our mistake, not the CBO's). The inaccuracy is always towards too high selectivity. You get the wrong driving table, and indexed loop joins instead of scanned hash joins.
I ALWAYS set optimizer_dynamic_sampling=4, which permits the CBO the opportunity to gather extended stats on the fly if necessary. Improvement can be spectacular.
But of course, none of that would explain why the query was running fine before. If you run it again, does cardinality feedback kick in?
|
|
|
|
|
|
| Re: Queries "break" after automatic statistic gathering. [message #620467 is a reply to message #620457] |
Fri, 01 August 2014 13:34 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Thomas,
In my production environment, for the objects dedicated to batch process, we do not gather stats anytime other than scheduled On-release activities or data conversion activities. So my first question is whether you too have such kind of scenario? Did anything majorly change before the new stats were gathered? Usually, entire team is aware of any such scheduled maintenance activity unless something is covered under secret blanket.
So, it is important to know :
1. Whether the queries running slow now, are a part of daily batch process?
2. How frequently do you gather stats?
3. Did anything change in the system?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
