pl/sql enhancements in oracle 11g

Advertisements

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.

SQL> DECLARE
  2     n PLS_INTEGER;
  3  BEGIN
  4     n := s.NEXTVAL;
  5     DBMS_OUTPUT.PUT_LINE('Nextval=' || TO_CHAR(n));
  6  END;
  7  /

Nextval=1

PL/SQL procedure successfully completed.

We can also use the sequence expression to set a default for our variable, as the following example demonstrates.

SQL> DECLARE
  2     n PLS_INTEGER := s.NEXTVAL;
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE('Nextval=' || TO_CHAR(n));
  5  END;
  6  /

Nextval=2

PL/SQL procedure successfully completed.

The sequence pseudo-columns can be referenced directly, which means that we can bypass the intermediate variable assignment if necessary, as follows.

SQL> BEGIN
  2     DBMS_OUTPUT.PUT_LINE('Nextval=' || TO_CHAR(s.NEXTVAL));
  3  END;
  4  /

Nextval=3

PL/SQL procedure successfully completed.

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.

SQL> BEGIN
  2     IF s.NEXTVAL > 3 THEN
  3        DBMS_OUTPUT.PUT_LINE('Currval=' || TO_CHAR(s.CURRVAL));
  4     END IF;
  5  END;
  6  /

Currval=4

PL/SQL procedure successfully completed.

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.

SQL> ALTER SESSION SET SQL_TRACE = TRUE;

Session altered.


SQL> DECLARE
  2     n PLS_INTEGER;
  3  BEGIN
  4     FOR i IN 1 .. 10000 LOOP
  5        n := s.NEXTVAL;
  6     END LOOP;
  7     FOR i IN 1 .. 10000 LOOP
  8        SELECT s.NEXTVAL AS nval INTO n FROM dual;
  9     END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.


SQL> ALTER SESSION SET SQL_TRACE = FALSE;

Session altered.

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.

********************************************************************************

SQL ID : 0k4rn80j4ya0n
Select S.NEXTVAL
from
 dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.17       0.19          0          0          0           0
Fetch    10000      0.39       0.42          0          0        500       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.56       0.62          0          0        500       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SEQUENCE  S (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

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.

********************************************************************************

SQL ID : 9kwysc3kyrusj
SELECT S.NEXTVAL AS NVAL
FROM
 DUAL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute  10000      0.20       0.19          0          0          0           0
Fetch    10000      0.42       0.42          0          0        500       10000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    20001      0.62       0.61          0          0        500       10000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SEQUENCE  S (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

********************************************************************************

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.

BEGIN
   package_name.procedure_name( p_parameter1 => 'some value',
                                p_parameter2 => 'another_value' );
END;

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.

SQL> CREATE FUNCTION function_name(
  2                  p_arg1 IN INTEGER DEFAULT 0,
  3                  p_arg2 IN INTEGER DEFAULT 0,
  4                  p_arg3 IN INTEGER DEFAULT 0 ) RETURN INTEGER IS
  5  BEGIN
  6     RETURN p_arg1 + p_arg2 + p_arg3;
  7  END function_name;
  8  /

Function created.

We will test our function with the standard positional notation that is our only option in versions prior to 11g.

SQL> SELECT function_name(10,20,30)
  2  FROM   dual;

FUNCTION_NAME(10,20,30)
-----------------------
                     60

1 row selected.

We will repeat the function call, but using named notation, as follows.

SQL> SELECT function_name(p_arg1 => 10,
  2                       p_arg2 => 20,
  3                       p_arg3 => 30)
  4  FROM   dual;

FUNCTION_NAME(P_ARG1=>10,P_ARG2=>20,P_ARG3=>30)
-----------------------------------------------
                                             60

1 row selected.

Named notation is useful for skipping default parameters that we do not need. In the following example, we will supply the first and third parameters only.

SQL> SELECT function_name(p_arg1 => 10,
  2                       p_arg3 => 30)
  3  FROM   dual;

FUNCTION_NAME(P_ARG1=>10,P_ARG3=>30)
------------------------------------
                                  40

1 row selected.

The new support for named notation in SQL also includes mixed notation, as follows.

SQL> SELECT function_name(10, p_arg3=>30)
  2  FROM   dual;

FUNCTION_NAME(10,P_ARG3=>30)
----------------------------
                          40

1 row selected.

There is no distinction between calling our user-defined functions and the DBMS_% built-ins. We can call Oracle's packaged functions with named notation, as follows.

SQL> SELECT DBMS_UTILITY.GET_HASH_VALUE(name      => 'ABCDE',
  2                                     base      => 16,
  3                                     hash_size => 1024)
  4  FROM   dual;

DBMS_UTILITY.GET_HASH_VALUE(NAME=>'ABCDE',BASE=>16,HASH_SIZE=>1024)
-------------------------------------------------------------------
                                                                569

1 row selected.

However, we cannot call the built-in SQL functions with named notation in SQL, as the following demonstrates.

SQL> SELECT UPPER(ch => 'hello world')
  2  FROM   dual;

SELECT UPPER(ch => 'hello world')
                *
ERROR at line 1:
ORA-00907: missing right parenthesis

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

FUNC_ID                       : 33
NAME                          : UPPER
MINARGS                       : 1
MAXARGS                       : 1
DATATYPE                      : STRING
VERSION                       : V6 Oracle
ANALYTIC                      : NO
AGGREGATE                     : NO
DISP_TYPE                     : NORMAL
USAGE                         :
DESCR                         :
-----------------

PL/SQL procedure successfully completed.

The UPPER function accept a single parameter, which is described in the V$SQLFN_ARG_METADATA view, as follows.

SQL> exec print_table('SELECT * FROM v$sqlfn_arg_metadata WHERE func_id = 33');

FUNC_ID                       : 33
ARGNUM                        : 1
DATATYPE                      : STRING
DESCR                         :
-----------------

PL/SQL procedure successfully completed.

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.

SQL> CREATE PACKAGE package_name AS
  2
  3     FUNCTION function_name(
  4              p_arg IN INTEGER
  5              ) RETURN INTEGER;
  6
  7     FUNCTION function_name(
  8              p_parameter IN INTEGER
  9              ) RETURN INTEGER;
 10
 11  END package_name;
 12  /

Package created.

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.

SQL> CREATE PACKAGE BODY package_name AS
  2
  3     FUNCTION function_name(
  4              p_arg IN INTEGER
  5              ) RETURN INTEGER IS
  6     BEGIN
  7        RETURN p_arg;
  8     END function_name;
  9
 10     FUNCTION function_name(
 11              p_parameter IN INTEGER
 12              ) RETURN INTEGER IS
 13     BEGIN
 14        RETURN -p_parameter;
 15     END function_name;
 16
 17  END package_name;
 18  /

Package body created.

In the following example, we will test a call to one of the overloads using positional notation.

SQL> SELECT package_name.function_name(10)
  2  FROM   dual;

SELECT package_name.function_name(10)
       *
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'FUNCTION_NAME' match this call

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.

SQL> SELECT package_name.function_name(p_arg => 10)
  2  FROM   dual;

PACKAGE_NAME.FUNCTION_NAME(P_ARG=>10)
-------------------------------------
                                   10

1 row selected.

By supplying the name of the parameter, Oracle is able to call the correct overload of our function. For completeness, we will call the alternative version of this overload, as follows.

SQL> SELECT package_name.function_name(p_parameter => 10)
  2  FROM   dual;

PACKAGE_NAME.FUNCTION_NAME(P_PARAMETER=>10)
-------------------------------------------
                                        -10

1 row selected.

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.

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

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.

SQL> BEGIN
  2     <<main_loop>>
  3     FOR i IN 1 .. 5 LOOP
  4
  5        DBMS_OUTPUT.PUT_LINE('Starting main_loop ' || i || '...');
  6
  7        <<inner_loop>>
  8        FOR ii IN 1 .. 5 LOOP
  9
 10           CONTINUE main_loop WHEN i IN (2,4);
 11
 12           CONTINUE inner_loop WHEN ii NOT IN (1,2,3);
 13
 14           DBMS_OUTPUT.PUT_LINE('   Inner loop ' || i || '.' || ii);
 15
 16        END LOOP;
 17
 18     END LOOP;
 19  END;
 20  /

Starting main_loop 1...
   Inner loop 1.1
   Inner loop 1.2
   Inner loop 1.3
Starting main_loop 2...
Starting main_loop 3...
   Inner loop 3.1
   Inner loop 3.2
   Inner loop 3.3
Starting main_loop 4...
Starting main_loop 5...
   Inner loop 5.1
   Inner loop 5.2
   Inner loop 5.3

PL/SQL procedure successfully completed.

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.

SQL> DECLARE
  2     s SIMPLE_INTEGER := 0;
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE('Simple integer is ' || s);
  5  END;
  6  /

Simple integer is 0

PL/SQL procedure successfully completed.

This constraint also affects how we handle parameters. We will create a small procedure with a single parameter of SIMPLE_INTEGER, as follows.

SQL> CREATE PROCEDURE simple_integer_parameter(
  2                   p_simple_integer IN SIMPLE_INTEGER
  3                   ) IS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(p_simple_integer);
  6  END simple_integer_parameter;
  7  /

Procedure created.

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.

SQL> exec simple_integer_parameter(NULL);

BEGIN simple_integer_parameter(NULL); END;

                               *
ERROR at line 1:
ORA-06550: line 1, column 32:
PLS-00567: cannot pass NULL to a NOT NULL constrained formal parameter
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

As expected, Oracle raises an exception. Similarly, we cannot define a NULL default value for this parameter, as the following demonstrates.

SQL> CREATE OR REPLACE PROCEDURE simple_integer_parameter(
  2                              p_simple_integer IN SIMPLE_INTEGER DEFAULT NULL
  3                              ) IS
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE(p_simple_integer);
  6  END simple_integer_parameter;
  7  /

Warning: Procedure created with compilation errors.

We have compilation errors. We will lookup the errors for our procedure, as follows.

SQL> show errors

Errors for PROCEDURE SIMPLE_INTEGER_PARAMETER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
2/72     PLS-00382: expression is of wrong type

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.

SQL> DECLARE
  2     s SIMPLE_INTEGER := 2147483648;
  3  BEGIN
  4     DBMS_OUTPUT.PUT_LINE(TO_CHAR(s, 'S9999999999'));
  5  END;
  6  /

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.

SQL> DECLARE
  2     s SIMPLE_INTEGER := 2147483647;
  3  BEGIN
  4     s := s + 1;
  5     DBMS_OUTPUT.PUT_LINE(TO_CHAR(s, 'S9999999999'));
  6  END;
  7  /

-2147483648

PL/SQL procedure successfully completed.

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.

SQL> DECLARE
  2     s SIMPLE_INTEGER := -2147483648;
  3  BEGIN
  4     s := s - 1;
  5     DBMS_OUTPUT.PUT_LINE(TO_CHAR(s, 'S9999999999'));
  6  END;
  7  /

+2147483647

PL/SQL procedure successfully completed.

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.

SQL> DECLARE
  2
  3     s SIMPLE_INTEGER := 0;
  4     p PLS_INTEGER    := 0;
  5     n PLS_INTEGER    := &power;
  6
  7  BEGIN
  8
  9     timer.snap();
 10     FOR i IN 1 .. 10 ** n LOOP
 11        s := s + 1;
 12     END LOOP;
 13     timer.show('SIMPLE_INTEGER');
 14
 15     timer.snap();
 16     FOR i IN 1 .. 10 ** n LOOP
 17        p := p + 1;
 18     END LOOP;
 19     timer.show('PLS_INTEGER   ');
 20
 21  END;
 22  /

Note that we have used a substitution variable to define the number of loop iterations (in powers of ten). We will begin with one million iterations, as follows.

Enter value for power: 6
[SIMPLE_INTEGER] 0.04 seconds
[PLS_INTEGER   ] 0.06 seconds

PL/SQL procedure successfully completed.

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.

SQL> /

Enter value for power: 7
[SIMPLE_INTEGER] 0.33 seconds
[PLS_INTEGER   ] 0.33 seconds

PL/SQL procedure successfully completed.

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.

SQL> /

Enter value for power: 8
[SIMPLE_INTEGER] 2.75 seconds
[PLS_INTEGER   ] 3.11 seconds

PL/SQL procedure successfully completed.

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.

SQL> /

Enter value for power: 9
[SIMPLE_INTEGER] 27.12 seconds
[PLS_INTEGER   ] 31.11 seconds

PL/SQL procedure successfully completed.

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.

SQL> CREATE PACKAGE ncomp_test AS
  2
  3     PROCEDURE simple(p_comp IN VARCHAR2);
  4
  5     PROCEDURE pls(p_comp IN VARCHAR2);
  6
  7  END ncomp_test;
  8  /

Package created.

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.

SQL> CREATE PACKAGE BODY ncomp_test AS
  2
  3     PROCEDURE simple(p_comp IN VARCHAR2) IS
  4        s SIMPLE_INTEGER := 0;
  5     BEGIN
  6        timer.snap();
  7        FOR i IN 1 .. 10 ** 8 LOOP
  8           s := s + 1;
  9        END LOOP;
 10        timer.show(p_comp || ' SIMPLE_INTEGER');
 11     END simple;
 12
 13     PROCEDURE pls(p_comp IN VARCHAR2) IS
 14        p PLS_INTEGER := 0;
 15     BEGIN
 16        timer.snap();
 17        FOR i IN 1 .. 10 ** 8 LOOP
 18           p := p + 1;
 19        END LOOP;
 20        timer.show(p_comp || ' PLS_INTEGER   ');
 21     END pls;
 22
 23  END ncomp_test;
 24  /

Package body created.

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.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'NCOMP_TEST';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
NCOMP_TEST                     PACKAGE      INTERPRETED
NCOMP_TEST                     PACKAGE BODY INTERPRETED

2 rows selected.

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.

SQL> BEGIN
  2     ncomp_test.simple('INTERPRETED');
  3     ncomp_test.pls('INTERPRETED');
  4  END;
  5  /

[INTERPRETED SIMPLE_INTEGER] 2.61 seconds
[INTERPRETED PLS_INTEGER   ] 3.05 seconds

PL/SQL procedure successfully completed.

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.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'NCOMP_TEST';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
NCOMP_TEST                     PACKAGE      NATIVE
NCOMP_TEST                     PACKAGE BODY NATIVE

2 rows selected.

We will now repeat our test in native mode, as follows.

SQL> BEGIN
  2     ncomp_test.simple('NATIVE');
  3     ncomp_test.pls('NATIVE');
  4  END;
  5  /

[NATIVE SIMPLE_INTEGER] 0.37 seconds
[NATIVE PLS_INTEGER   ] 0.97 seconds

PL/SQL procedure successfully completed.

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.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Session altered.

We will now create a new procedure in this session, as follows.

SQL> CREATE PROCEDURE native_by_default AS
  2  BEGIN
  3     NULL;
  4  END native_by_default;
  5  /

Procedure created.

We will confirm that our session settings took effect, as follows.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'NATIVE_BY_DEFAULT';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
NATIVE_BY_DEFAULT              PROCEDURE    NATIVE

1 row selected.

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
    ALTER [PACKAGE|PROCEDURE|FUNCTION] COMPILE [BODY] PLSQL_CODE_TYPE = [NATIVE|INTERPRETED] (to explicitly set the compilation type).

We saw the second of these in an earlier example. We will demonstrate the first below. We will begin by creating a dummy procedure in interpreted mode, as follows.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;

Session altered.


SQL> CREATE PROCEDURE interpreted_procedure AS
  2  BEGIN
  3     NULL;
  4  END;
  5  /

Procedure created.

We will confirm that Oracle created our procedure in interpreted mode, as follows.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'INTERPRETED_PROCEDURE';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
INTERPRETED_PROCEDURE          PROCEDURE    INTERPRETED

1 row selected.

We will now alter our session state to compile all programs natively.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = NATIVE;

Session altered.

We will recompile our dummy procedure without any syntax extensions and check its compilation settings, as follows.

SQL> ALTER PROCEDURE interpreted_procedure COMPILE;

Procedure altered.


SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'INTERPRETED_PROCEDURE';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
INTERPRETED_PROCEDURE          PROCEDURE    NATIVE

1 row selected.

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.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE = INTERPRETED;

Session altered.


SQL> ALTER PROCEDURE interpreted_procedure COMPILE REUSE SETTINGS;

Procedure altered.

Finally, we will check the object settings once more below.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'INTERPRETED_PROCEDURE';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
INTERPRETED_PROCEDURE          PROCEDURE    NATIVE

1 row selected.

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.

SQL> ALTER PACKAGE ncomp_test COMPILE BODY PLSQL_CODE_TYPE = INTERPRETED;

Package body altered.

The package settings for NCOMP_TEST are as follows.

SQL> SELECT name, type, plsql_code_type
  2  FROM   user_plsql_object_settings
  3  WHERE  name = 'NCOMP_TEST';

NAME                           TYPE         PLSQL_CODE_TYPE
------------------------------ ------------ --------------------
NCOMP_TEST                     PACKAGE      NATIVE
NCOMP_TEST                     PACKAGE BODY INTERPRETED

2 rows selected.

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.