Figure 20-3. Execution of a program that calls a stored procedure

Here, the compiler needs to resolve the external reference foo to determine whether it refers to a program that the user has privilege to execute. It will also need foo's DIANA to determine whether the anonymous block is, in fact, making a legal call to the stored program. Notice one of the benefits of stored code: having the DIANA and bytecode already stored in the data dictionary means that Oracle won't have to waste any time recompiling it. Even better, once the code is read from disk, Oracle will store that code in a memory area called the library cache, which will reduce or eliminate the need for any disk I/O to reread the code.

Further, after the initial compilation (and as long as there have been no changes in dependencies that would force an automatic recompilation), Oracle requires only bytecode in order to execute a program. In other words, when foo calls bar, Oracle can omit the reading of the DIANA for bar, thereby saving the expense of some I/O and library cache memory.

Suppose that the stored procedure does include embedded SQL—say, a nice long SELECT statement. Because Oracle compiles PL/SQL before it's stored in the data dictionary, resulting in speedier execution, you might be tempted to believe that the embedded SQL will also get this kind of performance boost. But in the same way that Oracle processes SQL in anonymous blocks, so it treats SQL in stored PL/SQL: saving it in textual form in PL/SQL's bytecode and compiling at runtime. There is no "precompiling" boost.

Nevertheless, PL/SQL performs several tricks at runtime to avoid "soft parses" of the query string. (A soft parse is how Oracle matches the query string to an in-memory compiled form of the SQL statement.) In contrast, if you run a SQL statement from a tool like SQL*Plus, there is no way to avoid soft parses. So you may, after all, see somewhat improved SQL parsing performance merely by wrapping up your SQL statements inside PL/SQL. It's true that some other programming environments may also avoid soft parses, but even something like the low-level C interface known as the Oracle Call Interface (OCI) will have communications overhead (network or IPC), which stored PL/SQL avoids.

Finally, let's take a look at native compilation—which may more properly be called native execution. This Oracle9i feature provides the option to convert server-side PL/SQL into C, and compile and link it into a shared object file. This form generally executes faster than conventionally compiled PL/SQL, unless the program spends a relatively large amount of time processing SQL statements.

If I have a procedure called bar that I've compiled using this nifty feature, Oracle creates and retrieves the corresponding compiled program in the operating system, as illustrated in Figure 20-4.