Database Reference
In-Depth Information
■
to see the difference between hard parsing and soft parsing live and in action, i recommend you review the
demonstration hosted at
http://tinyurl.com/RWP-OLTP-PARSING
. this was put together by a team i work with, the
real World performance team at oracle. it clearly shows the difference between soft parsing and hard parsing—it is close
to an order of magnitude difference! We can get ten times as much work performed on a transactional system architected
to use bind variables as not. this short visual presentation is something you can use to convince other developers about
the impact of bind variables (or the lack thereof) on performance!
Note
If you use bind variables, then everyone who submits the same exact query that references the same object will
use the compiled plan from the pool. You will compile your subroutine once and use it over and over again. This is
very efficient and is the way the database intends you to work. Not only will you use fewer resources (a soft parse
is much less resource-intensive), but also you will hold latches for less time and need them less frequently. This
increases your performance and greatly increases your scalability.
Just to give you a tiny idea of how huge a difference this can make performance-wise, you only need to run a very
small test. In this test, we'll just be inserting some rows into a table; the simple table we will use is:
EODA@ORA12CR1> create table t ( x int );
Table created.
Now we'll create two very simple stored procedures. They both will insert the numbers 1 through 10,000 into this
table; however, the first procedure uses a single SQL statement with a bind variable:
EODA@ORA12CR1> create or replace procedure proc1
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values ( :x )' using i;
8 end loop;
9 end;
10 /
Procedure created.
The second procedure constructs a unique SQL statement for each row to be inserted:
EODA@ORA12CR1> create or replace procedure proc2
2 as
3 begin
4 for i in 1 .. 10000
5 loop
6 execute immediate
7 'insert into t values ( '||i||')';
8 end loop;
9 end;
10 /
Procedure created.
