1 PACKAGE BODY fnd_conc_stat AS
2 /* $Header: AFAMRSCB.pls 115.8 2003/11/21 22:19:47 pferguso ship $ */
3
4
5 -- Overview of statistic collection:
6
7 -- A concurrent request may be comprised of one or two
8 -- resource-consuming processes: a sql*net shadow process which consumes
9 -- database server resources, and a front-end process (such as a C
10 -- executable). We collect data for both types of processes. For the
11 -- front-end process, we currently collect only the CPU time used. For a
12 -- complete list of the statistics available for the shadow process,
13 -- select * from the V$STATNAME view.
14
15 -- There are four levels of collection, set at the program level in
16 -- the STAT_COLLECT column of FND_CONCURRENT_PROGRAMS:
17 --
18 -- O = Off
19 -- S = Standard
20 -- E = Extra which includes S
21 -- A = All
22 --
23 -- The statistics corresponding to these levels are set in the table
24 -- FND_CONC_STAT_LIST. The STATISTIC# is from V$STATNAME and the
25 -- COLLECT_LEVEL is 'S' or 'E'. The level 'E' is made up of all
26 -- those in S plus any in E. So S is included in E. If the system
27 -- profile CONC_REQUEST_STAT <> 'Y', or if STAT_COLLECT is NULL or 'O'
28 -- no statistics will be collected for that program. The front-end CPU
29 -- statistics do not correspond to any entries in V$STATNAME.
30 -- These correspond to the statistics: -1 "system seconds",
31 -- -2 "system microseconds", -3 "user seconds",
32 -- -4 "user microseconds", and -5 "real-world seconds".
33
34 -- stat_collect_level:
35 -- Called by 'collect()', 'put_frontend_cpu()' ,'store_initial()',
36 -- and 'store_final()'
37 -- Do we collect statistics ? If so, at what level.
38 -- IS the 'CONC_REQUEST_STAT' profile SET to 'Y' AND
39 -- IS STAT_COLLECT not set to NULL ?
40 -- IF SO, return STAT_COLLECT, otherwise return 'O' for off
41 --
42 -- NOTE: STAT_COLLECT values are :
43 -- NULL ( unchanged )
44 -- O = off
45 -- S = Standard
46 -- E = Extra which includes S
47 -- A = All
48
49 --
50 -- PRIVATE VARIABLES
51 --
52 P_CPU_REQ_ID integer := null;
53 P_SHADOW_REQ_ID integer := null;
54
55
56 FUNCTION stat_collect_level RETURN VARCHAR2 IS
57 crs VARCHAR2(80);
58 f_stat_collect VARCHAR2(1);
59 BEGIN
60 fnd_profile.get('CONC_REQUEST_STAT', crs);
61 IF crs = 'Y' THEN
62 SELECT stat_collect
63 INTO f_stat_collect
64 FROM fnd_concurrent_programs
65 WHERE concurrent_program_id = fnd_global.conc_program_id
66 AND application_id = fnd_global.prog_appl_id;
67 IF f_stat_collect IS NULL THEN
68 RETURN 'O';
69 ELSE
70 RETURN f_stat_collect;
71 END IF;
72 ELSE
73 RETURN 'O';
74 END IF;
75 END stat_collect_level;
76
77
78 FUNCTION rt_perf_stat_enabled RETURN BOOLEAN IS
79 rt_perf BOOLEAN := FALSE;
80 rt_perf_val VARCHAR2(1);
81 BEGIN
82 if( fnd_profile.defined('RT_PERF_STAT') ) then
83 fnd_profile.get('RT_PERF_STAT', rt_perf_val );
84 if ( rt_perf_val = 'Y' ) then
85 rt_perf := TRUE;
86 end if;
87 end if;
88 return rt_perf;
89 END;
90
91
92 -- put_frontend_cpu:
93 -- Called by the frontend process to insert CPU time
94 -- and real seconds into the stats tables.
95 -- Frontend process computes CPU time on its
96 -- own; this is just an insert statement we do here.
97
98 PROCEDURE put_frontend_cpu(sys_sec IN NUMBER,
99 sys_mic IN NUMBER,
100 usr_sec IN NUMBER,
101 usr_mic IN NUMBER)
102 IS
103 p_req_id NUMBER;
104 p_actual_start_date DATE;
105 BEGIN
106 p_req_id := fnd_global.conc_request_id;
107 if P_CPU_REQ_ID = p_req_id then
108 return;
109 else
110 P_CPU_REQ_ID := p_req_id;
111 end if;
112
113 IF stat_collect_level <> 'O' THEN
114
115 SELECT actual_start_date
116 INTO p_actual_start_date
117 FROM fnd_concurrent_requests fcr
118 WHERE fcr.request_id = p_req_id;
119
120 INSERT INTO fnd_conc_req_stat (req_id, process_type, statistic#, value)
121 VALUES (p_req_id, 'F', SYS_SEC_ID, sys_sec);
122
123 INSERT INTO fnd_conc_req_stat (req_id, process_type, statistic#, value)
124 VALUES (p_req_id, 'F', SYS_MIC_ID, sys_mic);
125
126 INSERT INTO fnd_conc_req_stat (req_id, process_type, statistic#, value)
127 VALUES (p_req_id, 'F', USR_SEC_ID, usr_sec);
128
129 INSERT INTO fnd_conc_req_stat (req_id, process_type, statistic#, value)
130 VALUES (p_req_id, 'F', USR_MIC_ID, usr_mic);
131
132 INSERT INTO fnd_conc_req_stat (req_id, process_type, statistic#, value)
133 VALUES (p_req_id, 'F', REAL_SEC_ID, (SYSDATE - p_actual_start_date ) * DAYSECS );
134 END IF;
135
136 END put_frontend_cpu;
137
138
139 -- collect:
140 -- collect stats for a shadow process
141 -- "Collect" is run after request completion, if a
142 -- request runs in its own database session.
143 -- see also store_initial and store_final
144 -- NOTE: collection levels decode to
145 -- S = Standard
146 -- E = Extra which includes S
147 -- A = All
148
149 PROCEDURE collect IS
150 p_req_id NUMBER;
151 p_stat_collect VARCHAR2(1);
152 session NUMBER;
153 BEGIN
154
155 p_req_id := fnd_global.conc_request_id;
156
157 if P_SHADOW_REQ_ID = p_req_id then
158 return;
159 else
160 P_SHADOW_REQ_ID := p_req_id;
161 end if;
162 p_stat_collect := stat_collect_level;
163
164 IF p_stat_collect <> 'O' THEN
165
166 SELECT sid
167 INTO session
168 FROM v$session
169 WHERE audsid = userenv('SESSIONID');
170
171 IF p_stat_collect = 'S' THEN
172 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
173 SELECT p_req_id, 'S', slist.statistic#, vstat.value
174 FROM v$sesstat vstat, fnd_conc_stat_list slist
175 WHERE vstat.sid = session
176 AND slist.statistic# = vstat.statistic#
177 AND slist.collect_level = 'S';
178 ELSIF p_stat_collect = 'E' THEN
179 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
180 SELECT p_req_id, 'S', slist.statistic#, vstat.value
181 FROM v$sesstat vstat, fnd_conc_stat_list slist
182 WHERE vstat.sid = session
183 AND slist.statistic# = vstat.statistic#;
184 ELSIF p_stat_collect = 'A' THEN
185 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
186 SELECT p_req_id, 'S', vstat.statistic#, vstat.value
187 FROM v$sesstat vstat
188 WHERE vstat.sid = session;
189 END IF;
190
191 END IF;
192
193 -- Call performace package.
194 if ( rt_perf_stat_enabled ) then
195 fnd_apd.collect;
196 end if;
197
198 END collect;
199
200 -- store_initial:
201 -- store_initial and store_final are used if a request uses
202 -- the manager's existing session.
203 -- store_initial is called prior to request execution and store_final
204 -- is used immediately after the request completes.
205 -- Store_initial stores the initial values, store_final stores the
206 -- difference between the initial values and the values at the
207 -- time the request completes
208
209 PROCEDURE store_initial IS
210 p_req_id NUMBER;
211 p_stat_collect VARCHAR2(1);
212 session NUMBER;
213 BEGIN
214
215 p_stat_collect := stat_collect_level;
216
217 IF p_stat_collect <> 'O' THEN
218
219 p_req_id := fnd_global.conc_request_id;
220
221 SELECT sid
222 INTO session
223 FROM v$session
224 WHERE audsid = userenv('SESSIONID');
225
226 IF p_stat_collect = 'S' THEN
227 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
228 SELECT - p_req_id, 'S', slist.statistic#, vstat.value
229 FROM v$sesstat vstat, fnd_conc_stat_list slist
230 WHERE vstat.sid = session
231 AND slist.statistic# = vstat.statistic#
232 AND slist.collect_level = 'S';
233 ELSIF p_stat_collect = 'E' THEN
234 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
235 SELECT - p_req_id, 'S', slist.statistic#, vstat.value
236 FROM v$sesstat vstat, fnd_conc_stat_list slist
237 WHERE vstat.sid = session
238 AND slist.statistic# = vstat.statistic#;
239 ELSIF p_stat_collect = 'A' THEN
240 INSERT INTO fnd_conc_req_stat(req_id, process_type, statistic#, value)
241 SELECT - p_req_id, 'S', vstat.statistic#, vstat.value
242 FROM v$sesstat vstat
243 WHERE vstat.sid = session;
244 END IF;
245
246 END IF; -- end of if p_stat_collect.
247
248 -- Call performace package.
249 if ( rt_perf_stat_enabled ) then
250 fnd_apd.store_initial;
251 end if;
252
253 END store_initial;
254
255 -- store_final:
256 -- store_initial and store_final are used if a request uses
257 -- the manager's existing session.
258 -- store_initial is called prior to request execution and store_final
259 -- is used immediately after the request completes.
260 -- Store_initial stores the initial values, store_final stores the
261 -- difference between the initial values and the values at the
262 -- time the request completes
263
264 PROCEDURE store_final IS
265
266 CURSOR initial_values IS
267 SELECT statistic#, value
268 FROM fnd_conc_req_stat
269 WHERE req_id = - fnd_global.conc_request_id;
270
271 curr_val NUMBER;
272 p_req_id NUMBER;
273 session NUMBER;
274 BEGIN
275
276 IF stat_collect_level <> 'O' THEN
277
278 p_req_id := fnd_global.conc_request_id;
279
280 SELECT sid
281 INTO session
282 FROM v$session
283 WHERE audsid = userenv('SESSIONID');
284
285 FOR oldstat IN initial_values LOOP
286
287 SELECT value
288 INTO curr_val
289 FROM v$sesstat vstat
290 WHERE vstat.sid = session
291 AND statistic# = oldstat.statistic#;
292
293 UPDATE fnd_conc_req_stat
294 SET value = curr_val - oldstat.value, req_id = p_req_id
295 WHERE req_id = - p_req_id
296 AND statistic# = oldstat.statistic#;
297
298 END LOOP;
299
300 END IF; -- end of if p_stat_collect.
301
302 -- Call performace package.
303 if ( rt_perf_stat_enabled ) then
304 fnd_apd.store_final;
305 end if;
306
307 END store_final;
308
309 END fnd_conc_stat;
[Home] [Help]