CODEWITHSUNDEEP
oracle-databaseplsqlcode-coverageprofiler
rajesh
rajesh

Reputation: 3407

Building a PL/SQL coverage report with DBMS Profiler

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?

Edit: This is not same as Code coverage for PL/SQL I am already using DBMS Profiler. My query was on building the report.

Upvotes: 10

Views: 1790

Answers (2)

Tom Tresansky
Tom Tresansky

Reputation: 19877

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.

Upvotes: 1

Guillem Vicens
Guillem Vicens

Reputation: 3996

Actually there are some tools for PL/SQL that do code coverage. See the answers to this question for more information.

Said this, you can find information on user created data structure and code in following tables:

  • user_source: here you can find the source in the TEXT field typified by function, procedure, package, etc.
  • User_tables
  • user_indexes
  • user_types: if you use some kind of OO code.
  • Other tables beginning with user_ that you may need.

Basically you would need to check the result of your query against user_source and get extra information from the other tables.

Upvotes: 0

Related Questions