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 (refer to parameter declarations in the Oracle PL/SQL Language Reference manual).

2. In-line PL/SQL sub programs

In versions of PLSQL before Oracle 10g, there could sometimes 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 PLSQL 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 the value of this parameter to 3 causes the PL/SQL compiler to convert subprograms to in-line code wherever it can unless instructed not to by use of the INLINE pragam.

This might sound just what you want but in-lining 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 sub-programs to in-line code when the INLINE pragma is specified before the invocation of the sub program. In other words you decide which procedure and function invocations should be converted to in-line code. This enables you to benfit from the other compiler optimisations as well as conversting sub-programs to in-line 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;

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.

3. Tune Embedded SQL Statements

All too often PL/SQL programs are blamed for poor performance when it's caused by un-tuned SQL statements embedded in the PLSQL code. 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 you will have a reduced load on the database processing those statements.

See here for more tips on tuning SQL.

4. Tune Cursor Handling

Closely aligned to tuning SQL statements but not the same 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 reduce them. Specifically if you have something of the form:

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 PLSQL 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;

Looking for a PL/SQL training course? Learn PLSQL from real-world experts with PL/SQL training in the UK. Advance your skills and sky rocket your career with expert instructor-led Oracle training.

Oracle PL/SQL Performance Tuning

1. Tune PLSQL Subprogram Invocation