Hungry DBA .com for DBAs who like food

To peek or not to peek that is the question

In order to make the most efficient use of your shared-pool and make your application as high performing and scalable as possible by avoiding frequent parsing of SQL; best practices dictate using bind variables.

Unfortunately the decision of when to use bind variables is not as straight forward as it would seem.

The problem with bind variables is that Oracle is unable to make full use of all available statististics (the optimizer will not make full use of histograms) in order to calculate the explain plan… so the explain plan used could be sub-optimal.

From 9i Oracle attempted to tackle this problem by introducing bind variable peeking. When optimizing a SQL statement that uses bind variables; Oracle will peek the value of the first bind variable that is parsed so that it can use that value to calculate a optimal explain plan based on all available statistics (as it would if a literal value was used).

The problem with this approach is that Oracle will then use that same explain plan for all following executions of the same SQL statement. This is a really bad idea since it is possible that explain plan which was optimal for the first parsed data value will not be optimal for the majority of data values. For example, the first parsed data value may cause a full table scan and therefore performance will then be bad for all following executions.

Therefore it is advisable to disable bind variable peeking by setting the Oracle parameter "_optim_peek_user_binds" to FALSE.

The downside of disabling this feature, as explain earlier, is that Oracle will not use all available statistics in order to calculate the most optimal explain plan.

Using a literal value will overcome this issue but will also mean that the SQL (assuming that different literal values are used) will not be shared; thus the shared pool can become overloaded.

So it’s left to us to calculate what is the lesser evil…

In a situation where the performance of a query that is using bind variables is poor, then it is sensible to question if bind variable usage is a necessity considering the optimizer limitations regarding bind variables; for example:

1. Are the problem queries very frequently executed?

2. Is an accurate plan for every execution of a query more important than a few extra cursors in the shared pool?

From the optimizer point of view, it is not recommended to use bind variables for queries where data values are critical to the selection of a good plan. Bind variables are recommended for situations where cursors are frequently executed with different column values and would otherwise cause shared pool fragmentation and contention, and whose best plans are not value sensitive.