2 Replies Latest reply: May 4, 2013 6:07 AM by Srini Chavali-Oracle RSS

    Execution Plans & Optimizer Version

    696547
      Hi,

      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 (11.2.0.1) on Windows 7 (32 bit)

      Basically, I tried changing OPTIMIZER_FEATURES_ENABLE (11.2.0.1 <-> 10.2.0.1 <-> 9.2.0.8) 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:
      http://oraclenbeyond.blogspot.in/2013/05/oracle-join-methods-workshop.html

      Regards,
      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

          HTH
          Srini
          • 2. Re: Execution Plans & Optimizer Version
            696547
            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.

            Regards,
            Ankit Rathi