| |
 |
|
Oracle bind variables
Oracle Tips by Michael R. Ault |
The perils of Non-Use of
Bind Variables in Oracle
You’ll hear it from most DBA's, 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? Well, 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 that might be avoided by using bind variables. See
these notes on Oracle cursor_sharing
for details.
The problems caused by ad-hoc query generators
(Crystal Reports, Discoverer, Business Objects) is that their SQL
does 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).
Bind variables and shared pool usage
The proper 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, and this is an example output of a database that is
utilizing bind variables (or cursor_sharing) and the SQL is fully
reentrant:
Time: 03:15 PM Bind Variable Utilization PERFSTAT
dbaville database
users Non-Shared SQL Shared SQL Percent
Shared
-------------------- -------------- --------------
--------------
DBAVILLAGE 9,601,173 81,949,581
89.513
PERFSTAT 2,652,827 199,868
7.006
DBASTAGER 1,168,137 35,468,687
96.812
SYS 76,037 5,119,125
98.536
------------- -------------- --------------
--------------
4 13,498,174 122,737,261
90.092
Other notes on using bind variables includes:
 |
If you like Oracle tuning, you may enjoy my new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of my favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Is your RAC database Healthy?
Get the experts at Burleson Consulting to conduct a two day
RAC
health check and ensure the health of your RAC database.
Why guess? Have your RAC database certified by experienced RAC
experts. |
|
|
