Native Compilation, CASE, and Dynamic Bulk Bindingby Steven Feuerstein, coauthor of Oracle PL/SQL Programming, 3rd Edition and Bryn Llewellyn
Editor's note: In previous articles (see sidebar below for links) Steven Feuerstein examined the Oracle 9i inheritance capabilities of Oracle object types. This article continues his exploration of new PL/SQL features in Oracle 9i, an exploration that will consist of a series of articles written with Bryn Llewellyn, PL/SQL Product Manager at Oracle Corporation. The material in this article is based on an Oracle Corporation white paper originally presented by Bryn Llewellyn at Oracle OpenWorld 2001 in San Francisco.
We'll kick this off this second part of our series on new PL/SQL features with an exploration of Oracle 9i PL/SQL enhancements. The most important enhancements to PL/SQL in Oracle 9i can be categorized as falling into one of these areas:
Its implementation (that which effects the execution characteristics of a given system of source code)
Language features (the addition of new syntax to express powerful new semantics)
Oracle-supplied PL/SQL library units
Also In This Series
Some of the enhancements are transparent; for example, the change to using the same parser for compile-time checked embedded SQL as is used for compiling SQL issued from other programming environments; or the re-implementation of the
UTL_TCP package (moving from Java to native C). You don't have to do anything besides upgrade to Oracle 9i to take advantage of these improvements.
Some are semi-transparent, which means that you'll need to take small, declarative steps (that is, you don't have to change any of your code). The best example of such a semi-transparent enhancement is the new option to compile PL/SQL source to native C--a topic to be explored in a future article.
Finally, some new features introduce new semantics, either in the language itself or by virtue of new APIs in the supplied PL/SQL library units. To take advantage of these enhancements, you'll need to first learn what's possible and then carefully decide how to upgrade existing code or integrate these features in new applications.
In future articles, we'll explore in much more detail the following Oracle 9i PL/SQL enhancements:
- Table functions and cursor expressions
- Multi-level collections
- Enhancements to the
- Use of the "Any" datatypes to write highly generic code
In each case, we'll introduce you to the technology and then provide extensive, complete code samples that provide working demonstrations for all these features. In this article, we'll cover native compilation of PL/SQL,
CASE statements and
CASE expressions, and bulk binding in native dynamic SQL.
Native Compilation of PL/SQL
In pre-Oracle 9i versions, compilation of PL/SQL source code always results in a representation (usually referred to as bytecode) that's stored in the database and interpreted at runtime by a virtual machine implemented within Oracle that, in turn, runs natively on the given platform. Oracle 9i introduces a new approach. PL/SQL source code may optionally be compiled into native object code that's linked into Oracle. (Note, however, that an anonymous PL/SQL block is never compiled natively.) When would this feature come in handy? How do you turn on native compilation? So many questions... and here come the answers.
PL/SQL is often used as a thin wrapper for executing SQL statements, setting bind variables, and handling result sets; one example of such a wrapper can be seen in Example 1. For these kinds of programs, the execution speed of the PL/SQL code is rarely an issue. It is, rather, the execution speed of the SQL that determines the performance. (The efficiency of the context switch between the PL/SQL and the SQL operating environments might be an issue, but that's a different discussion. See the section in this article on bulk binding as one way of addressing this context switch issue.)
Example 1. Thin wrapper for executing SQL statements.
BEGIN FOR department IN (SELECT department_id d, department_name FROM departments ORDER BY department_name) LOOP DBMS_OUTPUT.PUT_LINE ( CHR (10) || department.department_name); FOR employee IN (SELECT last_name FROM employees WHERE department_id = department.d ORDER BY last_name) LOOP DBMS_OUTPUT.PUT_LINE ( '- ' || employee.last_name); END LOOP; END LOOP; END;
There are many other applications and programs, however, that rely on PL/SQL to perform computationally-intensive tasks that are all but completely independent of the database. It is, after all, a fully functional procedural language. Consider, for example, the code shown in Example 2. This program takes on the task of finding all right-angled triangles with all side lengths integer (a.k.a. perfect triangles). We must count only unique triangles-that is, those whose sides are not each the same integral multiple of the sides of a perfect triangle already found.
Example 2. Computing perfect triangles.
CREATE OR REPLACE PROCEDURE perfect_triangles (p_max IN INTEGER) IS t1 INTEGER; t2 INTEGER; long INTEGER; short INTEGER; hyp NUMBER; ihyp INTEGER; TYPE side_r IS RECORD ( short INTEGER, long INTEGER); TYPE sides_t IS TABLE OF side_r INDEX BY BINARY_INTEGER; unique_sides sides_t; n INTEGER := 0 /* curr max elements in unique_sides */; dup_sides sides_t; m INTEGER := 0 /* curr max elements in dup_sides */; PROCEDURE store_dup_sides (p_long IN INTEGER, p_short IN INTEGER) IS mult INTEGER := 2; long_mult INTEGER := p_long * 2; short_mult INTEGER := p_short * 2; BEGIN WHILE (long_mult < p_max) OR (short_mult < p_max) LOOP n := n + 1; dup_sides (n).long := long_mult; dup_sides (n).short := short_mult; mult := mult + 1; long_mult := p_long * mult; short_mult := p_short * mult; END LOOP; END store_dup_sides; FUNCTION sides_are_unique (p_long IN INTEGER, p_short IN INTEGER) RETURN BOOLEAN IS BEGIN FOR j IN 1 .. n LOOP IF (p_long = dup_sides (j).long) AND (p_short = dup_sides (j).short) THEN RETURN FALSE; END IF; END LOOP; RETURN TRUE; END sides_are_unique; BEGIN /* Perfect_Triangles */ t1 := DBMS_UTILITY.get_time; FOR long IN 1 .. p_max LOOP FOR short IN 1 .. long LOOP hyp := SQRT ( long * long + short * short); ihyp := FLOOR (hyp); IF hyp - ihyp < 0.01 THEN IF (ihyp * ihyp = long * long + short * short ) THEN IF sides_are_unique (long, short) THEN m := m + 1; unique_sides (m).long := long; unique_sides (m).short := short; store_dup_sides (long, short); END IF; END IF; END IF; END LOOP; END LOOP; t2 := DBMS_UTILITY.get_time; DBMS_OUTPUT.put_line ( CHR (10) || TO_CHAR ((( t2 - t1 ) / 100 ), '9999.9') || ' sec' ); END perfect_triangles;
This program implements an exhaustive search among candidate triangles with all possible combinations of lengths of the two shorter sides, each in the range of one to a specified maximum. Testing whether the square root of the sum of the squares of the two short sides is within 0.01 of an integer coarsely filters each candidate. Exactly applying Pythagoras's theorem using integer arithmetic tests triangles that pass this test. Candidate- perfect triangles are tested against the list of multiples of perfect triangles found so far. Each new unique perfect triangle is stored in a PL/SQL table, and its multiples (up to the maximum length) are stored in a separate PL/SQL table to facilitate uniqueness testing.
The implementation thus involves a doubly nested loop with these steps at its heart: several arithmetic operations, casts and comparisons; calls to procedures implementing comparisons driven by iteration through a PL/SQL table (with yet more arithmetic operations); and extension of PL/SQL tables where appropriate.
So what impact does native compilation have on such code? The elapsed time was measured for
p_max =5000 (that is, 12.5 million repetitions of the heart of the loop) using
interpreted and natively compiled versions of the procedure. The times were 548 seconds
and 366 seconds, respectively (on a Sun Ultra60 with no load apart from the test). Thus,
the natively compiled version was about 33 percent faster.
That's not bad for a semi-transparent enhancement. In other words, no code changes were required in our application. And, while for data-intensive programs, native compilation may give only a marginal performance improvement, we've never seen it give performance degradation. So how do you turn on native compilation? Read on...