Oracle Database 11g:
The Top Features for DBAs and Developers

by Arup Nanda Oracle ACE Director

PL/SQL Performance

Explore use cases in which in-lining of code, true native compilation, and use of simple integers can improve code performance.

See Series TOC

Oracle Database 11g introduces a number of nifty new features to improve the performance of PL/SQL code, but the most dramatic ones are native compilation and intra-unit inlining.

Native compilation is not a new feature in itself, but the lack of any preconditions for using it—such as a C compiler install—is indeed new. (Oracle calls this improved feature "Real Native Compilation".) In addition, a new datatype, simple_integer, makes code perform better under native compilation. Intra-unit inlining is an optimization technique applied to the PL/SQL code during compilation time to produce efficient code.

In this installment you will learn appropriate use cases for these new features. You will also examine their performance under different scenarios: when compiled natively, when using simple integers, when inlining the program, and in various combinations.

Real Native Compilation

You may recall the native compilation introduced in Oracle9i Database Release 2; it made the execution of PL/SQL programs much faster compared to the interpreted form. Despite this benefit, adoption was slow because many sysadmins are reluctant to install the required C compiler on a production database server. Furthermore, such compilers require you to define a parameter plsql_native_library_dir where the intermediate O/S files are created.

In Oracle Database 11g, you can native-compile without a C compiler in the server or setting the parameter. All you have to do is set a session parameter before creating or recompiling stored code:

alter session set plsql_code_type = native;
...  
                              
compile the code here ...

Native compilation takes longer than interpreted compilation, but as the process is now much faster in Oracle Database 11g, the effect may not be as noticeable. As a good practice, you may want to compile-interpreted during the regular development cycle and compile-native when development is complete.

As a part of an 11g migration, I performed an experiment with actual code from a mission-critical application; a fairly large package with 5,827 lines. I compiled it natively on the existing 10g database as well as a new 11g one, and then repeated the same with interpreted compilation. Each of these compilations was done with plsql_optimize_level set to 2. I measured the compilation time in each case, shown below (in seconds).

	10g	11g
Interpreted	1.66	1.64
Native	4.66	2.81

The results are self-explanatory. When compiled interpreted, the completion time is pretty much the same. However, when compiled natively, the compilation time under 11g is about 60% that of 10g, a significant improvement. So, while native compilation under 11g added to the compilation time, it's still much faster than native compilation under 10g.

To find out which objects have been compiled using NATIVE, you can check the view USER_PLSQL_OBJECT_SETTINGS:

SQL> select name, PLSQL_code_type
  2> from user_plsql_object_settings;
 
NAME                 PLSQL_CODE_TYPE
--------------------           ---------------
DO_CALC              NATIVE
PERFECT_TRIANGLES    NATIVE
PRIME_NUMBERS        NATIVE
PRIME_NUMBERS        NATIVE
SOME_MATH            INTERPRETED
TR_BOOKINGS_TRACK    INTERPRETED
TR_SALGRADE_COMP     INTERPRETED
UPD_INT              NATIVE

There is a similar view, DBA_PLSQL_OBJECT_SETTINGS, for all objects.

New Datatype: Simple Integer

The power of native compilation is even more pronounced when you use a new datatype called simple_integer. Technically speaking it's not really a datatype, but rather a subtype of the datatype pls_integer. The simple integer is defined to take advantage of hardware arithmetic rather than software arithmetic. When simple integer is combined with real native compilation, the performance will be significantly better. In the forthcoming experiment, you will see why.

Since simple_integer is a subtype of pls_integer, it inherits the property that it's a 32-bit signed integer and can have integer values between negative 2,147,483,648 and 2,147,483,647. However, it differs from pls_integer as follows: It does not allow nulls, but does allow overflow—when the value assigned is more than the maximum, it wraps around instead of returning an error.

This datatype can be syntactically used anywhere the pls_integer can be used but note the differences carefully; the additional properties of simple_integer may make it unsuitable in some situations.

Let's examine some potential issues you should be aware of before substituting pls_integer with simple_integer:

This variable can't be null so if you don't write the variable declarations as:
```
num1    simple_integer:= 1;
```
but rather as:
```
num1    simple_integer;
```

then you will get a compilation error:


PLS-00218: a variable declared NOT NULL must have an initialization assignment

If you set the variable to NULL inside the program, such as:
```
num1 := NULL;
```

you will get a compilation error:


PLS-00382: expression is of wrong type

Be aware of these error messages, which may not seem to convey the exact nature of the error. If your program expects to set a variable to null, you can't define that variable as simple_integer.
The other important point about simple_integers involves wrapping around the values beyond the maximum and minimum. Remember, the maximum positive value of a pls_integer is 2147483647. What will happen if you try to store a higher value? Let's see in a code sample:
```
declare
v1 pls_integer := 2147483647;
begin
v1 := v1 + 1;
dbms_output.put_line('v1='||v1);
end;
/
```

It will throw an error:


declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4

The error is obvious and quite appropriate; you have tried to exceed the maximum allowed value of the datatype. If you had used simple_integer instead of pls_integer:
```
declare
v1 simple_integer := 2147483647;
begin
v1 := v1 + 1;
dbms_output.put_line('v1='||v1);
end;
/
```
The output would have been:
```
v1=-2147483648
```
Note the value (-2147483648), which is the minimum value of the simple_integer. When you incremented the maximum value (2147483647), the value simply wrapped around to the beginning—a trait of the simple_integers. Beware of this behavior.

Use of Real Native Compilation with Simple Integers

As you can see, simple_integers can't be used just anywhere; you have to be careful in deciding the boundary conditions (especially the potential wrapping of values) before implementing them. Furthermore, simple_integers are designed for native compilation. In interpreted compilation, they may not offer a large performance boost (but it doesn't hurt either, as you'll see later). In real native mode, the performance benefits of simple_integers are far more pronounced.

Most business applications in PL/SQL are actually SQL-heavy so these applications will not see a significant performance boost from native compilation. In a "previous life" I developed a database capacity planning tool using PL/SQL involving many numerical and statistical calculations over several thousand lines of code. That got a tremendous performance boost when compiled natively. Simple integer was not available that time, but if it were, it would have added even more performance benefits.

Intra-unit Inlining

Intr-unit inlining involves replacing a call to a subroutine with copy of the code of that subroutine. In general, the modified code runs faster. In Oracle Database 11g, the PL/SQL compiler has the ability to identify which calls to which subroutine should be copied (or inlined) and makes the changes, resulting in better performance.

This is best explained with an example. The code below updates a table called BALANCES with the calculated interest from the account balance. The code loops through all the records of the table, calculates the interest, and updates the table in the balance column.

create or replace procedure upd_int is
/* original version */
    l_rate_type     balances.rate_type%type;
    l_bal           balances.balance%type;
    l_accno         balances.accno%type;
    l_int_rate      number;
    procedure calc_int (
        p_bal in out balances.balance%type,
        p_rate  in number
    ) is
    begin
        if (p_rate >= 0) then
            p_bal := p_bal * (1+(p_rate/12/100));
        end if;
    end;
begin
    for ctr in 1..10000 loop
        l_accno := ctr;
        select balance, rate_type
        into l_bal, l_rate_type
        from balances
        where accno = l_accno;
        select decode(l_rate_type,
            'C', 1, 'S', 3, 'M', 5, 0)
        into l_int_rate
        from dual;
        for mth in 1..12 loop
            calc_int (l_bal, l_int_rate);
            update balances
            set balance = l_bal
            where accno = l_accno;
        end loop;
    end loop;
end;
/

As the actual calculation of the interest is same for all record types, I have placed that logic in a separate procedure calc_int() inside the main procedure. This improves readability and maintainability of the code, but unfortunately it's also inefficient.

However, if I had written the code shown in calc_int() instead of calling the calc_int(), I would have created a faster program, as shown below:

create or replace procedure upd_int is
/* revised version */
    l_rate_type     balances.rate_type%type;
    l_bal           balances.balance%type;
    l_accno         balances.accno%type;
    l_int_rate      number;
begin
    for ctr in 1..10000 loop
        l_accno := ctr;
        select balance, rate_type
        into l_bal, l_rate_type
        from balances
        where accno = l_accno;
        select decode(l_rate_type,
            'C', 1, 'S', 3, 'M', 5, 0)
        into l_int_rate
        from dual;
        for mth in 1..12 loop
            -- this is the int calc routine
            if (l_int_rate >= 0) then
                l_bal := l_bal * (1+(l_int_rate/12/100));
            end if;
            update balances
            set balance = l_bal
            where accno = l_accno;
        end loop;
    end loop;
end;
/

This revised code differs from the original only in that the code for interest calculation is now within the loop instead of inside a procedure calc_int().

Note that the new version may be faster but it's not a good example of coding practices. The piece of code doing the interest calculation is executed once for each iteration of the loop for the months and then for each of the account numbers. As that portion of the code is repeated, it makes more sense to place it separately, as shown in the previous version of the upd_int code, in a procedure (calc_int). That approach makes the code modular, easy to maintain, and eminently readable—but also less efficient.

So how can you accomplish the conflicting objectives of making the code modular yet faster? Well, how about writing the code in a modular manner (like the first version of upd_int) and then letting the PL/SQL compiler "optimize" it to look like the second version of the code?

In Oracle Database 11g you can do that. All you have to do is recompile the procedure with a higher level of PL/SQL Optimization. You can achieve that two ways:

Set a session level parameter and recompile the procedure:


SQL> alter session set plsql_optimize_level = 3;
 
Session altered.

The above setting instructs the PL/SQL compiler to rewrite the code to inline the code.

Compile the procedure with the plsql settings directly.


SQL> alter procedure upd_int
  2  compile
  3  plsql_optimize_level = 3
  4  reuse settings;
 
Procedure altered.

Any other procedure compiled in the same session will not be affected. This is a better way to handle inlining when you have a lot of procedures to compile in the same session. You can also use a pragma, which is a compiler directive.


create or replace procedure upd_int is
    l_rate_type     varchar2(1);
...
...
begin
     
                              
pragma inline (calc_int, 'YES');
    for ctr in 1..10000 loop
...
...
end;

I added the line pragma inline (calc_int, 'YES'); to encourage the compiler to inline that procedure. Likewise, you can place a "NO" here to tell the compiler not to inline this procedure even if the plsql_optimizer_level is set to 3.

This inlining makes the code execute faster. The exact degree of benefit will of course vary by how much inlining was possibly done by the compiler. At the end of this installment you will see an example code where inlining was used and you can see the performance boost as a result of that.

Compilation Time

Of course, this optimization process makes the compiler work harder. But how much harder?

To answer that question, I took the real life application code provided earlier and compiled it under different combinations of inlined/not-inlined and interpreted/native. Here are the compile times:

	Inlined	Not inlined
Interpreted	1.70	1.64
Native	3.15	2.81

The results speak for themselves. When you compile with the inline option, the compilation time goes up only a miniscule amount (about 4%) when interpreted. When native-compiled, it goes up as well, but by a larger value—about 12%. Thus, you may want to compile your apps using inline/interpreted options during development cycles and then native-compile at the final stage. The use of inlining does not seem to add significant time to the compilation process so development cycle time may not be affected much.

Now comes an important question: Since you don't change code, how do you confirm that the code was inlined? You can do that by setting a session variable:

alter session set plsql_warnings = 'enable:all';

Now, after you recreate the procedure:


SQL> @upd_int
 
SP2-0804: Procedure created with compilation warnings

Note the warning issued now. To see the warning, use the following command:


SQL> show error
Errors for PROCEDURE UPD_INT:
 
LINE/COL ERROR
--------     -----------------------------------------------------------------
7/5      PLW-06006: uncalled procedure "CALC_INT" is removed.
28/13    PLW-06005: inlining of call of procedure 'CALC_INT' was done

Note the last line, where it is confirmed that the procedure calc_int was indeed inlined.

If you want to find out which objects have been compiled with what level, you can query the view USER_PLSQL_OBJECT_SETTINGS:

sql> select name, plsql_optimize_level
  2> from user_plsql_object_settings;
 
NAME                 PLSQL_OPTIMIZE_LEVEL
--------------------                     --------------------
DO_CALC                                 2
PERFECT_TRIANGLES                       2
TR_BOOKINGS_TRACK                       2
TR_SALGRADE_COMP                        2
UPD_INT                                 3
... and so on ...

There is a similar view for all objects: DBA_PLSQL_OBJECT_SETTINGS.

Remember, this is an intra-unit inlining, which means only procedures that are inside a unit are inlined. Subroutines outside a unit are not inlined.

Running the Experiment

Now it's time to examine these benefits via a repeatable experiment. Here you will create a base version of a package and then modify the variable datatypes and compilation directives as per the concepts you learned previously.

First, create an implementation of the Euclidean algorithm to find the greatest common divisor of two numbers. Here is the logic of the function in the Wiki page:

function gcd(a, b)
     if a = 0 return b
     while b ≠ 0
         if a > b
             a := a - b
         else
             b := b - a
     return a

We will examine the CPU times of the execution of this package under the various combinations of these modifiers and record the elapsed times. So next, create a table to store CPU times:


create table times(
  native        char(1) check (native    in ('Y', 'N')) enable,
  simple        char(1) check (simple    in ('Y', 'N')) enable,
  inlining      char(1) check (inlining  in ('Y', 'N')) enable,
  centiseconds  number not null,
  constraint times_pk primary key (simple, inlining, native))
/

You have three columns—native, simple, and inlining—for specifying native compilation, simple integers, and inlined code, respectively. A "Y" in the column means the code was compiled for that. So, a record such as this:


NATIVE SIMPLE INLINING CENTISECONDS
------    ------    --------           ------------
Y      N      N                 100

indicates that the program was compiled natively but simple integer was not used, inlining didn't take place, and that this combination took 100 centi-seconds of CPU time.

To use only one copy of the package, use conditional compilation (introduced in Oracle Database 10g Release 2) to modify the package. Here is how you create the package:

-- suppress these expected warnings:
--   inlining of call of procedure 'gcd' was done
--   uncalled procedure "gcd" is removed.
--   unreachable code
--   keyword "native" used as a defined name
alter session set plsql_warnings = 'enable:all, disable:06002, disable:06005, disable:06006, disable:06010'
/
alter session set plsql_ccflags = 'simple:false'
/
create package gcd_test is
  procedure time_it;
end gcd_test;
/
create package body gcd_test is
  $if $$simple $then
    subtype my_integer is simple_integer;
    simple constant times.simple%type := 'y';
  $else
    subtype my_integer is pls_integer not null;
    simple constant times.simple%type := 'n';
  $end

  function gcd(p1 in my_integer, p2 in my_integer) return my_integer is
    v1 my_integer := p1; v2 my_integer := p2;
  begin
    while v2 > 0 loop
      if v1 > v2 then
        v1 := v1 - v2;
      else
        v2 := v2 - v1;
      end if;
    end loop;
    return v1;
  end gcd;

  function exercise_gcd return number is
    -- expected value depends on no_of_iterations.
    expected_checksum my_integer := 74069926; -- 2475190;
    no_of_iterations constant my_integer := 5000; -- 1000;
    checksum my_integer := 0; 
    v my_integer := 0;
    t0 number; t1 number;
  begin
    for warmup in 1..2 loop
      checksum := 0;
      t0 := dbms_utility.get_cpu_time();

      for j in 1..no_of_iterations loop
        v := gcd(j, j);
        if v <> j then
          raise_application_error(-20000, 'logic error: gcd(j, j) <> j');
        end if;
        checksum := checksum + v;

        for k in (j + 1)..no_of_iterations loop
          v := gcd(j, k);
          if gcd(k, j) <> v then
            raise_application_error(-20000, 'logic error: gcd(j, k) <> gcd(k, j)');
          end if;
          checksum := checksum + v;
        end loop;
      end loop;

      if checksum <> expected_checksum then
        raise_application_error(-20000, 'checksum <> expected_checksum: '||checksum);
      end if;

      t1 := dbms_utility.get_cpu_time();
    end loop;
   return t1 - t0;
  end exercise_gcd;

  procedure time_it is
    inlining times.inlining%type;
    native times.native%type;
    centiseconds constant times.centiseconds%type := exercise_gcd();
  begin
    if lower($$plsql_code_type) = 'native' then
      native := 'y';
    else
      native := 'n';
    end if;

    if $$plsql_optimize_level = 3 then
      inlining := 'y';
    else
      inlining := 'n';
    end if;

    insert into times(native, simple, inlining, centiseconds)
      values(time_it.native, gcd_test.simple, time_it.inlining, time_it.centiseconds);
    commit;
  end time_it;
end gcd_test;
/
show errors

The package has enough inline comments to make the code self-documenting so I will not explain it in detail here. In summary, the function GCD() accepts two values and returns the greatest common divisor of them. The function exercise_gcd() calls the GCD() function and returns the CPU time of execution in centiseconds. Finally TIME_IT(), the public procedure calls the EXERCISE_GCD() function with the appropriate degree of freedom and inserts a record into the TIMES table.

Now you need to call the packaged procedure several times with different modifiers and record the CPU time in each case. Do that by changing the conditional compilation variables before compiling the package:

truncate table times
/

-- Interpreted ---------------------------------------------
-- Simple:false
-- No Inlining
alter package GCD_Test compile body
  PLSQL_Code_Type = interpreted
  PLSQL_CCFlags = 'Simple:false'
  PLSQL_Optimize_Level = 2 /* no inlining */
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- inlining
alter package GCD_Test compile body
  PLSQL_Code_Type = interpreted
  PLSQL_CCFlags = 'Simple:false'
  PLSQL_Optimize_Level = 3 /* inlined */
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- Simple:true
-- no inlining
alter package GCD_Test compile body
  PLSQL_Code_Type = interpreted
  PLSQL_CCFlags = 'Simple:true'
  PLSQL_Optimize_Level = 2
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- inlined
alter package GCD_Test compile body
  PLSQL_Code_Type = interpreted
  PLSQL_CCFlags = 'Simple:true'
  PLSQL_Optimize_Level = 3
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- Native -------------------------------------------------
-- Simple:false
-- no inlining
alter package GCD_Test compile body
  PLSQL_Code_Type = native
  PLSQL_CCFlags = 'Simple:false'
  PLSQL_Optimize_Level = 2
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- inlined
alter package GCD_Test compile body
  PLSQL_Code_Type = native
  PLSQL_CCFlags = 'Simple:false'
  PLSQL_Optimize_Level = 3
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- Simple:true
-- no linlining
alter package GCD_Test compile body
  PLSQL_Code_Type = native
  PLSQL_CCFlags = 'Simple:true'
  PLSQL_Optimize_Level = 2
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

-- inlined
alter package GCD_Test compile body
  PLSQL_Code_Type = native
  PLSQL_CCFlags = 'Simple:true'
  PLSQL_Optimize_Level = 3
  reuse settings
/
begin GCD_Test.Time_It(); end;
/

To find out how performance improved under each of these scenarios, use the following code:


spool timings.txt
<<b>>declare
  Interp_Pls_Integer_Noinline     Times.Centiseconds%type;
  Interp_Pls_Integer_Inline       Times.Centiseconds%type;
  Interp_Simple_Integer_Noinline  Times.Centiseconds%type;
  Interp_Simple_Integer_Inline    Times.Centiseconds%type;

  Native_Pls_Integer_Noinline     Times.Centiseconds%type;
  Native_Pls_Integer_Inline       Times.Centiseconds%type;
  Native_Simple_Integer_Noinline  Times.Centiseconds%type;
  Native_Simple_Integer_Inline    Times.Centiseconds%type;

  procedure Show_Caption(Caption in varchar2) is
  begin
    DBMS_Output.Put_Line(Chr(10)||Rpad('-', 60, '-')||Chr(10)||Chr(10)||Caption||Chr(10));
  end Show_Caption;

  procedure Show_Ratio(Var1 in varchar2, Var2 in varchar2, Ratio in number) is
  begin
    DBMS_Output.Put_Line(Rpad(Var1, 15)||'and '||Rpad(Var2, 14)||To_Char(Ratio, '99.99'));
  end Show_Ratio;
begin
  select  a.Centiseconds
  into    b.Interp_Pls_Integer_Noinline
  from    Times a
  where   a.Native = 'N' and a.Simple = 'N' and a.Inlining = 'N';

  select  a.Centiseconds
  into    b.Interp_Pls_Integer_Inline
  from    Times a
  where   a.Native = 'N' and a.Simple = 'N' and a.Inlining = 'Y';

  select  a.Centiseconds
  into    b.Interp_Simple_Integer_Noinline
  from    Times a
  where   a.Native = 'N' and a.Simple = 'Y' and a.Inlining = 'N';

  select  a.Centiseconds
  into    b.Interp_Simple_Integer_Inline
  from    Times a
  where   a.Native = 'N' and a.Simple = 'Y' and a.Inlining = 'Y';

  select  a.Centiseconds
  into    b.Native_Pls_Integer_Noinline
  from    Times a
  where   a.Native = 'Y' and a.Simple = 'N' and a.Inlining = 'N';

  select  a.Centiseconds
  into    b.Native_Pls_Integer_Inline
  from    Times a
  where   a.Native = 'Y' and a.Simple = 'N' and a.Inlining = 'Y';

  select  a.Centiseconds
  into    b.Native_Simple_Integer_Noinline
  from    Times a
  where   a.Native = 'Y' and a.Simple = 'Y' and a.Inlining = 'N';

  select  a.Centiseconds
  into    b.Native_Simple_Integer_Inline
  from    Times a
  where   a.Native = 'Y' and a.Simple = 'Y' and a.Inlining = 'Y';

  Show_Caption('Benefit of simple_integer');
  Show_Ratio('Interpreted',    'no inlining',    Interp_Pls_Integer_Noinline   / Interp_Simple_Integer_Noinline);
  Show_Ratio('Interpreted',    'inlining',       Interp_Pls_Integer_Inline     / Interp_Simple_Integer_Inline);
  Show_Ratio('Native',         'no inlining',    Native_Pls_Integer_Noinline   / Native_Simple_Integer_Noinline);
  Show_Ratio('Native',         'inlining',       Native_Pls_Integer_Inline     / Native_Simple_Integer_Inline);

  Show_Caption('Benefit of inlining');
  Show_Ratio('Interpreted',    'pls_integer',    Interp_Pls_Integer_Noinline    / Interp_Pls_Integer_Inline);
  Show_Ratio('Interpreted',    'simple_integer', Interp_Simple_Integer_Noinline / Interp_Simple_Integer_Inline);
  Show_Ratio('Native',         'pls_integer',    Native_Pls_Integer_Noinline    / Native_Pls_Integer_Inline);
  Show_Ratio('Native',         'simple_integer', Native_Simple_Integer_NoInline / Native_Simple_Integer_Inline);

  Show_Caption('Benefit of native');
  Show_Ratio('pls_integer',    'no inlining',    Interp_Pls_Integer_Noinline    / Native_Pls_Integer_Noinline);
  Show_Ratio('pls_integer',    'inlining',       Interp_Pls_Integer_Inline      / Native_Pls_Integer_Inline);
  Show_Ratio('simple_integer', 'no inlining',    Interp_Simple_Integer_Noinline / Native_Simple_Integer_Noinline);
  Show_Ratio('simple_integer', 'inlining',       Interp_Simple_Integer_Inline   / Native_Simple_Integer_Inline);

end b;
/
spool off

Here is the output. It shows the ratio of the CPU time compared to the default: no inlining, interpreted compilation, and using pls_integer.

------------------------------------------------------------

Benefit of simple_integer

Interpreted    and no inlining     1.00
Interpreted    and inlining        1.00
Native         and no inlining     2.19
Native         and inlining        2.79

------------------------------------------------------------

Benefit of inlining

Interpreted    and pls_integer     1.07
Interpreted    and simple_integer  1.07
Native         and pls_integer     1.16
Native         and simple_integer  1.48

------------------------------------------------------------

Benefit of native

pls_integer    and no inlining     4.78
pls_integer    and inlining        5.18
simple_integer and no inlining    10.53
simple_integer and inlining       14.49

From the above output, you can see that the CPU time of execution was 14.49 times for the default set up compared to the native compilation with inlining and simple_integer—a pretty impressive gain, by any standard.

Conclusion

You should now appreciate the power and usefulness of these new features. In summary:

The simple_integer datatype can be syntactically used wherever pls_integer can but beware of the potential wrapping of values and the not-null requirement. This means simple integers can't be blindly used everywhere. If your application has no possibility of accidentally going over the maximum value or going under the minimum value, then this is a very good choice when combined with native compilation.
The benefit of simple_integer is dramatic when used with native compilation and minor when used with interpreted compilation. Notice that even with interpreted compilation, simple_integer is beneficial rather than harmful.
The benefit of inlining is also greater when used with native compilation than when used with interpreted compilation. This is harder to explain. Loosely, the tighter the code that comes toward the back end of the common compilation, the greater the chances for the native branch to do smart optimizations in its domain that are not available in the interpreted branch.
The benefit of native is greatest when the program is amenable—that is, does no SQL and does not use Oracle number, date, and so on. Notice that even when these conditions hold, the size of the benefit varies within a wide range.
Choosing native compilation and inlining in Oracle Database 11g is a no-brainer. The only reason not to would be a punitive compilation time (maybe in the development shop during the early cycles).
The opportunities for safely using simple_integer will be rare. But when an opportunity does arise, you should seize it.

Back to Series TOC

E-mail this page

Printer View

Oracle Cloud

Java

Customer and Events

Communities

Services and Store

Log In to My Oracle Support
Training and Certification
Become a Partner
Find a Partner Solution
Purchase from the Oracle Store
Contact and Chat
Global Contacts
Oracle Support
Phone: 800-633-0738

Products and Services

Solutions

Downloads

Store

Support

Training

Partners

About

Oracle Technology Network

Oracle Database 11g:
The Top Features for DBAs and Developers

PL/SQL Performance

Real Native Compilation

New Datatype: Simple Integer

Use of Real Native Compilation with Simple Integers

Intra-unit Inlining

Compilation Time

Running the Experiment

Conclusion

Oracle Cloud

Java

Customer and Events

Communities

Services and Store

Contact and Chat

Hardware and Software, Engineered to Work Together

Products and Services

Solutions

Downloads

Store

Support

Training

Partners

About

Oracle Technology Network

Oracle Database 11g: The Top Features for DBAs and Developers

PL/SQL Performance

Real Native Compilation

New Datatype: Simple Integer

Use of Real Native Compilation with Simple Integers

Intra-unit Inlining

Compilation Time

Running the Experiment

Conclusion

Oracle Cloud

Java

Customer and Events

Communities

Services and Store

Contact and Chat

Hardware and Software, Engineered to Work Together

Oracle Database 11g:
The Top Features for DBAs and Developers