Oracle PLSQL - The Top Myths Of High Performance Code

PLSQL has been around since the early 1990s and perhaps unsurprisingly over time a number of myths have grown up over that time about the best ways to write PL/SQL code to get high performance. Some of these myths may have been valid in the past, but as the language evolves, good coding techniques have to evolve too. 

Our aim here is to debunk a few of these myths and if you believe we've missed something important let us know.

1. Structuring your Oracle PLSQL code leads to poor performance


Wrong.
 

It's badly written code that leads to poor performance not giving it a proper structure. Furthermore, poorly structured Oracle PL/SQL code increases development costs (because it takes longer to debug huge monolithic procedures than smaller tightly-focused functions and procedures) and increases maintenance costs because whoever comes after you has to figure out which bit of the code does what and whether or not it's safe to change it!

Right approach: design your code before you write it. As soon as a PLSQL package (or standalone function or procedure) is loaded, all sub-routines declared in the package/function/procedure are also loaded.


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.


2. Making one PL/SQL function or procedure do several different things

The next myth is that having one PL/SQL function or procedure do several different things depending on the value of an input parameter saves coding and is more efficient.

Wrong.

A PLSQL function or procedure should be designed to do one thing and one thing only. Passing in a parameter that controls the functionality of the subroutine does not improve performance.

For example the following procedure should either be split into  3 seperate procedures or, if the columns are small, always return all 3 columns.

PROCEDURE fetch_employee_details
(emp_id IN NUMBER
,value_wanted IN VARCHAR2
,emp_name OUT VARCHAR2
,emp_sal OUT VARCHAR2
,emp_job OUT VARCHAR2)

BEGIN

    IF value_wanted = 'NAME' THEN
        SELECT employee_name INTO emp_name 
        FROM employees
        WHERE employee_id = emp_id;
    ELSIF value_wanted = 'SAL' THEN
        SELECT salary INTO emp_sal
        FROM employees
        WHERE employee_id = emp_id;
    ELSE
        SELECT job_title INTO emp_job
        FROM employees
        WHERE employee_id = emp_id;

END fetch_employee_details; 

Passing in a control variable is not good practice and doesn't make the code any more efficient. Returning all 3 columns every time and leaving the calling routine to ignore the unwanted columns would have a very slight (if any) overhead because Oracle reads the data in multiples of Oracle blocks which in turn are multiples of operating system blocks so just reading 1 column would not save anything. 

3. Breaking complicated expressions into several steps hurts performance.


Wrong.

The PLSQL compiler for Oracle 10g and above optimizes the code as it compiles it by default.

Consider the following example:

string := ' -----'||Lpad(To_Char(i), 20, '+')||
          'XYZ||'-----';
temp_str := Lower (string);
temp_str := Ltrim(temp_str,' ');
temp_str := Ltrim(temp_str,'-');
temp_str := Ltrim(temp_str,'+');
temp_str := Rtrim(temp_str,' ');
temp_str := Rtrim(temp_str,'-');
temp_str := Replace (temp_str, 'xyz','abc');
temp_str := Replace (temp_str, To_Char(r),null);
temp_str := Ltrim(temp_str,'a');

if temp_str = 'bc'
then raise program_error; 
end if;


The performance of the above manually optimsed code would be little different from the performance of this version.

if
Ltrim(

  Replace (
    Replace (
      Rtrim(
        Rtrim(
          Ltrim(
            Ltrim(
              Ltrim(
                Lower (' -----'||                                     Lpad(To_Char(r),20,'+')||
                  'XYZ'||'-----'),
                ' '),
              '-'),
            '+'),
          ' '),
        '-'),
      'xyz',
    'abc'),
  To_Char(r), null),
'a') =‘bc’ then
    raise program_error;
end if;

4. When fetching records from a table using a primary or unique key, using OPEN ... FETCH ... CLOSE ... in PLSQL is more efficient than SELECT ... INTO ... because SELECT ... INTO ... does an extra select to check for the TOO_MANY_ROWS exception

Wrong.

Since Oracle 10g, SELECT ... INTO ... is faster due to compiler optimizations especially for native dynamic sql.

5. Cursor FOR LOOPS are the most efficient way of processing multiple rows from a query.


Wrong.

A faster, more efficient way of processing a large number of rows in PL/SQL is to use BULK COLLECT. The keywords BULK COLLECT instruct the SQL engine to bulk-bind output collections before returning them to the PL/SQL engine.

This first example using a cursor FOR LOOP is less efficient than the 2nd

example using BULK COLLECT

DECLARE
    TYPE emp_tab_ty TABLE OF employees%TYPE INDEX BY BINARY_INTEGER;
     emp_tab emp_tab_ty;
     idx BINARY_INTEGER;

BEGIN
    i := 1;
    FOR employee IN (SELECT * FROM employees) LOOP
        emp_tab(i) := employee;
    END LOOP;
END;

The above example is less efficient than the following example.

DECLARE
    TYPE emp_tab_ty TABLE OF employees%TYPE;
    emp_tab emp_tab_ty;

BEGIN

    SELECT * BULK COLLECT INTO emp_tab 
    FROM employees
;

END;
     


Looking for PL/SQL training? Learn PLSQL from real-world experts with Smartsoft's PL/SQL training in the UK. Advance your skills and sky rocket your career.


Return to the introductory PLSQL tutorial for an overview of Oracle PL/SQL
Return to home page