Improve SQL Query Performance by Using Bind Variables

The Securing Account Details story in the previous blog post highlighted two problems with SQL that uses literals instead of bind variables: decreased performance and security holes. In this post we'll investigate the impact on execution times in more detail.

Why do bind variables matter for performance?

Before Oracle runs a SQL statement it checks it's valid and determines how to access the tables and join them together. This is called parsing The optimizer has the task of figuring out which table access and join methods to use. This produces an execution plan. When Oracle sends a statement to the optimizer to do this it's called a hard parse.

If a plan already exists for a query, Oracle doesn't need to go through the optimization process again. It can reuse the existing plan. This is referred to as soft parsing.

Oracle considers even minor formatting differences between two SQL queries to be different statements. For example, Oracle sees:

select * from orders where order_id = :ord

SELECT * FROM orders WHERE order_id = :ord

as different statements, even though it's obvious (to us) that these are equivalent. Therefore it hard parses the first execution of both.

Where do bind variables come into this?

select * from orders where order_id = 1234;
select * from orders where order_id = 9876;

The texts of these two are different however. Therefore both require a hard parse. This applies even if Oracle has already generated the plan for one of them.

This is a bit like opening your favorite mapping application to find your route to work just because you're using a different car. In the vast majority of cases this is wasted effort. The brand and model of car has no impact on the direction you take.

To avoid the extra hard parse, we need to replace the numbers in the statements above with a bind variable. This gives a single statement:

select * from orders where order_id = :ord;

If we run this query twice in succession with different values for :ord there's one hard parse and one soft parse.

What's the impact of hard parsing?

To answer this, let's look at some numbers. Let's take a basic orders table:

create table orders (
 order_id       integer not null primary key ,
 customer_id    integer not null ,
 order_datetime date not null ,
 status         varchar2(10) not null 
);

We'll query this by primary key 50,000 times. Each query will compare order_id to a different value. We'll take two approaches. One will build the SQL string dynamically, so each query is different. The other will use bind variables. Both will use execute immediate.

/* No bind variables */
declare
   order_rec orders%rowtype;
 begin
   for i in 1 .. 50000 loop
     begin
       execute immediate
         'select * from orders where order_id = ' || i
         into order_rec;
     exception
       when no_data_found then null;
     end;
   end loop;
 end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:01:33.94

/* Bind variables */
declare
   order_rec orders%rowtype;
 begin
   for i in 1 .. 50000 loop
     begin
       execute immediate
         'select * from orders where order_id = :i'
         into order_rec
         using i;
     exception
       when no_data_found then null;
     end;
   end loop;
 end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.49

Ninety seconds down to less than four! The overhead for each individual execution is small (around additional 2ms). When we're executing queries thousands of times this adds up. Hard parsing is expensive relative to soft parsing.

How does cursor_sharing = force affect this?

The behavior described above relies on us setting the parameter cursor_sharing to exact. This is the default, so it's likely your databases have this setting.

We can change this mode of operation by setting cursor_sharing to force. When we do this, Oracle strips out all the literals from the query and replaces them with bind variables in the optimization phase. For example, the previous two order queries now both become:

select * from orders where order_id = :"SYS_B_0"

In effect Oracle has transformed the query into one that does use bind variables.

Problems with cursor_sharing = force

Given that changing cursor_sharing is easy to do, it's tempting to say we should just set it to force. This introduces some other issues however. Let's say we have a process to find all the open orders for a customer. The query is:

select * from orders
where status = 'ACTIVE'
and   customer_id = :customer;

The status will always be 'ACTIVE' for this query. Yet Oracle still changes this query to become:

select * from orders
where status = :"SYS_B_0"
and   customer_id = :customer;

Why is this an issue?

We may have a similar query elsewhere in the application to find all the completed orders for a customer, such as:

select * from orders
where status = 'COMPLETE'
and   customer_id = :customer;

Oracle will also transform this to the bound version shown above. Instead of two distinct queries we've ended up with one.

To see why this could be an issue, let's return to the car analogy. You don't want to change your route just because you're using a different car. It's likely you would want to take a different path if using another form of transport however (e.g. bike, walking, bus, …). If you don't know which type of transport you're using, how do you select which route to take?

Oracle addressed this issue with bind variable peeking (introduced in 9i). This enables the optimizer to look the actual values passed when parsing a query and produce a plan suited to them.

When the database first started peeking it only did this check on the first execution of a query. Subsequent executions of it would use this first plan. This applied even if there were more efficient plans for the values passed.

For example, imagine you get a new job. No matter which transport method you use, you're forced to take the same route you took to get there on your first day. If your first journey was on foot, it's likely you'll take paths which are difficult (or impossible!) to traverse in a car!

To reduce this problem, Oracle introduced Adaptive Cursor Sharing in 11g. This enabled the database to use multiple plans for the same query. It's able to identify when supplying different values for bind variables should result in different plans. In terms of your journey to work, you no longer need to force your car down a footpath because you walked to work on your first day :)

These features reduce problems associated with forced cursor sharing merging queries that are genuinely different. They're not entirely gone however. The more information you can provide to Oracle, the more likely it is to choose the optimal execution plan. Converting a fixed literal to a bind variable removes some information. This increases the chance that the optimizer produces a sub-optimal query plan.

This isn't the only issue with forced cursor sharing however. It still performs worse than using bind variables. Let's see what happens when we run the previous 50,000 order id lookups without binding and forced cursor sharing:

alter session set cursor_sharing = force;

Session altered.

Elapsed: 00:00:00.00

declare
   order_rec orders%rowtype;
 begin
   for i in 1 .. 50000 loop
     begin
       execute immediate
         'select * from orders where order_id = ' || i
         into order_rec;
     exception
       when no_data_found then null;
     end;
   end loop;
 end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.92

It's a lot faster than the default (exact). It's still noticeably slower than using bind variables however.

Setting cursor_sharing to force may be useful as a quick fix if you have a poorly performing application that doesn't use binds. It's still slower than using bind variables and may adversely affect queries with literals. The best long-term solution is to write your queries to use bind variables.

The advantage of using PL/SQL

To finish off it's worth exploring a final way of implementing the query. In the story Dave was writing SQL queries directly in the middle tier. An alternative method is to place all your SQL in PL/SQL stored procedures. The application then just calls these modules.

Let's repeat the test above, this time with the SQL statement written directly in the PL/SQL block:

declare
   order_rec orders%rowtype;
 begin
   for i in 1 .. 50000 loop
     begin
       select *
       into   order_rec
       from   orders
       where  order_id = i;
     exception
       when no_data_found then null;
     end;
   end loop;
 end;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.48

As you can see this gives similar performance to the bound version of execute immediate.

Why this is? Provided you're writing static SQL in PL/SQL blocks (i.e. not as part of an execute immediate or dbms_sql statement) you're already using bind variables! Additionally, Oracle doesn't replace genuine literals in PL/SQL when cursor_sharing = force (MOS note 285447.1). This makes it more likely Oracle will choose the optimal execution plan.

Conclusion

As we've seen, not using bind variables causes Oracle to do more work when determining how to execute queries. Instead of coming up with an execution plan once and reusing it, the optimizer regenerates the plan for each execution with different input values.