One
Optimization that PL/SQL Compiler can perform is known as “Subprogram in
lining”, that replaces the subprogram invocation with copy of the invoked
subprogram (as long as if the invoked and invoking subprograms are in the same
program unit). Subprogram inline can be controlled by this parameter
PLSQL_OPTIMIZE_LEVEL with its default value (which is set to 2) or set it to 3.
With
plsql_optimize_level=2 (the default value), you must specify each subprogram to
be in lined with the INLINE pragma explicitly, with plsql_optimize_level set to
3 the compiler will make the Subprogram in liningimplicitly as part of
compilation process.
Let
start the demo for the “subprogram in-lining” with a simple procedure that
check the inputs parameter to be purely numeric or not.
demo@ORA11G> create or replace procedure is_num
2 as
3 function check_num(x varchar2)
4 return varchar2 as
5 x1 int;
6 begin
7 x1 := to_number(x);
8 return 'Y';
9 exception
10 when others then
11 return 'N';
12 end;
13 begin
14 dbms_output.put_line(' 1 ='||
check_num('1'));
15 dbms_output.put_line(' A ='||
check_num('A'));
16 end;
17 /
Procedure created.
demo@ORA11G> exec is_num;
1 =Y
A =N
PL/SQL procedure successfully completed.
demo@ORA11G>
So
far look good; now let’s see what happens when PLSQL_OPTIMIZE_LEVEL is set to
3.
demo@ORA11G>
demo@ORA11G> alter procedure is_num compile
plsql_optimize_level=3;
Procedure altered.
demo@ORA11G> exec is_num;
1 =Y
A =Y
PL/SQL procedure successfully completed.
demo@ORA11G>
This is not a BUG! This is documented:
What
has happened, when this procedure is recompiled with PLSQL_OPTIMIZE_LEVEL is
set to 3?
- We know that variable X1 is unreferenced, don’t use it anywhere other than the assignment.
- We know that to_number() is deterministic
- We know that to_number() is pure. (Don’t make any changes to database tables, don’t write contents to file, don’t make changes to package global variables)
So
the above code check_num() inside the function, becomes like this.
Begin
Return ‘Y’;
End;
So
the right way to code the above “check_num()” function would be to explicitly
disable Pragma In lining.
demo@ORA11G> create or replace procedure is_num
2 as
3 function check_num(x varchar2)
4 return varchar2 as
5 x1 int;
6 begin
7 x1 := to_number(x);
8 return 'Y';
9 exception
10 when others then
11 return 'N';
12 end;
13 begin
14 pragma inline(check_num,'NO');
15 dbms_output.put_line(' 1 ='||
check_num('1'));
16 pragma inline(check_num,'NO');
17 dbms_output.put_line(' A ='||
check_num('A'));
18 end;
19 /
Procedure created.
demo@ORA11G> exec is_num;
1 =Y
A =N
PL/SQL procedure successfully completed.
demo@ORA11G> alter procedure is_num compile
plsql_optimize_level=3;
Procedure altered.
demo@ORA11G> exec is_num;
1 =Y
A =N
PL/SQL procedure successfully completed.
demo@ORA11G>
You
can see the output remains the same in both cases, even when the program is
recompiled with PLSQL_OPTIMIZE_LEVEL is set to 3
Other
possible approach would be rewriting the function “check_num” to avoid the un-referenced
variables.
demo@ORA11G> create or replace procedure is_num
2 as
3 function check_num(x varchar2)
4 return varchar2 as
5 x1 int;
6 begin
7 x1 := to_number(x);
8 if x1 is not null then
9 return 'Y';
10 else
11 return 'N';
12 end if;
13 exception
14 when others then
15 return 'N';
16 end;
17 begin
18 dbms_output.put_line(' 1 ='||
check_num('1'));
19 dbms_output.put_line(' A ='||
check_num('A'));
20 end;
21 /
Procedure created.
demo@ORA11G> exec is_num;
1 =Y
A =N
PL/SQL procedure successfully completed.
demo@ORA11G> alter procedure is_num compile
plsql_optimize_level=3;
Procedure altered.
demo@ORA11G> exec is_num;
1 =Y
A =N
PL/SQL procedure successfully completed.
demo@ORA11G>
