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.
20.1.2 Physical Storage of Server-Side PL/SQL
Now that you've been introduced to the role that different components play in the compilation and execution of PL/SQL, let's take this abstract information and find out where the different forms of the language live on the disk. Table 20-1 lists the major hiding places in the Oracle database server.
| Table 20-1. Locations of server-side PL/SQL code and metadata | |||
| Underlying location (all tables owned by SYS) | Name of view typically used by application developers | Applies to whichPL/SQL objects | Most useful contents |
| OBJ$ | USER_OBJECTS | All except anonymous blocks | Name, object type, last DDL time, compilation status (VALID or INVALID) |
| SOURCE$ | USER_SOURCE | All except triggers and anonymous blocks | Source code |
| TRIGGER$ | USER_TRIGGERS | Triggers | Source code, description of triggering event |
| ERROR$ | USER_ERRORS | All except anonymous blocks | Most recent outstanding compilation error for any stored PL/SQL (including triggers) |
| DEPENDENCY$ | USER_DEPENDENCIES | All except anonymous blocks | Object dependency hierarchy |
| SETTINGS$ | USER_STORED_SETTINGS | All except anonymous blocks | PL/SQL compiler flags |
| IDL_UB1$ IDL_CHAR$ IDL_UB2$ IDL_SB4$ | USER_OBJECT_SIZE | All except anonymous blocks | Internal storage of DIANA, bytecode, and debug data; used by the compiler and runtime engine |
| Directory in operating system, designated by PLSQL_NATIVE_LIBRARY_DIR database parameter | N/A | All that have been natively compiled | Shared object files natively compiled from PL/SQL via C |
You probably haven't been lying awake at night wondering about these tables, so I'll mention a few reasons you might find them useful, along with the data dictionary views you can query for information:
· To find out which objects are in an INVALID state (USER_OBJECTS)
· To discover which programs have been compiled using native compilation (USER_STORED_SETTINGS)
· To find out what local objects will be invalidated by a change in a particular PL/SQL program (USER_DEPENDENCIES)
· To recover source code of programs whose original creation script has been lost ( USER_SOURCE, USER_TRIGGERS)
· To get an idea of the relative sizes of stored programs (USER_OBJECT_SIZE)
Regarding that last item, if you look at the data dictionary view USER_OBJECT_SIZE, you can find the size of each stored portion of your programs. For each object, this view will tell you the following (these are column names):
SOURCE_SIZE
Number of bytes on disk that the source code occupies.
PARSED_SIZE
Number of bytes on disk that the DIANA occupies.
CODE_SIZE
Sum of bytes on disk occupied bybytecode and debug data. For PL/SQL objects, the latter appears only if the program was compiled with the DEBUG option.
ERROR_SIZE
Number of bytes on disk that the error text occupies; zero if no errors.
Don't get too excited—this information is less useful than it might seem. For example, when Oracle puts the DIANA in data dictionary tables, it's not stored in exactly the same form as when it's live in memory. On disk, it is "linearized" and compressed; in memory, it is expanded into its customary tree structure. Sooner or later, you are going to want to understand runtime memory requirements; see the later section Section 20.3 for more information about this important topic.
However, exploring the information in the USER_OBJECT_SIZE view does confirm one thing: Oracle discards DIANA for package bodies. The value of the PARSED_SIZE column for package bodies is always zero.
|
Does Oracle ever really need DIANA for a package body? Yes, but only during compilation, because the compiler uses DIANA to generate bytecode and to match the signatures in the package body with those of the package specification. Because no stored object will ever depend on the package body, Oracle discards its DIANA. If Oracle needs to recompile the body later (for example, if there has been a change in some object that it depends upon), the compiler will re-create the DIANA for the body, redo type checks, regenerate the bytecode, and throw that DIANA away again. Even if Oracle had retained the old DIANA, it would not have helped the recompilation in any way.
I am not, by the way, making a blind recommendation to put everything into packages no matter what. Objectives such as ease of development might outweigh the need to squeeze every drop of performance out of a given database machine. A good case in point is Oracle'sPL/SQL Server Pages (PSP) feature, which generates top-level PL/SQL procedures from tagged HTML files.[3] As a general guideline, though, the larger the amount of code, the more significant the gain from using packages.
[3] I suppose you could bundle those PSP-generated procedures into packages, but if you're developing on "Internet time," your schedule might not allow it!