2 Replies Latest reply on May 4, 2013 11:44 AM by 696547

    Execution Plans & Optimizer Version


      I was working with the examples given in 'Oracle® Database Performance Tuning Guide 11g Release 2 (11.2)' (Chapter 11: The Query Optimizer).

      Environment details: Oracle 11gR2 ( on Windows 7 (32 bit)

      Basically, I tried changing OPTIMIZER_FEATURES_ENABLE ( <-> <-> and generated explain plans for same queries. I can notice and want to understand why there are significant changes in join methods, join orders and estimated rows while everything is constant in the database except OPTIMIZER_FEATURES_ENABLE. I can understand that its like running same queries on different versions but did not expect these many changes.

      I have documented my activities here:

      Ankit Rathi
        • 1. Re: Execution Plans & Optimizer Version
          Srini Chavali-Oracle
          This is normal and expected - the optimizer is continually being changed/tuned to provide the most efficient execution plan - the plan will vary from version to version depending on the features available in the optimizer for that version, and the amount/type of statistics available in that version.

          See the "Note" section here - http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i82005

          1 person found this helpful
          • 2. Re: Execution Plans & Optimizer Version
            Hi Shrini,

            Thanks for the response.

            Yeah, I got a bit carried away in the differences to notice if the plan has improved over the releases.

            Better stats available -> better/effective plan

            I hope this can be validated by using 10053 trace event.

            Ankit Rathi