Why is Explain Plan different between environments

The optimizer is using a different explain plan for the same query between my staging environment and the production environment. Staging is using an index while production uses a full table scan

All of the parameters (validated from v$parameter) are the same in the 2 environments. The statistics for the table were calculated on staging and imported to production so those are the same. As far as I can tell there is no logic reason for the difference so why are the plans different?

The table is partitioned (range by date) and if the query is executed on production against last months partition, the plan will use an index. Executed for the current month will use a full table scan (staging uses the index for both queries).

I have re-ran statistic (I am using global stats) in production but the plan did not change. What should I do to get production using the correct plan?

Thanks in advance.

Can you see a difference between the statistics on the table and index between the two partitions? eg., clustering factor on the index partitions?

We are using global statistics so there are no stats at the partition level. The clustering factor on the index is little different between the staging and production environment (5252629 vs 6272657 respectively). Everything else looks the same.

Quote:

---

... if the query is executed on production against last months partition, the plan will use an index. Executed for the current month will use a full table scan (staging uses the index for both queries). ...

---

If your queries are using partition elimination, you need partition level statistics -- the global statistics are only used for queries that cannot use partition elimination. So it sounds like the queries are effectively running without statistics.

Gathering partition-level stats might not solve this problem, but i would certainly try gathering them and retesting before you do anything else.

Oracle's repsonse was to run partition stats also. Your explaination makes more sense than theirs. This does get the query using the right plan. :) I am still wondering what caused the different plan between the 2 environments?

Thanks

It's odd bahaviour, but the only statistics available prior to the analyze would be the segment sizes for the table and index partitions, and i suppose the high-water mark on the table partition.