Oracle Bind By Value Howto

Oracle Bind By Value Howto

Introduction

Simplified statement: We have two basic ways how to create SQL statements in Oracle:

• dynamic SQL: SQL is created by joining strings = Qore's %d, %s
• bind by value: the driver binds values directly into SQL templates = Qore's %v

Every SQL statement has to run through various states before it can return any response. The main difference is between the so called Hard Parse and Soft Parse.

Long story short: Soft Parsing is good, Hard Parsing is bad. And using %v results Soft Parsed statements, while %d and %s result in Hard Parsed ones. Mostly.

Hard parse

If a session executes an SQL statement that does not exist in the shared pool, Oracle has to do a hard parse. Oracle must then:

1. Allocate memory for the statement from the shared pool.
2. Check the statement syntactically
3. Check if the user trying to execute the statement has the necessary rights to execute it

A hard parse is expensive in both terms of CPU used and number of shared pool and library cache latch it needs to acquire and release. should be avoided whenever possible.

Soft parse

If a session executes an SQL statement that exists in the shared pool and is a version of the statement that can be used, then this is referred to as a soft parse.

Let's try to make some "benchmarks"

Assuming we are talking about OLTP systems. Also I hope it will be clearer at the end of this message...

-- start with clear system
alter system flush shared_pool;

-- hard parsing for each statement: +000000000 00:00:05.912614000
declare
  st timestamp := current_timestamp;
begin
  for i in 1..100 loop
    execute immediate 'select object_name from all_objects where object_id in ( '|| i || ')';
  end loop;
  dbms_output.put_line(current_timestamp - st);
end;
/

-- variable binding style: +000000000 00:00:00.088596000
declare
  st timestamp := current_timestamp;
begin
  for i in 1..100 loop
    execute immediate 'select object_name from all_objects where object_id in( :i )' using i;
  end loop;
  dbms_output.put_line(current_timestamp - st);
end;
/

for 1..1000

for 100 unique IDs

hard parsing:           +000000000 00:00:05.912614000
variable binding style: +000000000 00:00:00.088596000

for 1000 unique IDs

hard parsing:           +000000000 00:01:01.631089000
variable binding style: +000000000 00:00:00.132568000

Also there is an additional problem, that parsed statements are stored in shared pool "forever" (the cleaning conditions are out of scope of this issue). So for example in systems where there are more data than few rows there can be "infinite" count of statements in shared pool which is impossible due the memory size.

Also note that so called hard parsing cannot be serialized in oracle, it's blocking operation because of shared pool access (well, it's more complicated but let's simplify it to this status) - it means that permanent hardparsing blocks all schemas in the DB instance.

Now we have following situation in testing system. The 1st line is "safe" statement, it was executed 1000 times with only one hard parsing, using single amount of memory.

Rest of lines are "dynamic SQL" statements with literals - every occurrence of literal combination creates new prepared statement - with amount of memory and limited use.

SQL                                                            sharable     persistent  runtime versions    executions  parsecalls  buffer gets
select object_name from all_objects where object_id in( :i )    409141      157728      156120  1           1000        1           10

select object_name from all_objects where object_id in ( 808)   11646       157680      156104  0           1           1           10
select object_name from all_objects where object_id in ( 55)    405053      157680      156104  1           2           2           10
… 997 rows removed …                            
select object_name from all_objects where object_id in ( 87)    405053      157680      156104  1           2           2           10

                                                        SUM:    96573232    45096528    44645760               

and then let's assume that IDs sequence is growing. In this case there will be still one statement using variable binding or on the other side system on its knees handling growing shared pool with single-use statements.