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 (184.108.40.206) on Windows 7 (32 bit)
Basically, I tried changing OPTIMIZER_FEATURES_ENABLE (220.127.116.11 <-> 10.2.0.1 <-> 18.104.22.168) 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:
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