Subprogram Inlining and plsql optimization in 11g

One of the most important optimization that happened to Oracle 11g plsql compiler is Sub-program in-lining. more about sub-program in-lining are available in docs.  In short its a new plsql enhancement in compiler to replace all sub-program invocation with a copy of invoked sub-program ( given that both invoking and invoked subprogram are in same plsql unit ). Which can happen by default if plsql_optimize_level is set to 2 or 3

with plsql_optimize_level=2 you must specify each subprogram to be inlined with INLINE pragma
with plsql_optimize_level=3 the plsql optimizer seeks opportunities to inline subprograms.

Consider a simple routine which validates a the input string is purely numerical or not.

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2 as
  5             y number ;
  6     begin
  7             y := to_number(x);
  8                     return 'Y';
  9             exception
 10                     when others then
 11                     return 'N' ;
 12     end;
 13  begin
 14     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 15     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 16  end;
 17  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

When plsql_optimize_level set to 3 the execution changes like this.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p ;
 Results for 1A is = Y
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

so in short what happened during plsql_optimize_level=3 is

a) to_number is pure and deterministic
b) the variable y in code is assigned but never used, so its dead. Its a dead code.
c) therefore the above code can be reduced to:

create or replace procedure p
as
    function is_number(x in varchar2)
    return varchar2 as
    begin
        return 'Y';
    end;
begin
    dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
    dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
end;   
/


the way to code the "is_datatype" function - knowing that to_date/to_number are pure deterministic functions - is to include that non-inlinable function call

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             return 'Y';
 10             exception
 11                     when others then
 12                             return 'N' ;
 13     end;
 14  begin
 15     pragma inline(is_number,'NO');
 16     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 17     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 18  end;
 19  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2>
rajesh@ORA11GR2> exec p ;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>

Another valid approach would be like this

rajesh@ORA11GR2> create or replace procedure p
  2  as
  3     function is_number(x in varchar2)
  4     return varchar2
  5     as
  6             y number;
  7     begin
  8             y := to_number(x);
  9             if y is not null then
 10                     return 'Y';
 11             else
 12                     return 'N';
 13             end if;
 14             exception
 15                     when others then
 16                     return 'N' ;
 17     end;
 18  begin
 19     dbms_output.put_line (' Results for 1A is = '|| is_number('1A') );
 20     dbms_output.put_line (' Results for 12 is = '|| is_number('12') );
 21  end;
 22  /

Procedure created.

rajesh@ORA11GR2>
rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=2;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2> alter procedure p compile plsql_optimize_level=3;

Procedure altered.

rajesh@ORA11GR2> exec p;
 Results for 1A is = N
 Results for 12 is = Y

PL/SQL procedure successfully completed.

rajesh@ORA11GR2>