From many Oracle Database Administration courses and Performance Tuning courses that I have tought in the past, I can tell that a very basic (as we should assume) knowledge about the appropriate use of Bindvariables resp. Literals is missing, even among people who have dealt with Oracle Databases for years. So I have decided to cover that extremely important topic as short but illustrative as possible.

If a SELECT statement is processed, this is done in the steps 1) Parse 2) Bind 3) Execute 4) Fetch. The Parse step contains the computation of an execution plan and is relatively expensive for statements with a short duration. Typically, in an OLTP like Database, statements have a runtime of the fraction of a second. Many users run these statements at the same time. Avoiding the parse step is therefore a huge benefit and reduces the overall runtime greatly. The statements are cached in the Shared Pool, together with their execution plan and can be reexecuted with the same plan, if exactly the same statement occurs again. Thats where Bindvariables (step 2) kick in. They can be used to get exactly the same statements that need not to be parsed again:

SQL> create table parseornot as select rownum as id from dual connect by level<=1e4;

Table created.

SQL> alter system flush shared_pool -- to have a level playing field ;

System altered.

create or replace procedure goodbinds
as
v_id number;
begin
 for i in 1..10000 loop
 select id into v_id from parseornot where id=i;
end loop;
end;
/
Procedure created.

Apart from the complete senselessnes of the SELECT inside this PL/SQL procedure, it uses Bindvariables (the i is one). If called, that SELECT gets parsed once and then executed 10000 times. Please notice also that there is only one possible  execution plan for this statement – there is no index on the table.

SQL> set timing on
SQL> exec goodbinds

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.36
SQL> select count(*),count(parse_calls) from v$sql
where lower(sql_text) like '%from parseornot%'
and sql_text not like  '%like%';  

 COUNT(*) COUNT(PARSE_CALLS)
---------- ------------------
 1                  1
col sql_text format a70
select sql_text,executions from v$sql
where lower(sql_text) like '%from parseornot%'
and sql_text not like  '%like%';
SQL_TEXT                                                               EXECUTIONS
---------------------------------------------------------------------- ----------
SELECT ID FROM PARSEORNOT WHERE ID=:B1                                      10000

In this case, the usage of literals resp.  dynamic SQL is not advisable:

SQL> alter system flush shared_pool -- to have a level playing field ;

System altered.
create or replace procedure badlits
as
v_id number;
begin
 for i in 1..10000 loop
 execute immediate
 'select id from parseornot where id=' || i into v_id;
 end loop;
end;
/
Procedure created.

SQL>exec badlits

PL/SQL procedure successfully completed.

Elapsed: 00:00:47.13

Although the above procedure does exactly the same (senseless) thing, it has a much longer runtime. Why? Because:

SQL> select count(*),count(parse_calls) from v$sql
where lower(sql_text) like '%from parseornot%'
and sql_text not like  '%like%';  

 COUNT(*) COUNT(PARSE_CALLS)
---------- ------------------
 10000              10000

SQL> col sql_text format a70
select sql_text,executions from v$sql
where lower(sql_text) like '%from parseornot%'
and sql_text not like  '%like%' and rownum<10;

I limit the output because I don’t want 10000 rows on the screen.

SQL_TEXT                                                               EXECUTIONS
---------------------------------------------------------------------- ----------
select id from parseornot where id=1012                                         1
select id from parseornot where id=7028                                         1
select id from parseornot where id=9988                                         1
select id from parseornot where id=5318                                         1
select id from parseornot where id=8209                                         1
select id from parseornot where id=4986                                         1
select id from parseornot where id=2807                                         1
select id from parseornot where id=5990                                         1
select id from parseornot where id=1159                                         1

9 rows selected.

We have just seen a classical example why Bindvariables are good and literals are bad – under certain circumstances. It would be wrong, though, to generalize that and simply always use Bindvariables, as they can be not appropriate as well. If statements have a relatively long runtime, the percentual part of the parse step contributing to the runtime gets neglectable. In Data Warehouse like Databases, statements have runtimes in the ranges of minutes, so it doesn’t matter much if they got parsed for a fraction of a second. Also, few users are issuing statements and often statements are ad hoc constructed and will not be repeated (soon). In such an environment, best execution plans are important and literals are preferrable.

Why? Because the execution plan gets computed (Step 1) before the content of the Bindvariable gets bound (Step 2). If there are multiple execution plans for the statement, depending of the value of the (Bind-)variable, that is extremely important:

SQL> select channel_id,count(*) from sales group by channel_id;

CHANNEL_ID   COUNT(*)
---------- ----------
 1                1
 2          4128400
 3          8645247
 4          1894656
 9            33184

Above constructs a typical situation where Bindvariables are evil and Literals are the way to go. There is a (Bitmap) Index on the column channel_id. The value 1 is extremely selective. The value 3 is extremely unselective. Depending on the value of channel_id in a where condition, different execution plans are optimal:

SQL> select max(amount_sold) from sales where channel_id=1;

MAX(AMOUNT_SOLD)
----------------
 1232.16

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 3388944652

-------------------------------------------------------------------------------------------
| Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |           |     1 |     8 |  3306   (1)| 00:00:40 |
|   1 |  SORT AGGREGATE               |           |     1 |     8 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID | SALES     | 17995 |   140K|  3306   (1)| 00:00:40 |
|   3 |    BITMAP CONVERSION TO ROWIDS|           |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE | SALES_BIX |       |       |            |          |
-------------------------------------------------------------------------------------------

SQL> select max(amount_sold) from sales where channel_id=3;

MAX(AMOUNT_SOLD)
----------------
 1782.72

Elapsed: 00:00:03.57

Execution Plan
----------------------------------------------------------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     8 | 19464   (1)| 00:03:54 |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| SALES |  8649K|    65M| 19464   (1)| 00:03:54 |
----------------------------------------------------------------------------

When we use literals (1 resp. 3), the optimizer can pick the appropriate execution plan during the parse step. Here, Bindvariables are bad and Literals are good:

create or replace procedure badbinds (v_id number)
as
v_max_amount_sold number;
begin
select max(amount_sold)
into v_max_amount_sold
from sales where channel_id=v_id;
dbms_output.put_line(to_char(v_max_amount_sold));
end;
/

alter system flush shared_pool;
alter system flush buffer_cache;

SQL> set timing on
SQL> set serveroutput on
SQL> exec badbinds(3)
1782.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.43
SQL> exec badbinds(1)
1232.16

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.97

The usage of the Bindvariables refrain the optimizer from using the index in the second call of the procedure, as you can tell by runtime. With literals, the same outcome takes much less time because now the index can be used:

create or replace procedure goodlits (v_id number)
as
v_max_amount_sold number;
begin
execute immediate
'select max(amount_sold) from sales where channel_id='
 || v_id into v_max_amount_sold;
dbms_output.put_line(to_char(v_max_amount_sold));
end;
/

alter system flush shared_pool;
alter system flush buffer_cache;
SQL> exec goodlits(3)
1782.72

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.96
SQL> exec goodlits(1)
1232.16

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

When I demonstrated the above the last time, one student got to the conclusion: „So in Oracle, Bindvariables make it impossible to use indexes!“ That is of course a misconception. If there where only one good execution plan for the statement (index access) then it will be chosen with Bindvariables as well. The point is: If there are multiple good execution plans for one statement, then Bindvariables will probably lead to suboptimal execution plans. As a Developer, you should be aware of the kind of Database you are developing statements against and choose Bindvariables resp. Literals as appropriate. They are both not good or evil per se.

I have intentionally not mentioned CURSOR_SHARING=FORCE/SIMILAR, Bind Peeking and Adaptive Cursor Sharing in order to keep the posting as simple and clear as possible and to get to the point as exactly as I could. The three techniques have in common that they can reduce the harm of an inappropriate usage of Literals resp. Bindvariables to some degree.

This will be my last posting in 2009, I guess, and I could hardly find the time for it due to much other work. I wish you all a Merry Christmas & a Happy New Year 2010 🙂