We are going to Oracle 11g in our customer's system. One of our DBAs took a look at the new features for 11g. The developers were supposed to see whether we could take advantage of these features. So far I only took a look at the first feature mentioned. That is Adaptive Cursor Sharing (ACS).There are specific requirements for ACS to kick in. You need to be doing an EXECUTE IMMEDIATE in your PL/SQL. You also need to be using bind variables. Finally I hear that if you use too many bind variables (15 or more), then ACS will be skipped.
The optimizer may use different execution paths based on the values in the bind variables. Skewed column data may cause the execution to be suboptimal. Normally the optimizer makes use of histogram data generated from gathering statics.
With ACS, the database tries to determine which values for bind variables work best with certain execution paths. Then it would use this information in the future to choose the best execution path. We get this benefit automatically in 11g. It can be disabled. I am thinking we shall not and try to get the benefit.
