Oracle Database 11g: The Top Features for DBAs and Developers
by Arup Nanda
Explore use cases in which in-lining of code, true native compilation, and use of simple integers can improve code performance.
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).
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:
- but rather as:
- then you will get a compilation error:
- If you set the variable to NULL inside the program, such as:
- you will get a compilation error:
- 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:
- It will throw an error:
- 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:
- The output would have been:
- 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.
num1 simple_integer:= 1;
PLS-00218: a variable declared NOT NULL must have an initialization assignment
num1 := NULL;
PLS-00382: expression is of wrong type
declare v1 pls_integer := 2147483647; begin v1 := v1 + 1; dbms_output.put_line('v1='||v1); end; /
declare * ERROR at line 1: ORA-01426: numeric overflow ORA-06512: at line 4
declare v1 simple_integer := 2147483647; begin v1 := v1 + 1; dbms_output.put_line('v1='||v1); end; /
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:
- The above setting instructs the PL/SQL compiler to rewrite the code to inline the code.
- Compile the procedure with the plsql settings directly.
- 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.
SQL> alter session set plsql_optimize_level = 3; Session altered.
SQL> alter procedure upd_int 2 compile 3 plsql_optimize_level = 3 4 reuse settings; Procedure altered.
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.
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:
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.
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.