Добавил:
Upload Опубликованный материал нарушает ваши авторские права? Сообщите нам.
Вуз: Предмет: Файл:
Programming PL SQL.doc
Скачиваний:
3
Добавлен:
01.07.2025
Размер:
5 Мб
Скачать
☆

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.

Figure 20-4. Execution of an anonymous block that calls a natively compiled program

In this case, the compiler did produce the usual DIANA during compilation, but there is no bytecode as such. Instead, the data dictionary and library cache structures that would otherwise store the bytecode contain the name and location of the object file. The called program, which lives in a shared library file, has no bytecode to interpret; Oracle makes operating system-level calls to load the shared library dynamically and run it natively.

There are other ways that you can build stored code and call it from PL/SQL:

Java stored procedures

The default Oracle server installation includes not just a PL/SQL virtual machine but also a Java virtual machine. You can write a PL/SQL call spec whose logic is implemented as a static Java class. For details and examples, see Chapter 22.

External procedures

You can also implement the executable portion of a PL/SQL subprogram in custom C code, and at runtime Oracle will run your code in a separate process and memory space from the main Oracle server. Chapter 23 discusses external procedures.

You can learn more about the runtime architecture of these two approaches in their respective chapters.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]