This discussion is archived
2 Replies Latest reply: May 4, 2013 4:07 AM by Srini Chavali-Oracle RSS

Execution Plans & Optimizer Version

696547 Explorer
Currently Being Moderated

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 Oracle ACE Director
    Currently Being Moderated
    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 -

  • 2. Re: Execution Plans & Optimizer Version
    696547 Explorer
    Currently Being Moderated
    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


  • Correct Answers - 10 points
  • Helpful Answers - 5 points