Working with Wrapped Code

I have found the following guidelines useful in working with wrapped code:

· Create batch files so that you can easily, quickly, and uniformly wrap one or more files. In Windows NT, I create bat files that contain lines like this in my source code directories:

c:\orant\bin\wrap iname=plvrep.sps oname=plvrep.pls

Of course, you can also create parameterized scripts and pass in the names of the files you want to wrap.

· To wrap your source code, you must place that code in an operating system file. If you are working within a PL/SQL development environment that allows you to build and maintain source directly in the database, you will have to "dump" this code to a file, wrap it, and then compile it back into the database—thereby wiping out your original, readable, and maintainable source code. This is not an issue as you deploy software to customers, but it could cause some uncomfortable situations as you develop and maintain applications.

· You can only wrap package specifications, package bodies, and standalone functions and procedures. You can run the wrapped binary against any other kind of SQL or PL/SQL statement, but those files will not be changed.

· You can tell that a program is wrapped by examining the program header. It will contain the keyword WRAPPED, as in:

PACKAGE BODY package_name WRAPPED

Even if you don't notice the keyword WRAPPED on the first line, you will immediately know that you are looking at wrapped code because the text in USER_SOURCE will look like this:

LINE TEXT------- ---------------------- 45 abcd 46 95a425ff 47 a2 48 7 PACKAGE:

and no matter how bad your coding style is, it surely isn't that bad!

· Wrapped code is much larger than the original source. I have found in my experience that a 57 KB readable package body turns into a 153 KB wrapped package body, while a 86 KB readable package body turns into a 357 KB wrapped package body. These increases in file size do result in increased requirements for storing source code in the database. The size of compiled code stays the same, although the time it takes to compile may increase.

19.3 Using Native Compilation

In pre-Oracle9i versions, compilation of PL/SQL source code always results in a representation (usually referred to as bytecode) that is stored in the database and is interpreted at runtime by a virtual machine implemented within Oracle that, in turn, runs natively on the given platform. Oracle9i introduces a new approach. PL/SQL source code may optionally be compiled into native object code that is 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? This section addresses these questions.

PL/SQL is often used as a thin wrapper for executing SQL statements, setting bind variables, and handling result sets. For these kinds of programs, the execution speed of the PL/SQL code is rarely an issue; it is 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 this is addressed very effectively by the FORALL and BULK COLLECT features introduced in Oracle8i and described in Chapter 13).

There are many other applications and programs, however, that rely on PL/SQL to perform computationally intensive tasks that are independent of the database. PL/SQL is, after all, a fully functional procedural language. Suppose, for example, that I wrote a program to find 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. (You will find the code to perform this function in the perfect_triangles.sp file on the O'Reilly site.)

This program implements an exhaustive search among candidate triangles with all possible combinations of lengths of the two shorter sides, each in the range 1 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. Triangles that pass this test are tested again by exactly applying Pythagoras's theorem using integer arithmetic. Candidate perfect triangles are then 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 the following 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 is the impact of native compilation on such code? We measured the elapsed time for p_max =5000 (i.e., 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% faster.

That's not bad for a semitransparent enhancement (i.e., no code changes were required in my application). And while native compilation may give only a marginal performance improvement for data-intensive programs, I have never seen it degrade performance. So how do you turn on native compilation? Read on . . .