Remote Dependencies

Server-based PL/SQL immediately becomes invalid whenever there's a change in a local object on which it depends. However, if it depends on an object on a different computer and that object changes, Oracle does not attempt to invalidate the calling PL/SQL program in real time. Instead, Oracle defers the checking until runtime. This deferred checking applies to two situations:

· A client-side PL/SQL program, such as a procedure in an Oracle Forms module, calls out to a stored program on any database server.

· A server-side program makes a remote procedure call (RPC) over a database link. For example:

· PROCEDURE synch_em_up (tax_site_in IN VARCHAR2, since_in IN DATE)· IS· BEGIN· IF tax_site_in = 'LONDON'· THEN· [email protected](cutoff_time => since_in); END IF;

In these situations, if the runtime engine decides that it won't execute the remote program, you'll see an ORA-04062 error with accompanying text such as timestamp (or signature) of package "SCOTT.recompute_prices" has been changed. To understand how the runtime engine makes this decision, you need to know that the PL/SQL compiler always stores two kinds of information about each referenced remote procedure: its timestamp and its signature.

Timestamp

The most recent date and time (down to the second) when an object's specification was reconstructed, as given by the TIMESTAMP column in the USER_OBJECTS view. For PL/SQL programs, this is not necessarily the same as the most recent compilation time because it's possible to recompile an object without reconstructing its specification. (Note that this column is of the DATE datatype, not the newer datatype that also happens to have the name TIMESTAMP.)

Signature

A footprint of the actual shape of the object's specification. Signature information includes the object's name and the ordering, datatype family, and mode of each parameter.

So when I compiled synch_em_up, Oracle retrieved both the timestamp and the signature of the remote procedure called recomputed_prices and stored a representation of them with the bytecode of synch_em_up.

How do you suppose Oracle uses this information at runtime? The model is simple: it uses either the timestamp or the signature, depending on the current value of the parameter REMOTE_DEPENDENCIES_MODE. If that timestamp or signature information, which is stored in the local program's bytecode, doesn't match the actual value of the remote procedure at runtime, you get the ORA-04062 error.

Oracle's default remote dependency mode is the timestamp method, but this setting can sometimes cause unnecessary recompilations. The DBA can change the system-wide setting via the initialization file or an ALTER SYSTEM command; an application developer can set it for the current session using the following command:

ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE;

or, inside PL/SQL:

EXECUTE IMMEDIATE 'ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = SIGNATURE';

Thereafter, for the remainder of that session, every PL/SQL program run will use the signature method. As a matter of fact, Oracle's client-side tools always execute this ALTER SESSION...SIGNATURE statement as the first thing they do after connecting to the database, overriding whatever is in the Oracle initialization file (INIT.ORA).

Oracle recommends using signature mode on client tools like Oracle Forms, and timestamp mode on server-to-server procedure calls. Be aware that signature mode can cause false negatives—situations where the runtime engine thinks that the signature hasn't changed, but it really has—in which case Oracle does not force an invalidation of a program that calls it remotely. You can wind up with silent computational errors that are difficult to detect and even more difficult to debug. Here are several risky scenarios:

· Changing only the default value of one of the called program's formal parameters. The caller will continue to use the old default value.

· Adding an overloaded program to an existing package. The caller will not bind to the new version of the overloaded program even if it is supposed to.

· Changing just the name of a formal parameter. The caller may have problems if it uses named parameter notation.

In these cases, you will have to perform a manual recompilation of the caller. In contrast, the timestamp mode, while prone to false positives, is immune to false negatives. In other words, it won't miss any needed recompilations, but it may force recompilation that is not strictly required. This safety is no doubt why Oracle uses it as the default for server-to-server RPCs.

If you do use the signature method, Oracle recommends that you add any new functions or procedures at the end of package specifications, as doing so reduces false positives.

20.3 PL/SQL's Use of Memory in the Oracle Server

By economizing on its use of machine resources such as memory and CPU, Oracle can support tens of thousands of simultaneous users on a single database. Oracle's memory management techniques have become quite sophisticated over the years, and correspondingly difficult to understand. Although administrators of busy databases need a thorough knowledge of memory management, advanced PL/SQL programmers should also have a good understanding of this topic. Virtually all PL/SQL programmers will want to know how to avoid undermining Oracle's memory sharing algorithms.