Database Reference
In-Depth Information
Figure 3-4.
The profiler stores the gathered information in three database tables. Notice that the primary keys consist of
the underlined columns
The
dbmshp_runs
table gives information about which sessions have been profiled. The
dbmshp_function_info
table provides the list of subprograms that have been executed for each run. The
dbmshp_parent_child_info
table
gives the parent-child relationship between callers and callees. In other words, it contains information to reconstruct
the call hierarchy.
Installing the Output Tables
The
dbms_hprof
package runs with the privileges of the user executing it. Consequently, the output tables don't
necessarily need to be created by the
sys
user. Either the database administrator installs the output tables (by running
the
dbmshptab.sql
script) once and provides the necessary synonyms and privileges to use them, or each user installs
them in his own schema. In the following example, the database administrator installs the tables once:
CONNECT / AS SYSDBA
@?/rdbms/admin/dbmshptab.sql
CREATE PUBLIC SYNONYM dbmshp_runs FOR dbmshp_runs;
CREATE PUBLIC SYNONYM dbmshp_function_info FOR dbmshp_function_info;
CREATE PUBLIC SYNONYM dbmshp_parent_child_info FOR dbmshp_parent_child_info;
CREATE PUBLIC SYNONYM dbmshp_runnumber FOR dbmshp_runnumber;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbmshp_runs TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbmshp_function_info TO PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbmshp_parent_child_info TO PUBLIC;
GRANT SELECT ON dbmshp_runnumber TO PUBLIC;
Gathering the Profiling Data
Begin a profiling analysis by calling the
start_profiling
procedure to enable the profiler. The procedure supports
three parameters:
location
specifies the name of the directory object pointing to an OS-level directory where
the trace file containing the profiling data will be stored.
