1 package body ad_stats_util_pkg as
2 /* $Header: adustatb.pls 120.1 2005/11/17 03:11:29 sgaruday noship $ */
3
4
5 --
6 -- Private program units
7 --
8
9 -- APPLSYS schema name
10
11 G_UN_FND varchar2(30) := null;
12
13 -- Minimum # of actions in the combined candidate starter patch-runs, that
14 -- when exceeded, should be deemed as a "big patch" (primarily for purposes of
15 -- switching from NESTED-LOOP to HASH-JOIN's).
16
17 G_BIG_PATCH_THRESHOLD constant number := 10000;
18 -- G_BIG_PATCH_THRESHOLD constant number := 2; --testing. %% comment out later
19
20
21 --
22 --
23 -- Debug utils START
24 --
25 G_DEBUG constant boolean := FALSE; --%%set to FALSE in production code
26
27 procedure put_line
28 (msg varchar2,
29 len number default 80)
30 is
31 n number := 1;
32 nmax number;
33 begin
34 nmax := nvl(length(msg), 0);
35 if not G_DEBUG then
36 return;
37 end if;
38
39 loop
40 -- dbms_output.put_line(substr(msg, n, len)); --%%comment out in prodn code
41 n := n + len;
42 exit when n > nmax;
43 end loop;
44 end put_line;
45 --
46 -- Debug utils END
47 --
48 --
49
50
51 procedure gather_stats
52 (p_subsystem_code varchar2)
53 is
54 exist_flag varchar2(6) :=null;
55 begin
56 if G_DEBUG then
57 put_line('Gathering stats on: '||p_subsystem_code);
58 end if;
59
60 if p_subsystem_code = 'PATCH_HIST' then
61 begin
62 select distinct('EXIST')
63 into exist_flag
64 from FND_HISTOGRAM_COLS
65 where application_id = 50
66 and table_name = 'AD_PATCH_COMMON_ACTIONS'
67 and column_name = 'ACTION_CODE';
68
69 exception
70 when no_data_found then
71 fnd_stats.load_histogram_cols('INSERT', 50, 'AD_PATCH_COMMON_ACTIONS',
72 'ACTION_CODE',NULL, hsize=>250);
73 when others then
74 raise_application_error(-20000 ,sqlerrm||': Error getting hisotgram
75 column from FND_HISTOGRAM_COLS');
76 end;
77
78 -- Bug: 4661028. sgaruday
79 begin
80 select distinct('EXIST')
81 into exist_flag
82 from FND_HISTOGRAM_COLS
83 where application_id = 50
84 and table_name = 'AD_FILES'
85 and column_name = 'IS_FLAGGED_FILE';
86
87 exception
88 when no_data_found then
89 fnd_stats.load_histogram_cols('INSERT', 50, 'AD_FILES',
90 'IS_FLAGGED_FILE', NULL, hsize=>250);
91 when others then
92 raise_application_error(-20000 ,sqlerrm||': Error getting hisotgram
93 column from FND_HISTOGRAM_COLS');
94 end;
95 --
96
97 fnd_stats.gather_table_stats(G_UN_FND, 'AD_RELEASES');
98 fnd_stats.gather_table_stats(G_UN_FND, 'AD_APPL_TOPS');
99 fnd_stats.gather_table_stats(G_UN_FND, 'AD_APPLIED_PATCHES');
100 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_DRIVERS');
101 fnd_stats.gather_table_stats(G_UN_FND, 'AD_COMPRISING_PATCHES');
102 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_DRIVER_MINIPKS');
103 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_DRIVER_LANGS');
104 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_RUNS');
105 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_RUN_BUGS');
106 fnd_stats.gather_table_stats(G_UN_FND, 'AD_BUGS');
107 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_COMMON_ACTIONS');
108 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_RUN_BUG_ACTIONS');
109 fnd_stats.gather_table_stats(G_UN_FND, 'AD_FILE_VERSIONS');
110 fnd_stats.gather_table_stats(G_UN_FND, 'AD_FILES');
111 elsif p_subsystem_code = 'SNAPSHOT' then
112 fnd_stats.gather_table_stats(G_UN_FND, 'AD_SNAPSHOTS');
113 fnd_stats.gather_table_stats(G_UN_FND, 'AD_SNAPSHOT_BUGFIXES');
114 fnd_stats.gather_table_stats(G_UN_FND, 'AD_SNAPSHOT_FILES');
115 elsif p_subsystem_code = 'TASK_TIMING' then
116 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PROGRAM_RUN_TASKS');
117 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PROGRAM_RUN_TASK_JOBS');
118 fnd_stats.gather_table_stats(G_UN_FND, 'AD_PATCH_RUN_SESS_ATTRIBS');
119 else
120 raise_application_error(-20000, 'Invalid p_subsystem_code: '||
121 p_subsystem_code);
122 end if;
123
124 end gather_stats;
125
126 procedure validate_subsystem
127 (p_subsystem_code varchar2)
128 is
129 begin
130 if p_subsystem_code not in ('PATCH_HIST', 'SNAPSHOT','TASK_TIMING') then
131 raise_application_error(-20000, 'Invalid p_subsystem_code: '||
132 p_subsystem_code);
133 end if;
134 end validate_subsystem;
135
136 procedure validate_increment
137 (p_increment number)
138 is
139 begin
140 if (p_increment >= 0 and trunc(p_increment) = p_increment) then
141 return;
142 else
143 raise_application_error(-20000, 'Invalid increment: '||
144 to_char(p_increment));
145 end if;
146 end validate_increment;
147
148
149 --
150 -- Public program units
151 --
152
153 procedure gather_stats_if_necessary
154 (p_subsystem_code varchar2,
155 p_rows_inserted_this_run number,
156 p_gather_stats_flag boolean,
157 p_commit_flag boolean,
158 p_gathered_stats_flag out NOCOPY boolean)
159 is
160 l_count_when_last_analyzed number := 0;
161 l_count_till_last_run number := 0;
162 l_newcount number := 0;
163 l_found boolean := FALSE;
164 begin
165 -- fail if p_gather_stats_flag is TRUE but p_commit_flag is FALSE.
166
167 if p_gather_stats_flag and (not p_commit_flag) then
168 raise_application_error(-20000,
169 'Invalid args. Cannot expect to gather stats and not commit.');
170 end if;
171
172 p_gathered_stats_flag := FALSE;
173
174 validate_subsystem(p_subsystem_code);
175 validate_increment(p_rows_inserted_this_run);
176
177 begin
178 select
179 nvl(to_number(attribute1), 0),
180 nvl(to_number(attribute2), 0)
181 into
182 l_count_when_last_analyzed,
183 l_count_till_last_run
184 from ad_timestamps
185 where type = 'COUNTS_FOR_ANALYZE'
186 and attribute = p_subsystem_code;
187
188 l_found := TRUE;
189
190 exception when no_data_found then
191 l_count_till_last_run := 0;
192 l_count_when_last_analyzed := 0;
193 end;
194
195 l_newcount := l_count_till_last_run + p_rows_inserted_this_run;
196
197 if l_found then
198
199 if G_DEBUG then
200 put_line('bumping up count-till-date by '||
201 to_char(p_rows_inserted_this_run));
202 end if;
203
204 if p_gather_stats_flag and
205 (l_newcount - l_count_when_last_analyzed > G_BIG_PATCH_THRESHOLD) then
206
207 gather_stats(p_subsystem_code);
208
209 p_gathered_stats_flag := TRUE;
210
211 -- save both counts (count-till-date and count-when-last-analyzed) in
212 -- AD_TIMESTAMPS.
213
214 update ad_timestamps
215 set attribute1 = to_char(l_newcount),
216 attribute2 = to_char(l_newcount),
217 timestamp = sysdate
218 where type = 'COUNTS_FOR_ANALYZE'
219 and attribute = p_subsystem_code;
220
221 else
222
223 -- save (only) the count-till-date in AD_TIMESTAMPS.
224
225 update ad_timestamps
226 set attribute2 = to_char(l_newcount),
227 timestamp = sysdate
228 where type = 'COUNTS_FOR_ANALYZE'
229 and attribute = p_subsystem_code;
230
231 end if;
232
233 else -- if not found
234
235 -- we could fail here. lets not do that. rather, lets insert an "initial"
236 -- row. Why? well, its not a big deal. The next run onwards should be ok.
237
238 if G_DEBUG then
239 put_line('creating initial row');
240 end if;
241
242 if p_gather_stats_flag and
243 (l_newcount - l_count_when_last_analyzed > G_BIG_PATCH_THRESHOLD) then
244
245 gather_stats(p_subsystem_code);
246
247 p_gathered_stats_flag := TRUE;
248
249 l_count_when_last_analyzed := l_newcount;
250 end if;
251
252 begin
253 insert into ad_timestamps
254 (
255 type, attribute, timestamp,
256 attribute1, attribute2
257 ) values
258 (
259 'COUNTS_FOR_ANALYZE', p_subsystem_code, sysdate,
260 to_char(l_count_when_last_analyzed), to_char(l_newcount)
261 );
262
263 exception when dup_val_on_index then
264 -- Handle the corner case where we didn't see a row when we "saw", but
265 -- someone else inserted after that. We dont want to fail in such a
266 -- case. Rather, we just ignore and continue on. It would seem like we
267 -- could handle this with select-for-update, etc. But not really, bcoz
268 -- we do a DDL in between (gather stats). QED.
269
270 null;
271 end;
272
273 end if; -- End If not found
274
275
276 -- If p_gather_stats_flag is TRUE, then commit regardless of p_commit_flag.
277
278 if p_gather_stats_flag or p_commit_flag then
279 commit;
280 end if;
281
282 end gather_stats_if_necessary;
283
284 --
285 --
286 --
287
288 begin
289 -- initialization code
290
291 declare
292 l_stat varchar2(1);
293 l_ind varchar2(1);
294 begin
295 if not FND_INSTALLATION.Get_App_Info('FND', l_stat, l_ind, G_UN_FND) then
296 raise_application_error(-20000, 'Error calling Get_App_Info().');
297 end if;
298 end;
299
300 end ad_stats_util_pkg;
[Home] [Help]