Monday, March 1, 2010  Using the PL/SQL Profiler  Oracle has
provided the ability to see how much time each step of a PL/SQL
routine takes since Oracle8i. The environment is easy to setup
and the information is easy to retrieve.   Why Profile? When
tuning SQL, it is easy because there is just the single SQL
statement. With PL/SQL, there are SQL statements, SQL imbedded
in implicit and explicit cursors, called routines
(functions/procedures), and the PL/SQL code itself. When a
PL/SQL routine is taking 5 minutes to run, exactly what code is
taking how much time. This is the information that the PL/SQL
profiler provides. Without this information, the person trying
to tune the PL/SQL is only shooting in the dark, perhaps pulling
out and tuning the SQL within the code, but otherwise has no
idea what a PL/SQL routine is doing when it comes to time spent
on each line of code.  The profiler is easy to setup and easy to
use. Tools like Quest Software’s TOAD provides a nice GUI
interface to this profiler.   The profile