[Home] [Help]
PACKAGE BODY: APPS.FND_PERFMON
Source
1 package body FND_PERFMON as
2 /* $Header: AFPMMONB.pls 115.8 2003/08/22 20:49:29 fskinner ship $ */
3
4
5
6
7 --
8 -- SET_WAIT_SAMPLE_EXPIRATION
9 -- Set the number of days that wait_samples will be stored in the db.
10 -- Then delete expired samples.
11 --
12 procedure SET_WAIT_SAMPLE_EXPIRATION(new_expire float) is
13 begin
14 update fnd_perf_variables set value = to_char(new_expire)
15 where variable = 'wait_sample_expiration';
16
17 /* Delete expired samples. */
18 delete from fnd_wait_samples
19 where snapdate < sysdate - new_expire;
20 commit;
21 end;
22
23
24
25 --
26 -- GET_WAIT_SAMPLE_EXPIRATION
27 -- Return the current expiration limit (ie: the number of days that
28 -- wait_samples will be stored in the db).
29 --
30 function GET_WAIT_SAMPLE_EXPIRATION return float is
31 wait_sample_expiration float;
32 begin
33 select to_number(value) into wait_sample_expiration
34 from fnd_perf_variables
35 where variable = 'wait_sample_expiration';
36 return(wait_sample_expiration);
37 end;
38
39
40
41 --
42 -- TAKE_WAIT_SAMPLE
43 -- Sample v$session_wait to capture:
44 -- encoded event, background or foreground, batch or real-time, snapdate,
45 -- and group similar events so it records a count for the group.
46 --
47 procedure TAKE_WAIT_SAMPLE is
48 wait_sample_expiration float;
49 begin
50
51 /* Delete expired samples. */
52 wait_sample_expiration := get_wait_sample_expiration;
53 delete from fnd_wait_samples
54 where snapdate < sysdate - wait_sample_expiration;
55
56 /* Take sample. */
57 insert into fnd_wait_samples( ct, event, detail, fgbg, rtbt, snapdate )
58 -- Identify the DB Message wait sessions
59 select count (*),
60 'DBW',
61 decode( w.wait_time, 0, decode( w.event, 'latch free', w.p2, w.p1 ), NULL ),
62 decode( s.type, 'BACKGROUND', 'B', 'F' ),
63 decode( substr( s.program, 1, 7 ),
64 ' ? @', /* real-time programs have this funny name */
65 decode( s.terminal, NULL, 'B', 'R' ),
66 'B' ),
67 sysdate
68 from v$session_wait w, v$session s
69 where s.sid = w.sid
70 and s.sid not in
71 (select s2.sid from v$session s2
72 where s2.audsid = userenv( 'SESSIONID' ))
73 and w.wait_time <> 0
74 and exists ( select 1
75 from v$session s3
76 where
77 s.paddr = s3.paddr
78 and s.sid <> s3.sid )
79 group by 2,
80 decode( w.wait_time, 0, decode( w.event, 'latch free', w.p2, w.p1 ), NULL ),
81 decode( s.type, 'BACKGROUND', 'B', 'F' ),
82 decode( substr( program, 1, 7 ),
83 ' ? @', decode( s.terminal, NULL, 'B', 'R' ),
84 'B' ),
85 sysdate
86 UNION ALL
87 -- All other cases
88 select count (*),
89 decode( w.wait_time,
90 0, decode( w.event,
91 'client message', 'CM',
92 'Null event', 'N',
93 'db file scattered read', 'DSR',
94 'db file sequential read', 'DRR',
95 'latch free', 'LF',
96 'enqueue', 'NQ',
97 'rdbms ipc message', 'RM',
98 'rdbms ipc reply', 'RR',
99 'control file sequential read', 'CSR',
100 'control file parallel write', 'CPW',
101 'db file parallel write', 'DPW',
102 'db file single write', 'DSW',
103 'log file parallel write', 'LPW',
104 'log file space/switch', 'LSS',
105 'log file sync', 'LS',
106 'library cache pin', 'LCP',
107 'library cache load lock', 'LCL',
108 'row cache lock', 'RCL',
109 'PL/SQL lock timer', 'LT',
110 'pmon timer', 'PT',
111 'smon timer', 'ST',
112 'free buffer available', 'FBA',
113 'free buffer waits', 'FBW',
114 'SQL*Net message from client', 'NMC',
115 w.event ),
116 'C' ), /* If not waiting, then likely doing CPU */
117 decode( w.wait_time, 0, decode( w.event, 'latch free', w.p2, w.p1 ), NULL ),
118 decode( s.type, 'BACKGROUND', 'B', 'F' ),
119 decode( substr( s.program, 1, 7 ),
120 ' ? @', /* real-time programs have this funny name */
121 decode( s.terminal, NULL, 'B', 'R' ),
122 'B' ),
123 sysdate
124 from v$session_wait w, v$session s
125 where s.sid = w.sid
126 and s.sid not in
127 (select s2.sid from v$session s2
128 where s2.audsid = userenv( 'SESSIONID' ))
129 and (w.wait_time = 0
130 or not exists ( select 1
131 from v$session s3
132 where
133 s.paddr = s3.paddr
134 and s.sid <> s3.sid ) )
135 group by decode( w.wait_time,
136 0, decode( w.event,
137 'client message', 'CM',
138 'Null event', 'N',
139 'db file scattered read', 'DSR',
140 'db file sequential read', 'DRR',
141 'latch free', 'LF',
142 'enqueue', 'NQ',
143 'rdbms ipc message', 'RM',
144 'rdbms ipc reply', 'RR',
145 'control file sequential read', 'CSR',
146 'control file parallel write', 'CPW',
147 'db file parallel write', 'DPW',
148 'db file single write', 'DSW',
149 'log file parallel write', 'LPW',
150 'log file space/switch', 'LSS',
151 'log file sync', 'LS',
152 'library cache pin', 'LCP',
153 'library cache load lock', 'LCL',
154 'row cache lock', 'RCL',
155 'PL/SQL lock timer', 'LT',
156 'pmon timer', 'PT',
157 'smon timer', 'ST',
158 'free buffer available', 'FBA',
159 'free buffer waits', 'FBW',
160 'SQL*Net message from client', 'NMC',
161 w.event ),
162 'C' ),
163 decode( w.wait_time, 0, decode( w.event, 'latch free', w.p2, w.p1 ), NULL ),
164 decode( s.type, 'BACKGROUND', 'B', 'F' ),
165 decode( substr( s.program, 1, 7 ),
166 ' ? @', decode( s.terminal, NULL, 'B', 'R' ),
167 'B' ),
168 sysdate;
169 commit;
170 end TAKE_WAIT_SAMPLE;
171
172
173
174
175
176 --
177 -- SET_SQL_SAMPLE_EXPIRATION
178 -- Set the number of days that sql_samples will be stored in the db.
179 -- Then delete expired samples.
180 --
181 procedure SET_SQL_SAMPLE_EXPIRATION(new_expire float) is
182 begin
183 update fnd_perf_variables set value = to_char(new_expire)
184 where variable = 'sql_sample_expiration';
185
186 /* Delete expired samples. */
187 delete from fnd_sql_samples
188 where snapdate < sysdate - new_expire;
189 commit;
190 end;
191
192
193
194 --
195 -- GET_SQL_SAMPLE_EXPIRATION
196 -- Return the current expiration limit (ie: the number of days that
197 -- sql_samples will be stored in the db).
198 --
199 function GET_SQL_SAMPLE_EXPIRATION return float is
200 sql_sample_expiration float;
201 begin
202 select to_number(value) into sql_sample_expiration
203 from fnd_perf_variables
204 where variable = 'sql_sample_expiration';
205 return(sql_sample_expiration);
206 end;
207
208
209
210 --
211 -- TAKE_SQL_SAMPLE
212 -- Sample v$session to capture
213 -- the sql currently being executed by every session.
214 --
215 procedure TAKE_SQL_SAMPLE is
216 sql_sample_expiration float;
217 begin
218
219 /* Delete expired samples. */
220 sql_sample_expiration := get_sql_sample_expiration;
221 delete from fnd_sql_samples
222 where snapdate < sysdate - sql_sample_expiration;
223
224 /* Take sample. */
225 insert into fnd_sql_samples(sql_hash_value, type, program, snapdate)
226 select s.sql_hash_value,
227 decode(w.WAIT_TIME,
228 0, decode(w.event, 'db file sequential read', 'R', 'S'),
229 'C'),
230 substr(s.program,1,8),
231 sysdate
232 from v$session s, v$session_wait w
233 where
234 w.sid = s.sid
235 and ((w.WAIT_TIME <> 0 -- CPU case
236 and not exists
237 (select 1 from v$session s3 where s.paddr = s3.paddr and s.sid <> s3.sid))
238 or
239 (w.WAIT_TIME = 0 -- I/O case
240 and w.event in ('db file scattered read', 'db file sequential read')
241 ));
242 commit;
243 end TAKE_SQL_SAMPLE;
244
245
246
247 /*
248 begin -- package init
249
250 we should init wait_sample_expiration here,
251 but a bug in dbms_job (Rel 7.1.6) re-initilizes package
252 each time the job is called.
253 So, init wait_sample_expiration when fnd_perf_variables
254 table is created.
255 set_wait_sample_expiration(7 * 6);
256 */
257
258 end FND_PERFMON;