1 package dbms_profiler
2 authid current_user is
3
4 ------------
5 -- OVERVIEW
6 --
7 -- This package provides an API for gathering and persistently storing
8 -- execution performance (profiler) and code coverage data for PL/SQL
9 -- applications.
10 --
11 -- Improving application performance is an iterative process. Each
12 -- iteration involves:
13 -- 1. Exercising the application with one or more benchmark tests with
14 -- profiler data collection enabled.
15 -- 2. Analyzing the profiler data and identifying performance problems.
16 -- 3. Fixing the problems.
17 --
18 -- To support this process, the PL/SQL profiler supports the notion of
19 -- a run. A run involves running the application through some benchmark
20 -- test with profiler data collection enabled. The profiler user controls
21 -- the beginning and end of the run by calling the API functions
22 -- start_profiler, stop_profiler respectively.
23 --
24 -- A typical sequence of calls in a session may be:
25 -- start profiler data collection in session
26 -- execute PL/SQL code for which profiler/code coverage data is required
27 -- stop profiler data collection
28 --
29 -- Note that stopping data collection flushes out the collected data
30 -- as a side effect.
31 --
32 -- Profiler data is collected in data structures which last for the
33 -- duration of the session. Users may call the flush_data function at
34 -- intermediate points during the session to get incremental data and
35 -- to free memory for allocated profiler data structures.
36 --
37 -- Note that some PL/SQL operations, such as the very first execution
38 -- of a PL/SQL unit may involve I/O to catalog tables to load the byte
39 -- code for the PL/SQL unit to be executed. Also some time may be spent
40 -- executing package initialization code, the first time a package
41 -- procedure/function is called. To avoid timing this overhead, it is
42 -- recommended that the database be "warmed up" before collecting profile
43 -- data. Warming up involves running the application once, without
44 -- gathering profiler data.
45 --
46 -- The headers on the interface functions/procedures describe the
47 -- meanings of arguments in greater detail.
48
49 -- All facilites are available either as functions (which return a
50 -- status, and will never raise an exception), or as procedures, (which
51 -- will always raise an exception if they fail).
52 --
53 --------------
54 -- ERROR CODES
55 -- a 0 return value from any function denotes successful completion
56 -- postive error returns are raised from the C implementation
57 -- negative errors are reserved for errors from the PL/SQL package
58 -- implementation (for example, ICD version mismatch).
59 --
60
61 success constant binary_integer := 0;
62 -- interface function/procedure called with an incorrect parameter
63 error_param constant binary_integer := 1;
64 -- data flush operation failed. check to see if the profiler tables have
65 -- been created and there is adequate space.
66 error_io constant binary_integer := 2;
67
68 -- mismatch between package and C implementation
69 error_version constant binary_integer := -1;
70
71 -- version history:
72 -- 1.0 - creation
73 --
74 major_version constant binary_integer := 2;
75 minor_version constant binary_integer := 0;
76
77 version_mismatch exception;
78 pragma exception_init(version_mismatch, -6529);
79 profiler_error exception ;
80 pragma exception_init(profiler_error, -6528);
81
82
83 ----------------------------
84 -- PROCEDURES AND FUNCTIONS
85 --
86
87 --
88 -- Start profiler data collection in session.
89 --
90 -- PARAMETERS:
91 -- comment - each profiler run can be associated with a comment. For
92 -- example, the comment could denote the name and version
93 -- of the benchmark test that was used to collect data.
94 -- comment1 - an additional comment
95 -- run_number - each profiler run is uniquely identified by a generated
96 -- run number. This allows the caller to determine what the
97 -- generated run-number is, so that other tools may use it
98 -- as a foreign key. On a successful return, a skeletal
99 -- record in the run_table exists.
100
101 function start_profiler(run_comment IN varchar2 := sysdate,
102 run_comment1 IN varchar2 := '',
103 run_number OUT binary_integer)
104 return binary_integer;
105
106 procedure start_profiler(run_comment IN varchar2 := sysdate,
107 run_comment1 IN varchar2 := '',
108 run_number OUT binary_integer);
109 function start_profiler(run_comment IN varchar2 := sysdate,
110 run_comment1 IN varchar2 := '')
111 return binary_integer;
112 procedure start_profiler(run_comment IN varchar2 := sysdate,
113 run_comment1 IN varchar2 := '');
114
115 --
116 -- Stop profiler data collection in session. This function has the side
117 -- effect of flushing data collected so far in the session and denotes
118 -- the end of a run.
119 --
120 function stop_profiler return binary_integer;
121 procedure stop_profiler;
122
123 -- Pause profiler data collection, without terminating the run, or flushing
124 -- data
125 function pause_profiler return binary_integer;
126 procedure pause_profiler;
127
128 -- Resume a paused profiler run
129 function resume_profiler return binary_integer;
130 procedure resume_profiler;
131
132 --
133 -- Flushes profiler data collected in session. The data is flushed to
134 -- database tables, which are expected to pre-exist. Use proftab.sql
135 -- script to create the tables and other data structures required for
136 -- persistently storing the profiler data.
137 --
138 function flush_data return binary_integer;
139 procedure flush_data;
140
141 --
142 -- get the version of this API
143 --
144 procedure get_version(major out binary_integer,
145 minor out binary_integer);
146
147 --
148 -- This function verifies that this version of the dbms_profiler package
149 -- can work with the implementation in the database.
150 --
151 function internal_version_check return binary_integer;
152
153 -- General purpose routines
154 --
155 -- compute the total time spent executing this unit - the sum of the
156 -- time spent executing lines in this unit (for this run)
157 --
158 procedure rollup_unit(run_number IN number, unit IN number);
159
160 -- rollup all units for the given run
161 --
162 procedure rollup_run(run_number IN number);
163 end dbms_profiler;
[Home] [Help]