A number of polls taken during various PL/SQL-related presentations paints a very strange picture: one of the most critical FREE server-side performance tuning tools is almost totally unknown! If you ask basic questions such as “how much time is spent on routine A?” or “how often is function B called?” most developers would start to hand-code their own solution instead of utilizing the Oracle PL/SQL Hierarchical Profiler (HProf). This is not due to a dislike of the provided functionality, but because most developers are simply not aware of its existence! My presentation is an attempt to alter this trend and reintroduce HProf to a wider audience.
The PL/SQL Hierarchical Profiler became available in Oracle 11g Release 1 and replaced the old DBMS_PROFILER package. The goal was the same: to profile runtime behavior of PL/SQL code, i.e. to register and timestamp every operation (including SQL statements) that occurs during the monitoring window. However, the changes were startling:
Profiler output is now a file that can be generated in one environment and analyzed elsewhere. This makes production debugging significantly easier because performance-tuning specialists don’t need to touch PROD as long as they have access to the same code base.
In addition to loading profiler output to the database and running queries against it, the command line utility PLSHPROF creates HTML-based reports (human-readable!). These reports contain various data aggregations to speed up the review process.
This presentation covers a number of real use-cases when HProf significantly shortened response time to production performance problems. You will see how easy it is to figure out the actual source of the slowdown, namely:
Hundreds of thousands of calls to a pretty light user-defined function --> Check execution plans of corresponding queries
Sluggish SQL statement --> Don’t blame PL/SQL and start SQL tracing
Strange third-party calls in wrapped packages --> Collect hard evidence and start complaining
Overall, if you write PL/SQL, you must utilize the PL/SQL Hierarchical Profiler. Otherwise, you will be guessing at your code behavior instead of knowing it, which could lead to unpredictable performance. Unpredictable performance often means production calls at 3 am on Sunday. This presentation will help you sleep longer!