Why is Explain Plan different between environments
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Why is Explain Plan different between environments

  1. #1
    Join Date
    Mar 2004
    Posts
    4

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Can you see a difference between the statistics on the table and index between the two partitions? eg., clustering factor on the index partitions?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2004
    Posts
    4
    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.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ... 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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2004
    Posts
    4
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width