Oracle SQL Optimization and Variables

I was going to get around to writing something up about the Oracle Optimizer in the future, and how it can sometimes choose non-optimal plans for what it thinks are perfectly logical reasons. But suddenly today I find that the optimizer development group inside Oracle have done their own post on this. So I thought I would point you at it as an excellent description of the issues I was going to discuss, and so that you can see that in 11g Oracle have finally got around to addressing a problem caused by previous enhancements to the optimizer.

What I'd add is that I see the nature of the problem of these non-optimal SQL execution plans as being one of the consequences of previous attempts by Oracle to improve performance for the majority of SQL queries. Although most queries have indeed benefited from better and faster execution plans, some have not and have actually got worse between versions of Oracle.

Originally there was no Cost Based Optimizer (CBO) and only the Rule Based Optimizer, which was unaware of even things like table sizes. So Oracle added in the CBO and it used some simple data on table sizes and indexes on columns to make better execution decisions. Over time Oracle added more and more features to the CBO to improve the performance of most SQL statements most of the time, and added extra sets of statistics to make more accurate predictions about query execution behaviour. Unfortunately, while the vast majority of queries have indeed benefited from these changes to the way the CBO works, there have always been examples of queries that have suffered and actually got worse. The example given in the post by Oracle is just one. I know of many others.

The problem is really due to two issues, now solved by 11g, hopefully. The shared execution plan in the library cache, means that the same execution will always be used for the same SQL statement since it was first parsed ('same' meaning identical text of the SQL statement). Initially this was okay. But then Oracle introduced bind variable peeking, to try and optimize the query even further. By looking at the actual value supplied for a bind variable in a query, and using statistics on the value distribution of the queried column, Oracle could better optimize that particular query. But now all subsequent executions of the same query, regardless of the value used in the bind variable, would be forced to use the same, original execution plan. Which may not be optimal for those subsequent query executions.

There is in fact another, more manual, solution to this problem. Do not use bind variables in queries you know are sensitive to the query values used, and for which you want distinct execution plans. By putting the query value itself directly into the SQL statement, Oracle will treat each of them as distinct SQL statements and parse and optimize them individually, each with their own entry and associated execution plan in the library cache. Unless, of course, you have enabled forced cursor sharing, where Oracle dynamically rewrites SQL statements to remove hard coded values and replace them with references to bind variables. Which would simply map them all to the same, single SQL statement and execution plan in the library cache. Something to be aware of.

I've recommended this solution a few times to customers with data sets that have only a few values and very skewed distributions, and queries that do not strongly constrain on other columns. The example in Oracle's post is one such case - no other constraints other than on department, which is highly skewed. These cases are very few and far between, but often they do occur in critical pieces of code e.g. pick the next request to process that is in a 'ready to run' state. The longer such a query takes, the more time that is wasted before the application gets on with processing the request itself. And this can exasperate things when picking the request ends up taking longer than processing the request itself.

It is nice to see that Oracle have got around to addressing this problem of queries that are sensitive to the constraining values used in the query.