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.
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 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.
Hard-parse vs soft parse
Oracle SQL is parsed before execution, and checked for syntax (and
parts of the semantic check) before the SQL is loaded into the library
cache. As opposed to a soft parse (which does not require loading into
the shared pool), a hard parse includes these steps:
- Loading into shared
pool -
The SQL source code is loaded into RAM for parsing. (the "hard"
parse step)
- Syntax parse - Oracle parses the syntax
to check for misspelled SQL keywords.
- Semantic parse - Oracle verifies all table
& column names from the dictionary and checks to see if you are
authorized to see the data.
- Query Transformation - Oracle will transform complex
SQL into simpler, equivalent forms and replace aggregations with
materialized views, as appropriate. In earlier releases of Oracle the
query_rewrite=true parameter had to be set for materialized view
rewriting.
- Optimization - Oracle then creates an
execution plan, based on your schema statistics (or maybe with statistics
from dynamic sampling in 10g). Oracle build the decision tree of
costs during this period, choosing the path with the lowest perceived
cost.
- Create executable - Oracle builds an executable
file with native file calls to service the SQL query.
- Fetch rows - Oracle then executes the native
calls to the data files to retrieve the rows and passes them back to the
calling program.
Anytime a session issues SQL statement that does not already exist
in the shared pool, then Oracle has to do a hard parse. essentially performing
all of the above steps. If the statement already exists, then a soft
parse occurs, skipping step 1.
In a soft parse, Oracle must still perform a syntax parse and
semantic check because it is possible that a DDL change altered one of the
target tables or views since the SQL statement was originally executed.
In the case of DDL, all related SQL is marked as invalidated.
Oracle gives us the shared_pool_size
parm to cache SQL so that we
don't have to parse, over-and-over again. However, SQL can age-out if the
shared_pool_size is too small or if it is cluttered with non-reusable SQL (i.e.
SQL that has literals "where name = "fred") in the
source. Hence, the shared_pool_size parameter (memory_target in 12c AMM) has an impact of
hard parses, because re-entrant SQL can age out of the shared pool.
What the difference between a hard parse and a soft parse in
Oracle? Just the first step, step 1 as shown in red, above. In
other words, a soft parse does not require a shared pool reload (and the
associated RAM memory allocation).
Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is
paged out), or when you have non-reusable SQL statements without host
variables.
The cursor_sharing parameter for a easy way to make SQL
reentrant and remember that you should always use host variables in you SQL so
that they can be reentrant
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;
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;
/
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;
/
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;
/
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...
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...
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...
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.
I hope you all have enjoyed reading this article. Comments are welcome....
