Database Reference
In-Depth Information
filename
specifies the name of the trace file. If the file already exists, it's silently overwritten.
max_depth
specifies whether the gathering of profiling data is limited to a specific call
depth. By default (
NULL
), there's no limit.
While the profiler is enabled, profiling data is gathered for code executed by the PL/SQL engine. Profiling is
disabled by calling the
stop_profiling
procedure.
Once a trace file containing the profiling data is available, it can be loaded into the output tables by calling the
analyze
function. Two parameters are required when calling the
analyze
function:
location
and
filename
. It goes
without saying that their purpose is exactly the same as for the identically named parameters in the
start_profiling
procedure. Hence, you should set them to the same values. Other parameters are supported by the
analyze
function,
and you can view them in the
PL/SQL Packages and Types Reference
manual.
The following example is an excerpt of the output generated by the
dbms_hprof.sql
script. The example shows a
minimal run aimed at profiling an anonymous PL/SQL block. The
runid
value selected while loading the profiler data
into the database is used in the next section to analyze the output of the profiling session:
SQL> BEGIN
2 dbms_hprof.start_profiling(location => 'PLSHPROF_DIR',
3 filename => 'dbms_hprof.trc');
4 END;
5 /
SQL> DECLARE
2 l_count INTEGER;
3 BEGIN
4 perfect_triangles(1000);
5 SELECT count(*) INTO l_count
6 FROM all_objects;
7 END;
8 /
SQL> BEGIN
2 dbms_hprof.stop_profiling;
3 END;
4 /
SQL> SELECT dbms_hprof.analyze(location => 'PLSHPROF_DIR',
2 filename => 'dbms_hprof.trc') AS runid
3 FROM dual;
RUNID
----------
1
Once the profiling data has been loaded into the output tables, it's time to report it. The three main available
options are described in the next sections.
Manually Reporting the Profiling Data
After the load, the profiling data is stored in the output tables. It's then possible to query that data with regular queries,
as shown in this section. What follows is an excerpt of the output generated by the
dbms_hprof.sql
script.
