Chapter 11. Profiling and Tracing PL/SQL Code

Now that we have stepped out of the code development stage, we are discussing best practices of code management and maintenance. In the last chapter, we walked through the strategies of code tracking, error tracking, and the PL/Scope tool for identifier tracking. We noticed that the PL/Scope tool does static code analysis. In this chapter, we are going to learn two important techniques for measuring code performance. The techniques are known as tracing and profiling. The primary goal of the code tracing and profiling techniques is to identify performance bottlenecks in the PL/SQL code and gather performance statistics at each execution step. We will discuss the tracing and profiling features in PL/SQL in the following topics:

• Tracing PL/SQL programs

  • The DBMS_TRACE package

  • Viewing trace information

• Profiling PL/SQL programs

  • The DBMS_HPROF package

  • The plshprof utility

  • Generating HTML profiler reports

Tracing the PL/SQL programs

Code tracing is an important technique to measure the code performance during runtime and identify the expensive areas in the code which can be worked upon to improve the performance. The tracing feature shows the code execution path followed by the server and reveals the time consumed at each step. Often developers assume tracing and debugging as one step, but both are distinctive features. Tracing is a one-time activity which analyses the complete code and prepares the platform for debugging. On the other hand, debugging is the bug identification and fixing activity where the trace report can be used to identify and work upon the problematic points.

Oracle offers multiple methods of tracing:

• DBMS_APPLICATION_INFO: The SET_MODULE and SET_ACTION subprograms can be used to register a specific action in a specific module.

• DBMS_TRACE: The Oracle built-in package allows tracing of PL/SQL subprograms, exceptions and SQL execution. The trace information is logged...

Profiling the PL/SQL programs

We just saw tracing capabilities in PL/SQL programs. It presents the execution flow of the program in an interactive format with clear comments at each stage. But it doesn't provide the execution statistics of the program which prevents the user from determining the performance of a program. The user never comes to know about the time consumed at each step or process.

Before the release of Oracle 11g, DBMS_PROFILER was used as the primary tool for profiling PL/SQL programs.

Oracle hierarchical profiler—the DBMS_HPROF package

Oracle introduced the PL/SQL hierarchical profiler in Oracle 11g release 1. The profiling was restructured as hierarchical profiling. The hierarchical profiling could profile even the subprogram calls made in the PL/SQL code. It fills the gap between tracing loopholes and the expectations of performance tracing. The hierarchical profiler creates the dynamic execution profile of a PL/SQL program. The efficiencies of the hierarchical profiler...

Summary

In this chapter, we learned the tracing and profiling features of Oracle 11g. While the tracing feature tracks the execution path of PL/SQL code, the profiling feature reports the time consumed at each subprogram call or line number. We demonstrated the implementation and analysis of tracing and profiling features.

In the next chapter, we will see how to identify vulnerable areas in a PL/SQL code and safeguard them against injective attacks.

Practice exercise

1. Which component of the PL/SQL hierarchical profiler uploads the result of profiling into database tables?

   1. The Profiler component

   2. The Analyzer component

   3. The shared library component

   4. The Data collector component

2. The plshprof utility is a SQL utility to generate a HTML profiler report from profiler tables in the database.

   1. True

   2. False

3. Suppose that you are using Oracle 11g Release 2 express edition and you issue the following command:

   ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL'
   /
   Session altered.
   ALTER FUNCTION FUNC COMPILE PLSQL_DEBUG=TRUE
   /
   Function altered.

   Determine the output of the following SELECT statement

   SELECT * FROM USER_ERRRORS
   /

   1. No output

   2. PLW-06015: parameter PLSQL_DEBUG is deprecated; use PLSQL_OPTIMIZE_LEVEL = 1

   3. PLW-06013: deprecated parameter PLSQL_DEBUG forces PLSQL_ OPTIMIZE_LEVEL <= 1

   4. Both b and c

4. Identify the trace log tables:

   1. PLSQL_TRACE

   2. PLSQL_TRACE_ACTIONS

   3. PLSQL_TRACE_EVENTS

   4. PLSQL_TRACE_INFO

5. Identify the correct trace level combination from the following...