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
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 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 - http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.htm#i82005

    HTH
    Srini
  • 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.

    Regards,
    Ankit Rathi

Legend

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