1 package FND_STATS as
2 /* $Header: AFSTATSS.pls 120.7.12010000.4 2009/02/09 10:15:15 suchauha 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 estimate_percent in number default 10,
99 degree in number default null,
100 internal_flag in varchar2 default 'NOBACKUP',
101 request_id in number default null,
102 hmode in varchar2 default 'LASTRUN',
103 options in varchar2 default 'GATHER',
104 modpercent in number default 10,
105 invalidate in varchar2 default 'Y'
106 );
107
108 procedure GATHER_SCHEMA_STATS(schemaname in varchar2,
109 estimate_percent in number default 10,
110 degree in number default null,
111 internal_flag in varchar2 default 'NOBACKUP',
112 --Errors OUT NOCOPY Error_Out, -- commented to handle the error collection
113 request_id in number default null,
114 hmode in varchar2 default 'LASTRUN',
115 options in varchar2 default 'GATHER',
116 modpercent in number default 10,
117 invalidate in varchar2 default 'Y'
118 );
119
120
121
122
123
124 procedure GATHER_SCHEMA_STATS_SQLPLUS(schemaname in varchar2,
125 estimate_percent in number default 10,
126 degree in number default null,
127 internal_flag in varchar2 default 'NOBACKUP',
128 Errors OUT NOCOPY Error_Out, -- commented to handle the error collection
129 request_id in number default null,
130 hmode in varchar2 default 'LASTRUN',
131 options in varchar2 default 'GATHER',
132 modpercent in number default 10,
133 invalidate in varchar2 default 'Y'
134 );
135
136 procedure GATHER_SCHEMA_STATS(errbuf OUT NOCOPY varchar2,
137 retcode OUT NOCOPY varchar2,
138 schemaname in varchar2,
139 estimate_percent in number default 10,
140 degree in number default null,
141 internal_flag in varchar2 default 'NOBACKUP',
142 request_id in number default null,
143 hmode in varchar2 default 'LASTRUN',
144 options in varchar2 default 'GATHER',
145 modpercent in number default 10,
146 invalidate in varchar2 default 'Y'
147 );
148
149 procedure GATHER_INDEX_STATS(ownname in varchar2,
150 indname in varchar2,
151 percent in number default null,
152 degree in number default null,
153 partname in varchar2 default null,
154 backup_flag in varchar2 default 'NOBACKUP',
155 hmode in varchar2 default 'LASTRUN',
156 invalidate in varchar2 default 'Y'
157 );
158
159 procedure GATHER_TABLE_STATS(ownname in varchar2,
160 tabname in varchar2,
161 percent in number default null,
162 degree in number default null,
163 partname in varchar2 default null,
164 backup_flag in varchar2 default 'NOBACKUP',
165 cascade in boolean default true,
166 granularity in varchar2 default 'DEFAULT',
167 hmode in varchar2 default 'LASTRUN',
168 invalidate in varchar2 default 'Y'
169 );
170
171 procedure GATHER_TABLE_STATS(errbuf OUT NOCOPY varchar2,
172 retcode OUT NOCOPY varchar2,
173 ownname in varchar2,
174 tabname in varchar2,
175 percent in number default null,
176 degree in number default null,
177 partname in varchar2 default null,
178 backup_flag in varchar2 default 'NOBACKUP',
179 granularity in varchar2 default 'DEFAULT',
180 hmode in varchar2 default 'LASTRUN',
181 invalidate in varchar2 default 'Y'
182 );
183
184 procedure GATHER_COLUMN_STATS(appl_id in number default null,
185 percent in number default null,
186 degree in number default null,
187 backup_flag in varchar2 default 'NOBACKUP',
188 --Errors OUT NOCOPY Error_Out,--commented to handle the error collection
189 hmode in varchar2 default 'LASTRUN',
190 invalidate in varchar2 default 'Y'
191 );
192
193 procedure GATHER_ALL_COLUMN_STATS(ownname in varchar2,
194 percent in number default null,
195 degree in number default null,
196 hmode in varchar2 default 'LASTRUN',
197 invalidate in varchar2 default 'Y'
198 );
199
200 procedure GATHER_ALL_COLUMN_STATS(errbuf OUT NOCOPY varchar2,
201 retcode OUT NOCOPY varchar2,
202 ownname in varchar2,
203 percent in number default null,
204 degree in number default null,
205 hmode in varchar2 default 'LASTRUN',
206 invalidate in varchar2 default 'Y'
207 );
208
209 procedure GATHER_COLUMN_STATS(ownname in varchar2,
210 tabname in varchar2,
211 colname in varchar2,
212 percent in number default null,
213 degree in number default null,
214 hsize in number default 254,
215 backup_flag in varchar2 default 'NOBACKUP',
216 partname in varchar2 default null,
217 hmode in varchar2 default 'LASTRUN',
218 invalidate in varchar2 default 'Y'
219 );
220
221 procedure GATHER_COLUMN_STATS(errbuf OUT NOCOPY varchar2,
222 retcode OUT NOCOPY varchar2,
223 ownname in varchar2,
224 tabname in varchar2,
225 colname in varchar2,
226 percent in number default null,
227 degree in number default null,
228 hsize in number default 254,
229 backup_flag in varchar2 default 'NOBACKUP',
230 partname in varchar2 default null,
231 hmode in varchar2 default 'LASTRUN',
232 invalidate in varchar2 default 'Y'
233 );
234
235 /* Purges all records of the FND_STATS_HIST that fall between from_req_id and to_req_id */
236 procedure PURGE_STAT_HISTORY(from_req_id in number,
237 to_req_id in number
238 ) ;
239
240 /* Purges all records of the FND_STATS_HIST that fall between from_req_id and to_req_id */
241 procedure PURGE_STAT_HISTORY(purge_from_date in varchar2 ,
242 purge_to_date in varchar2
243 ) ;
244 procedure PURGE_STAT_HISTORY(errbuf OUT NOCOPY varchar2,
245 retcode OUT NOCOPY varchar2,
246 purge_mode in varchar2 ,
247 from_value in varchar2 ,
248 to_value in varchar2 );
249
250
251 /* Undocumented and for INTERNAL use only */
252 procedure SET_TABLE_STATS(ownname in varchar2,
253 tabname in varchar2,
254 numrows in number,
255 numblks in number,
256 avgrlen in number,
257 partname in varchar2 default null);
258
259
260 /* Undocumented and for INTERNAL use only */
261 procedure SET_INDEX_STATS(ownname in varchar2,
262 indname in varchar2,
263 numrows in number,
264 numlblks in number,
265 numdist in number,
266 avglblk in number,
267 avgdblk in number,
268 clstfct in number,
269 indlevel in number,
270 partname in varchar2 default null);
271
272 procedure LOAD_XCLUD_STATS(schemaname in varchar2);
273
274 /* This one is for a particular INTERFACE TABLE */
275 procedure LOAD_XCLUD_STATS(schemaname in varchar2,
276 tablename in varchar2);
277
278 /* This is for loading exclusion list into fnd_exclude_table_stats */
279 procedure LOAD_XCLUD_TAB(action in varchar2,
280 appl_id in number,
281 tabname in varchar2);
282
283 /* This is for internal/support purpose only. For loading/deleting SEED database */
284 /* procedure DELETE_XCLUD_IND( appl_id in number,
285 tabname in varchar2,
286 indname in varchar2,
287 partname in varchar2 default null);
288 */
289 /* This is for internal purpose only. For loading into SEED database */
290 procedure LOAD_HISTOGRAM_COLS(action in varchar2,
291 appl_id in number,
292 tabname in varchar2,
293 colname in varchar2,
294 partname in varchar2 default null,
295 hsize in number default 254,
296 commit_flag in varchar2 default 'Y' );
297
298 /* This is for internal purpose only. This is for seeding Materialized View columns For loading into SEED database */
299 procedure LOAD_HISTOGRAM_COLS_MV(action in varchar2,
300 ownername in varchar2,
301 tabname in varchar2,
302 colname in varchar2,
303 partname in varchar2 default null,
304 hsize in number default 254,
305 commit_flag in varchar2 default 'Y');
306 /* This is to check if the leading cols of non-unique indexes of
307 a list of input table_names needs histograms */
308 procedure CHECK_HISTOGRAM_COLS(tablelist in varchar2,
309 factor in integer default 75,
310 percent in number default 10,
311 degree in number default null);
312
313 /* This is to create histograms on all leading cols of non-unique indexes of all the
314 tables in a given schema */
315 procedure ANALYZE_ALL_COLUMNS(ownname in varchar2,
316 percent in number default null,
317 hsize in number default 254,
318 hmode in varchar2 default 'LASTRUN');
319 /* conc. job version of ANALYZE_ALL_COLUMNS */
320 procedure ANALYZE_ALL_COLUMNS(errbuf OUT NOCOPY varchar2,
321 retcode OUT NOCOPY varchar2,
322 ownname in varchar2,
323 percent in number default null,
324 hsize in number default 254,
325 hmode in varchar2 default 'LASTRUN');
326
327 /* Used for updating the FND_STATS_HIST with autonomous_transaction */
328 procedure UPDATE_HIST(schemaname varchar2,
329 objectname in varchar2,
330 objecttype in varchar2,
331 partname in varchar2,
332 columntablename in varchar2,
333 degree in number,
334 upd_ins_flag in varchar2,
335 percent in number default null
336 ) ;
337 /* This procedure checks tables, indexes and histograms to see if statistics exist or are stale */
338 procedure verify_stats(schemaname varchar2 default null,
339 tableList varchar2 default null,
340 days_old number default null,
341 column_stat boolean default false);
342 end FND_STATS;
[Home] [Help]