||Donald K. Burleson
Oracle Utilities Tips
Best Practices for Using dbms_profiler Everywhere
• Wrap only for production - Wrapping code is desired for production
environments but not for profiling. It is much easier to see the
unencrypted form of the text in our reports than it is to connect line
numbers to source versions. Use dbms_profiler before you wrap your code in
a test environment, wrap it, and then put it in production.
• Eliminate system packages most of the time - Knowing the performance
data for internal Oracle processing does not buy you much since you cannot
change anything. However, knowing the performance problem is within the
system packages will save you some tuning time since the problem is
somewhere other than your own code.
• When analyzing lines of code, it is best to concentrate on the
• Lines of code that are frequently executed - For example, a loop that
executes 5000 times is a great candidate for tuning. Guru Oracle tuners
typically look for that “low hanging fruit” in which one line or a group
of lines of code are executed much more than others. The benefits of
tuning one line of code that is executed often far outweigh tuning those
lines that may cost more yet are executed infrequently in comparison.
• Lines of code with a high value for average time executed – The minimum
and maximum values of execution time are interesting although not as
useful as the average execution time. Min and max only tell us how much
the execution time varies depending on database activity. Line by line, a
PL/SQL developer should focus on those lines that cost the most on an
average execution basis. dbms_profiler does not provide the average, but
it does provide enough data to allow it to be computed (Total Execution
Time / # Times Executed).
• Lines of code that contain SQL syntax - The main resource consumers are
those lines that execute SQL. Once the data is sorted by average execution
time, the statements that are the worst usually contain SQL. Optimize and
tune the SQL through utilities, such as Explain Plan, tkprof, and third
Obtaining execution statistics for PL/SQL code is a requirement for good
PL/SQL developers. The next section describes how developers can fully
debug their code, stepping line by line through PL/SQL code during
execution by utilizing the dbms_debug utility.
To learn more about
these techniques, see the book "Advanced
Oracle Utilities: The Definitive Reference".
You can buy it directly
and get instant access to the code depot of utilities scripts.