’To Bind or not to Bind’, there is no question about it!!!


Philippe Bertolino

Print this article  Send the URL to a friend

Introduction

It is common to think about optimizing the execution plan of SQL requests (joins, tables, indexes, ...) but less common to worry about the stage which precedes the execution. However before executing any request, Oracle needs to validate the script in a phase called ’parsing’ which also has to be optimized. It is very important to think about this aspect of SQL coding from the outset. The sooner the better because it can be difficult to modify afterwards.

What is ’parsing’ ?

’Parsing’ is needed to validate and prepare a query before execution. It consists of checking SQL syntax, semantics and access rights (soft parse) and then, if the query is not already cached into the ’shared pool’, in generating an execution plan to access the objects (hard parse). Once this is done the query is cached and is ready for a later use.

These operations are Cpu and latch (Oracle internal locks) intensive. On a box with few users and SQL queries which have not been optimized for parsing, the response time can be quite long (one program executing thousands of hard-coded queries could freeze the entire database for a while, due to the extreme pressure on the ’shared pool’ (See the excellent book Oracle 8i Internal Services for Waits, Latches, Locks, and Memory by Steve Adams, O’Reilly-1999)

To avoid this unpleasant state of affairs, one solution exists : the technique of ’binding’.

’Binding’ comes to the rescue !!!

With this solution we can achieve the famous Oracle motto: "parse once, execute many".

The ’binding’ allow us to make these queries appear to be identical to Oracle:

replacing the hard coded values (1234, 5678, ...) by an Oracle variable (such as a PL/SQL variable) in order to make the SQL text of the queries unique

custid will successively hold the values required, letting Oracle read the value in memory on execution.

This technique has been available since at least Oracle V6, and can be used in many languages such as PL/SQL, C/C++ with Oci, Pro* (C, Fortran, ...) and Java just to mention the most commonly used ones.

It is an easy technique to use, it improves performance, not only of one program, but of the overall performance of the database and in the end it could also make life easier for your favourite Dba !!!

Example

Here is a real example which comes from an accounting program. The most critical performance problem was occurring during a process to reconcile data read from a file (and massively inserted into a table) with data from an existing application table.

I will use a PL/SQL procedure using a loop to model this process and to demonstrate the use of ’binding’.

Furthermore this example will make user of another PL/SQL optimization technique, namely the ’bulk insert’ (or array insert) - also available with other languages.

To summarize, the procedure takes one input value to specify its behaviour:

  • NOBIND
Execution in the loop of queries containing hard coded values
(loop parse execute endloop)
  • SOFTPARSE
Execution in the loop of parsing and execution of a query using bind variables
(loop parse bind execute endloop)
  • NOBULK
Statement parsed outside of the loop, then repeatedly executed with bind variables
(parse loop bind execute endloop)
  • BULK
Same as NOBULK but using array insert
(arrayfetch loop bind bulkinsert endloop)

The results from SQL*Plus (option ’set timing’ turned on).

  NOBIND SOFTPARSE NOBULK BULK
Session Statistics        
opened cursors cumulative 14252 14243 7 7
parse count (hard) 14246 2 2 2
parse count (total) 12255 14243 7 7
parse time cpu 846 85 - -
Session Events        
latch free (total waits) 1933 1192 20 6
         
Elapsed Time (s) 42 35 22 1

Here are links to the procedures used in this example :

(Nb: a tracing package has been used (k_trace_ora) which computes the differences between 2 calls, for the session’s statistics and events. The results in the array above were obtained with this package).

Conclusion

The technique of ’binding’ is not very complicated and hardly makes coding more complicated when a development project takes this particular type of optimization into account from the very start. Without ’binding’ we cannot suppress the 2 types of ’parsing’ for repeated queries using hard coded values, but with ’binding’ we can get rid of n-1 ’hard parses’ and possibly n-1 ’soft parses’ depending on the coding (where n = number of identical requests to execute).


Copyright © 2004, Philippe Bertolino