[Home] [Help]
PACKAGE BODY: APPS.XLA_UTILITY_PROFILER_PKG
Source
1 PACKAGE BODY xla_utility_profiler_pkg AS
2 /* $Header: xlacmupr.pkb 120.6 2005/10/22 00:09:38 awan ship $ */
3 /*======================================================================+
4 | Copyright (c) 2000-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_utility_profiler_pkg |
10 | |
11 | DESCRIPTION |
12 | XLA Utility profiler_Package |
13 | |
14 | Debug/Profiler activities. |
15 | |
16 | HISTORY |
17 | 12-Jan-00 P. Labrevois Created |
18 | 08-Feb-01 Created for XLA |
19 | 20-Oct-05 A.Wan Bug 4693865 remove g_keep_going |
20 +=======================================================================*/
21
22
23 /*======================================================================+
24 | |
25 | Private Procedure |
26 | |
27 | Activate_profiler |
28 | |
29 | Activate the profiler. |
30 | |
31 +======================================================================*/
32 PROCEDURE start_profiler
33
34 IS
35
36 l_cr INTEGER;
37
38 BEGIN
39 --
40 -- To be uncommented when dbms_profiler is installed
41 --
42 l_cr := dbms_profiler.start_profiler(xla_utility_pkg.g_unique_location);
43
44 NULL;
45
46 END start_profiler;
47
48
49 /*======================================================================+
50 | |
51 | Private Procedure |
52 | |
53 | Stop_profiler |
54 | |
55 | Unactivate the profiler. |
56 | |
57 +======================================================================*/
58 PROCEDURE stop_profiler
59
60 IS
61
62 l_cr INTEGER;
63
64 BEGIN
65 --
66 -- To be uncommented when dbms_profiler is installed
67 --
68 l_cr := dbms_profiler.stop_profiler;
69 l_cr := dbms_profiler.flush_data;
70
71 END stop_profiler;
72
73
74 /*======================================================================+
75 | |
76 | Private Procedure |
77 | |
78 | dump_profiler_data |
79 | |
80 | Print the information from the profiler |
81 | |
82 +======================================================================*/
83 PROCEDURE dump_profiler_data
84
85 IS
86
87 l_statement_runid VARCHAR2(4000);
88 l_statement_unit VARCHAR2(4000);
89 l_statement_data VARCHAR2(4000);
90 l_rows_runid INTEGER;
91 l_rows_unit INTEGER;
92 l_rows_data INTEGER;
93 l_cr_runid INTEGER;
94 l_cr_unit INTEGER;
95 l_cr_data INTEGER;
96 l_runid INTEGER;
97 l_unit_number INTEGER;
98 l_unit_type VARCHAR2(30);
99 l_unit_name VARCHAR2(30);
100 l_text VARCHAR2(2000);
101 l_total_occur INTEGER;
102 l_total_time INTEGER;
103 l_min_time INTEGER;
104 l_max_time INTEGER;
105 c_chr CONSTANT VARCHAR2(9) := xla_environment_pkg.g_chr_newline;
106 l_table_not_exist EXCEPTION;
107
108 PRAGMA exception_init (l_table_not_exist , -0942);
109
110 BEGIN
111 xla_utility_pkg.trace(RPAD('+',76,'-')||'+' ,-10);
112 xla_utility_pkg.trace('Profiler info' ,-10);
113
114 --
115 -- Get the runid
116 --
117 l_statement_runid := 'SELECT runid '
118 || xla_environment_pkg.g_chr_newline
119 || 'FROM plsql_profiler_runs '
120 || xla_environment_pkg.g_chr_newline
121 || 'WHERE run_comment = '''||xla_utility_pkg.g_unique_location||'''';
122
123 -- xla_utility_pkg.trace(l_statement_runid , 50);
124
125 l_cr_runid := dbms_sql.open_cursor;
126
127 dbms_sql.parse
128 (l_cr_runid
129 ,l_statement_runid
130 ,dbms_sql.native);
131
132 dbms_sql.define_column
133 (l_cr_runid
134 ,1
135 ,l_runid);
136
137 l_rows_runid := dbms_sql.execute_and_fetch(l_cr_runid);
138
139 dbms_sql.column_value
140 (l_cr_runid
141 ,1
142 ,l_runid);
143
144 dbms_sql.close_cursor(l_cr_runid);
145
146
147 --
148 -- Prepare to fetch accross all units
149 --
150 l_statement_unit := 'SELECT unit_type '
151 || xla_environment_pkg.g_chr_newline
152 || ' ,unit_name '
153 || xla_environment_pkg.g_chr_newline
154 || ' ,unit_number '
155 || xla_environment_pkg.g_chr_newline
156 || 'FROM plsql_profiler_units '
157 || xla_environment_pkg.g_chr_newline
158 || 'WHERE runid = '||l_runid||' '
159 || xla_environment_pkg.g_chr_newline
160 || ' AND unit_name NOT IN (''DBMS_PROFILER'' '
161 || xla_environment_pkg.g_chr_newline
162 || ' ,''<anonymous>'') ';
163
164 -- xla_utility_pkg.trace(l_statement_unit , 50);
165
166 l_cr_unit := dbms_sql.open_cursor;
167
168 dbms_sql.parse
169 (l_cr_unit
170 ,l_statement_unit
171 ,dbms_sql.native);
172
173 dbms_sql.define_column
174 (l_cr_unit
175 ,1
176 ,l_unit_type
177 ,255);
178
179 dbms_sql.define_column
180 (l_cr_unit
181 ,2
182 ,l_unit_name
183 ,255);
184
185 dbms_sql.define_column
186 (l_cr_unit
187 ,3
188 ,l_unit_number);
189
190 l_rows_unit := dbms_sql.execute(l_cr_unit);
191
192 --
193 -- Fetch accross all units
194 --
195
196 LOOP
197 IF dbms_sql.fetch_rows (l_cr_unit) >0 THEN
198 dbms_sql.column_value(l_cr_unit,1,l_unit_type);
199 dbms_sql.column_value(l_cr_unit,2,l_unit_name);
200 dbms_sql.column_value(l_cr_unit,3,l_unit_number);
201
202 xla_utility_pkg.trace(l_unit_name ,-10);
203 xla_utility_pkg.trace('' ,-10);
204
205 l_statement_data := 'SELECT s.text '
206 || c_chr || ' ,d.total_occur '
207 || c_chr || ' ,d.total_time '
208 || c_chr || ' ,d.min_time '
209 || c_chr || ' ,d.max_time '
210 || c_chr || 'FROM plsql_profiler_data d '
211 || c_chr || ' ,user_source s '
212 || c_chr || 'WHERE s.type = ''' || l_unit_type ||''' '
213 || c_chr || ' AND s.name = ''' || l_unit_name ||''' '
214 || c_chr || ' AND d.runid (+) = ' || l_runid
215 || c_chr || ' AND d.unit_number (+) = ' || l_unit_number
216 || c_chr || ' AND d.line# (+) = s.line '
217 || c_chr || 'ORDER BY '
218 || c_chr || ' s.line';
219
220 l_cr_data := dbms_sql.open_cursor;
221
222 dbms_sql.parse (l_cr_data
223 ,l_statement_data
224 ,dbms_sql.native);
225
226 dbms_sql.define_column
227 (l_cr_data
228 ,1
229 ,l_text
230 ,255);
231
232 dbms_sql.define_column
233 (l_cr_data
234 ,2
235 ,l_total_occur);
236
237 dbms_sql.define_column
238 (l_cr_data
239 ,3
240 ,l_total_time);
241
242 dbms_sql.define_column
243 (l_cr_data
244 ,4
245 ,l_min_time);
246
247 dbms_sql.define_column
248 (l_cr_data
249 ,5
250 ,l_max_time);
251
252 l_rows_data := dbms_sql.execute(l_cr_data);
253
254 LOOP
255 IF dbms_sql.fetch_rows (l_cr_data) >0 THEN
256
257 dbms_sql.column_value(l_cr_data, 1,l_text );
258 dbms_sql.column_value(l_cr_data, 2,l_total_occur );
259 dbms_sql.column_value(l_cr_data, 3,l_total_time );
260 dbms_sql.column_value(l_cr_data, 4,l_min_time );
261 dbms_sql.column_value(l_cr_data, 5,l_max_time );
262
263 xla_utility_pkg.trace ('! '
264 || SUBSTR(
265 RPAD(
266 REPLACE(l_text ,xla_environment_pkg.g_chr_newline,''),75,' '),1,75)
267 || ' '
268 || TO_CHAR(l_total_occur,'999999')
269 || TO_CHAR(l_total_time/1000000000 ,'99999.9999999')
270 || TO_CHAR(l_min_time /1000000000 ,'99999.9999999')
271 || TO_CHAR(l_max_time /1000000000 ,'99999.9999999'),-10);
272 ELSE
273 EXIT;
274 END IF;
275 END LOOP;
276
277 dbms_sql.close_cursor(l_cr_data);
278
279 ELSE
280 EXIT;
281 END IF;
282
283 END LOOP;
284
285 dbms_sql.close_cursor(l_cr_unit);
286
287 EXCEPTION
288 WHEN l_table_not_exist THEN
289 xla_exceptions_pkg.raise_message
290 (p_appli_s_name => 'XLA'
291 ,p_msg_name => 'XLA_DEBUG_PROFILER'
292 );
293 END dump_profiler_data;
294
295
296 END xla_utility_profiler_pkg;