oracle-developer.net

pl/sql enhancements in oracle 11g

Oracle has released some major new features for PL/SQL developers with 11g (see this list of articles for details). Oracle has also included several smaller PL/SQL enhancements and the following features are briefly covered in this article:

simplified sequence expressions;

named notation support in SQL;

CONTINUE statement;

SIMPLE_INTEGER datatype; and

simplified native compilation.

simplified sequence fetching

We will begin with Oracle 11g's support for sequence expressions in PL/SQL. As most developers will be aware, sequences have never been directly supported in PL/SQL. In versions prior to 11g, assigning a variable from a sequence (i.e. retrieving NEXTVAL or CURRVAL) is a SQL-only operation (we typically use a SELECT..INTO..FROM DUAL construct). In 11g, however, Oracle has added sequence fetching (using NEXTVAL and CURRVAL) to the list of valid PL/SQL expressions. This means we can use sequence expressions in PL/SQL anywhere we can validly use a number.

To demonstrate this, we will create a sequence ("S") and run some simple examples, as follows.

SQL> CREATE SEQUENCE s;

Sequence created.

In our first example, we will assign the NEXTVAL pseudo-column to a variable, as follows.

We can see that, in this case, the sequence pseudo-column is used in a similar manner to a function call. Note that we can also use CURRVAL in the same manner as NEXTVAL, as the following example demonstrates.

This new feature provides greater simplicity, but not improved performance. Oracle's underlying implementation of sequences and sequence-fetching hasn't changed, as we will now demonstrate. In the following example, we will use SQL trace to compare the performance of sequence assignment expressions with the pre-11g method of selecting the next values from the DUAL table. We will execute 10,000 assignments per method, as follows. Note how we alias the pseudo-column as "NVAL" in the SQL method: this will be important when we examine our trace file.

Interestingly, the tkprof output for our trace file includes two similar SQL statements, both of which select the sequence's next value from DUAL. As stated, Oracle's implementation of sequence fetching hasn't changed. The new PL/SQL sequence expression is executed "behind the scenes" using a query from DUAL (as are the SYSDATE and USER PL/SQL functions) and our tkprof file highlights this. The first entry is as follows.

We have 10,000 executions and fetches of this statement (sequence cache size is at the default of 20 as we can see from the current gets above. Oracle updated the SEQ$ table 500 times to assign 10,000 sequence numbers; with a series of 20 sequence values per update). This is the statement that Oracle executed to support our new sequence expression. We know this because there is no "NVAL" alias in the SQL text, unlike the second statement in the tkprof output below.

This is the statement that we explicitly coded into our example. We can see that the statistics for I/O, executes and fetches are identical to those for our 10,000 PL/SQL sequence expressions. Critically, the elapsed and CPU times are also the same (we attribute such tiny differences to "noise"). We conclude, therefore, that the new sequence syntax provides greater convenience, but nothing else.

named notation in sql

Continuing with the theme of convenience, Oracle 11g now supports named notation in SQL calls to PL/SQL functions. This is technically a SQL enhancement, but as it relates directly to PL/SQL invocation, it is included in this article. Named notation has been a PL/SQL-only feature for many years and versions. With named notation, we are able to supply parameters to functions and procedures by name as well as position, using syntax such as the following.

Named parameters do not need to be ordered according to the parameter list of the target program. Similarly, they can be mixed with positional notation if required (a good example of this is DBMS_STATS.GATHER_TABLE_STATS(USER, 'TABLE', cascade=>TRUE) which is used in many of Tom Kyte's earlier demonstrations). Named notation enables us to skip non-mandatory parameters that we do not need to change or supply, so can be very convenient when calling procedures with long lists of defaults.

As stated, we can now use named notation in SQL and we will see some examples below. We will begin by creating a function with three default parameters, as follows.

Note that using named notation for built-in functions in PL/SQL is valid, however, but whether we should use it or not is another question entirely. These functions are defined in the STANDARD package (function UPPER(ch VARCHAR2 CHARACTER SET ANY_CS) but for our purposes the parameter names are meaningless. As an aside, 11g includes a new pair of views, V$SQLFN_METADATA and V$SQLFN_ARG_METADATA, that provide information on the signatures of the built-in functions. For example, we can get the signature of the UPPER function as follows (note that we've used Tom Kyte's print_table procedure for convenience).

SQL> exec print_table('SELECT * FROM v$sqlfn_metadata WHERE name = ''UPPER''');

We can see that Oracle describes the parameters for the built-ins according to position, and not name.

Finally, named notation in SQL can overcome problems with overloaded functions, particularly if the type signatures clash (in other words, the list of parameters for different overloads have matching datatypes). It isn't necessarily good practice to have overloaded functions with the same type signatures, but nevertheless, named notation makes it possible (remember that it is possible in PL/SQL prior to 11g but we are referring to named notation in SQL).

To demonstrate this, we will create the simplest example of overloaded functions that share the same type signatures, as follows.

We can see that we have two overloads of a packaged function, but more significant is the fact that the parameters only differ by name and not data type. We will create the package body with trivial implementations for these functions, as follows.

Despite the fact that Oracle allows us to create "clashing" overloads such as these, it cannot resolve any invocations that use positional notation. Oracle cannot possibly know which of the versions we wish to call. Overloads commonly have different signatures (i.e. different parameter lists and datatypes) that enable Oracle to infer which version we require. In these cases, positional notation is usually sufficient. In 11g (or PL/SQL in earlier versions of course), this issue disappears, because we can use named notation, as follows.

As stated above, this is not necessarily good practice, but 11g makes it possible in SQL as well as in PL/SQL.

continue

Most programming languages have multiple control commands that enable us to control program-flow within a loop. PL/SQL has supported the GOTO and EXIT statements for many years, but in 11g, Oracle has added the CONTINUE statement. This statement enables us to terminate the current iteration of a loop and return to the start of the next cycle.

The easiest way to understand the CONTINUE statement is by example. We will begin by coding a simple FOR loop with five iterations. At the end of each iteration we will output a message, but on the even-numbered iterations, we will skip to the start of the next iteration.

SQL> BEGIN
2 FOR i IN 1 .. 5 LOOP
3 IF i IN (2,4) THEN
4 CONTINUE;
5 END IF;
6 DBMS_OUTPUT.PUT_LINE('Reached on line ' || TO_CHAR(i));
7 END LOOP;
8 END;
9 /

Reached on line 1
Reached on line 3
Reached on line 5
PL/SQL procedure successfully completed.

As we can see from the output, only the odd-numbered iterations reached the end of the loop. The even-numbered iterations returned to the start of the loop for the next iteration. Note that this example used an IF statement to test the iteration numbers, but the new CONTINUE statement has a WHEN condition for this purpose, as follows.

SQL> BEGIN
2 FOR i IN 1 .. 5 LOOP
3 CONTINUE WHEN i IN (2,4);
4 DBMS_OUTPUT.PUT_LINE('Reached on line ' || TO_CHAR(i));
5 END LOOP;
6 END;
7 /

Reached on line 1
Reached on line 3
Reached on line 5
PL/SQL procedure successfully completed.

The CONTINUE statement can also be used with loop labels (an existing PL/SQL feature), as follows.

Reached on line 1
Reached on line 3
Reached on line 5
PL/SQL procedure successfully completed.

We can extend this to include multiple loops and CONTINUE instructions. In the following example, we will create two simple FOR loops. We will label them "main_loop" and "inner_loop" and use two CONTINUE statements to direct to the top of each loop as required, based on our WHEN conditions.

We can see that we re-started the main loop on its even-numbered iterations and skipped the last two iterations of every inner loop we reached.

simple_integer

SIMPLE_INTEGER is a new integer type for PL/SQL. It is a subtype of PLS_INTEGER but has a NOT NULL constraint and simplified overflow checking, which means it is faster. Where raw performance is of critical importance (and the two restrictions of SIMPLE_INTEGER will not be an issue), SIMPLE_INTEGER can be used over PLS_INTEGER for a small gain. Note that the 10g types, BINARY_FLOAT and BINARY_DOUBLE, also have their SIMPLE_% equivalents. We will concentrate on SIMPLE_INTEGER for this article.

As stated, SIMPLE_INTEGER has a NOT NULL constraint. This means we must initialise all variables, otherwise Oracle will raise an exception, as follows.

SQL> DECLARE
2 s SIMPLE_INTEGER;
3 BEGIN
4 s := 0;
5 END;
6 /

s SIMPLE_INTEGER;
*
ERROR at line 2:
ORA-06550: line 2, column 6:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

The error message tells us exactly how to avoid this exception, which we will do below.

Note that we do not need to define a default value for this parameter, but we do need to supply a NOT NULL value when we invoke the procedure. In the following example, we will attempt to pass a NULL value for the parameter.

The error is not as explicit as our previous exceptions, but it is basically telling us that we cannot use a NULL default for a constrained parameter.

We stated earlier that Oracle has simplified the SIMPLE_INTEGER overflow checking to speed up arithmetic using this type. We will see how below. First, the basic integer range for SIMPLE_INTEGER is the same as for PLS_INTEGER (that is, -2,147,483,648 to 2,147,483,647 inclusive), which means that we cannot assign a value outside of this range, as follows.

DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2

This is to be expected because our initial value is outside the range of valid integers, but when we perform arithmetic operations on a very large (or very small) SIMPLE_INTEGER, we see different behaviour to a PLS_INTEGER. In the following example, we will start with an integer value at the upper limit of the integer range and increment it by one.

This behaviour is very different from other numeric and integer types. When we reach the upper limit of a SIMPLE_INTEGER, Oracle cycles and starts at the lower end of the range again. The opposite is true if we subtract from the lower limit of the integer, as in the following example.

This time Oracle has cycled backwards to the upper limit of the integer range. Note that in both scenarios, using a PLS_INTEGER would result in an overflow exception.

Of course, the intended result of these differences between SIMPLE_INTEGER and PLS_INTEGER is increased performance. We can run some basic performance comparisons to test out this claim. In the following example, we will run a "wall-clock" test using a simple timer package. For each execution of the test, we will loop a given number of times and output the time taken. The test harness is as follows.

For one million iterations, the SIMPLE_INTEGER appears faster, but both examples are so quick that we cannot discount interference such as instrumentation (i.e. the timer package). We will repeat the test using an order of magnitude more loop iterations, as follows.

Our test still completes in less than half a second; this time with the results matching. At this point, most developers will realise that the SIMPLE_INTEGER type is not going to make much difference to the performance of their programs, but we will continue our test using another order of magnitude more iterations below.

We now see what can be considered a difference in the performance of the types, but it is very marginal given the number of iterations we used. Finally, we will test with one thousand million iterations (a billion to some), as follows.

At this intensity of use, SIMPLE_INTEGER is faster than PLS_INTEGER, but we would need to have a very specific type of application to make use of it (and whether PL/SQL is the right language for such applications is another question). Note, however, that using SIMPLE_INTEGER for more complicated whole-number arithmetic or calculations than those above will yield better performance gains (in other words, the more times a PLS_INTEGER can be replaced with a SIMPLE_INTEGER, the greater the benefit). For raw speed, therefore, SIMPLE_INTEGER is the quickest integer type available, but it would perhaps be better combined with native compilation, which we will move onto below.

native compilation

Native compilation has been available since Oracle 9i. It enables us to compile our PL/SQL programs directly as C, rather than as interpreted "p-code". It is typically faster for computationally intensive programs (rather than those with a large proportion of work done by SQL).

Oracle 11g simplifies the way in which we can make use of native compilation (many developers will be aware of the setup difficulties in earlier versions of the database). It does this by shipping with a built-in C-compiler (many servers do not have C-compilers due to either cost or security) and reducing the setup needed such that it works "out of the box". We will see examples of this below.

To test native compilation, we will continue with the SIMPLE_INTEGER performance tests we used in the previous section of this article. We will first create a package to store these tests, as follows.

Note that we have compiled this package in the default "interpreted" mode (as defined by the plsql_code_type database parameter). To have all packages natively compiled by default, this can be set to "native" at instance, session or individual package level. In this example, we will be controlling the compilation mode at package level. We will now create our package body, as follows.

We now have a package to compare the performance of SIMPLE_INTEGER and PLS_INTEGER using interpreted and native compilation. We can check the compilation mode of our test package in the USER_PLSQL_OBJECT_SETTINGS view as follows.

Note that we can check several other settings related to program settings with this view, such as its optimisation level, conditional compilation flags etc. In this case, we have simply confirmed that our test package is compiled in interpreted mode. We will execute it in this mode to give us a baseline, as follows.

This gives us a baseline performance indicator of approximately 2-3 seconds, depending on the datatype. We will now compare this with native compilation. First we will recompile our package natively, as follows.

SQL> ALTER PACKAGE ncomp_test COMPILE PLSQL_CODE_TYPE = NATIVE;

Package altered.

Note how simple this is. No setup has been required on this 11g database to enable native compilation. This will encourage more developers to make use of this feature (where it is applicable). We will confirm that this worked by repeating our query against USER_PLSQL_OBJECT_SETTINGS.

This is significantly faster than in interpreted mode. As stated earlier, computationally-intensive programs generally benefit more from native compilation than SQL-intensive packages and we can see an example of how much the former can improve.

For our native compilation test, we recompiled an existing interpreted program unit as native, but we can control this at a higher level (either session or instance). In the following example, we will change our session to compile all new packages natively.

This setting can also affect recompilations (although this does not apply to Oracle's automatic recompilation of invalid objects). For example, if we recompile a package, procedure or function, it will use the session or system setting for plsql_code_type unless we use either of the following syntax extensions:

ALTER [PACKAGE|PROCEDURE|FUNCTION] COMPILE [BODY] REUSE SETTINGS (to retain the mode it was last compiled in); or

Our originally-interpreted procedure is now natively compiled. We stated above that we have two syntax extensions to ensure that this procedure retains its current settings for future recompilations. We will demonstrate one of these below. We will restore our session plsql_code_type parameter to interpreted, but recompile our (now) natively-compiled dummy procedure with the REUSE SETTINGS directive, as follows.

We can see that this time the session-level setting was ignored in favour of the procedure's existing compilation mode.

To complete this section on native compilation, note that we can mix compilation modes for packages and their package bodies. In the following example, we will recompile the NCOMP_TEST package body that we used earlier. Remember that we left this package in a natively-compiled state. This time, we will recompile the package body only, using interpreted compilation, as follows.

We can see that the two components of NCOMP_TEST now have different compilation modes.

These examples demonstrate quite clearly that native compilation has become simple to use. No setup was required before running any of the examples on a vanilla 11g database.

further reading

One subject that has been excluded from this article is triggers: Oracle 11g has introduced compound triggers and trigger execution ordering but these are large subjects. For information on trigger enhancements, read the Using Triggers chapter in the PL/SQL Language Reference. Further detail on all of the subjects of this article can be found in the PL/SQL Language Reference. Chapter 6 has more information on sequence expressions, while the CONTINUE statement is described in Chapter 4. Named notation is described in more detail in Chapter 8 and the SIMPLE_INTEGER datatype is documented in Chapter 3. Finally, for more information on native compilation, read Chapter 12 on PL/SQL performance tuning.

source code

The source code for the examples in this article can be downloaded from here.