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_TRACEpackageViewing trace information
Profiling PL/SQL programs
The
DBMS_HPROFpackageThe
plshprofutilityGenerating HTML profiler reports