Download PL/SQL User's Guide and Reference

PL/SQL Features for Performance Tuning
UPDATE emp SET sal = sal * 1.1
WHERE empno = emp_id
RETURNING ename, sal INTO name, new_sal;
-- Now do computations involving name and new_sal
END;
Tuning PL/SQL Performance with External Routines
PL/SQL provides an interface for calling routines written in other languages.
Standard libraries already written and available in other languages can be called
from PL/SQL programs. This promotes reusability, efficiency, and modularity.
PL/SQL is specialized for SQL transaction processing. Some tasks are more quickly
done in a lower-level language such as C, which is very efficient at
machine-precision calculations.
To speed up execution, you can rewrite computation-bound programs in C. In
addition, you can move such programs from client to server, where they will
execute faster thanks to more computing power and less across-network
communication.
For example, you can write methods for an image object type in C, store them in a
dynamic link library (DLL), register the library with PL/SQL, then call it from your
applications. At run time, the library loads dynamically and, for safety, runs in a
separate address space (implemented as a separate process).
For more information, see Oracle9i Application Developer’s Guide - Fundamentals.
Improving PL/SQL Performance with Object Types and Collections
Collection types (see Chapter 5) and object types (see Chapter 10) increase your
productivity by allowing for realistic data modeling. Complex real-world entities
and relationships map directly into object types. And, a well-constructed object
model can improve application performance by eliminating table joins, reducing
round trips, and the like.
Client programs, including PL/SQL programs, can declare objects and collections,
pass them as parameters, store them in the database, retrieve them, and so on. Also,
by encapsulating operations with data, object types let you move data-maintenance
code out of SQL scripts and PL/SQL blocks into methods.
Objects and collections are more efficient to store and retrieve because they can be
manipulated as a whole. Also, object support is integrated with the database
architecture, so it can take advantage of the many scalability and performance
improvements built into each Oracle release.
Tuning PL/SQL Applications
12-13