Subprogram Inlining / Continue statement / Pl Sql Warnings

Subprogram Inlining / Continue statement / Pl Sql Warnings 

Subprogram inlining has been around in optimizing compilers for a long time.  With the 11g database, Oracle has included this feature in PL/SQL.  

Basically subprogram inlining is where the program calls a subprogram (procedure or function) and the compiler places the actual subprogram code in place of the call to that code.  Since the subprogram code is in the main code (INLINE), the overhead of calling that subprogram is saved.  This allows you to program using a modular method, producing readable, maintainable source code, while the compiler optimizes the code to make it more efficient.

-------------------   inlining subprograms ---------------

Oracle 11g has taken compiler optimisation further, in particular with the concept of subprogram inlining.

With subprogram inlining, Oracle will replace a call to a subroutine (such as a function) with the subroutine code itself during compilation. One of the benefits of this is that we can continue to write well-structured, modular code without any performance penalties. For SQL-intensive PL/SQL programs, the gains from inlining might be marginal, but for procedural code, inlining might provide some greater optimisation benefits, as we will see in this article.

If the PLSQL_OPTIMIZER_LEVEL is set to 3 the compiler will automatically try and inline subprograms. 

 If the PLSQL_OPTIMIZE_LEVEL is set to 2 (the default) it will not automatically try and inline subprograms.  So the PRAGMA INLINE is used to tell the optimizer to inline or not to inline depending on the setting of the optimizer level.

ALTER SESSION set PLSQL_OPTIMIZE_LEVEL = 2;

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

The CONTINUE statement causes the loop to skip the remainder of its body and immediately retest its condition prior to reiterating. In other words, it forces the next iteration of the loop to take place, skipping any code in between.

DECLARE 

   a number(2) := 10; 

BEGIN 

   -- while loop execution  

   WHILE a < 20 LOOP 

      dbms_output.put_line ('value of a: ' || a); 

      a := a + 1; 

      IF a = 15 THEN 

         -- skip the loop using the CONTINUE statement 

         a := a + 1; 

         CONTINUE; 

      END IF; 

   END LOOP; 

END; 

/ 

value of a: 10 

value of a: 11 

value of a: 12 

value of a: 13 

value of a: 14 

value of a: 16 

value of a: 17 

value of a: 18 

value of a: 19

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

To avoid runtime bugs, Oracle 10g introduced the PLSQL_WARNINGS initialization parameter. It will not stop compiling the program but will only give a compile time warning about inefficient coding.Lets see how we can use it.

First we need to enable compile time warning. It can be enabled for session or system.

We can use the ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL' or the DBMS_WARNING package.

 CREATE OR REPLACE PROCEDURE test_warnings AS

      l_dummy  VARCHAR2(10) := '1';

    BEGIN

      IF 1=1 THEN

        SELECT '2'

        INTO   l_dummy

        FROM   dual;

      ELSE

        RAISE_APPLICATION_ERROR(-20000, 'l_dummy != 1!');

     END IF;

   END;

   /

Procedure created.

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

-- run the same code to display warnings

 CREATE OR REPLACE PROCEDURE test_warnings AS

      l_dummy  VARCHAR2(10) := '1';

    BEGIN

      IF 1=1 THEN

        SELECT '2'

        INTO   l_dummy

        FROM   dual;

      ELSE

        RAISE_APPLICATION_ERROR(-20000, 'l_dummy != 1!');

     END IF;

   END;

SP2-0804: Procedure created with compilation warnings

SQL> show err

Errors for PROCEDURE TEST_WARNINGS:

LINE/COL ERROR

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

9/5      PLW-06002: Unreachable code

SQL>