In the 3rd part of this series, I introduced you to the concept of the bind variables. We also learned their importance in regard to cursors, i.e. how with the presence of bind variables, cursor reparsing can be reduced significantly. But as is famously said in the world of performance tuning, there is nothing black or white: it’s all grey.

Similarly, it’s not all straightforward with the usage of bind variables. In this instalment of this article series, we will understand the potential issues surrounding bind variables and how to handle them.

Literals – when they are safe to be used

As discussed in the previous article, using bind variables is good when we are expecting the cursors not to require reparsing. It means that the cursors, with different bind variable values, would also need to share their execution plans. For example, what would happen if we select two records from the EMP table in the example Scott schema, selecting with the EMPNO? Since EMPNO is a Primary Key column, it is indexed and unique. This means that there is no chance of having one employee code occurring more than once. In this scenario, use of bind variables will definitely be the right thing to do. And because of the bind variables, we will end up with just one cursor being created in the memory.

Let’s see what’s really happening in the database. First we execute two different SQL statements in the Scott session and then we’ll check how many cursors are created by the database for those two different employee codes.

First, executing the queries using bind variable in the Scott schema.

Now, in a different session using the Sys user, let’s check the child cursor count:

We can see that for both the statements just one cursor is created and the plan, as expected, is an index plan because a repeat of the same employee number is just not possible.

But what would happen if the data distribution didn’t favour the reuse of the same execution plan for multiple bind variable values? What if for one value, the database would be better off using a distinct plan rather than one originally used for another value? Incidentally, this is the point where the use of bind variables becomes less useful – a phenomenon known as Bind Variable Peeking!

Bind Variables Peeking – When bind variables strike!

As mentioned earlier, use of bind variables is good if reparsing needs to be avoided. But if we change the data distribution, demanding distinct execution plans for different bind variable values, the benefit gained from the absence of reparsing starts impacting performance in an adverse manner.

Let’s explore this by creating a data set where the occurrence of one value is going to be very high and another is much lower, thus ensuring different plans must be used for both values. The column is going to be indexed. Since indexes are generally preferred by the database optimizer when the occurrence of the searched predicate value is much lower (this is known as Cardinality), and utilises the access path of Full Table Scan if the cardinality is very high, we shall use this as the basis of our demonstration. With a particular value (1), we will force the database to pick up the Index access path and with another value (99), we’ll see that the database uses the Full Table Scan to execute the query. So, let’s start by creating the table, creating an index on the ID column, and then gathering the statistics on the table with histograms.

Let’s check the data distribution in the column ID.

Now it’s certain that if we are going to select value 1, the database must choose the Index access path since it’s occurrence is much lower (just 1 occurrence in 84k rows). Let’s confirm our assumptions by executing the queries, but without using the bind variable. The reason to do so is that we want to enforce hard parsing for each execution of the query by passing the literal constants. Later, we will compare these executions along with the usage of the bind variables.

So as expected with literals being used, the database is choosing the correct execution plan. But what happens when we bring bind variables into the mix? Let’s check now by using the 10053 trace event and formatting the raw trace file with TKPROF.

Here is an extract from the formatted trace file, leaving out the sections that are not needed in this discussion.

We can clearly see in the bold sections of the output that the query was hard-parsed upon first execution, (this is reflected in the Misses in the Library Cache shown with value 1) and that the correct plan was chosen. But for the 2^nd execution, the query selected the index plan despite it not being needed. This is due to soft parsing happening and the cursor not getting reparsed (the number of misses in the library cache is 0). Clearly this is a case where bind variables did much less good.

Now, you may definitely want to know if we can stop this behaviour from happening. Unfortunately no is be the answer, since this is a default feature and is controlled by an internal parameter. Fortunately, from Oracle 11g onwards we do have a feature that can take care of this behaviour – Adaptive Cursor Sharing.

Conclusion

It’s always good to check all aspects of a feature before considering them, because no solution is one-size-fits-all. In the next article, we will explore the Adaptive Cursor Sharing feature and learn how it addresses Bind Variable Peeking.