RSS Feed

Method R Discussion Group

I have a query which performs poorly in production, but well on the test server.  A comparision of the explain plans shows production does a FTS while test uses an index.
Test is a file copy of production from about 3 weeks ago.So, why?
1. Made sure appropriate index was in production and has the same columns in the same order. 
2. Made sure tables and indexes analyzed.

3. Looked at all_indexes view for the index in question on both systems.
      BL    LEAF  DIST  AVG_LEAF  AVG_DATA      CLUS
SVR  LEVEL    BLOCKS KEYS    BLOCKS/KEY    BLOCKS/KEY    FACTOR STATUS    NUM_ROWS      SAMPLE_SIZE
Test  2      42490      55          772        12073    664019 VALID    6032901.439    1698129
Prod  3      53836      44        1223        19604    862596 VALID     7325092.292    2148164

So, there is some increase in size in the prod system, but doesn't seem like all that much. I realize that stats on other tables and indexs have also changed, and now the optimizer sees this index as less desirable.

4. Read Breitling's "A look under the hood of CBO: the 10053 event" paper.

5. Acquired a 10053 trace from both machines.  The answer has got to be in there somewhere, right?

I pulled the two traces up in a diff editor. Of course there were hundreds of differences because the stats are different on most all of the tables and indexes.

6. I am stuck now.  What can I do to point to a place in the trace files to say, yeah, this is why it did that?

With the index stats you've provided, it looks like the statistics differences between test and production are the culprit.  Note the differences in the stats for everything: production has higher values for every statistic.  So, the calculations made by the optimizer to compute the cost of using the index was higher in production vs. test and caused the optimizer to pick the full scan. 

When you import the production data and then gather statistics in test, you will have different values.  It might be a better choice to import both the production data and the production statistics so that you really do have an identical set of both data and stats for the optimizer to work with.

You could manually change the stats on the index in test to match the stats on production and I'd bet you'd get the full scan plan.  That'd certainly be a good proof of copying both the data and the stats from production and not having stats gathered separately in test.

So, I'd say you've got at least a couple of choices: 1) see if there's another way to write the query that helps the optimizer have an easier time with its calculations of cost or 2) modify the estimate % for your collection to see if a higher (or lower) % provides better estimates.

But, I think the "why" it happened is clear.  Production and test statistics are different...therefore, the optimizer made a different choice.  If the index plan is the better performing plan, you now need to work on a way to make the production optimizer able to derive the same/better plan.

Thanks for the input Karen.  I agree with what you said.  I ended up putting an INDEX() hint in the production code.  In OPINT class they termed this a BAD hint, but I don't really see that there are viable options.  Maybe I could spend another day or two figuring out cluster factors/ density etc, and then there is a good chance I would use the hint anyway.

Well, sometimes you just may not be able to avoid the use of a hint.  And "bad" is relative.  It's always a good idea to try and find a way to get the plan you want without a hint as it allows the optimizer the flexibility to change a plan later if it is truly warranted without the need for you to intervene and change the code to remove a hint.  However, if you're confident that the INDEX plan is going to be the best plan for the foreseeable future, then it's not really bad, is it?  :)

The bottom-line is that you were able to evaluate why the index was being used and were then able to choose to use a hint to solve your problem.  I think it's a win whenever you *know* why something happened and can choose your response.  That's much better than just guessing and not understanding why your action was needed and successful.  It makes the whole process less a mystery and gives you confidence in what you're doing.  Pretty cool...