Oracle SQL Developer offers the ability to create reports with graphs in it. A number of reports are included with the tool, unfortunately a report which shows information based on DBMS_PROFILER built in package isn’t there. DBMS_PROFILER allows developers to profile the run-time behaviour of PL/SQL code, making it easier to identify performance issues. Because I couldn’t find an existing report to show this information, I created a couple of reports to show this information. ....

Using the Reports

After starting up SQL Developer, navigate to the Reports tab. Right click on "User Defined Reports" and choose "Import". Locate the Report file, which are included at the end of this blog, and that’s it. An extra folder is created, named "Profiler Reports" with two reports in it. After using DBMS_PROFILER, choose the Report titled "Run Overview".
To see more detailed information right click the run of choice and choose "Detailed Profile". (I haven’t yet figured out how to remove the other "Detailed Profile" or "Profiler Report" labels – these were other trial reports which no longer exist.) This will open up a second report with more detailed information.
The Graph shows you the execution time per line of PL/SQL code. The Line numbers are shown on the horizontal axis of the Graph. When you hover your mouse pointer over the graph, you will see a context popup with the line number and the source code text. Behind the Graph is another Tab, labelled "Source Code", which shows the source of the tested stored procedure.

Testing

To show the use of these reports, I used a simple Stored Procedure to demonstrate the efficiency of PLS_INTEGER when used as a loop-counter. There are three procedures which have a similar body: 

begin
   for i in 1..100
   loop
      num := num + 1;
   end loop;
end;

The only thing that is different in these procedures is the datatype of the NUM variable.

Procedure Datatype of NUM variable
PNUM_UNRESTRICTED NUMBER
PNUM_RESTRICTED NUMBER (3,0)
PLS PLS_INTEGER

All of these procedures are included in the profiler_test package at the bottom of this post. To test these procedures using DBMS_PROFILER, you can use an anonymous block: 

SQL> begin
  2     dbms_profiler.start_profiler ('Private Counter Performance');
  3     profiler_test.pnum_unrestricted;
  4     profiler_test.pnum_restricted;
  5     profiler_test.pls;
  6     dbms_profiler.stop_profiler ();
  7  end;
  8  /

PL/SQL procedure successfully completed.

Based on this simple test, you can easily tell that the PLS_INTEGER is the preferred method as a local counter instead of a NUMBER. Hopefully you will have as much fun with these reports as I have. Any suggestions for improvement, let me know… or even better make the changes, drop me a line and I will put the reports up here for everybody to use (with full credits of course).

Resources