Interpreted Versus Native Compilation Modes

The compilationmode is determined by the session parameter PLSQL_COMPILER_FLAGS. The user sets 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 mode the user intended will still be used.

You can determine the compilation mode by querying the data dictionary using the SELECT statement shown here:

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');

Here's one thing you need to be aware of: if you use the DBMS_UTILITY.COMPILE_SCHEMA built-in 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.

Oracle recommends that all of the PL/SQL library units called from a given top-level unit be compiled in the same mode (see the sidebar). That's because there is a cost for the context switch when a library unit compiled in one mode invokes one compiled in the other mode. Significantly, this recommendation includes the Oracle-supplied library units. These are always shipped compiled in interpreted mode.

Upgrading an Entire Database to Native The simplest way to follow Oracle's recommendation that all PL/SQL library units called from a given top-level unit be compiled in the same mode is to upgrade the whole database so that all PL/SQL library units are compiled NATIVE. A future release of the database (post-Oracle9i Release 2) will include a script to accomplish this, as well as a script to downgrade a whole database so that all PL/SQL library units are compiled INTERPRETED. For now, however, the best way to achieve this goal is to follow the instructions posted on the Oracle Technology Network at: http://otn.oracle.com//tech/pl_sql/htdocs/README_2188517.htm.

Our conclusion? If your application contains a significant amount of compute-intensive logic, you should seriously consider switching to native compilation.

19.4 Testing PL/SQL Programs

I get great satisfaction out of creating new things, and that is one of the reasons I so enjoy writing software. I love to take an interesting idea or challenge, and then come up with a way of using the PL/SQL language to meet that challenge.

I have to admit, though, that I don't really like having to take the time to test my software (nor do I like to write documentation for it). I do it, but I don't really do enough of it. And I have this funny feeling that I am not alone. The overwhelming reality is that developers generally perform an inadequate number of inadequate tests and figure that if the users don't find a bug, there is no bug. Why does this happen? Let me count the ways . . .

The psychology of success and failure

We are so focused on getting our code to work correctly that we generally shy away from bad news—or from taking the chance of getting bad news. Better to do some cursory testing, confirm that everything seems to be working OK, and then wait for others to find bugs, if there are any (as if there were any doubt).

Deadline pressures

Hey, it's Internet time! Time to market determines all. We need everything yesterday, so let's be just like Microsoft and Netscape—release pre-beta software as production and let our users test/suffer through our applications.

Management's lack of understanding

IT management is notorious for not really understanding the software development process. If we are not given the time and authority to write (and I mean "write" in the broadest sense, including testing, documentation, refinement, etc.) code properly, we will always end up with buggy junk that no one wants to admit ownership of.

Overhead of setting up and running tests

If it's a big deal to write and run tests, they won't get done. We'll decide that we don't have time; after all, there is always something else to work on. One consequence of this is that more and more of the testing is handed over to the QA department, if there is one. That transfer of responsibility is, on the one hand, positive. Professional quality assurance professionals can have a tremendous impact on application quality. Yet developers must take and exercise responsibility for unit testing their own code; otherwise, the testing/QA process is much more frustrating and extended.

The bottom line is that our code almost universally needs more testing. I recently spent a fair amount of time thinking about how to improve my testing procedures. I have studied test "frameworks" developed by other programmers who work primarily with object-oriented languages. An obsessive coder, I then proceeded to construct my own framework for unit testing PL/SQL programs, which I named utPLSQL. utPLSQL is now an open source project that is being used by developers around the world.

In the following sections, I take a look at the way we often test (or think we are testing) our code. I then introduce utPLSQL, give you a sense of how it works, and tell you where you can get the software.