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