Donald K. Burleson Oracle Utilities Tips

Using dbms_profiler

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 following:

• 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 party software.

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 from the publisher and get instant access to the code depot of utilities scripts.