DBMS_PROFILE: Overview what can be done to tune the PL/SQL APPLICATIONS

DBMS_PROFILE

The profiler provides us with a source code tuning for our PL/SQL applications. It is still a unknown feature for developers and DBA’s. In the past, you would tune your PL/SQL applications using SQL_TRACE and TKPROF or by using dbms_output. Well, you no longer have to do this. There is a DBMS_PROFILER package available as of oracle 8i version.

 
One of its two main uses is source code profiling to detect where in the code time is being spent, or to compare two different algorithms. The other major use is as a code coverage tool, to report back the percentage of executable statements your test routines actually exercised in the application. While 100 percent code coverage does not assure you of bug free code. It certainly brings you a step closer though. A report script is created, based on the example profiler report provided by Oracle. This report extracts the basic information you need in order to use the DBMS_PROFILER tool successfully. It avoids the great detail you can go into, providing you with the aggregate view of what happened in your application, and more details on the most expensive parts. It may be the only report you really need to use with this tool in order to identify bottlenecks and tune your application.
 
The DBMS_PROFILER has to be installed by the DBA’s. The function is not default installed. DBMS_PROFILER statistics will be gathering into database tables. These tables are set up to hold the statistics for many different runs of the code.
 
Detailed information see DBMS_PROFILE: Overview what can be done to tune the PL/SQL APPLICATIONS .