Hierarchical Profiler

Hierarchical Profiler

You can use the PL/SQL hierarchical profiler to identify bottlenecks and performance-tuning opportunities in PL/SQL applications.

The profiler reports the dynamic execution profile of a PL/SQL program organized by function calls, and accounts for SQL and PL/SQL execution times separately. No special source or compile-time preparation is required; any PL/SQL program can be profiled.

Overview of PL/SQL Hierarchical Profiler

Nonhierarchical (flat) profilers record the time that a program spends within each subprogram—the function time or self time of each subprogram. Function time is helpful, but often inadequate. For example, it is helpful to know that a program spends 40% of its time in the subprogram INSERT_ORDER, but it is more helpful to know which subprograms callINSERT_ORDER often and the total time the program spends under INSERT_ORDER (including its descendant subprograms). Hierarchical profilers provide such information.

The PL/SQL hierarchical profiler does the following:

1. Reports the dynamic execution profile of your PL/SQL program, organized by subprogram calls
2. Accounts for SQL and PL/SQL execution times separately
3. Requires no special source or compile-time preparation
4. Stores results in database tables (hierarchical profiler tables) for custom report generation by integrated development environment (IDE) tools (such as SQL Developer and third-party tools)
5. Provides subprogram-level execution summary information, such as:
6. Number of calls to the subprogram
7. Time spent in the subprogram itself (function time or self time)
8. Time spent in the subprogram itself and in its descendent subprograms (subtree time)
9. Detailed parent-children information, for example:
10. All callers of a given subprogram (parents)
11. All subprograms that a given subprogram called (children)
12. How much time was spent in subprogram x when called from y
13. How many calls to subprogram x were from y

The PL/SQL hierarchical profiler is implemented by the DBMS_HPROF package and has two components:

14. Data collection

The data collection component is an intrinsic part of the PL/SQL Virtual Machine. The DBMS_HPROF package provides APIs to turn hierarchical profiling on and off. The raw profiler output is written to a file.

15. Analyzer

The analyzer component processes the raw profiler output and stores the results in hierarchical profiler tables.

Collecting Profile Data

To collect profile data from your PL/SQL program for the PL/SQL hierarchical profiler, follow these steps:

1. Ensure that you have the following privileges:

16. EXECUTE privilege on the DBMS_HPROF package
17. WRITE privilege on the directory that you specify when you call DBMS_HPROF.START_PROFILING

2. Use the DBMS_HPROF.START_PROFILING PL/SQL API to start hierarchical profiler data collection in a session.

3. Run your PL/SQL program long enough to get adequate code coverage.

To get the most accurate measurements of elapsed time, avoid unrelated activity on the system on which your PL/SQL program is running.

4. Use the DBMS_HPROF.STOP_PROFILING PL/SQL API to stop hierarchical profiler data collection.

Consider the following PL/SQL procedure, test:

CREATE OR REPLACE PROCEDURE test IS
n NUMBER;

PROCEDURE foo IS
BEGIN
SELECT COUNT(*) INTO n FROM EMPLOYEES;
END foo;

BEGIN -- test
FOR i IN 1..3 LOOP
foo;
END LOOP;
END test;
/

Example 9-1 Profiling a PL/SQL Procedure

BEGIN
/* Start profiling.
Write raw profiler output to file test.trc in a directory
that is mapped to directory object PLSHPROF_DIR
(see note following example). */

DBMS_HPROF.START_PROFILING('PLSHPROF_DIR', 'test.trc');
END;
/
-- Execute procedure to be profiled
BEGIN
test;
END;
/
BEGIN
-- Stop profiling
DBMS_HPROF.STOP_PROFILING;
END;
/

Understanding Raw Profiler Output

The SQL script in Example 9-1 wrote the following raw profiler output to the file test.trc:

P#V PLSHPROF Internal Version 1.0
P#! PL/SQL Timer Started
P#C PLSQL."".""."__plsql_vm"
P#X 2
P#C PLSQL."".""."__anonymous_block"
P#X 50
P#C PLSQL."HR"."TEST"::7."TEST"#980980e97e42f8ec #1
P#X 3

IndicatorMeaning
P#V	PLSHPROF banner with version number
P#C	Call to a subprogram (call event)
P#R	Return from a subprogram (return event)
P#X	Elapsed time between preceding and following events
P#!	Comment

consider the following entry in the preceding example of raw profiler output:

P#C PLSQL."HR"."TEST"::7."TEST.FOO"#980980e97e42f8ec #4

Component

Meaning

PLSQL	PLSQL is the namespace to which the called subprogram belongs.
"HR"."TEST"	HR.TEST is the name of the PL/SQL module in which the called subprogram is defined.
7	7 is the internal enumerator for the module type of HR.TEST. Examples of module types are procedure, package, and package body.
"TEST.FOO"	TEST.FOO is the name of the called subprogram.
#980980e97e42f8ec	#980980e97e42f8ec is a hexadecimal value that represents an MD5 hash of the signature of TEST.FOO.
#4	4 is the line number in the PL/SQL module HR.TEST at which TEST.FOO is defined.