Building a PL/SQL coverage report with DBMS Profiler

Question

I am using DBMS_PROFILER for basic profiling of my PL/SQL packages. I am also using it to get code coverage statistics using the following query:

SELECT EXEC.unit_name unitname,ROUND (EXEC.cnt/total.cnt * 100, 1) Code_coverage FROM 
    (SELECT u.unit_name, COUNT(1) cnt FROM plsql_profiler_data d, plsql_profiler_units u WHERE u.unit_number = d.unit_number GROUP BY u.unit_name) total, 
    (SELECT u.unit_name, COUNT(1) cnt FROM plsql_profiler_data d, plsql_profiler_units u WHERE u.unit_number = d.unit_number AND d.total_occur > 0 GROUP BY u.unit_name) EXEC 
    WHERE EXEC.unit_name = total.unit_name

I clear the plsql_profiler_data,plsql_profiler_units,plsql_profiler_runs tables before each profiler runs so that I need not know the run id each time.

This will give me Package wise information on the percentage of code that was covered during the profiling. Now I am trying to see if this can be built as a normal coverage report where I can know which line of code was covered and which one wasnt(say select lineOfCode, iscovered from...) so that I can built a report with html formatting to indicate if a line was covered or not.

I am not too proficient in Oracle table structures on where the functions and procedures get saved etc. (Got the above query from a blog and modified slightly to remove run id's)

Is this possible?

If so how can I achieve this?

Answer 1

I think this approaches what you're after:

-- View lines of code profiled, along with run times, next to the complete, ordered source..
-- Provides an annotated view of profiled packages, procs, etc.
-- Only the first line of a multiline SQL statement will register with timings.
SELECT u.UNIT_OWNER || '.' || u.UNIT_NAME AS "Unit"
  , s.line
  , CASE WHEN d.TOTAL_OCCUR >= 0 THEN 'C'
    ELSE ' ' END AS Covered
  , s.TEXT
  , TO_CHAR(d.TOTAL_TIME / (1000*1000*1000), 'fm990.000009') AS "Total Time (sec)"
  , CASE WHEN NVL(d.TOTAL_OCCUR, 1) > 0 THEN d.TOTAL_OCCUR ELSE 1 END AS "# Iterations"
  , TO_CHAR(CASE WHEN d.TOTAL_OCCUR > 0 THEN d.TOTAL_TIME / (d.TOTAL_OCCUR * (1000*1000*1000))
    ELSE NULL END, 'fm990.000009') AS "Avg Time (sec)"
FROM all_source s 
  LEFT JOIN plsql_profiler_units u ON s.OWNER = u.UNIT_OWNER
    AND s.NAME = u.UNIT_NAME
    AND s.TYPE = u.UNIT_TYPE
  LEFT JOIN plsql_profiler_data d ON u.UNIT_NUMBER = d.UNIT_NUMBER
    AND s.LINE = d.LINE#
    AND d.RUNID = u.RUNID
WHERE u.RUNID = ? -- Add RUNID of profiler run to investigate here 
ORDER BY u.UNIT_NAME
  , s.LINE

There are few issues to keep in mind.

1) Many rows in the plsql_profiler_data table will NOT have accurate values in their TOTAL_TIME column because they executed faster than the resolution of the timer.

Ask Tom re: timings:

The timings are collected using some unit of time, typically only granular to the HSECS.

That means that many discrete events that take less then 1/100th of a second, appear to take ZERO seconds.

Many discrete events that take less then 1/100ths of a second may appear to take 1/100th of a second.

2) Only the FIRST line in a multiline statement will show as covered. So if you split an INSERT or whatever across multiple lines, I don't know of any easy way to have every line of that statement to show as profiled in an Annotated Source style of report.

Also, check out Oracle's dbms_profiler documentation and this useful package reference for help crafting queries against the collected profiler data.