1 package FND_STATS AUTHID CURRENT_USER as
2 /* $Header: AFSTATSS.pls 120.19.12020000.5 2012/12/13 10:29:44 msaleem ship $ */
3
4
5 AUTO_SAMPLE_SIZE NUMBER :=0; --
6
7
8 -- table having fewer blocks than this thold will be serialized
9 SMALL_TAB_FOR_PAR_THOLD NUMBER := 500;
10
11 -- table having fewer blocks than this thold will be gathered at 100%
12 SMALL_TAB_FOR_EST_THOLD NUMBER := 500;
13
14 -- index having fewer blocks than this thold will be serialized
15 SMALL_IND_FOR_PAR_THOLD NUMBER := 500;
16
17 -- index having fewer blocks than this thold will be gathered at 100%
18 SMALL_IND_FOR_EST_THOLD NUMBER := 500;
19
20 TYPE Error_Out IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
21
22 procedure CREATE_STAT_TABLE ;
23
24 procedure ENABLE_SCHEMA_MONITORING(schemaname in varchar2 default 'ALL');
25
26 procedure DISABLE_SCHEMA_MONITORING(schemaname in varchar2 default 'ALL');
27
28 /* Undocumented and for INTERNAL use only */
29 procedure CREATE_STAT_TABLE( schemaname in varchar2,
30 tabname in varchar2,
31 tblspcname in varchar2 default null);
32
33 procedure TRANSFER_STATS( errbuf OUT NOCOPY varchar2,
34 retcode OUT NOCOPY varchar2,
35 action in varchar2,
36 schemaname in varchar2,
37 tabname in varchar2,
38 stattab in varchar2 default 'FND_STATTAB',
39 statid in varchar2
40 ) ;
41
42 procedure BACKUP_SCHEMA_STATS( schemaname in varchar2,
43 statid in varchar2 default null);
44
45 procedure BACKUP_TABLE_STATS( schemaname in varchar2,
46 tabname in varchar2,
47 statid in varchar2 default 'BACKUP',
48 partname in varchar2 default null,
49 cascade in boolean default true
50 ) ;
51
52 procedure BACKUP_TABLE_STATS( errbuf OUT NOCOPY varchar2,
53 retcode OUT NOCOPY varchar2,
54 schemaname in varchar2,
55 tabname in varchar2,
56 statid in varchar2 default 'BACKUP',
57 partname in varchar2 default null,
58 cascade in boolean default true
59 ) ;
60
61 procedure RESTORE_SCHEMA_STATS( schemaname in varchar2,
62 statid in varchar2 default null);
63
64 procedure RESTORE_TABLE_STATS(ownname in varchar2,
65 tabname in varchar2,
66 statid in varchar2 default null,
67 partname in varchar2 default null,
68 cascade in boolean default true
69 );
70
71 procedure RESTORE_TABLE_STATS( errbuf OUT NOCOPY varchar2,
72 retcode OUT NOCOPY varchar2,
73 ownname in varchar2,
74 tabname in varchar2,
75 statid in varchar2 default null,
76 partname in varchar2 default null,
77 cascade in boolean default true
78 );
79
80 /* Undocumented and for INTERNAL use only */
81 procedure RESTORE_INDEX_STATS(ownname in varchar2,
82 indname in varchar2,
83 statid in varchar2 default null,
84 partname in varchar2 default null);
85
86 procedure RESTORE_COLUMN_STATS(ownname in varchar2,
87 tabname in varchar2,
88 colname in varchar2,
89 partname in varchar2 default null,
90 statid in varchar2 default null);
91
92 /* This restores the column stats for all cols specified in FND_HISTOGRAM_COLS */
93 procedure RESTORE_COLUMN_STATS(statid in varchar2 default null) ;
94
95 /* This procedure is created so that it can be called from SQL prompt
96 This is exactly same except it doesn't have the output parameter */
97 procedure GATHER_SCHEMA_STATISTICS(schemaname in varchar2,
98 -- changes done for bug 11835452
99 estimate_percent in number default null , -- default 10,
100 degree in number default null,
101 internal_flag in varchar2 default 'NOBACKUP',
102 request_id in number default null,
103 hmode in varchar2 default 'LASTRUN',
104 options in varchar2 default 'GATHER',
105 modpercent in number default 10,
106 invalidate in varchar2 default 'Y'
107 );
108
109 procedure GATHER_SCHEMA_STATS(schemaname in varchar2,
110 -- changes done for bug 11835452
111 estimate_percent in number default null, -- default 10,
112 degree in number default null,
113 internal_flag in varchar2 default 'NOBACKUP',
114 --Errors OUT NOCOPY Error_Out, -- commented to handle the error collection
115 request_id in number default null,
116 hmode in varchar2 default 'LASTRUN',
117 options in varchar2 default 'GATHER',
118 modpercent in number default 10,
119 invalidate in varchar2 default 'Y'
120 );
121
122
123
124
125
126 procedure GATHER_SCHEMA_STATS_SQLPLUS(schemaname in varchar2,
127 -- changes done for bug 11835452
128 estimate_percent in number default null, -- default 10,
129 degree in number default null,
130 internal_flag in varchar2 default 'NOBACKUP',
131 Errors OUT NOCOPY Error_Out, -- commented to handle the error collection
132 request_id in number default null,
133 hmode in varchar2 default 'LASTRUN',
134 options in varchar2 default 'GATHER',
135 modpercent in number default 10,
136 invalidate in varchar2 default 'Y'
137 );
138
139 procedure GATHER_SCHEMA_STATS(errbuf OUT NOCOPY varchar2,
140 retcode OUT NOCOPY varchar2,
141 schemaname in varchar2,
142 -- changes done for bug 11835452
143 estimate_percent in number default null, -- default 10,
144 degree in number default null,
145 internal_flag in varchar2 default 'NOBACKUP',
146 request_id in number default null,
147 hmode in varchar2 default 'LASTRUN',
148 options in varchar2 default 'GATHER',
149 modpercent in number default 10,
150 invalidate in varchar2 default 'Y'
151 );
152
153 procedure GATHER_INDEX_STATS(ownname in varchar2,
154 indname in varchar2,
155 percent in number default null,
156 degree in number default null,
157 partname in varchar2 default null,
158 backup_flag in varchar2 default 'NOBACKUP',
159 hmode in varchar2 default 'LASTRUN',
160 invalidate in varchar2 default 'Y'
161 );
162
163 procedure GATHER_TABLE_STATS(ownname in varchar2,
164 tabname in varchar2,
165 percent in number default null,
166 degree in number default null,
167 partname in varchar2 default null,
168 backup_flag in varchar2 default 'NOBACKUP',
169 cascade in boolean default true,
170 granularity in varchar2 default 'DEFAULT',
171 hmode in varchar2 default 'LASTRUN',
172 invalidate in varchar2 default 'Y'
173 );
174
175 procedure GATHER_TABLE_STATS(errbuf OUT NOCOPY varchar2,
176 retcode OUT NOCOPY varchar2,
177 ownname in varchar2,
178 tabname in varchar2,
179 percent in number default null,
180 degree in number default null,
181 partname in varchar2 default null,
182 backup_flag in varchar2 default 'NOBACKUP',
183 granularity in varchar2 default 'DEFAULT',
184 hmode in varchar2 default 'LASTRUN',
185 invalidate in varchar2 default 'Y'
186 );
187
188 procedure GATHER_COLUMN_STATS(appl_id in number default null,
189 percent in number default null,
190 degree in number default null,
191 backup_flag in varchar2 default 'NOBACKUP',
192 --Errors OUT NOCOPY Error_Out,--commented to handle the error collection
193 hmode in varchar2 default 'LASTRUN',
194 invalidate in varchar2 default 'Y'
195 );
196
197 procedure GATHER_ALL_COLUMN_STATS(ownname in varchar2,
198 percent in number default null,
199 degree in number default null,
200 hmode in varchar2 default 'LASTRUN',
201 invalidate in varchar2 default 'Y'
202 );
203
204 procedure GATHER_ALL_COLUMN_STATS(errbuf OUT NOCOPY varchar2,
205 retcode OUT NOCOPY varchar2,
206 ownname in varchar2,
207 percent in number default null,
208 degree in number default null,
209 hmode in varchar2 default 'LASTRUN',
210 invalidate in varchar2 default 'Y'
211 );
212
213 procedure GATHER_COLUMN_STATS(ownname in varchar2,
214 tabname in varchar2,
215 colname in varchar2,
216 percent in number default null,
217 degree in number default null,
218 hsize in number default 254,
219 backup_flag in varchar2 default 'NOBACKUP',
220 partname in varchar2 default null,
221 hmode in varchar2 default 'LASTRUN',
222 invalidate in varchar2 default 'Y'
223 );
224
225 procedure GATHER_COLUMN_STATS(errbuf OUT NOCOPY varchar2,
226 retcode OUT NOCOPY varchar2,
227 ownname in varchar2,
228 tabname in varchar2,
229 colname in varchar2,
230 percent in number default null,
231 degree in number default null,
232 hsize in number default 254,
233 backup_flag in varchar2 default 'NOBACKUP',
234 partname in varchar2 default null,
235 hmode in varchar2 default 'LASTRUN',
236 invalidate in varchar2 default 'Y'
237 );
238
239 /* Purges all records of the FND_STATS_HIST that fall between from_req_id and to_req_id */
240 procedure PURGE_STAT_HISTORY(from_req_id in number,
241 to_req_id in number
242 ) ;
243
244 /* Purges all records of the FND_STATS_HIST that fall between from_req_id and to_req_id */
245 procedure PURGE_STAT_HISTORY(purge_from_date in varchar2 ,
246 purge_to_date in varchar2
247 ) ;
248 procedure PURGE_STAT_HISTORY(errbuf OUT NOCOPY varchar2,
249 retcode OUT NOCOPY varchar2,
250 purge_mode in varchar2 ,
251 from_value in varchar2 ,
252 to_value in varchar2 );
253
254
255 /* Undocumented and for INTERNAL use only */
256 procedure SET_TABLE_STATS(ownname in varchar2,
257 tabname in varchar2,
258 numrows in number,
259 numblks in number,
260 avgrlen in number,
261 partname in varchar2 default null);
262
263
264 /* Undocumented and for INTERNAL use only */
265 procedure SET_INDEX_STATS(ownname in varchar2,
266 indname in varchar2,
267 numrows in number,
268 numlblks in number,
269 numdist in number,
270 avglblk in number,
271 avgdblk in number,
272 clstfct in number,
273 indlevel in number,
274 partname in varchar2 default null);
275
276 procedure LOAD_XCLUD_STATS(schemaname in varchar2);
277
278 /* This one is for a particular INTERFACE TABLE */
279 procedure LOAD_XCLUD_STATS(schemaname in varchar2,
280 tablename in varchar2);
281
282 /* This is for loading exclusion list into fnd_exclude_table_stats */
283 procedure LOAD_XCLUD_TAB(action in varchar2,
284 appl_id in number,
285 tabname in varchar2);
286
287 /* This is for internal/support purpose only. For loading/deleting SEED database */
288 /* procedure DELETE_XCLUD_IND( appl_id in number,
289 tabname in varchar2,
290 indname in varchar2,
291 partname in varchar2 default null);
292 */
293 /* This is for internal purpose only. For loading into SEED database */
294 procedure LOAD_HISTOGRAM_COLS(action in varchar2,
295 appl_id in number,
296 tabname in varchar2,
297 colname in varchar2,
298 partname in varchar2 default null,
299 hsize in number default 254,
300 commit_flag in varchar2 default 'Y' );
301
302 /* This is for internal purpose only. This is for seeding Materialized View columns For loading into SEED database */
303 procedure LOAD_HISTOGRAM_COLS_MV(action in varchar2,
304 ownername in varchar2,
305 tabname in varchar2,
306 colname in varchar2,
307 partname in varchar2 default null,
308 hsize in number default 254,
309 commit_flag in varchar2 default 'Y');
310 /* This is to check if the leading cols of non-unique indexes of
311 a list of input table_names needs histograms */
312 procedure CHECK_HISTOGRAM_COLS(tablelist in varchar2,
313 factor in integer default 75,
314 percent in number default 10,
315 degree in number default null);
316
317 /* This is to create histograms on all leading cols of non-unique indexes of all the
318 tables in a given schema */
319 procedure ANALYZE_ALL_COLUMNS(ownname in varchar2,
320 percent in number default null,
321 hsize in number default 254,
322 hmode in varchar2 default 'LASTRUN');
323 /* conc. job version of ANALYZE_ALL_COLUMNS */
324 procedure ANALYZE_ALL_COLUMNS(errbuf OUT NOCOPY varchar2,
325 retcode OUT NOCOPY varchar2,
326 ownname in varchar2,
327 percent in number default null,
328 hsize in number default 254,
329 hmode in varchar2 default 'LASTRUN');
330 /* This is for internal purpose only. For loading 11G extension stats into SEED database */
331 procedure LOAD_EXTNSTATS_COLS(action in varchar2,
332 appl_id in number,
333 owner in varchar2,
334 tabname in varchar2,
335 colname1 in varchar2,
336 colname2 in varchar2,
337 colname3 in varchar2 default null,
338 colname4 in varchar2 default null,
339 partname in varchar2 default null,
340 hsize in number default 254,
341 commit_flag in varchar2 default 'Y' );
342
343 /* Used for updating the FND_STATS_HIST with autonomous_transaction */
344 procedure UPDATE_HIST(schemaname varchar2,
345 objectname in varchar2,
346 objecttype in varchar2,
347 partname in varchar2,
348 columntablename in varchar2,
349 degree in number,
350 upd_ins_flag in varchar2,
351 percent in number default null
352 ) ;
353 /* This procedure checks tables, indexes and histograms to see if statistics exist or are stale */
354 procedure verify_stats(schemaname varchar2 default null,
355 tableList varchar2 default null,
356 days_old number default null,
357 column_stat boolean default false);
358 end FND_STATS;
[Home] [Help]