Oracle PLSQL
- The Top Myths Of High Performance Code
PLSQL has
been around for many years (since the early 1990s) and perhaps
unsurprisingly 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.
- 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.
- 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.
- 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;
- 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 dynmaic sql.
- 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;
If you want to learn more about PLSQL our
partners in
New Zealand
and in
the UK
offer instructor-led training on PL/SQL (as well as other aspects of
Oracle).
|
