Hi,
I am implementing a system for automated unit testing of our plsql, and would like include statistics on code coverage, especially to highlight where code has not been tested, e.g. "your tests only cover 75% of the code".
I have been looking at dbms_profiler, and while it's great for identifying performance/iteration issues, I am struggling to get an accurate percentage of code covered. On inspection, you can plainly see where code is not executed, but I want to get an automated report.
Since dbms_profiler does not seem to count variable declarations or comments, and only counts the first lines of selects or DML statements, the number of lines showing as executed is not fully accurate, which makes it difficult to determine the lines
not executed.
For example,
delete from plsql_profiler_data where runid = (select p2.runid from plsql_profiler_runs p2 where p2.run_comment = 'PROFILETEST');
delete from plsql_profiler_units where runid = (select p2.runid from plsql_profiler_runs p2 where p2.run_comment = 'PROFILETEST');
delete from plsql_profiler_runs where run_comment = 'PROFILETEST';
--
create table profiletemp
(
text1 varchar2(1000),
text2 varchar2(1000)
);
--
create or replace procedure profiletest as
--
-- Variables.
--
l1 varchar2(1000);
--
-- Cursors.
--
cursor c_select_data is
select 'Line 1',
'Line 2',
'Line 3'
from dual;
--
begin
--
-- Comments.
--
l1 := '?';
if 1=1 then
for c in c_select_data loop
update profiletemp t
set t.text1 = '1',
t.text2 = '2';
end loop;
else
l1 := '1';
l1 := '1';
l1 := '1';
l1 := '1';
l1 := '1';
end if;
--
commit;
--
end;
/
--
--
declare
begin
dbms_profiler.start_profiler ('PROFILETEST');
profiletest;
dbms_profiler.stop_profiler;
end;
/
--
select * from plsql_profiler_runs where run_comment = 'PROFILETEST';
select * from plsql_profiler_units where runid = (select p2.runid from plsql_profiler_runs p2 where p2.run_comment = 'PROFILETEST');
select * from plsql_profiler_data where runid = (select p2.runid from plsql_profiler_runs p2 where p2.run_comment = 'PROFILETEST') and unit_number = 2;
The
profiletest procedure is 36 lines long. Of these, only the
else section does not get executed, 6 lines of code. So (roughly) 83% of the code is executed (30 lines), 17% is not executed (6 lines).
However, the profile data only shows 8 lines as executed, which equates to about
22% of the code. (As mentioned, the discrepancy is made up of comments, and the extra lines in the select and update statements. This would flag about 78% of the code as not covered, which is inaccurate.
So, my questions:
* Is there a method to get a more complete executed/not executed line information from dbms_profiler?
* A colleague recently attended OpenWorld, and I have seen pdf of your presentation, so I know there is a new code coverage facility coming in 12c Release 2. Will this give me the information I need? (If so, any idea of when this will be available?)
Thanks in advance,
Richard.
Using lines of code executed is a fundamentally flawed way of measuring code coverage.
To see why, consider this:
We have a basic function that returns its argument and calls dbms_output.
The procedure makes multiple calls to it in a single if statement:
create or replace function f (p int)
return int as
begin
dbms_output.put_line('Executed: ' || p);
return p;
end;
/
create or replace procedure p is
begin
-- this is a comment
if f(1) = 1 or f(2) = 2 then
dbms_output.put_line('this');
else
dbms_output.put_line('that');
end if;
end p;
/
Due to short-circuit evaluation, f(2) is never executed! You can see this from the output:
SQL> exec p;
Executed: 1
this
Anything working at the line level will incorrectly report this as fully covered.
Bearing this in mind, you can get around the whitespace/comment issue. Join to user_source, ignoring any lines that you don't want:
declare
l_result binary_integer;
begin
l_result := dbms_profiler.start_profiler(run_comment => 'PROFILETEST');
p;
l_result := dbms_profiler.stop_profiler;
end;
/
select trim(chr(10) from us.text) text,
us.name,
us.line,
d.total_time
from user_source us
join plsql_profiler_units u
on u.unit_name = us.name
left join plsql_profiler_data d
on u.runid = d.runid
and u.unit_number = d.unit_number
and d.line# = us.line
and d.total_occur > 0
where u.runid in (
select runid from plsql_profiler_runs
where run_comment = 'PROFILETEST'
)
and u.unit_owner = user
and not regexp_like (text, '^\s*$')
and not regexp_like (text, '^\s*--')
order by us.name, us.line;
TEXT NAME LINE TOTAL_TIME
function f (p int) F 1
return int as F 2
begin F 3
dbms_output.put_line('Executed: ' || p); F 5 37828
return p; F 7 4323
end; F 8 3242
procedure p is P 1
begin P 2
if f(1) = 1 or f(2) = 2 then P 6 2291313
dbms_output.put_line('this'); P 7 3242
else P 8
dbms_output.put_line('that'); P 9
end if; P 10
end p; P 12 3242
Still not perfect, but getting closer. You could also try joining with the data you get from PL/Scope.
So as you've heard, to address these issues we've implemented code coverage in 12.2! :)
This reports at the "basic block" level. This is nothing to do with PL/SQL blocks. It refers to each separately executable section of code. So:
if f(1) = 1 or f(2) = 2 then
dbms_output.put_line('this');
else
dbms_output.put_line('that');
end if;
has four basic blocks. One for each call to f and the two calls to dbms_output.put_line.
Using it is easy. Just create coverage tables to store the details and call start/stop coverage before and after your tests:
exec dbms_plsql_code_coverage.create_coverage_tables;
begin
dbms_plsql_code_coverage.start_coverage(null, 'TEST');
p;
dbms_plsql_code_coverage.stop_coverage;
end;
/
select unit_owner, unit_name, unit_type,
round(blocks_covered/basic_block_count*100) pct_covered
from dbmspcc_runs r
join dbmspcc_units u
on r.run_id = u.run_id
where r.run_comment = 'TEST';
UNIT_OWNER UNIT_NAME UNIT_TYPE PCT_COVERED
CHRIS F FUNCTION 100
CHRIS P PROCEDURE 50
When is it available?
Now!
You can use it in the 12.2 Exadata Express Cloud service:
https://cloud.oracle.com/database/exadata-express/features