Building a PL/SQL coverage report with DBMS Profiler

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.RUNIDWHERE 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.

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.