Code Tip: Use Bind Variables for Excellent Application Performance
The article Bind variables - The key to application performance showed us just how important to use
bind variables when writing SQLs in your application. If you are supporting an old application coded by
others before you, you may want to search the entire source code to spot areas need to use bind variables.
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.
The article compares two examples (before and after) which showed the drastic improvements in
response time:
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.
|
To see the full article, click here. |
