I found in tuning general purpose queries, performance varies depending on the values of certain filtering values in a query. An example of this is based on a histogram of a particular column in a table which is the value I need to select by. So if I tune the query using one specific value I may be using one that is on the lighter side ( fewer rows for value) of the spectrum, but if I execute it using one from the middle or higher side I get totally different results. My enhancement suggestion for
this is to allow entry of a range, or list of individual values for my bind variable, to where I can do a more realistic tuning job if I could evaluate all the rewrites using multiple values. We can already execute each query multiple times. All you could do is change the multiple-value bind variable to a different value and average the results.
Yes, it is a good idea that we have considered for years, but we are worry about how much people can understand and appreciate this feature. Now, we will put in a higher priority new features list.
Richard
Your case depend on Oracle version. In 11gR2 oracle has several cursor for same SQL depending on bind values … just because of bind problem.
In 12c they are going even further …