To understand bind variables, consider an
application that generates thousands of SELECT statements against a table; for
example:
SELECT fname, lname, pcode FROM cust
WHERE id = 674;
SELECT fname, lname, pcode FROM cust WHERE id =
234;
SELECT fname, lname, pcode FROM cust WHERE id =
332;
Each time the query is submitted, Oracle first
checks in the shared pool to see whether this statement has been submitted before. If it
has, the execution plan that this statement previously used is retrieved, and the SQL is
executed. If the statement cannot be found in the shared pool, Oracle has to go through
the process of parsing the statement, working out the various execution paths and coming
up with an optimal access plan before it can be executed. This process is know as a
«hard parse» and for OLTP applications can actually take longer to carry
out that the DML instruction itself.
When looking for a matching statement in the shared
pool, only statements that exactly match the text of the statements are considered; so,
if every SQL statement you submit is unique (in that the predicate changes each time,
from id = 674 to id=234 and so on) then you'll never get a match, and every statement you
submit will need to be hard parsed. Hard parsing is very CPU intensive, and
involves obtaining latches on key shared memory areas, which whilst it might not
affect a single program running against a small set of data, can bring a multi-user
system to it's knees if hundreds of copies of the program are trying to hard parse
statements at the same time. The extra bonus with this problem is that contention caused
by hard parsing is pretty much immune to measures such as increasing available memory,
numbers of processors and so on, as hard parsing statements is one thing Oracle can't do
concurrently with many other operations, and it's a problem that often only comes to
light when trying to scale up a development system from a single user working on subset
of records to many hundreds of users working on a full data set.
The way to get Oracle to reuse the execution plans for these
statements is to use bind variables. Bind variables are
«substituion» variables that are used in place of literals
(such as 674, 234, 332) and that have the effect of sending exactly
the same SQL to Oracle every time the query is executed. For example, in our application,
we would just submit
SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;
and this time we would be able to reuse the execution plan every
time, reducing the latch activity in the SGA, and therefore the total CPU activity, which
has the effect of allowing our application to scale up to many users on a large
dataset.
Bind Variables in SQL*Plus
In SQL*Plus you can use bind variables as follows:
SQL> variable deptno number SQL>
exec :deptno := 10 SQL> select * from emp where
deptno = :deptno;
What we've done to the SELECT statement now is take the literal
value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus
passing the value of the bind variable to Oracle when the statement is processed. This
bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the
bind variable in the SELECT statement)
Bind Variables in PL/SQL
Taking PL/SQL first of all, the good news is that PL/SQL itself
takes care of most of the issues to do with bind variables, to the point where most
code that you write already uses bind variables without you knowing. Take, for example,
the following bit of PL/SQL:
create or replace procedure dsal(p_empno in
number)
as
begin
update emp
set sal=sal*2
where empno = p_empno;
commit;
end;
/
Now you might be thinking that you've got to replace
the p_empno with a bind variable. However, the good news is that
every reference to a PL/SQL variable is in fact a bind
variable.
Dynamic SQL
In fact, the only time you need to consciously decide to use bind
variables when working with PL/SQL is when using Dynamic SQL.
Dynamic SQL, allows you to execute a string containing SQL using
the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it
is submitted:
create or replace procedure dsal(p_empno in
number)
as
begin
execute immediate
'update emp set sal = sal*2 where empno
= '||p_empno;
commit;
end;
/
The way to use bind variables instead is to change the EXECUTE
IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in
number)
as
begin
execute immediate
'update emp set
sal = sal*2 where empno =
:x' using p_empno;
commit;
end;
/
And that's all there is to it. One thing to bear in mind, though,
is that you can't substitute actual object names (tables, views, columns etc) with bind
variables - you can only subsitute literals. If the object name is generated at runtime,
you'll still need to string concatenate these parts, and the SQL will only match with
those already in the shared pool when the same object name comes up. However, whenever
you're using dynamic SQL to build up the predicate part of a statement, use bind
variables instead and you'll reduce dramatically the amount of latch contention going
on.
The Performance Killer
Just to give you a tiny idea of how huge of a difference this can
make performance wise, you only need to run a very small test:
Here is the Performance Killer ....
SQL> alter system flush shared_pool;
SQL> set serveroutput on;
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from
all_objects
where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
--
dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2)
||
' Seconds...' );
end;
/
101.71 Seconds...
... and here is the Performance Winner:
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 1000
loop
open l_rc for
'select object_name
from
all_objects
where object_id = :x'
using i;
fetch l_rc into l_dummy;
close l_rc;
--
dbms_output.put_line(l_dummy);
end loop;
dbms_output.put_line
(round((dbms_utility.get_time-l_start)/100, 2)
||
' Seconds...' );
end;
/
1.9 Seconds...
That is pretty dramatic. The fact is that not only
does this execute much faster (we spent more time PARSING our queries then actually
EXECUTING them!) it will let more users use your system simultaneously.
Bind Variables in VB, Java and other
applications
The next question is though, what about VB, Java and other
applications that fire SQL queries against an Oracle database. How do these use bind
variables? Do you have to in fact split your SQL into two statements, one to set the bind
variable, and one for the statement itself?
In fact, the answer to this is actually quite simple. When you put
together an SQL statement using Java, or VB, or whatever, you usually use an API
for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of
these APIs have built-in support for bind variables, and it's just a case of using
this support rather than just concatenating a string yourself and submitting it to the
database.
For example, Java has PreparedStatement, which allows the
use of bind variables, and Statement, which uses the string concatenation approach. If
you use the method that supports bind variables, the API itself passes the bind variable
value to Oracle at runtime, and you just submit your SQL statement as normal. There's no
need to separately pass the bind variable value to Oracle, and actually no additional
work on your part. Support for bind variables isn't just limited to Oracle - it's
common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse
for not using them just because they might be an Oracle-only feature.
Conclusion
Lastly, it's worth bearing in mind that there are some instances
where bind variables are probably not appropriate, usually where instead of your query
being executed many times a second (as with OLTP systems) your query in fact actually
takes several seconds, or minutes, or hours to execute - a situation you get in decision
support and data warehousing. In this instance, the time taken to hard parse your query
is only a small proportion of the total query execution time, and the benefit of avoiding
a hard parse is probably outweighed by the reduction in important information you're
making available to the query optimizer - by substituting the actual predicate with a
bind variable, you're removing the ability for the optimiser to compare your value with
the data distribution in the column, which might make it opt for a full table scan or an
index when this isn't appropriate. Oracle 9i helps deal with this using a feature known
as bind variable
peeking, which allows Oracle to look at the value behind a bind variable to help
choose the best execution plan.
Another potential drawback with bind variables and data warehousing
queries is that the use of bind variables disallows
the potential for star transformations, taking away this powerful option for
efficiently joining fact and dimension tables in a star schema.
Source: http://www.akadia.com/services/ora_bind_variables.html |
Không có nhận xét nào:
Đăng nhận xét