Profiling PL/SQL code

Oracle enables database developers to perform dynamic analysis of their PL/SQL code through tracing and profiling. As an application developer, you write a multi-line PL/SQL subprogram, which may include SQL statements, PL/SQL constructs, calls to routine subprograms, exceptions, and many more items. You can follow the execution flow of the program by using the DBMS_TRACE package, but it doesn't reveal the time consumed at each step. You can profile the PL/SQL program to check its performance aspects. The performance profile reveals how much time is spent at each line of code in a PL/SQL program.

Profiling is a vital exercise in the development stage of a database, as you can identify the areas in your PL/SQL program code that can be fine-tuned for performance. Oracle provides two built-in utility packages to profile PL/SQL code: DBMS_PROFILER and DBMS_HPROF. The DBMS_PROFILER package gathers the performance metrics and produces a flat profiler output. On the other...