These are our top 10 tips on Oracle PL/SQL performance tuning to make your code blindingly fast. One point to bear in mind though is that you don't have to wait until after the PL/SQL code has been written and tested and found lacking in performance to start tuning it. A great deal of the tuning can be done as you write the code if not at the design stage.
1. Tune PL/SQL Subprogram InvocationAll 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 (refer to parameter declarations in the Oracle PL/SQL Language Reference manual available from the Oracle Technology Network.
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 parameterPLSQL_OPTIMIZE_LEVEL which can be changed at the session level. Setting this 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 great but there is a drawback - 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 as the programmer 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 converting sub-programs to in-line code.
For example, with PLSQL_OPTIMIZE_LEVEL set to 3 the procedure call willnot be optimised to use in line code.
PROCEDURE my_proc IS
PRAGMA INLINE (my_proc,'NO');
my_proc(1);-- call to my_proc is NOT converted to in line code
With PLSQL_OPTIMIZE_LEVEL set to 2 the following code will be optimised to use in line code.
PROCEDURE my_proc IS
PRAGMA INLINE (my_proc,'YES');
my_proc(1); --call to my_proc is converted to in line code
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.
All too often PL/SQL programs are blamed for poor performance when it's actually un-tuned 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.
More tips on tuning SQL are available here.
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)
FOR recb IN
(SELECT some_other_data FROM tableb
FOR recc IN
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.
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.
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
You can replace this Oracle PL/SQL code with one SQL statement:
UPDATE emp SET sal=sal*1.1; -- add 10% to all salaries
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 andBINARY_DOUBLE if NULL values are possible or SIMPLE_FLOAT andSIMPLE_DOUBLE if not.
The constrained data types NATURAL, NATURALN, POSITIVE, POSITIVENand SIGNTYPE should also be avoided in your PLSQL code to obtain maximum performance.
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.
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 FETCHstatements and the RETURNING clause of DELETE, INSERT and UPDATEstatements.
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;
SELECT * FROM employee BULK COLLECT INTO employee_tab;
FETCH all_employees INTO all_emps;
UPDATE employees SET sal=sal*1.1 WHERE role='DEVELOPER'
RETURNING emp_id INTO emp_id_tab;
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.
TYPE emp_id_tab_typ IS TABLE OF employees.emp_id%TYPE;
emp_ids emp_id_tab_typ := emp_id_tab_typ();
<populate nested table>
FORALL i IN emp_ids.FIRST..emp_ids.LAST
UPDATE employees SET salary = salary*2
WHERE emp_id = emp_ids(i);
Looking for a PL/SQL training course? Learn PLSQL from real-world experts either on-demand or at a sceduled training course and sky rocket your career. Click here for the Oracle training options.