Native Compilation, CASE, and Dynamic Bulk Binding
Pages: 1, 2, 3, 4
One-Time DBA Setup
Native PL/SQL compilation is achieved by translating the PL/SQL source code into C source code that's then compiled on the given platform. The compiling and linking of the generated C source code is done using third-party utilities whose location has been specified by the DBA, typically in the init.ora parameter file. (Check the Release Notes for your platform to see which third-party utilities are supported.)
The object code for each natively compiled PL/SQL library unit is stored on the platform's file system in directories, similarly under the DBA's control. Thus, native compilation does take longer than interpreted-mode compilation. Our tests have shown a factor of about times two. This is because it involves these extra steps: generating C code from the initial output of the PL/SQL compilation; writing this to the file system; invoking and running the C compiler; and linking the resulting object code into Oracle.
Oracle recommends that the C compiler be configured to do no optimization. Our tests have shown that optimizing the generated C produces negligible improvement in runtime performance but substantially increases the compilation time.
The DBA should appreciate that the utilities for compilation and linking must be under his or her strict control, owned by the Oracle user or root (or its equivalent on non-Unix systems) and with write-access granted only to these users. You already protect the Oracle executables in this way, for obvious reasons. You can imagine what might happen if you didn't! Well, the risk would be just the same if a malicious user could subvert the utilities for compilation and linking used by native compilation.
Choosing Between Interpreted and Native Compilation Modes
The compiler mode is determined by the session parameter plsql_compiler_flags. The user
may set it as follows:
ALTER SESSION
SET plsql_compiler_flags =
'NATIVE' /* or 'INTERPRETED' */;The compilation mode is then set for subsequently compiled PL/SQL library units (during that session). The mode is stored with the library unit's metadata, so that if the program is implicitly recompiled as a consequence of dependency checking, the original mode the user intended will be used.
You can determine the compilation mode by querying the data dictionary using the
SELECT statement shown in Example 3.
Example 3. Determining the compilation mode of a program unit.
SELECT o.object_name NAME, s.param_value comp_mode
FROM USER_STORED_SETTINGS s,
USER_OBJECTS o
WHERE o.object_id = s.object_id
AND param_name = 'plsql_compiler_flags'
AND o.object_type IN ('PACKAGE', 'PROCEDURE', 'FUNCTION');One thing to be aware of: If you use DBMS_UTILITY.COMPILE_SCHEMA to
attempt to recompile all invalid program units in your schema, it will use the current value
of plsql_compiler_flags rather than the compilation mode stored with each program unit.
(In other words, it does the equivalent of alter ... compile without reuse settings for the
whole schema.)
Oracle recommends that all the PL/SQL library units that are called from a given top- level unit be compiled in the same mode. This is because there's a cost for the context switch when a library unit compiled in one mode invokes one compiled in the other mode, particularly when a native unit calls an interpreted unit. Significantly, this recommendation includes the Oracle-supplied library units. These are always shipped (in the seed databases) compiled in interpreted mode.
Of course, if you compile some critical library units in native mode while everything else is in interpreted mode and if you measure an improvement in performance, then this is only good! But you might be missing an opportunity for yet more improvement by not having every unit in the database native. When starting from scratch, use the Database Configuration Assistant to set the plsql_% initialization parameters appropriately and create a new database. To upgrade an existing database to all native, use Oracle's script. (See the Oracle Technical Network for more information.)
Case Study: 170 Systems
170 Systems, Inc. has been an Oracle partner for 11 years and participated in the Beta Program for the Oracle 9i Database with particular interest in PL/SQL native compilation. They've now certified their 170 MarkView Document Management and Imaging SystemT against Oracle 9i and have updated the install scripts to optionally turn on native compilation.
The 170 MarkView Document Management and Imaging System provides Content Management, Document Management, Imaging, and Workflow Solutions-all tightly integrated with the Oracle 9i Database, Oracle 9i Application Server, and the Oracle E- Business Suite. Enabling businesses to capture and manage all of their information online in a single, unified system-regardless of original source or format-the 170 MarkView solution provides scalable, secure, production-quality Internet B2B and intranet access to all of an organization's vital information, while streamlining the associated business processes and maximizing operational efficiency.
A large-scale multi-user, multi-access system, 170 MarkViewT supports the very large numbers of documents, images, concurrent users, and the high transaction rates required by 170 Systems customers. Therefore, performance and scalability are especially important. 170 Systems customers include organizations such as British Telecommunications, E*TRADE Group, the Apollo Group, and the University of Pennsylvania. 170 MarkView uses several different mechanisms to interface to the Oracle 9i Database. Part of the business logic, including preparation of data for presentation, is implemented in the database in PL/SQL.
The computation involves string processing supported by stacks and lists of values modeled as PL/SQL collections. Several PL/SQL modules implement complex logic and include intensive string manipulation and processing. PL/SQL collections are leveraged in this complex processing.
They've observed a performance increase of up to 40 percent for computationally- intensive routines, and no performance degradation, in line with our observations using the code in Example 1 and Example 2 of this article. Native compilation offers compelling advantages to existing and new applications written in PL/SQL. It's just one example of Oracle's commitment to improving PL/SQL "from the bottom on up."
CASE Statements and CASE Expressions
Over the years, the PL/SQL user community has been vocal about how they'd like to see the language improved. These enhancements have ranged from "big picture" functionality like, "Gee, we'd really like to have a debugger!" to very concrete requests such as, "Please let me insert into a table using a record." Another example of a long-requested, very specific desire is support for CASE within PL/SQL. Well, with Oracle 9i, your wish has come true!
Actually, Oracle implemented support for a CASE expression in Oracle8i, but 1) it
was only available inside SQL statements, and 2) those SQL statements weren't
recognized as valid when compiled within a PL/SQL block. With Oracle 9i, SQL that uses
a CASE construct can be used in static SQL in a PL/SQL block...
BEGIN
FOR j IN (
SELECT
CASE ename
WHEN 'SMITH' THEN 'MR. SMITH'
WHEN 'ALLEN' THEN 'MR. ALLEN'
ELSE ename
END
FROM emp
)
LOOP ...; END LOOP;
END; ...since any SQL that works in the SQL environment works in the PL/SQL
environment by virtue of the new common parser. You can also write your own CASE
statements and expressions within your PL/SQL code.
CASE constructs don't offer any fundamentally new semantics (anything you write in a
CASE statement can be implemented with IF). They do, however, allow a more compact notation and some elimination of repetition with respect to what otherwise would be
expressed with an IF construct. Consider the implementation of a decision table whose
predicate is the value of a particular expression. The following two fragments are
semantically identical, but coding best practice gurus generally recommend the CASE
formulation because it more directly models the idea behind the code:
CASE expr
WHEN 1 THEN Action1;
WHEN 2 THEN Action2;
WHEN 3 THEN Action3;
ELSE ActionOther;
END CASE;and:
IF expr = 1 THEN Action1;
ELSIF expr = 2 THEN Action2;
ELSIF expr = 3 THEN Action2;
ELSE ActionOther;
END IF;By pulling out the decision expression expr to the start and by mentioning it only once,
the programmer's intention is clearer. This is significant both to a person reviewing the
code and to the compiler, which therefore has better information from which to generate
efficient code. For example, the compiler knows immediately that the decision expression
needs to be evaluated just once. Moreover, since the IF formulation repeats the decision
expression for each leg, there's a greater risk of typographical error that can be difficult to
spot.
Oracle offers a stand-alone CASE statement and a CASE expression (part of a larger statement, usually an expression). Let's look at the statement first.
