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.
’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’.
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
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 !!!
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:
| Execution in the loop of queries containing hard coded values
(loop parse execute endloop)
| Execution in the loop of parsing and execution of a query
using bind variables
(loop parse bind execute endloop)
| Statement parsed outside of the loop, then repeatedly executed
with bind variables
(parse loop bind execute endloop)
| Same as NOBULK but using array insert
(arrayfetch loop bind bulkinsert endloop)
The results from SQL*Plus (option ’set timing’ turned on).
|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||-||-|
|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).
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|