PL/SQL : IF-ELSIF-ELSE -> ELSE block is not considered as a basic block

user_5837376 0
Viewed 233

This is the definition of a PL/SQL basic block provided by ORACLE documentation.

A basic block refers to a single entry single exit block of PL/SQL code

This is the definition of the basic block from the internet

Basic block is a set of statements that always executes in a sequence one after the other. The characteristics of basic blocks are- They do not contain any kind of jump statements in them. There is no possibility of branching or getting halted in the middle. All the statements execute in the same order they appear.

I am trying to find the code coverage of PL/SQL code using the DBMS_PLSQL_CODE_COVERAGE package and this package allows to find the PL/SQL block-level coverage. Here I have some doubts about IF-ELSIF-ELSE code coverage. The main thing is ELSE block of IF-ELSIF-ELSE is not considered as a basic block. Please refer to the following codes.

IF-ELSE

This is my first example. In the second column, all the values (0s and 1s) represent a basic block. Here you can clearly see both IF and ELSE conditions are counted as basic blocks which are pretty fair to me.

Now, this is what bothers me. Please refer to the following code.

IF-ELSIF-ELSE

Here you can see all IF and ELSIF blocks are counted as basic blocks, which is okay. But the ELSE block is not considered a basic block, which means, according to the definition of basic block if execution comes to the last ELSIF block, no matter what ELSE part is also get executed. This affects the code coverage percentage.

But in actual execution, this does not happen. But DBMS_PLSQL_CODE_COVERAGE package gives me this output. Could someone explain why this happens? I have no idea of the internal implementation of this package. All I know is it is using DBMS_PROFILER to get code coverage.

If you are curious that, which query provided me with the above tables here is the query.

SELECT LISTAGG(ccb.col, ',') WITHIN GROUP (ORDER BY ccb.col) AS col,
       LISTAGG(ccb.covered, ',') WITHIN GROUP (ORDER BY ccb.col) AS covered,
       s.line,
       LISTAGG(ccb.not_feasible, ',') WITHIN GROUP (ORDER BY ccb.col) AS not_feasible,
       s.text
FROM   user_source s
       JOIN dbmspcc_units ccu ON s.name = ccu.name AND s.type = ccu.type
       LEFT OUTER JOIN dbmspcc_blocks ccb ON ccu.run_id = ccb.run_id AND ccu.object_id = ccb.object_id AND s.line = ccb.line
WHERE  s.name = 'DEMO_UTILITY_TST'
AND    s.type = 'PACKAGE BODY'
AND    ccu.run_id = 248
GROUP BY s.line, s.text
ORDER BY 3;

DBMS_PLSQL_CODE_COVERAGE package documentation

code-coverage plsql oracle sql
1 Answers

Check your plsql_optimize_level. When this is 2 or higher, the compiler can substantially rearrange your code. As the docs say:

0 Maintains the evaluation order and hence the pattern of side effects, exceptions, and package initializations of Oracle9i and earlier releases. Also removes the new semantic identity of BINARY_INTEGER and PLS_INTEGER and restores the earlier rules for the evaluation of integer expressions. Although code will run somewhat faster than it did in Oracle9i, use of level 0 will forfeit most of the performance gains of PL/SQL in Oracle Database 10g.

1 Applies a wide range of optimizations to PL/SQL programs including the elimination of unnecessary computations and exceptions, but generally does not move source code out of its original source order.

2 Applies a wide range of modern optimization techniques beyond those of level 1 including changes which may move source code relatively far from its original location.

3 Applies a wide range of optimization techniques beyond those of level 2, automatically including techniques not specifically requested.

In your example both the first if and else branches return -1. So the compiler is merging these into one block at level 2 or higher.

To give an extreme example. The both the if and else branches in the function below return 0. There's no other code in the function.

At plsql_optimize_level = 1, the blocks are unchanged. So code coverage reports both as covered (assuming appropriate tests):

alter session set plsql_optimize_level = 1;
create or replace function f ( p int ) 
  return int as
  retval int;
begin
  if p < 10 then
    return 0;
  else
    return 0;
  end if;
end f;
/

declare
  run_id pls_integer;
begin
  dbms_plsql_code_coverage.create_coverage_tables ( true );
  run_id := dbms_plsql_code_coverage.start_coverage('TEST');
  dbms_output.put_line ( f ( 9 ) );
  dbms_output.put_line ( f ( 99 ) );
  dbms_plsql_code_coverage.stop_coverage;
end;
/

select max(ccb.covered) as covered,
       s.line,
       max(ccb.covered) as not_feasible,
       rtrim ( s.text, chr(10) ) text
from   user_source s
join   dbmspcc_units ccu 
on     s.name = ccu.name and s.type = ccu.type
left outer join dbmspcc_blocks ccb
on     ccu.run_id = ccb.run_id and ccu.object_id = ccb.object_id and s.line = ccb.line
group by s.line, s.text
order by s.line;

   COVERED       LINE NOT_FEASIBLE TEXT                                              
---------- ---------- ------------ --------------------------------------------------
         1          1            1 function f ( p int )                              
<null>              2 <null>         return int as                                   
<null>              3 <null>         retval int;                                     
<null>              4 <null>       begin                                             
<null>              5 <null>         if p < 10 then                                  
         1          6            1     return 0;                                     
<null>              7 <null>         else                                            
         1          8            1     return 0;                                     
<null>              9 <null>         end if;                                         
<null>             10 <null>       end f;

But increase the coverage to 2 and the compiler merges these together. Code coverage reports neither as covered!

alter session set plsql_optimize_level = 2;
alter function f compile;

declare
  run_id pls_integer;
begin
  dbms_plsql_code_coverage.create_coverage_tables ( true );
  run_id := dbms_plsql_code_coverage.start_coverage('TEST');
  dbms_output.put_line ( f ( 9 ) );
  dbms_output.put_line ( f ( 99 ) );
  dbms_plsql_code_coverage.stop_coverage;
end;
/

select max(ccb.covered) as covered,
       s.line,
       max(ccb.covered) as not_feasible,
       rtrim ( s.text, chr(10) ) text
from   user_source s
join   dbmspcc_units ccu 
on     s.name = ccu.name and s.type = ccu.type
left outer join dbmspcc_blocks ccb
on     ccu.run_id = ccb.run_id and ccu.object_id = ccb.object_id and s.line = ccb.line
group by s.line, s.text
order by s.line;

   COVERED       LINE NOT_FEASIBLE TEXT                                              
---------- ---------- ------------ --------------------------------------------------
         1          1            1 function f ( p int )                              
<null>              2 <null>         return int as                                   
<null>              3 <null>         retval int;                                     
<null>              4 <null>       begin                                             
<null>              5 <null>         if p < 10 then                                  
<null>              6 <null>           return 0;                                     
<null>              7 <null>         else                                            
<null>              8 <null>           return 0;                                     
<null>              9 <null>         end if;                                         
<null>             10 <null>       end f;

TL;DR Set plsql_optimize_level = 1 before running code coverage tests.

user_14859550
Related