Database Reference
In-Depth Information
Triggering the Profilers
Both profilers can only be enabled and disabled from within the session that executes the PL/SQL code to be profiled.
If the profiling can't be manually started, it's also possible to create database triggers like the following ones to
automatically enable and disable the profiler for a whole session:
CREATE TRIGGER start_hprof_profiler AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('HPROF_PROFILE'))
THEN
dbms_hprof.start_profiling(
location => 'PLSHPROF_DIR',
filename => 'dbms_hprof_'||sys_context('userenv','sessionid')||'.trc'
);
END IF;
END;
/
CREATE TRIGGER stop_hprof_profiler BEFORE LOGOFF ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('HPROF_PROFILE'))
THEN
dbms_hprof.stop_profiling();
END IF;
END;
/
The previous triggers are for the hierarchical profiler. You can find the code to create them for both profilers in
the
dbms_hprof_triggers.sql
and
dbms_profiler_triggers.sql
scripts. As the previous triggers show, to avoid
enabling the profiler for all users, I usually suggest creating a role (
hprof_profile
in this example) and temporarily
granting permission only to the user required for the test. Of course, it's also possible to define the triggers for a single
schema or to make other checks based, for example, on the
userenv
context.
On to Chapter 4
This chapter gives a detailed description of the tracing and profiling features provided by Oracle Database for
identifying performance issues of reproducible problems. Specifically, it describes SQL trace with its related tools,
and the two PL/SQL profilers externalized through the
dbms_hprof
and
dbms_profiler
packages. With those tools,
you can home in without any doubt on the SQL statements or pieces of PL/SQL code that are causing the suboptimal
performance that you're attempting to diagnose.
When you can't reproduce a problem or you have to analyze a problem while it's happening, the method described
in this chapter is, most of the time, useless. In those cases you can apply the features described next, in Chapter 4.
