1. Tune
PL/SQL Subprogram Invocation
All
PL/SQL parameters are by default passed by value which involves making
a copy
of the parameter. When the subprogram exits normally,
the values
of these temporary variables representing the OUT and IN OUT parameters
are then copied to the real variables. This is fine for parameters
that are
only a few bytes in size but for parameters that are very large
such as
associative arrays or other structures the overhead can be quite
significant.
The solution to this is to use the NOCOPY hint to suggest to the PL/SQL
compiler that parameters should be passed by reference. This hint
is not
guaranteed to be accepted by the compiler though as there are a
number of
restrictions on when it will be accepted .
2. Change PL/SQL sub programs to inline
code
In versions of PL/SQL in Oracle 9i and below, there could be a
trade off
between making your code well structured and therefore easier to
maintain
and making it fast. With Oracle 10g and above, the PL/SQL compiler
optimises the source code when compiling it.
The degree of optimisation is determined by the value of the
parameter
PLSQL_OPTIMIZE_LEVEL which
can be changed at the session level. Setting
this to 3 causes the PL/SQL compiler to convert subprograms to
inline
code
wherever it can unless instructed not to by use of the INLINE
pragam.
This might sound great but there is a drawback inlining
is the first
optimisation performed precluding other optimisations that may be
possible.
The default value of 2 for PLSQL_OPTIMIZE_LEVEL means
that the
compiler only converts subprograms
to inline
code when the INLINE
pragma is specified before the invocation of the sub program. In
other
words you as the programmer decide which procedure and function
invocations should be converted to inline
code. This enables you to benfit
from the other compiler optimisations as well as converting
subprograms
to inline
code.
For example, with PLSQL_OPTIMIZE_LEVEL
set to 3 the procedure call will
not be optimised to use in line code.
PROCEDURE my_proc IS
BEGIN
...
PRAGMA INLINE (my_proc,'NO');
my_proc(1);call
to my_proc is NOT converted to in line code
...
END;
With PLSQL_OPTIMIZE_LEVEL set to 2 the following code will be optimised
to use in line code.
PROCEDURE my_proc IS
...
BEGIN
...
PRAGMA INLINE (my_proc,'YES');
my_proc(1); call
to my_proc is converted to in line code
...
END;
3. Tune Embedded SQL Statements
All too often PL/SQL programs are blamed for poor performance when
it's
actually untuned
SQL statements embedded in the PLSQL code that are
causing the problems, so if your PL/SQL code is slow, examine each
of
your embedded SQL statements and tune those.
Are you using indexes? Would you be better off with full table
scans? Can
you combine multiple SQL statements into one?
Each time you run a SQL statement in PL/SQL (and vice versa) there
is a
context switch and SQL statements have to be sent to the SQL
statement
executor in the database. Obviously, therefore, if you have fewer
SQL
statements embedded in your PL/SQL code you will reduce the load
on the
Oracle database processing those statements.
4. Tune Cursor Handling
Closely aligned to tuning SQL statements is tuning the cursors in
your
PL/SQL.
How do you tune a cursor? Well. rather like with your SQL
statements, see
if you can combine them especially if there in nested loops. For
example
FOR reca IN
(SELECT some_data FROM tablea)
LOOP
FOR recb IN
(SELECT some_other_data FROM tableb
WHERE tableb.columnb=reca.fielda)
LOOP
FOR recc IN
(SELECT some_thing_else
FROM tablec
WHERE tablec.columnc=recb.fieldb)
LOOP
<process record>
END LOOP;
END LOOP;
END LOOP;
where the innermost query depends solely on the results of
previous
queries then you can combine all 3 cursors (in this case) into one
by
combining the queries into one (either via the use of sub queries
or by using
JOINs). This could have a dramatic impact on the performance of
your
PL/SQL code. If you combine this (if appropriate) with the use of BULK
COLLECT (see tip #9) and FORALL (see
tip #10), the performance
improvement could be even greater.
5. Tune Exception Handling
Exception handling is an important part of any PL/SQL program, but
exceptions should be just that, not something that's expected to
happen and
so exception handlers shouldn't include code for normal
processing.
This is not only good practice (as it makes your code easier to
understand
and therefore cheaper to maintain) it will also improve
performance
because every time an exception is raised, Oracle has to handle
that error
and jump to the exception handler or propagate the error to the
host
environment. For a single exception this is only a small overhead
but for
multiple exceptions in a very short period of time the overhead
could be
quite significant.
Many exceptions can be avoided by good design and anchoring
variables to
database items via use of the %TYPE and %ROWTYPE attributes.
6. Replace PL/SQL With SQL
There are times when PL/SQL is just the wrong tool for the job. If
you ever
see anything like the following or ever find yourself writing
something like
this, use pure SQL instead.
FOR rec IN (SELECT sal FROM emp) LOOP
UPDATE emp SET sal=sal*1.1; add
10% to salary
END LOOP;
You can replace this Oracle PL/SQL code with one SQL statement:
UPDATE emp SET sal=sal*1.1; add
10% to all salaries
7. Use PL/SQL Data Types
Whilst all the database types supported by Oracle are also
supported by
PL/SQL, using the native PLSQL data types results in much more
efficient
code. This particularly applies to the data types NUMBER and
INTEGER these
are designed for portability not performance.
Wherever possible you should use PLS_INTEGER if the value can be NULL or
you need overflow checking, or SIMPLE_INTEGER if not, for
integer
arithmetic. For floating point arithmetic use BINARY_FLOAT and
BINARY_DOUBLE if NULL values are
possible or SIMPLE_FLOAT and
SIMPLE_DOUBLE if not.
The constrained data types NATURAL, NATURALN, POSITIVE, POSITIVEN
and SIGNTYPE should also be avoided in your PLSQL code to obtain
maximum performance.
8. Tune IF Statements
IF statements should be organised so that the simplest (fastest to
evaluate)
conditions occur first and the complex conditions (most expensive)
occur
last. This is because as soon as a condition is found to be false
processing
of the IF statement stops.
The least expensive evaluations test the value of a PL/SQL
variable and the
most expensive invoke a subprogram. Be careful however when using
literals to ensure that no implicit data type conversion of the
literals is
required at run time. Therefore compare a character literal with a
character
variable and a number variable with a number literal and if using
floating
point variables ensure that the literal is floating point. Also
take note of tip
#7 and use PL/SQL data types whenever possible.
9. Use BULK COLLECT
When filling a PLSQL collection you can either do this one row at
a time (the
slow way) using a cursor FOR LOOP or you can fill it one hit by using BULK
COLLECT. That way your SQL statement only
has to be executed once by
the Oracle database instead of multiple times.
The BULK COLLECT clause can be
used with the SELECT INTO and FETCH
statements and the RETURNING
clause of DELETE, INSERT and
UPDATE
statements.
Examples
DECLARE
TYPE employee_tab_typ IS TABLE OF employee%ROWTYPE;
TYPE emp_id_tab_typ IS TABLE OF employee.emp_id%TYPE;
CURSOR all_employees IS SELECT * FROM employee;
TYPE all_emp_tab_typ IS TABLE OF all_employee%ROWTYPE;
employee_tab employee_tab_typ;
emp_id_tab emp_id_tab_typ;
all_emps all_emp_tab_typ;
BEGIN
SELECT * FROM employee BULK COLLECT INTO employee_tab;
OPEN all_employees;
FETCH all_employees INTO all_emps;
UPDATE employees SET sal=sal*1.1 WHERE role='DEVELOPER'
RETURNING emp_id INTO emp_id_tab;
END;
10. Use FORALL
Once you've filled your collection and want update your Oracle
database
using the values stored in your collection the fastest way to do
it is to use
the FORALL clause. This has the effect of sending all the required
DML
statements to the database simultaneously, however you can only
have a
single SQL statement that is repeated once for each element in the
collection.
Example:
DECLARE
TYPE emp_id_tab_typ IS TABLE OF employees.emp_id%TYPE;
emp_ids emp_id_tab_typ := emp_id_tab_typ();
BEGIN
<populate nested table>
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary*2
WHERE emp_id = emp_ids(i);
END;
No comments:
Post a Comment