The perils of Non-Use of
Bind Variables in Oracle
The biggest
problem in many applications is the non-use of bind variables.
Oracle bind variables are a super important way to make Oracle SQL
reentrant.
Why is the use of bind variables such an issue?
Oracle uses a
signature generation algorithm to assign a hash value to each SQL
statement based on the characters in the SQL statement. Any change
in a statement (generally speaking) will result in a new hash and
thus Oracle assumes it is a new statement. Each new statement must
be verified, parsed and have an execution plan generated and stored,
all high overhead procedures.
The high overhead procedures might be avoided by using bind variables. See
these notes on Oracle cursor_sharing
for details.
Ad-hoc query generators
(Crystal Reports, Discoverer, Business Objects) do not use bind variables, a major reason for Oracle developing
the cursor_sharing parameter to force SQL to use bind
variables (when cursor_sharing=force).
Use of bind variables can have a huge
impact on the stress in the shared pool and it is important to know
about locating similar SQL in
Oracle. This script
shows how to check your shared pool for SQL that is using bind
variables. Below is an example output of a database that is
utilizing bind variables and the SQL is fully
reentrant:
Time: 03:15 PM Bind Variable Utilization PERFSTAT
dbaville database
When SQL is placed within PL/SQL, the embedded SQL never changes
and a single library cache entry will be maintained and searched,
greatly improving the library cache hit ratio and reducing parsing
overhead.
Here are some particularly noteworthy advantages of placing SQL
within Oracle stored procedures and packages:
- High productivity: PL/SQL is a language
common to all Oracle environments. Developer productivity is
increased when applications are designed to use PL/SQL procedures
and packages because it avoids the need to rewrite code. Also,
the migration complexity to different programming environments and
front-end tools will be greatly reduced because Oracle process
logic code is maintained inside the database with the data, where
it belongs. The application code becomes a simple "shell"
consisting of calls to stored procedures and functions.
- Improved Security: Making use of the "grant
execute" construct, it is possible to restrict access to Oracle,
enabling the user to run only the commands that are inside the
procedures. For example, it allows an end user to access one
procedure that has a command delete in one particular table
instead of granting the delete privilege directly to the end user.
The security of the database is further improved since you can
define which variables, procedures and cursors will be public and
which will be private, thereby completely limiting access to those
objects inside the PL/SQL package. With the "grant" security
model, back doors like SQL*Plus can lead to problems; with "grant
execute" you force the end-user to play by your rules.
- Application portability: Every application
written in PL/SQL can be transferred to any other environment that
has the Oracle Database installed regardless of the platform.
Systems that consist without any embedded PL/SQL or SQL become
"database agnostic" and can be moved to other platforms without
changing a single line of code.
- Code Encapsulation: Placing all related
stored procedures and functions into packages allows for the
encapsulation of storage procedures, variables and datatypes in
one single program unit in the database, making packages perfect
for code organization in your applications.
- Global variables and cursors: Packages can
have global variables and cursors that are available to all the
procedures and functions inside the package.
The bind variables play a vital role in the
memory management and performance enhancement in dynamic SQL
processing. When an SQL query is executed multiple times with
different hard-coded values, it parses every single time based on the
unique hard-coded values as like below,
1.
DELETE FROM employees WHERE
employee_id=100;
2.
DELETE FROM employees WHERE
employee_id=101;
3.
DELETE FROM employees WHERE
employee_id=102;
By executing the V$SQL view with the
appropriate columns, the parsing information related to the above
statements can be gathered as like below,
SELECT sql_id,
sql_text,
first_load_time,
hash_value
FROM v$sql
WHERE sql_text LIKE 'DELETE FROM employees
WHERE employee_id=%';
|
SQL_ID
|
SQL_TEXT
|
FIRST_LOAD_TIME
|
HASH_VALUE
|
|
6mrtzn6s56nhq
|
DELETE FROM employees
WHERE employee_id=100
|
2016-05-04/01:39:08
|
2958250518
|
|
04bukanmmv3yp
|
DELETE FROM employees
WHERE employee_id=102
|
2016-05-04/01:40:14
|
658345941
|
|
7k3z2wxb8mx92
|
DELETE FROM employees
WHERE employee_id=101
|
2016-05-04/01:40:12
|
1451881762
|
In the above result set, all three queries are
parsed with different hash values.
However, when the query statement uses a bind
variable instead of the hard-coded value, the query doesn’t parse for
the multiple values of the bind variable but only once irrespective of
the number of executions.
DELETE FROM employees WHERE
employee_id=:employee_id;
Now, the below V$SQL query results as,
SELECT sql_id,
sql_text,
first_load_time,
hash_value
FROM v$sql
WHERE sql_text LIKE 'DELETE FROM employees
WHERE employee_id=%';
|
SQL_ID
|
SQL_TEXT
|
FIRST_LOAD_TIME
|
HASH_VALUE
|
|
85cyrmn7pjawc
|
DELETE FROM employees
WHERE employee_id=:employee_id
|
2016-05-04/02:02:59
|
257469324
|
By examining the above result set, it shows
that only one instance of the query is parsed irrespective of the
number of times the statement is executed. The reason behind this
behavior is, the parsing of the query happens right before the bind
value assignment thus, the query is common to multiple bind values.
Failing to use the bind variables may fill up the shared pool space
with a large number of identical queries resulting in bad performance
and resource containment.
In the below anonymous block, the employee IDs
100 through 105 are deleted from the employees table by dynamically
assigning the table name and the column name in the run time.
%
Note: The bind values
cannot be used for passing schema objects.
|
1.
SET SERVEROUTPUT ON 200000;
2.
DECLARE
3.
l_vc_table_name
VARCHAR2(30):='Employees';
4.
l_vc_column_name VARCHAR2(30):='Employee_id';
5.
BEGIN
6.
FOR i IN 100..105
7.
LOOP
8.
EXECUTE immediate 'delete from '||l_vc_table_name||'
where '||l_vc_column_name||'='||i;
9.
END LOOP i;
10.
END;
11.
/
|
Line No.
|
Description
|
|
1
|
This environment variable
opens up an output buffer of size limit of 200000.
|
|
2
|
Start of the declare
section of the block.
|
|
3
|
The local variable
l_vc_table_name is declared with VARCHAR2 data type with a
precision of 30 characters and is defaulted to the text
‘EMPLOYEES’.
|
|
4
|
The local variable
l_vc_column_name is declared with VARCHAR2 data type with a
precision of 30 characters and is defaulted to the text
‘EMPLOYEE_ID’.
|
|
5
|
Start of the execution
section of the block.
|
|
6,7
|
Start of the FOR loop with
the range 100 to 105.
|
|
8
|
A dynamic DELETE statement
is formed by using the table name and the column name from the
local variables l_vc_table_name and l_vc_column_name
respectively. This dynamically formed DELETE statement is then
parsed and executed using the EXECUTE IMMEDIATE statement for
the loop’s range values assigned to its WHERE condition in an
orderly fashion.
|
|
9
|
End of the FOR loop.
|
|
10,11
|
End of the execution
section of the block.
|
Here, as stated in the above cases, the above
anonymous block can be effectively modified by using the bind value as
like below,
1.
SET SERVEROUTPUT ON 200000;
2.
DECLARE
3.
l_vc_table_name
VARCHAR2(30):='Employees';
4.
l_vc_column_name VARCHAR2(30):='Employee_id';
5.
BEGIN
6.
FOR i IN 100..105
7.
LOOP
8.
EXECUTE immediate 'delete from '||l_vc_table_name||'
where '||l_vc_column_name||'=:i' using i;
9.
END LOOP i;
10.
END;
11.
/
|
Line No.
|
Description
|
|
1
|
This environment variable
opens up an output buffer of size limit of 200000.
|
|
2
|
Start of the declare
section of the block.
|
|
3
|
The local variable
l_vc_table_name is declared with VARCHAR2 data type with a
precision of 30 characters and is defaulted to the text
‘EMPLOYEES’.
|
|
4
|
The local variable
l_vc_column_name is declared with VARCHAR2 data type with a
precision of 30 characters and is defaulted to the text
‘EMPLOYEE_ID’.
|
|
5
|
Start of the execution
section of the block.
|
|
6,7
|
Start of the FOR loop with
the range 100 to 105.
|
|
8
|
A dynamic DELETE statement
is formed by using the table name and the column name from the
local variables l_vc_table_name and l_vc_column_name
respectively. This dynamically formed DELETE statement is then
parsed and executed using the EXECUTE IMMEDIATE statement with
a placeholder in its WHERE condition which gets its value from
the loop’s range value in the form of a bind value.
|
|
9
|
End of the FOR loop.
|
|
10,11
|
End of the execution
section of the block.
|
Other notes on using bind variables includes:
and include the URL for the page.
