[Home] [Help]
PACKAGE BODY: APPS.ASG_PERF
Source
1 PACKAGE BODY asg_perf AS
2 /*$Header: asgperfb.pls 120.2 2011/01/10 11:21:15 saradhak ship $*/
3
4 -- DESCRIPTION
5 -- Contains functions to report synch performance
6 --
7 --
8 -- HISTORY
9 -- 02-jun-2004 ssabesan Merge 115.3.1158.4 into main line (11.5.9.6)
10 -- Change literal to bind variables.
11 -- 20-Dec-2002 rsripada Fix Invalid Number error
12 -- 19-Nov-2002 rsripada Use FND Logging.
13 -- 07-Nov-2002 rsripada Created
14
15 -- 8 space double tab
16 g_dtab VARCHAR2(128) := ' ';
17 g_user_name VARCHAR2(100);
18 g_first_synch VARCHAR2(1);
19 g_pub_item VARCHAR2(30);
20 g_log_enabled VARCHAR2(1) := 'N';
21 g_last_tran_id NUMBER;
22 g_curr_tran_id NUMBER;
23
24 PROCEDURE log(p_mesg IN VARCHAR2)
25 IS
26 BEGIN
27 /*
28 IF (g_log_enabled = 'N') THEN
29 dbms_output.enable(100000);
30 g_log_enabled := 'Y';
31 END IF;
32 dbms_output.put_line(p_mesg);
33 */
34 asg_helper.log(p_mesg, 'asg_perf', FND_LOG.LEVEL_EVENT);
35 END log;
36
37 PROCEDURE log_newline
38 IS
39 BEGIN
40 /*
41 IF (g_log_enabled = 'N') THEN
42 dbms_output.enable(100000);
43 g_log_enabled := 'Y';
44 END IF;
45 -- Trick dbms_output to write a blank line
46 dbms_output.put(CHR(10));
47 */
48 asg_helper.log(' ', 'asg_perf', FND_LOG.LEVEL_EVENT);
49 END log_newline;
50
51 PROCEDURE print_header
52 IS
53 l_db_instance VARCHAR2(30);
54 BEGIN
55
56 select instance_name into l_db_instance
57 from v$instance;
58
59 log('Synch Query Performance Report');
60 log_newline();
61 log('User: ' || g_user_name);
62 log('Database: ' || l_db_instance);
63
64 IF (g_first_synch = asg_base.G_YES) THEN
65 log('Type of Synch: COMPLETE REFRESH');
66 ELSE
67 log('Type of Synch: INCREMENTAL REFRESH');
68 END IF;
69
70 IF g_pub_item IS NOT NULL THEN
71 log('Publication Item: ' || g_pub_item);
72 END IF;
73
74 log_newline();
75 log_newline();
76
77 END print_header;
78
79 PROCEDURE print_summary
80 IS
81 BEGIN
82 log_newline();
83 log_newline();
84 log('Summary: ');
85 log('Total Num of rows: ' || g_total_rows);
86 log('Time in download_init (sec): ' || g_dtab ||
87 g_dtab || g_dtab || to_char(g_download_init_time));
88 log('Total query time (sec) : ' || g_dtab || g_dtab || g_dtab ||
89 g_dtab || to_char(g_total_elapsed_query_time));
90 log('Total Time (sec) : ' || g_dtab || g_dtab || g_dtab ||
91 g_dtab || g_dtab || to_char(g_total_elapsed_time));
92 END print_summary;
93
94 PROCEDURE setup_pub_item_download(p_user_name IN VARCHAR2,
95 p_pub_item IN VARCHAR2,
96 p_first_synch IN VARCHAR2)
97 IS
98 l_begin_date DATE;
99 l_end_date DATE;
100 l_comp_ref VARCHAR2(1);
101 l_query_string VARCHAR2(512);
102 BEGIN
103
104 IF(p_first_synch = asg_base.G_YES) THEN
105 l_comp_ref := 'Y';
106 ELSE
107 l_comp_ref := 'N';
108 END IF;
109
110 l_query_string := 'insert into ' || asg_base.G_OLITE_SCHEMA ||
111 '.c$pub_list_q(name, comp_ref) ' ||
112 ' values (:1, :2)';
113 EXECUTE IMMEDIATE l_query_string
114 USING p_pub_item, l_comp_ref;
115
116 IF p_first_synch = asg_base.G_YES THEN
117 g_last_tran_id := -1;
118 g_curr_tran_id := 1;
119 ELSE
120 SELECT max(transaction_id) into g_last_tran_id
121 FROM asg_system_dirty_queue
122 WHERE client_id = p_user_name;
123 IF g_last_tran_id IS NULL THEN
124 g_last_tran_id := 1;
125 END IF;
126 g_curr_tran_id := g_last_tran_id +1;
127 END IF;
128
129 log('Download Init:');
130
131 select sysdate into l_begin_date from dual;
132 log('Begin time: ' ||
133 to_char(l_begin_date, 'DD-MON-YYYY HH24:MI:SS'));
134 apps.asg_cons_qpkg.download_init(p_user_name, g_last_tran_id,
135 g_curr_tran_id, 'N');
136 select sysdate into l_end_date from dual;
137 log('End time: ' ||
138 to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS'));
139
140 g_elapsed_time_in_days := l_end_date-l_begin_date;
141 -- Convert the elapsed time in seconds
142 g_elapsed_time := g_elapsed_time_in_days*60*60*24;
143 g_download_init_time := g_elapsed_time;
144 log('Time in download_init (sec): ' || g_dtab || g_dtab ||
145 g_dtab || g_dtab || to_char(g_download_init_time));
146 log_newline();
147
148 END setup_pub_item_download;
149
150 PROCEDURE compute_pub_item_time(p_pub_item IN VARCHAR2)
151 IS
152 l_begin_date DATE;
153 l_end_date DATE;
154 l_query_string VARCHAR2 (512);
155 begin
156 select sysdate into l_begin_date from dual;
157 log('Begin time: ' ||
158 to_char(l_begin_date, 'DD-MON-YYYY HH24:MI:SS'));
159 l_query_string := 'SELECT count(*) ' ||
160 ' FROM mobileadmin.ctm$' ||p_pub_item ||
161 ' WHERE clid$$cs = :1 AND ' ||
162 ' tranid$$ > :2';
163
164 EXECUTE IMMEDIATE l_query_string INTO g_num_rows
165 USING g_user_name, g_last_tran_id;
166 select sysdate into l_end_date from dual;
167 log('End time: ' ||
168 to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS'));
169 log('Num of rows: ' || g_num_rows);
170 g_elapsed_time_in_days := l_end_date-l_begin_date;
171 -- Convert the elapsed time in seconds
172 g_elapsed_time := g_elapsed_time_in_days*60*60*24;
173 log('Query Time (sec): ' || g_dtab || g_dtab || g_dtab ||
174 g_dtab || g_dtab || to_char(g_elapsed_time));
175 END compute_pub_item_time;
176
177 PROCEDURE setup_download(p_user_name IN VARCHAR2,
178 p_first_synch IN VARCHAR2)
179 IS
180 l_begin_date DATE;
181 l_end_date DATE;
182 l_comp_ref VARCHAR2(1);
183 l_query_string VARCHAR2(512);
184 BEGIN
185
186 IF(p_first_synch = asg_base.G_YES) THEN
187 l_comp_ref := 'Y';
188 ELSE
189 l_comp_ref := 'N';
190 END IF;
191
192 l_query_string := 'insert into ' || asg_base.G_OLITE_SCHEMA ||
193 '.c$pub_list_q(name, comp_ref) ' ||
194 ' select publication_item, ''' || l_comp_ref || '''' ||
195 ' from ' || asg_base.G_OLITE_SCHEMA ||
196 '.c$all_client_items ' ||
197 ' where clientid = :1 ' ||
198 ' and publication_item in ' ||
199 ' (select name from asg_pub_item)';
200 EXECUTE IMMEDIATE l_query_string
201 USING p_user_name;
202
203 IF p_first_synch = asg_base.G_YES THEN
204 g_last_tran_id := -1;
205 g_curr_tran_id := 1;
206 ELSE
207 SELECT max(transaction_id) into g_last_tran_id
208 FROM asg_system_dirty_queue
209 WHERE client_id = p_user_name;
210 IF g_last_tran_id IS NULL THEN
211 g_last_tran_id := 1;
212 END IF;
213 g_curr_tran_id := g_last_tran_id +1;
214 END IF;
215
216 log('Download Init:');
217 --log('Last tranid: ' || g_last_tran_id ||
218 -- ' Current tranid: ' || g_curr_tran_id);
219
220 select sysdate into l_begin_date from dual;
221 log('Begin time: ' ||
222 to_char(l_begin_date, 'DD-MON-YYYY HH24:MI:SS'));
223 apps.asg_cons_qpkg.download_init(p_user_name, g_last_tran_id,
224 g_curr_tran_id, 'N');
225 select sysdate into l_end_date from dual;
226 log('End time: ' ||
227 to_char(l_end_date, 'DD-MON-YYYY HH24:MI:SS'));
228
229 g_elapsed_time_in_days := l_end_date-l_begin_date;
230 -- Convert the elapsed time in seconds
231 g_elapsed_time := g_elapsed_time_in_days*60*60*24;
232 g_download_init_time := g_elapsed_time;
233 log('Time in download_init (sec): ' || g_dtab || g_dtab ||
234 g_dtab || g_dtab || to_char(g_download_init_time));
235 log_newline();
236
237 END setup_download;
238
239 PROCEDURE compute_download_time
240 IS
241 l_cursor_id NUMBER;
242 l_cursor_ret NUMBER;
243 l_select_pi_sqlstring VARCHAR2(4000);
244 l_curr_pub_item VARCHAR2(30);
245 begin
246
247 l_select_pi_sqlstring :=
248 'SELECT name ' ||
249 'FROM ' || asg_base.G_OLITE_SCHEMA ||'.' ||'c$pub_list_q';
250
251 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
252 DBMS_SQL.PARSE (l_cursor_id, l_select_pi_sqlstring, DBMS_SQL.v7);
253 DBMS_SQL.DEFINE_COLUMN (l_cursor_id, 1, l_curr_pub_item, 30);
254
255 l_cursor_ret := DBMS_SQL.EXECUTE (l_cursor_id);
256 WHILE ( DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 ) LOOP
257 DBMS_SQL.COLUMN_VALUE (l_cursor_id, 1, l_curr_pub_item);
258 log(l_curr_pub_item);
259 compute_pub_item_time(l_curr_pub_item);
260 g_total_rows := g_total_rows + g_num_rows;
261 g_total_elapsed_query_time := g_total_elapsed_query_time + g_elapsed_time;
262 END LOOP;
263
264 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
265
266 g_total_elapsed_time := g_download_init_time + g_total_elapsed_query_time;
267
268 END compute_download_time;
269
270 PROCEDURE cleanup_setup
271 IS
272 l_query_string VARCHAR2(512);
273 BEGIN
274 l_query_string := 'DELETE FROM ' || asg_base.G_OLITE_SCHEMA ||
275 '.c$pub_list_q';
276 EXECUTE IMMEDIATE l_query_string;
277 asg_base.reset_all_globals();
278 asg_download.reset_all_globals();
279 rollback;
280 END cleanup_setup;
281
282
283 -- Procedure to report the download init and query time statistics
284 -- for the specified user's first synch
285 PROCEDURE get_first_synch_report(p_user_name IN VARCHAR2)
286 IS
287 BEGIN
288 g_user_name := upper(p_user_name);
289 g_first_synch := asg_base.G_YES;
290 g_pub_item := null;
291
292 print_header();
293 setup_download(g_user_name, g_first_synch);
294 compute_download_time();
295 print_summary();
296 cleanup_setup;
297
298 END get_first_synch_report;
299
300 -- Procedure to report the download init and query time statistics
301 -- for the specified user's incremental synch
302 PROCEDURE get_incremental_synch_report(p_user_name IN VARCHAR2)
303 IS
304 BEGIN
305 g_user_name := upper(p_user_name);
306 g_first_synch := asg_base.G_NO;
307 g_pub_item := null;
308
309 print_header();
310 setup_download(g_user_name, g_first_synch);
311 compute_download_time();
312 print_summary();
313 cleanup_setup;
314
315 END get_incremental_synch_report;
316
317 -- Procedure to report the download init and query time statistics
318 -- for the specified user and publication-item's first synch
319 PROCEDURE get_first_synch_report(p_user_name IN VARCHAR2,
320 p_pub_item IN VARCHAR2)
321 IS
322 BEGIN
323 g_user_name := upper(p_user_name);
324 g_first_synch := asg_base.G_YES;
325 g_pub_item := upper(p_pub_item);
326
327 print_header();
328 setup_pub_item_download(g_user_name, g_pub_item, g_first_synch);
329 compute_pub_item_time(g_pub_item);
330 print_summary();
331 cleanup_setup;
332
333 END get_first_synch_report;
334
335 -- Procedure to report the download init and query time statistics
336 -- for the specified user and publication item's incremental synch
337 PROCEDURE get_incremental_synch_report(p_user_name IN VARCHAR2,
338 p_pub_item IN VARCHAR2)
339 IS
340 BEGIN
341 g_user_name := upper(p_user_name);
342 g_first_synch := asg_base.G_NO;
343 g_pub_item := upper(p_pub_item);
344
345 print_header();
346 setup_pub_item_download(g_user_name, g_pub_item, g_first_synch);
347 compute_pub_item_time(g_pub_item);
348 print_summary();
349 cleanup_setup;
350
351 END get_incremental_synch_report;
352
353 end;