1 PACKAGE body FND_STATS AS
2 /* $Header: AFSTATSB.pls 120.12.12010000.10 2009/02/25 11:05:02 suchauha ship $ */
3 db_versn NUMBER :=81;
4 request_from VARCHAR2(7) DEFAULT 'U';
5 MAX_ERRORS_PRINTED NUMBER := 20 ; -- The max nof. allowable errors.
6 STD_GRANULARITY VARCHAR2(15) := 'DEFAULT' ; -- Global/partion
7 PART_GRANULARITY VARCHAR2(15) := 'PARTITION' ;-- Granularity is partition level.
8 ALL_GRANULARITY VARCHAR2(15) := 'ALL' ; -- Granularity is ALL.
9 INDEX_LEVEL NUMBER := 1 ; /* default ind_level for fudged ind. stats,
10 came to this value so that optimizer
11 prefers index access */
12 fnd_stattab VARCHAR2(30) := 'FND_STATTAB'; -- Name of the backup table
13 fnd_statown VARCHAR2(30) := 'APPLSYS'; -- Owner of the backup table
14 stat_tab_exist BOOLEAN := false;
15 dummy1 VARCHAR2(30);
16 dummy2 VARCHAR2(30);
17 dummybool BOOLEAN ;
18 cur_request_id NUMBER(15) DEFAULT NULL;
19 call_from_sqlplus BOOLEAN :=false;
20 fm_first_flag BOOLEAN :=true; -- Flush_monitoring first time call flag
21 stathist VARCHAR2(8);
22 def_degree NUMBER; -- default degree for parallel
23 g_Errors Error_Out;
24 -- New cursort to support MVs
25 CURSOR schema_cur IS
26 SELECT upper(oracle_username) sname
27 FROM fnd_oracle_userid
28 WHERE oracle_id BETWEEN 900 AND 999
29 AND read_only_flag = 'U'
30
31 UNION ALL
32
33 SELECT DISTINCT upper(oracle_username) sname
34 FROM fnd_oracle_userid a,
35 fnd_product_installations b
36 WHERE a.oracle_id = b.oracle_id
37 ORDER BY sname;
38
39 /************************************************************************/
40 /* Function : GET_BLOCKS */
41 /* Desciption: Gets the size in blocks of the given table. */
42 /************************************************************************/
43 FUNCTION GET_BLOCKS(schemaname IN VARCHAR2,
44 object_name IN VARCHAR2,
45 object_type IN VARCHAR2)
46 RETURN NUMBER
47 IS
48 total_blocks NUMBER;
49 total_bytes NUMBER;
50 unused_blocks NUMBER;
51 unused_bytes NUMBER;
52 last_extf NUMBER;
53 last_extb NUMBER;
54 last_usedblock NUMBER;
55 BEGIN
56 DBMS_SPACE.UNUSED_SPACE(upper(schemaname),upper(object_name),upper(object_type),total_blocks, total_bytes,unused_blocks,unused_bytes,last_extf,last_extb, last_usedblock);
57 RETURN total_blocks-unused_blocks;
58 EXCEPTION
59 WHEN OTHERS THEN
60 -- For partitioned tables, we will get an exception as it unused space
61 -- expects a partition spec. If table is partitioned, we definitely
62 -- do not want to do serial, so will return thold+1000.
63 RETURN fnd_stats.SMALL_TAB_FOR_PAR_THOLD+1000;
64 END;
65 /************************************************************************/
66 /* Procedure: SCHEMA_MONITORING */
67 /* Desciption: Non Public procedure that is called by */
68 /* ENABLE_SCHEMA_MONITORING or DISABLE_SCHEMA_MONITORING */
69 /************************************************************************/
70 PROCEDURE SCHEMA_MONITORING(mmode IN VARCHAR2,
71 schemaname IN VARCHAR2)
72 IS
73 TYPE name_tab
74 IS
75 TABLE OF dba_tables.table_name%TYPE;
76 tmp_str VARCHAR2(200);
77 names name_tab;
78 num_tables NUMBER := 0;
79 modeval VARCHAR2(5);
80 modbool VARCHAR2(6);
81 BEGIN
82 IF mmode ='ENABLE' THEN
83 modeval:='YES';
84 modbool:='TRUE';
85 ELSE
86 modeval:='NO';
87 modbool:='FALSE';
88 END IF;
89 IF (( db_versn > 80) AND
90 (
91 db_versn < 90
92 )
93 ) THEN
94 -- 8i does not have the ALTER_SCHEMA_TAB_MONITORING function,
95 -- therefore this has to be taken care of manually.
96 IF schemaname ='ALL' THEN -- call itself with the schema name
97 FOR c_schema IN schema_cur
98 LOOP
99 FND_STATS.SCHEMA_MONITORING(mmode,c_schema.sname);
100 END LOOP;
101 /* schema_cur */
102 ELSE -- schemaname<>'ALL'
103 SELECT table_name BULK COLLECT
104 INTO names
105 FROM dba_tables
106 WHERE owner = upper(schemaname)
107 AND
108 (
109 iot_type <> 'IOT_OVERFLOW'
110 OR iot_type IS NULL
111 )
112 AND TEMPORARY <> 'Y'
113 AND monitoring <> modeval; -- skip table that already have the selected mode
114 num_tables := SQL%ROWCOUNT;
115 FOR i IN 1..num_tables
116 LOOP
117 IF mmode ='ENABLE' THEN
118 tmp_str:='ALTER TABLE '
119 ||upper(schemaname)
120 ||'.'
121 ||names(i)
122 ||' MONITORING';
123 elsif mmode ='DISABLE' THEN
124 tmp_str:='ALTER TABLE '
125 ||upper(schemaname)
126 ||'.'
127 ||names(i)
128 ||' NOMONITORING';
129 END IF;
130 EXECUTE IMMEDIATE tmp_str;
131 dbms_output.put_line(tmp_str);
132 END LOOP;
133 END IF; -- if schemaname ='ALL'
134 elsif ((db_versn > 90 ) AND
135 (
136 db_versn < 100
137 )
138 ) THEN
139 -- 8i does not have the ALTER_SCHEMA_TAB_MONITORING function,
140 -- therefore 9i specific function calls have to be dynamic sql.
141 IF schemaname ='ALL' THEN
142 tmp_str:='BEGIN dbms_stats.ALTER_DATABASE_TAB_MONITORING(monitoring=>'
143 ||modbool
144 ||',sysobjs=>FALSE); END;';
145 EXECUTE IMMEDIATE tmp_str;
146 ELSE
147 tmp_str:='BEGIN dbms_stats.ALTER_SCHEMA_TAB_MONITORING(ownname=>:SCHEMANAME,monitoring=>'
148 ||modbool
149 ||'); END;';
150 EXECUTE IMMEDIATE tmp_str USING schemaname;
151 END IF;
152 ELSE -- db version is 10, do nothing as it is taken care of by default.
153 -- db_versn is a 2 char code, which is 10 for 10g.
154 NULL;
155 END IF;
156 END;
157 /************************************************************************/
158 /* Procedure: ENABLE_SCHEMA_MONITORING */
159 /* Desciption: Enables MONITORING option for all tables in the */
160 /* given schema. If schemaname is not specified, defaults to 'ALL'. */
161 /************************************************************************/
162 PROCEDURE ENABLE_SCHEMA_MONITORING(schemaname IN VARCHAR2)
163 IS
164 BEGIN
165 SCHEMA_MONITORING('ENABLE',schemaname);
166 END;
167 /************************************************************************/
168 /* Procedure: ENABLE_SCHEMA_MONITORING */
169 /* Desciption: Enables MONITORING option for all tables in the */
170 /* given schema. If schemaname is not specified, defaults to 'ALL'. */
171 /************************************************************************/
172 PROCEDURE DISABLE_SCHEMA_MONITORING(schemaname IN VARCHAR2)
173 IS
174 BEGIN
175 SCHEMA_MONITORING('DISABLE',schemaname);
176 END;
177 /************************************************************************/
178 /* Procedure: GET_PARALLEL */
179 /* Desciption: Gets the min between number of parallel max servers */
180 /* and the cpu_count. This number is used as a default degree of */
181 /* parallelism is none is specified. */
182 /************************************************************************/
183 PROCEDURE GET_PARALLEL(parallel IN OUT NOCOPY NUMBER)
184 IS
185 BEGIN
186 SELECT MIN(to_number(value))
187 INTO parallel
188 FROM v$parameter
189 WHERE name ='parallel_max_servers'
190 OR name ='cpu_count';
191
192 END;
193 /************************************************************************/
194 /* Function: GET_REQUEST_ID */
195 /* Desciption: Gets the current request_id */
196 /* If the call is thru a concurrent program, the conc request id is */
197 /* returned, which can be later over-ridden if restart case. */
198 /* If is is not thru a concurrent program, a user request id is */
199 /* generated. */
200 /************************************************************************/
201 FUNCTION GET_REQUEST_ID RETURN NUMBER
202 IS
203 str_request_id VARCHAR2(30);
204 request_id_l NUMBER(15);
205 l_message VARCHAR2(1000);
206 BEGIN
207 -- FND_PROFILE.GET('CONC_REQUEST_ID', str_request_id);
208 -- if str_request_id is not null then -- call is via a conc program
209 IF FND_GLOBAL.CONC_REQUEST_ID > 0 THEN -- call is via a conc program
210 request_from :='C'; -- set request type C for CONC
211 request_id_l := FND_GLOBAL.CONC_REQUEST_ID; -- set request id to conc request id
212 elsif ( FND_GLOBAL.USER_ID > 0) THEN -- check if call from apps program
213 request_from :='P'; -- P for PROG , cal by program
214 -- generate it from sequence
215 SELECT fnd_stats_hist_s.nextval
216 INTO request_id_l
217 FROM dual;
218
219 ELSE -- call not from within apps context, maybe sqlplus
220 request_from:='U'; -- U for USER, called from sqlplus etc
221 -- generate it from sequence
222 SELECT fnd_stats_hist_s.nextval
223 INTO request_id_l
224 FROM dual;
225
226 END IF;
227 -- dbms_output.put_line('Request_id is '||request_id);
228 -- dbms_output.put_line('Effective Request_id is '||request_id_l);
229 -- l_message := 'Request_id is '||cur_request_id|| 'Effective Request_id is '||request_id_l;
230 -- FND_FILE.put_line(FND_FILE.log,l_message);
231 RETURN request_id_l;
232 END;
233 /************************************************************************/
234 /* Procedure: CREATE_STAT_TABLE */
235 /* Desciption: Create stats table to hold statistics. Default parameters*/
236 /* are used for tablename and owner. */
237 /************************************************************************/
238 PROCEDURE CREATE_STAT_TABLE
239 IS
240 PRAGMA AUTONOMOUS_TRANSACTION;
241 BEGIN
242 -- if stat_tab has already been created, do not recreate
243 BEGIN
244 dummy1:='N';
245 EXECUTE immediate 'select ''Y'' from all_tables '
246 || ' where owner='''
247 ||fnd_statown
248 || ''' and table_name='''
249 ||fnd_stattab
250 ||'''' INTO dummy1;
251 EXCEPTION
252 WHEN OTHERS THEN
253 stat_tab_exist:=false;
254 END;
255 IF dummy1 ='Y' THEN
256 stat_tab_exist := true;
257 END IF;
258 IF stat_tab_exist = false THEN
259 DBMS_STATS.CREATE_STAT_TABLE(fnd_statown,fnd_stattab);
260 stat_tab_exist := true;
261 END IF;
262 EXCEPTION
263 WHEN OTHERS THEN
264 raise;
265 END ;
266 /* CREATE_STAT_TABLE */
267 /************************************************************************/
268 /* Procedure: CREATE_STAT_TABLE */
269 /* Desciption: Create stats table to hold statistics. Caller can specify*/
270 /* cusotm values for schema, tablename or tablespace name */
271 /************************************************************************/
272 PROCEDURE CREATE_STAT_TABLE( schemaname IN VARCHAR2,
273 tabname IN VARCHAR2,
274 tblspcname IN VARCHAR2 DEFAULT NULL)
275 IS
276 PRAGMA AUTONOMOUS_TRANSACTION;
277 BEGIN
278 DBMS_STATS.CREATE_STAT_TABLE(schemaname,tabname,tblspcname);
279 EXCEPTION
280 WHEN OTHERS THEN
281 raise;
282 END;
283 /* CREATE_STAT_TABLE(,,) */
284 /**
285 * procedure TRANSFER_STATS : Wrapper around backup/restore stats procedures,
286 * required for the new "Backup/Restore Statistics"
287 * conc program.
288 */
289 PROCEDURE TRANSFER_STATS( errbuf OUT NOCOPY VARCHAR2,
290 retcode OUT NOCOPY VARCHAR2,
291 action IN VARCHAR2,
292 schemaname IN VARCHAR2,
293 tabname IN VARCHAR2,
294 stattab IN VARCHAR2 DEFAULT 'FND_STATTAB',
295 statid IN VARCHAR2 )
296 IS
297 exist_insufficient EXCEPTION;
298 pragma exception_init(exist_insufficient,-20000);
299 l_message VARCHAR2(1000);
300 BEGIN
301 BEGIN
302 create_stat_table(schemaname,stattab);
303 EXCEPTION
304 WHEN OTHERS THEN
305 NULL;
306 END;
307 IF(upper(action) = 'BACKUP') THEN
308 IF(tabname IS NULL) THEN
309 BACKUP_SCHEMA_STATS( schemaname , statid );
310 ELSE
311 BACKUP_TABLE_STATS( schemaname , tabname , statid ) ;
312 END IF;
313 elsif(upper(action) = 'RESTORE') THEN
314 IF(tabname IS NULL) THEN
318 END IF;
315 RESTORE_SCHEMA_STATS( schemaname , statid );
316 ELSE
317 RESTORE_TABLE_STATS(schemaname , tabname , statid );
319 END IF;
320 EXCEPTION
321 WHEN exist_insufficient THEN
322 errbuf := sqlerrm ;
323 retcode := '2';
324 l_message := errbuf;
325 FND_FILE.put_line(FND_FILE.log,l_message);
326 raise;
327 WHEN OTHERS THEN
328 errbuf := sqlerrm ;
329 retcode := '2';
330 l_message := errbuf;
331 FND_FILE.put_line(FND_FILE.log,l_message);
332 raise;
333 END;
334 /************************************************************************/
335 /* Procedure: BACKUP_SCHEMA_STATS */
336 /* Desciption: Copies schema statistics to fnd_stattab table. If schema */
337 /* name is 'ALL', copies all schema stats. Statistics stored with */
338 /* a particular stat id. */
339 /************************************************************************/
340 PROCEDURE BACKUP_SCHEMA_STATS( schemaname IN VARCHAR2,
341 statid IN VARCHAR2)
342 IS
343 exist_insufficient EXCEPTION;
344 pragma exception_init(exist_insufficient,-20002);
345 BEGIN
346 -- First create the FND_STATTAB if it doesn't exist.
347 BEGIN
348 FND_STATS.CREATE_STAT_TABLE();
349 EXCEPTION
350 WHEN exist_insufficient THEN
351 NULL;
352 END;
353 IF (upper(schemaname) <> 'ALL') THEN
354 DBMS_STATS.EXPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid, fnd_statown);
355 ELSE
356 FOR c_schema IN schema_cur
357 LOOP
358 DBMS_STATS.EXPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid, fnd_statown);
359 END LOOP;
360 /* schema_cur */
361 END IF;
362 END;
363 /* BACKUP_SCHEMA_STATS() */
364 /************************************************************************/
365 /* Procedure: BACKUP_TABLE_STATS */
366 /* Desciption: Copies table statistics along with index and column */
367 /* stats if cascade is true. Procedure is called from concurrent program*/
368 /* manager. */
369 /************************************************************************/
370 PROCEDURE BACKUP_TABLE_STATS( errbuf OUT NOCOPY VARCHAR2,
371 retcode OUT NOCOPY VARCHAR2,
372 schemaname IN VARCHAR2,
373 tabname IN VARCHAR2,
374 statid IN VARCHAR2 DEFAULT 'BACKUP',
375 partname IN VARCHAR2 DEFAULT NULL,
376 CASCADE IN BOOLEAN DEFAULT true )
377 IS
378 exist_insufficient EXCEPTION;
379 pragma exception_init(exist_insufficient,-20000);
380 l_message VARCHAR2(1000);
381 BEGIN
382 FND_STATS.BACKUP_TABLE_STATS(schemaname, tabname, statid, partname, CASCADE);
383 EXCEPTION
384 WHEN exist_insufficient THEN
385 errbuf := sqlerrm ;
386 retcode := '2';
387 l_message := errbuf;
388 FND_FILE.put_line(FND_FILE.log,l_message);
389 raise;
390 WHEN OTHERS THEN
391 errbuf := sqlerrm ;
392 retcode := '2';
393 l_message := errbuf;
394 FND_FILE.put_line(FND_FILE.log,l_message);
395 raise;
396 END;
397 /* BACKUP_TABLE_STATS */
398 /************************************************************************/
399 /* Procedure: BACKUP_TABLE_STATS */
400 /* Desciption: Copies table statistics along with index and column */
401 /* stats if cascade is true. Procedure is called by the concurrent */
402 /* program manager version of BACKUP_TABLE_STATS. Procedure can also be */
403 /* called from sqlplus */
404 /************************************************************************/
405 PROCEDURE BACKUP_TABLE_STATS( schemaname IN VARCHAR2,
406 tabname IN VARCHAR2,
407 statid IN VARCHAR2 DEFAULT 'BACKUP',
408 partname IN VARCHAR2 DEFAULT NULL,
409 CASCADE IN BOOLEAN DEFAULT true )
410 IS
411 exist_insufficient EXCEPTION;
412 pragma exception_init(exist_insufficient,-20002);
413 BEGIN
414 -- First create the FND_STATTAB if it doesn't exist.
415 BEGIN
416 FND_STATS.CREATE_STAT_TABLE();
417 EXCEPTION
418 WHEN exist_insufficient THEN
419 NULL;
420 END;
421 DBMS_STATS.EXPORT_TABLE_STATS(schemaname, tabname, partname, fnd_stattab, statid, CASCADE, fnd_statown) ;
422 END;
423 /* BACKUP_TABLE_STATS() */
424 /************************************************************************/
425 /* Procedure: RESTORE_SCHEMA_STATS */
426 /* Desciption: Retores schema statistics from fnd_stattab table. If */
427 /* schema name is 'ALL', copies all schema stats. Statistics restored */
428 /* with a particular stat id. */
432 IS
429 /************************************************************************/
430 PROCEDURE RESTORE_SCHEMA_STATS(schemaname IN VARCHAR2,
431 statid IN VARCHAR2 DEFAULT NULL)
433 BEGIN
434 IF (upper(schemaname) <> 'ALL') THEN
435 DBMS_STATS.IMPORT_SCHEMA_STATS(schemaname, fnd_stattab, statid, fnd_statown);
436 ELSE
437 FOR c_schema IN schema_cur
438 LOOP
439 DBMS_STATS.IMPORT_SCHEMA_STATS(c_schema.sname, fnd_stattab, statid, fnd_statown);
440 END LOOP;
441 /* schema_cur */
442 END IF;
443 END;
444 /* RESTORE_SCHEMA_STATS() */
445 /************************************************************************/
446 /* Procedure: RESTORE_TABLE_STATS */
447 /* Desciption: Retores table statistics from fnd_stattab table. If */
448 /* cascase is true, restores column as well as index stats too. This */
449 /* procedure is called from concurrent program manager. */
450 /************************************************************************/
451 PROCEDURE RESTORE_TABLE_STATS(errbuf OUT NOCOPY VARCHAR2,
452 retcode OUT NOCOPY VARCHAR2,
453 ownname IN VARCHAR2,
454 tabname IN VARCHAR2,
455 statid IN VARCHAR2 DEFAULT NULL,
456 partname IN VARCHAR2 DEFAULT NULL,
457 CASCADE IN BOOLEAN DEFAULT true )
458 IS
459 exist_insufficient EXCEPTION;
460 exist_invalid EXCEPTION;
461 pragma exception_init(exist_insufficient,-20000);
462 pragma exception_init(exist_invalid,-20001);
463 l_message VARCHAR2(1000);
464 BEGIN
465 FND_STATS.RESTORE_TABLE_STATS(ownname,tabname,statid,partname,CASCADE);
466 EXCEPTION
467 WHEN exist_insufficient THEN
468 errbuf := sqlerrm;
469 retcode := '2';
470 l_message := errbuf;
471 FND_FILE.put_line(FND_FILE.log,l_message);
472 raise;
473 WHEN exist_invalid THEN
474 errbuf := 'ORA-20001: Invalid or inconsistent values in the user stattab ='
475 ||fnd_stattab
476 ||' statid='
477 ||statid ;
478 retcode := '2';
479 l_message := errbuf;
480 FND_FILE.put_line(FND_FILE.log,l_message);
481 raise;
482 WHEN OTHERS THEN
483 errbuf := sqlerrm ;
484 retcode := '2';
485 l_message := errbuf;
486 FND_FILE.put_line(FND_FILE.log,l_message);
487 raise;
488 END;
489 /* RESTORE_TABLE_STATS */
490 /************************************************************************/
491 /* Procedure: RESTORE_TABLE_STATS */
492 /* Desciption: Retores table statistics from fnd_stattab table. If */
493 /* cascase is true, restores column as well as index stats too. This */
494 /* procedure is called from the concurrent program manager version of */
495 /* RESTORE_TABLE_STATS as well as from sqlplus */
496 /************************************************************************/
497 PROCEDURE RESTORE_TABLE_STATS(ownname IN VARCHAR2,
498 tabname IN VARCHAR2,
499 statid IN VARCHAR2 DEFAULT NULL,
500 partname IN VARCHAR2 DEFAULT NULL,
501 CASCADE IN BOOLEAN DEFAULT true )
502 IS
503 BEGIN
504 DBMS_STATS.IMPORT_TABLE_STATS(ownname,tabname,partname, fnd_stattab,statid,CASCADE,fnd_statown);
505 END;
506 /* RESTORE_TABLE_STATS */
507 /************************************************************************/
508 /* Procedure: RESTORE_INDEX_STATS */
509 /* Desciption: Retores index statistics from fnd_stattab table for a */
510 /* particular table. */
511 /************************************************************************/
512 PROCEDURE RESTORE_INDEX_STATS(ownname IN VARCHAR2,
513 indname IN VARCHAR2,
514 statid IN VARCHAR2 DEFAULT NULL,
515 partname IN VARCHAR2 DEFAULT NULL)
516 IS
517 BEGIN
518 DBMS_STATS.IMPORT_INDEX_STATS(ownname,indname,partname,fnd_stattab, statid,fnd_statown) ;
519 END;
520 /* RESTORE_INDEX_STATS */
521 /************************************************************************/
522 /* Procedure: RESTORE_COLUMN_STATS */
523 /* Desciption: Retores column statistics from fnd_stattab table for a */
524 /* particular column. */
525 /************************************************************************/
526 PROCEDURE RESTORE_COLUMN_STATS(ownname IN VARCHAR2,
527 tabname IN VARCHAR2,
528 colname IN VARCHAR2,
529 partname IN VARCHAR2 DEFAULT NULL,
530 statid IN VARCHAR2 DEFAULT NULL)
531 IS
532 BEGIN
533 DBMS_STATS.IMPORT_COLUMN_STATS(ownname, tabname, colname, partname, fnd_stattab, statid, fnd_statown) ;
534 END;
535 /* RESTORE_COLUMN_STATS() */
539 /* columns seeded in the fnd_histogram_cols table. */
536 /************************************************************************/
537 /* Procedure: RESTORE_COLUMN_STATS */
538 /* Desciption: Retores column statistics from fnd_stattab table for all */
540 /************************************************************************/
541 PROCEDURE RESTORE_COLUMN_STATS(statid IN VARCHAR2 DEFAULT NULL)
542 IS
543 /* cursor col_cursor is
544 select upper(b.oracle_username) ownname ,
545 a.table_name tabname,
546 a.column_name colname,
547 a.partition partname
548 from FND_HISTOGRAM_COLS a,
549 FND_ORACLE_USERID b,
550 FND_PRODUCT_INSTALLATIONS c
551 where a.application_id = c.application_id
552 and c.oracle_id = b.oracle_id
553 order by ownname, tabname, column_name;
554 */
555 -- New cursor to support MVs
556 CURSOR col_cursor IS
557 SELECT NVL(upper(b.oracle_username), a.owner) ownname ,
558 a.table_name tabname ,
559 a.column_name colname ,
560 a.partition partname
561 FROM FND_HISTOGRAM_COLS a,
562 FND_ORACLE_USERID b ,
563 FND_PRODUCT_INSTALLATIONS c
564 WHERE a.application_id = c.application_id (+)
565 AND c.oracle_id = b.oracle_id (+)
566 ORDER BY ownname,
567 tabname,
568 colname;
569
570 BEGIN
571 FOR c_rec IN col_cursor
572 LOOP
573 DBMS_STATS.IMPORT_COLUMN_STATS(c_rec.ownname,c_rec.tabname, c_rec.colname,c_rec.partname, fnd_stattab,statid,fnd_statown);
574 END LOOP;
575 END;
576 /* RESTORE_COLUMN_STATS */
577 /************************************************************************/
578 /* Procedure: DLOG */
579 /* Desciption: Writes out log messages to the conc program log. */
580 /************************************************************************/
581 PROCEDURE dlog(p_str IN VARCHAR2)
582 IS
583 BEGIN
584 dbms_output.put_line(SUBSTR(p_str,1,250));
585 FND_FILE.put_line(FND_FILE.log,p_str);
586 END dlog;
587 /************************************************************************/
588 /* Procedure: GATHER_TABLE_STATS_PVT */
589 /* Desciption: Private package that now calls dbms_stats dynamically */
590 /* depending upon the version of the database. For 8i, */
591 /* dbms_stats is called as before, for higher versions, it */
592 /* is called with the no_invalidate flag. */
593 /************************************************************************/
594 PROCEDURE GATHER_TABLE_STATS_PVT(ownname IN VARCHAR2,
595 tabname IN VARCHAR2,
596 estimate_percent IN NUMBER DEFAULT NULL,
597 degree IN NUMBER DEFAULT NULL,
598 method_opt VARCHAR2 DEFAULT 'FOR ALL COLUMNS SIZE 1',
599 partname IN VARCHAR2 DEFAULT NULL,
600 CASCADE IN BOOLEAN DEFAULT true,
601 granularity IN VARCHAR2 DEFAULT 'DEFAULT',
602 stattab VARCHAR2 DEFAULT NULL,
603 statown VARCHAR2 DEFAULT NULL,
604 invalidate IN VARCHAR2 DEFAULT 'Y' )
605 IS
606 l_tmp_str VARCHAR2(600);
607 no_invalidate VARCHAR2(1);
608 BEGIN
609 IF ((upper(invalidate) ='Y') OR
610 (
611 upper(invalidate) ='YES'
612 )
613 ) THEN
614 no_invalidate:='N';
615 ELSE
616 no_invalidate:='Y';
617 END IF;
618 -- If db version is < 9iR2, OR it is 92 and no_inv is false OR it is > 92
619 -- and no_inv is true, calls dbms_stats statically, else ...
620 IF ( (db_versn <= 92) OR
621 (
622 db_versn=92 AND no_invalidate='N'
623 )
624 OR
625 (
626 db_versn>=100 AND no_invalidate='Y'
627 )
628 ) THEN
629 DBMS_STATS.GATHER_TABLE_STATS( ownname => ownname ,
630 tabname => tabname , estimate_percent => estimate_percent , degree => degree ,
631 method_opt => method_opt , block_sample => FALSE , partname => partname ,
632 CASCADE => CASCADE , granularity => granularity , stattab => stattab , statown => statown );
633 ELSE
634 l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => :ownname ,'
635 || ' tabname => :tabname ,'
636 || ' estimate_percent => :estimate_percent ,'
637 || ' degree => :degree ,'
638 || ' method_opt => :method_opt ,'
639 || ' block_sample => FALSE ,'
640 || ' partname => :partname ,'
641 || ' granularity => :granularity ,'
642 || ' stattab => :stattab ,'
646 || ' no_invalidate => TRUE ,';
643 || ' statown => :statown, ';
644 IF (no_invalidate ='Y') THEN
645 l_tmp_str:=l_tmp_str
647 ELSE
648 l_tmp_str:=l_tmp_str
649 || ' no_invalidate => FALSE ,';
650 END IF;
651 IF (CASCADE) THEN
652 l_tmp_str:=l_tmp_str
653 || ' cascade => TRUE ';
654 ELSE
655 l_tmp_str:=l_tmp_str
656 || ' cascade => FALSE ';
657 END IF;
658 l_tmp_str:=l_tmp_str
659 || ' ); end;';
660 EXECUTE IMMEDIATE l_tmp_str USING ownname , tabname , estimate_percent , degree , method_opt , partname , granularity , stattab , statown;
661 END IF;
662 EXCEPTION
663 WHEN OTHERS THEN
664 raise;
665 END;
666 /* GATHER_TABLE_STATS_PVT */
667 /************************************************************************/
668 /* Procedure: GATHER_INDEX_STATS_PVT */
669 /* Desciption: Private package that now calls dbms_stats dynamically */
670 /* depending upon the version of the database. For 8i, */
671 /* dbms_stats is called as before, for higher versions, it */
672 /* is called with the invalidate flag. */
673 /************************************************************************/
674 PROCEDURE GATHER_INDEX_STATS_PVT(ownname IN VARCHAR2,
675 indname IN VARCHAR2,
676 estimate_percent IN NUMBER DEFAULT NULL,
677 degree IN NUMBER DEFAULT NULL,
678 partname IN VARCHAR2 DEFAULT NULL,
679 invalidate IN VARCHAR2 DEFAULT 'Y' )
680 IS
681 l_tmp_str VARCHAR2(600);
682 no_invalidate VARCHAR2(1);
683 BEGIN
684 IF ((upper(invalidate) ='Y') OR
685 (
686 upper(invalidate) ='YES'
687 )
688 ) THEN
689 no_invalidate:='N';
690 ELSE
691 no_invalidate:='Y';
692 END IF;
693 -- If db version is < 9iR2, calls dbms_stats statically, else ...
694 IF (db_versn <= 92) THEN
695 DBMS_STATS.GATHER_INDEX_STATS( ownname => ownname , indname => indname , estimate_percent => estimate_percent , partname => partname );
696 ELSE
697 l_tmp_str:= 'BEGIN DBMS_STATS.GATHER_INDEX_STATS( ownname => :ownname ,'
698 || ' indname => :indname ,'
699 || ' estimate_percent => :estimate_percent ,'
700 || ' degree => :degree ,'
701 || ' partname => :partname ,';
702 IF (no_invalidate ='Y') THEN
703 l_tmp_str:=l_tmp_str
704 || ' no_invalidate => TRUE ';
705 ELSE
706 l_tmp_str:=l_tmp_str
707 || ' no_invalidate => FALSE ';
708 END IF;
709 l_tmp_str:=l_tmp_str
710 ||' ); END;';
711 EXECUTE IMMEDIATE l_tmp_str USING ownname , indname , estimate_percent , degree , partname ;
712 END IF;
713 END;
714 /* GATHER_INDEX_STATS_PVT */
715 /************************************************************************/
716 /* Procedure: GATHER_SCHEMA_STATS */
717 /* Desciption: Gather schema statistics. This is the concurrent program */
718 /* manager version. */
719 /************************************************************************/
720 PROCEDURE GATHER_SCHEMA_STATS(errbuf OUT NOCOPY VARCHAR2,
721 retcode OUT NOCOPY VARCHAR2,
722 schemaname IN VARCHAR2,
723 estimate_percent IN NUMBER,
724 degree IN NUMBER ,
725 internal_flag IN VARCHAR2,
726 request_id IN NUMBER,
727 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
728 OPTIONS IN VARCHAR2 DEFAULT 'GATHER',
729 modpercent IN NUMBER DEFAULT 10,
730 invalidate IN VARCHAR2 DEFAULT 'Y' )
731 IS
732 exist_insufficient EXCEPTION;
733 bad_input EXCEPTION;
734 pragma exception_init(exist_insufficient,-20000);
735 pragma exception_init(bad_input,-20001);
736 l_message VARCHAR2(1000);
737 Error_counter NUMBER := 0;
738 --Errors Error_Out; -- commented for bug error handling
739 -- num_request_id number(15);
740 conc_request_id NUMBER(15);
741 degree_parallel NUMBER(4);
742 BEGIN
743 -- Set the package body variable.
744 stathist := hmode;
745 -- check first if degree is null
746 IF degree IS NULL THEN
747 degree_parallel:=def_degree;
748 ELSE
752 || schemaname
749 degree_parallel := degree;
750 END IF;
751 l_message := 'In GATHER_SCHEMA_STATS , schema_name= '
753 || ' percent= '
754 || TO_CHAR(estimate_percent)
755 || ' degree = '
756 || TO_CHAR(degree_parallel)
757 || ' internal_flag= '
758 || internal_flag ;
759 FND_FILE.put_line(FND_FILE.log,l_message);
760 BEGIN
761 FND_STATS.GATHER_SCHEMA_STATS(schemaname, estimate_percent, degree_parallel, internal_flag , request_id,stathist, OPTIONS,modpercent,invalidate); -- removed errors parameter for error handling
762 EXCEPTION
763 WHEN exist_insufficient THEN
764 errbuf := sqlerrm ;
765 retcode := '2';
766 l_message := errbuf;
767 FND_FILE.put_line(FND_FILE.log,l_message);
768 raise;
769 WHEN bad_input THEN
770 errbuf := sqlerrm ;
771 retcode := '2';
772 l_message := errbuf;
773 FND_FILE.put_line(FND_FILE.log,l_message);
774 raise;
775 WHEN OTHERS THEN
776 errbuf := sqlerrm ;
777 retcode := '2';
778 l_message := errbuf;
779 FND_FILE.put_line(FND_FILE.log,l_message);
780 raise;
781 END;
782 FOR i IN 0..MAX_ERRORS_PRINTED
783 LOOP
784 EXIT
785 WHEN g_Errors(i) IS NULL;
786 Error_counter:=i+1;
787 FND_FILE.put_line(FND_FILE.log,'Error #'
788 ||Error_counter
789 || ': '
790 ||g_Errors(i));
791 -- added to send back status to concurrent program manager bug 2625022
792 errbuf := sqlerrm ;
793 retcode := '2';
794 END LOOP;
795 END;
796 /* GATHER_SCHEMA_STATS */
797 /************************************************************************/
798 /* Procedure: GATHER_SCHEMA_STATISTICS */
799 /* Desciption: Gather schema statistics. This is the sqlplus version. It*/
800 /* does not have any o/p parameters */
801 /************************************************************************/
802 PROCEDURE GATHER_SCHEMA_STATISTICS(schemaname IN VARCHAR2,
803 estimate_percent IN NUMBER ,
804 degree IN NUMBER ,
805 internal_flag IN VARCHAR2,
806 request_id IN NUMBER,
807 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
808 OPTIONS IN VARCHAR2 DEFAULT 'GATHER',
809 modpercent IN NUMBER DEFAULT 10,
810 invalidate IN VARCHAR2 DEFAULT 'Y' )
811 IS
812 Errors Error_Out;
813 BEGIN
814 call_from_sqlplus:=true;
815 FND_STATS.GATHER_SCHEMA_STATS_SQLPLUS(schemaname, estimate_percent, degree,internal_flag, Errors, request_id,hmode,OPTIONS ,modpercent,invalidate);
816 END;
817 /* end of GATHER_SCHEMA_STATISTICS */
818 /************************************************************************/
819 /* Procedure: GATHER_SCHEMA_STATS_SQLPLUS */
820 /* Desciption: Gather schema statistics. This is called by concurrent */
821 /* manager version of GATHER_SCHEMA_STATS. */
822 /* Notes: internal_flag='INTERNAL' will call dbms_utility.analyze_schema*/
823 /* insead of dbms_stats.gather_schema_stats */
824 /* internal_flag='NOBACKUP' will bypass dbms_stats.export_schema_stats */
825 /************************************************************************/
826 PROCEDURE GATHER_SCHEMA_STATS_SQLPLUS(schemaname IN VARCHAR2,
827 estimate_percent IN NUMBER ,
828 degree IN NUMBER ,
829 internal_flag IN VARCHAR2 ,
830 Errors OUT NOCOPY Error_Out,
831 request_id IN NUMBER DEFAULT NULL,
832 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
833 OPTIONS IN VARCHAR2 DEFAULT 'GATHER',
834 modpercent IN NUMBER DEFAULT 10,
835 invalidate IN VARCHAR2 DEFAULT 'Y' )
836 IS
837 TYPE name_tab
838 IS
839 TABLE OF dba_tables.table_name%TYPE;
840 TYPE partition_tab
841 IS
842 TABLE OF sys.dba_tab_modifications.partition_name%TYPE;
843 TYPE partition_type_tab
844 IS
845 TABLE OF dba_tables.partitioned%TYPE;
846 part_flag partition_type_tab;
847 names name_tab;
848 pnames partition_tab;
849 num_tables NUMBER := 0;
850 l_message VARCHAR2(1000) ;
851 granularity VARCHAR2(12);
852 exist_insufficient EXCEPTION;
853 pragma exception_init(exist_insufficient,-20002);
854 err_cnt BINARY_INTEGER := 0;
855 degree_parallel NUMBER(4);
856 str_request_id VARCHAR(30);
860 SELECT type ,
857 -- Cursor to get list of tables and indexes with no stats
858 CURSOR empty_cur(schemaname VARCHAR2)
859 IS
861 owner,
862 name
863 FROM
864 ( SELECT 'TABLE' type,
865 owner ,
866 table_name name
867 FROM dba_tables
868 WHERE owner=upper(schemaname)
869 AND
870 (
871 iot_type <> 'IOT_OVERFLOW'
872 OR iot_type IS NULL
873 )
874 AND TEMPORARY <> 'Y'
875 AND last_analyzed IS NULL
876
877 UNION ALL
878
879 SELECT 'INDEX' type,
880 owner ,
881 index_name name
882 FROM dba_indexes
883 WHERE
884 (
885 table_owner=upper(schemaname)
886 OR owner =upper(schemaname)
887 )
888 AND index_type <> 'LOB'
889 AND index_type <>'DOMAIN'
890 AND TEMPORARY <> 'Y'
891 AND last_analyzed IS NULL
892 )
893 ORDER BY type ,
894 owner,
895 name ;
896
897 CURSOR nomon_tab(schemaname VARCHAR2)
898 IS
899 SELECT owner,
900 table_name
901 FROM dba_tables dt
902 WHERE owner=upper(schemaname)
903 AND
904 (
905 iot_type <> 'IOT_OVERFLOW'
906 OR iot_type IS NULL
907 )
908 AND TEMPORARY <> 'Y'
909 AND monitoring ='NO'
910 AND NOT EXISTS
911 (SELECT NULL
912 FROM dba_external_tables de
913 WHERE de.table_name=dt.table_name
914 AND de.owner =dt.owner
915 )-- added this to avoid externale tables being selected
916 AND NOT EXISTS
917 ( SELECT NULL
918 FROM dba_tab_statistics dts
919 WHERE dts.stattype_locked IS NOT NULL
920 AND dts.table_name=dt.table_name
921 AND dts.owner =dt.owner
922 ); -- added by saleem to avoid locked objects
923 CURSOR lock_stats_tab(schemaname VARCHAR2)
924 IS -- cursor added by saleem to display the warning message for tables with locked stats
925 SELECT table_name
926 FROM dba_tab_statistics
927 WHERE stattype_locked IS NOT NULL
928 AND owner=upper(schemaname); -- added to display the warning for locked stats
929 BEGIN
930 -- Set the package body variable.
931 stathist := hmode;
932 -- if request id (restart case) is provided, then this is the cur_request_id
933 -- valid for both conc program and sql plus case.
934 IF request_id IS NOT NULL THEN
935 cur_request_id := request_id;
936 END IF;
937 -- get degree of parallelism
938 IF degree IS NULL THEN
939 degree_parallel:=def_degree;
940 ELSE
941 degree_parallel := degree;
942 END IF;
943 -- Initialize the TABLE Errors
944 Errors(0) := NULL;
945 granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables
946 err_cnt := 0;
947 -- If a specific schema is given
948 IF (upper(schemaname) <> 'SYS') THEN
949 IF (upper(schemaname) <> 'ALL') THEN
950 -- Insert/update the fnd_stats_hist table
951 IF(upper(stathist)<> 'NONE') THEN
952 BEGIN
953 -- if(cur_request_id is null) then
954 -- cur_request_id := GET_REQUEST_ID(request_id);
955 -- end if;
956 FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(estimate_percent,10));
957 END;
958 END IF; --if(upper(stathist)<> 'NONE')
959 -- backup the existing schema stats
960 IF ( (upper(internal_flag) = 'BACKUP') ) THEN
961 FND_STATS.BACKUP_SCHEMA_STATS( schemaname );
962 END IF;
963 IF(upper(OPTIONS)='GATHER') THEN
964 SELECT table_name ,
968 FROM dba_tables dt
965 partitioned BULK COLLECT
966 INTO names,
967 part_flag
969 WHERE owner = upper(schemaname)
970 AND
971 (
972 iot_type <> 'IOT_OVERFLOW'
973 OR iot_type IS NULL
974 )
975 AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
976 AND NOT EXISTS
977 (SELECT NULL
978 FROM fnd_stats_hist fsh
979 WHERE dt.owner =fsh.schema_name
980 AND fsh.REQUEST_ID = cur_request_id
981 AND fsh.object_type ='CASCADE'
982 AND fsh.history_mode=stathist
983 AND dt.table_name = fsh.object_name
984 AND LAST_GATHER_END_TIME IS NOT NULL
985 )
986 AND NOT EXISTS
987 (SELECT NULL
988 FROM fnd_exclude_table_stats fets,
989 fnd_oracle_userid fou ,
990 fnd_product_installations fpi
991 WHERE fou.oracle_username=upper(schemaname)
992 AND fou.oracle_id =fpi.oracle_id
993 AND fpi.application_id = fets.application_id
994 AND dt.table_name = fets.table_name
995 ) -- added by saleem for bug 7479909
996 AND NOT EXISTS
997 ( SELECT NULL
998 FROM dba_tab_statistics dts
999 WHERE dts.stattype_locked IS NOT NULL
1000 AND dts.table_name=dt.table_name
1001 AND dts.owner =dt.owner
1002 )
1003 ORDER BY table_name;
1004
1005 num_tables := SQL%ROWCOUNT;
1006 FOR i IN 1..num_tables
1007 LOOP
1008 IF ( part_flag(i) = 'YES' ) THEN
1009 granularity := FND_STATS.ALL_GRANULARITY ;
1010 ELSE
1011 granularity := FND_STATS.STD_GRANULARITY;
1012 END IF;
1013 BEGIN
1014 FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1015 tabname => names(i), percent => NVL(estimate_percent,10),
1016 degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1017 granularity => granularity, hmode => stathist,
1018 invalidate=> invalidate );
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 Errors(err_cnt) := 'ERROR: While GATHER_TABLE_STATS:
1022 object_name='
1023 ||schemaname
1024 ||'.'
1025 ||names(i)
1026 ||'***'
1027 ||SQLERRM
1028 ||'***' ;
1029 Errors(err_cnt+1) := NULL;
1030 err_cnt := err_cnt+1;
1031 END;
1032 END LOOP;
1033 /* end of individual tables */
1034 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1035 LOOP
1036 dbms_output.put_line('stats on table '
1037 || rec_cur.table_name
1038 || 'is locked ');
1039 dlog('stats on table '
1040 || rec_cur.table_name
1041 || ' is locked ');
1042 --fnd_file.put_line(FND_FILE.log,s_message);
1043 END LOOP;
1044 elsif ( (upper(OPTIONS)='GATHER AUTO') OR
1045 (
1046 upper(OPTIONS)='LIST AUTO'
1047 )
1048 ) THEN
1049 -- if db_versn > 81 then call flush, else use whatever
1050 -- data is available in dtm
1051 IF db_versn > 81 THEN
1052 IF(fm_first_flag) THEN
1053 EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;' ;
1054 fm_first_flag := false;
1055 END IF;
1056 END IF;
1057 -- gather stats for stale tables/partitions. Potentially, there
1058 -- could be some redundent stats gathering, if for eg the table
1059 -- and one of its partitions, both are statle. gather_table_stats
1060 -- would get called twice, once for the table ( which would gather
1061 -- stats for the partitions too, and the partition by itself. The
1062 -- probability of this happening is small, and even if that happens
1063 -- on a rare occasion, the overhead should not be that high, so
1064 -- leaving it as it is for the moment. This can be revisited if
1065 -- tests and experience show that that is not the case.
1066 SELECT iv.table_name,
1067 iv.partition_name -- ,subpartition_name
1068 BULK COLLECT
1069 INTO names,
1070 pnames -- ,spnames
1071 FROM
1072 ( SELECT dtm.table_name,
1073 dtm.partition_name
1074 FROM sys.dba_tab_modifications dtm
1075 WHERE dtm.table_owner = upper(schemaname)
1076 AND dtm.partition_name IS NULL
1077 AND EXISTS
1078 ( SELECT NULL
1079 FROM dba_tables dt
1080 WHERE dt.owner =dtm.table_owner
1081 AND dt.table_name=dtm.table_name
1082 AND
1083 (
1084 NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
1085 )
1086 > (modpercent*NVL(dt.num_rows,0))/100
1087 )
1088
1089 UNION ALL
1090
1091 SELECT dtm.table_name,
1092 dtm.partition_name
1093 FROM sys.dba_tab_modifications dtm
1094 WHERE dtm.table_owner = upper(schemaname)
1095 AND dtm.partition_name IS NOT NULL
1096 AND EXISTS
1100 AND dtp.table_name =dtm.table_name
1097 ( SELECT NULL
1098 FROM dba_tab_partitions dtp
1099 WHERE dtp.table_owner =dtm.table_owner
1101 AND dtp.partition_name=dtm.partition_name
1102 AND
1103 (
1104 NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
1105 )
1106 > (modpercent*NVL(dtp.num_rows,0))/100
1107 )
1108 ) iv
1109 ORDER BY table_name;
1110
1111 num_tables := SQL%ROWCOUNT;
1112 FOR i IN 1..num_tables
1113 LOOP
1114 BEGIN
1115 IF (upper(OPTIONS)='GATHER AUTO') THEN
1116 FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1117 tabname => names(i), percent => NVL(estimate_percent,10),
1118 degree => degree_parallel, partname=>pnames(i),
1119 CASCADE => TRUE, granularity => granularity,
1120 hmode => stathist, invalidate=> invalidate );
1121 ELSE
1122 dlog('Statistics on '
1123 ||schemaname
1124 ||'.'
1125 ||names(i)
1126 ||'Partition '
1127 ||NVL(pnames(i),'n/a')
1128 ||' are Stale');
1129 END IF;
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 Errors(err_cnt) := 'ERROR: While GATHER_TABLE_STATS:
1133 object_name='
1134 ||schemaname
1135 ||'.'
1136 ||names(i)
1137 ||'***'
1138 ||SQLERRM
1139 ||'***' ;
1140 Errors(err_cnt+1) := NULL;
1141 err_cnt := err_cnt+1;
1142 END;
1143 END LOOP;
1144 /* end of individual tables */
1145 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1146 LOOP
1147 dbms_output.put_line('stats on table '
1148 || rec_cur.table_name
1149 || 'is locked ');
1150 dlog('stats on table '
1151 || rec_cur.table_name
1152 || ' is locked ');
1153 --fnd_file.put_line(FND_FILE.log,s_message);
1154 END LOOP;
1155 -- GATHER AUTO includes GATHER EMPTY, so gather stats
1156 -- on any unalalyzed tables and/or indexes.
1157 FOR c_rec IN empty_cur(upper(schemaname))
1158 LOOP
1159 IF c_rec.type = 'TABLE' THEN
1160 IF (upper(OPTIONS)='GATHER AUTO') THEN
1161 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1162 percent => NVL(estimate_percent,10),
1163 degree => degree_parallel, partname=>NULL, CASCADE => TRUE, granularity => granularity,
1164 hmode => stathist, invalidate=> invalidate );
1165 ELSE
1166 dlog('Table '
1167 ||c_rec.owner
1168 ||'.'
1169 ||c_rec.name
1170 ||' is missing statistics.');
1171 END IF;
1172 elsif c_rec.type ='INDEX' THEN
1173 IF (upper(OPTIONS)='GATHER AUTO') THEN
1174 fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);
1175 ELSE
1176 dlog('Index '
1177 ||c_rec.owner
1181 END IF;
1178 ||'.'
1179 ||c_rec.name
1180 ||' is missing statistics! ');
1182 END IF;
1183 END LOOP;
1184 -- Check if there are any tables in the schema which does not have
1185 -- monitoring enabled. If yes, gather stats for them using 10% and
1186 -- enable monitoring for such tables so that we have data for them
1187 -- in dba_tab_modifications for next time.
1188 FOR c_rec IN nomon_tab(upper(schemaname))
1189 LOOP
1190 IF (upper(OPTIONS)='GATHER AUTO') THEN
1191 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,
1192 tabname => c_rec.table_name, percent => NVL(estimate_percent,10), degree => degree_parallel,
1193 partname=>NULL, CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1194 EXECUTE IMMEDIATE 'alter table '
1195 ||c_rec.owner
1196 ||'.'
1197 ||c_rec.table_name
1198 ||' monitoring';
1199 dlog('Monitoring has now been enabled for Table '
1200 ||c_rec.owner
1201 ||'.'
1202 ||c_rec.table_name
1203 ||'. Stats were gathered.' );
1204 ELSE
1205 dlog('Monitoring is not enabled for Table '
1206 ||c_rec.owner
1207 ||'.'
1208 ||c_rec.table_name );
1209 END IF;
1210 END LOOP; -- nomon_tab
1211 elsif ( (upper(OPTIONS)='GATHER EMPTY') OR
1212 (
1213 upper(OPTIONS)='LIST EMPTY'
1214 )
1215 ) THEN
1216 FOR c_rec IN empty_cur(upper(schemaname))
1217 LOOP
1218 IF c_rec.type = 'TABLE' THEN
1219 IF (upper(OPTIONS)='GATHER EMPTY') THEN
1220 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1221 percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1222 granularity => granularity, hmode => stathist, invalidate=> invalidate );
1223 ELSE
1224 dlog('Table '
1225 ||c_rec.owner
1226 ||'.'
1227 ||c_rec.name
1228 ||' is missing statistics! ');
1229 END IF;
1230 elsif c_rec.type ='INDEX' THEN
1231 IF (upper(OPTIONS)='GATHER EMPTY') THEN
1232 fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);
1233 ELSE
1234 dlog('Statistics for Index '
1235 ||c_rec.owner
1236 ||'.'
1237 ||c_rec.name
1238 ||' are Empty');
1239 END IF;
1240 END IF;
1241 END LOOP;
1242 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1243 LOOP
1244 dbms_output.put_line('stats on table '
1245 || rec_cur.table_name
1246 || ' is locked ');
1247 dlog('stats on table '
1248 || rec_cur.table_name
1249 || ' is locked ');
1250 --fnd_file.put_line(FND_FILE.log,s_message);
1251 END LOOP;
1252 END IF;
1253 /* end of if upper(options)= */
1254 -- End timestamp
1255 IF(upper(stathist) <> 'NONE') THEN
1259 END IF;
1256 BEGIN
1257 FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1258 END;
1260 ELSE
1261 /* This is for ALL schema */
1262 FOR c_schema IN schema_cur
1263 LOOP
1264 --dbms_output.put_line('start of schema = '|| c_schema.sname);
1265 -- make a recursive call to gather_schema_stats
1266 GATHER_SCHEMA_STATS_SQLPLUS(schemaname=>c_schema.sname , estimate_percent=>estimate_percent ,
1267 degree=>degree , internal_flag=>internal_flag , Errors=> Errors , request_id=>request_id , hmode=>stathist ,
1268 OPTIONS=>OPTIONS , modpercent=>modpercent , invalidate=> invalidate );
1269 /* for rec_cur in lock_stats_tab -- added by saleem
1270 loop
1271 dbms_output.put_line('stats on table ' || rec_cur.table_name || 'is locked ');
1272 dlog('stats on table ' || rec_cur.table_name || 'is locked ');
1273 s_message := 'stats on table ' || rec_cur.table_name || ' is locked ' ;
1274 fnd_file.put_line(FND_FILE.log,s_message);
1275 end loop; */
1276 END LOOP;
1277 /* schema_cur */
1278 END IF;
1279 ELSE -- schema is SYS, print message in log.
1280 dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');
1281 dlog('Please use DBMS_STATS package to gather stats on SYS objects.');
1282 END IF; -- end of schema<> SYS
1283 END;
1284 /* GATHER_SCHEMA_STATS_SQLPLUS */
1285 /************************************************************************/
1286 /* Procedure: GATHER_SCHEMA_STATS */
1287 /* Desciption: Gather schema statistics. This is called by concurrent */
1288 /* manager version of GATHER_SCHEMA_STATS. */
1289 /* Notes: internal_flag='INTERNAL' will call dbms_utility.analyze_schema*/
1290 /* insead of dbms_stats.gather_schema_stats */
1291 /* internal_flag='NOBACKUP' will bypass dbms_stats.export_schema_stats */
1292 /************************************************************************/
1293 PROCEDURE GATHER_SCHEMA_STATS(schemaname IN VARCHAR2,
1294 estimate_percent IN NUMBER ,
1295 degree IN NUMBER ,
1296 internal_flag IN VARCHAR2 ,
1297 --Errors OUT NOCOPY Error_Out,-- commented for handling errors
1298 request_id IN NUMBER DEFAULT NULL,
1299 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
1300 OPTIONS IN VARCHAR2 DEFAULT 'GATHER',
1301 modpercent IN NUMBER DEFAULT 10,
1302 invalidate IN VARCHAR2 DEFAULT 'Y' )
1303 IS
1304 TYPE name_tab
1305 IS
1306 TABLE OF dba_tables.table_name%TYPE;
1307 TYPE partition_tab
1308 IS
1309 TABLE OF sys.dba_tab_modifications.partition_name%TYPE;
1310 TYPE partition_type_tab
1311 IS
1312 TABLE OF dba_tables.partitioned%TYPE;
1313 part_flag partition_type_tab;
1314 names name_tab;
1315 pnames partition_tab;
1316 num_tables NUMBER := 0;
1317 l_message VARCHAR2(1000) ;
1318 granularity VARCHAR2(12);
1319 exist_insufficient EXCEPTION;
1320 pragma exception_init(exist_insufficient,-20002);
1321 err_cnt BINARY_INTEGER := 0;
1322 degree_parallel NUMBER(4);
1323 str_request_id VARCHAR(30);
1324 -- Cursor to get list of tables and indexes with no stats
1325 CURSOR empty_cur(schemaname VARCHAR2)
1326 IS
1327 SELECT type ,
1328 owner,
1329 name
1330 FROM
1331 ( SELECT 'TABLE' type,
1332 owner ,
1333 table_name name
1334 FROM dba_tables dt
1335 WHERE owner=upper(schemaname)
1336 AND
1337 (
1338 iot_type <> 'IOT_OVERFLOW'
1339 OR iot_type IS NULL
1340 )
1341 AND TEMPORARY <> 'Y'
1342 AND last_analyzed IS NULL
1343 -- leave alone if excluded table
1344 AND NOT EXISTS
1345 (SELECT NULL
1346 FROM fnd_exclude_table_stats fets,
1347 fnd_oracle_userid fou ,
1348 fnd_product_installations fpi
1349 WHERE fou.oracle_username=upper(schemaname)
1350 AND fou.oracle_id =fpi.oracle_id
1351 AND fpi.application_id = fets.application_id
1355 UNION
1352 AND dt.table_name = fets.table_name
1353 )
1354
1356
1357 SELECT DISTINCT 'TABLE' type ,
1358 table_owner owner,
1359 table_name name
1360 FROM dba_indexes di
1361 WHERE
1362 (
1363 di.table_owner=upper(schemaname)
1364 OR di.owner =upper(schemaname)
1365 )
1366 AND di.index_type <> 'LOB'
1367 AND di.temporary <> 'Y'
1368 AND di.last_analyzed IS NULL
1369 AND NOT EXISTS
1370 (SELECT NULL
1371 FROM fnd_exclude_table_stats fets,
1372 fnd_oracle_userid fou ,
1373 fnd_product_installations fpi
1374 WHERE fou.oracle_username=upper(schemaname)
1375 AND fou.oracle_id =fpi.oracle_id
1376 AND fpi.application_id =fets.application_id
1377 AND di.table_name =fets.table_name
1378 )
1379 )
1380 ORDER BY type ,
1381 owner,
1382 name ;
1383
1384 CURSOR nomon_tab(schemaname VARCHAR2)
1385 IS
1386 SELECT owner,
1387 table_name
1388 FROM dba_tables dt
1389 WHERE owner=upper(schemaname)
1390 AND
1391 (
1392 iot_type <> 'IOT_OVERFLOW'
1393 OR iot_type IS NULL
1394 )
1395 AND TEMPORARY <> 'Y'
1396 AND monitoring ='NO'
1397 AND NOT EXISTS
1398 (SELECT NULL
1399 FROM dba_external_tables de
1400 WHERE de.table_name=dt.table_name
1401 AND de.owner =dt.owner
1402 )-- added this to avoid externale tables being selected
1403 AND NOT EXISTS
1404 ( SELECT NULL
1405 FROM dba_tab_statistics dts
1406 WHERE dts.stattype_locked IS NOT NULL
1407 AND dts.table_name=dt.table_name
1408 AND dts.owner =dt.owner
1409 ); -- added this to avoid locked objects
1410 CURSOR lock_stats_tab(schemaname VARCHAR2)
1411 IS -- cursor added by saleem to display the warning message for tables with locked stats
1412 SELECT table_name
1413 FROM dba_tab_statistics
1414 WHERE stattype_locked IS NOT NULL
1415 AND owner=upper(schemaname); -- added to display the warning for locked stats
1416 BEGIN
1417 -- Set the package body variable.
1418 stathist := hmode;
1419 -- if request id (restart case) is provided, then this is the cur_request_id
1420 -- valid for both conc program and sql plus case.
1421 IF request_id IS NOT NULL THEN
1422 cur_request_id := request_id;
1423 END IF;
1424 -- get degree of parallelism
1425 IF degree IS NULL THEN
1426 degree_parallel:=def_degree;
1427 ELSE
1428 degree_parallel := degree;
1429 END IF;
1430 -- Initialize the TABLE Errors
1431 --Errors(0) := NULL; -- commented the initialization so that the errors will not be cleared
1432 granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables
1433 err_cnt := 0;
1434 -- If a specific schema is given
1435 IF (upper(schemaname) <> 'SYS') THEN
1436 IF (upper(schemaname) <> 'ALL') THEN
1437 -- Insert/update the fnd_stats_hist table
1438 IF(upper(stathist)<> 'NONE') THEN
1439 BEGIN
1440 -- if(cur_request_id is null) then
1441 -- cur_request_id := GET_REQUEST_ID(request_id);
1442 -- end if;
1443 FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname,
1444 objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S',
1445 percent=>NVL(estimate_percent,10));
1446 END;
1447 END IF; --if(upper(stathist)<> 'NONE')
1451 END IF;
1448 -- backup the existing schema stats
1449 IF ( (upper(internal_flag) = 'BACKUP') ) THEN
1450 FND_STATS.BACKUP_SCHEMA_STATS( schemaname );
1452 IF(upper(OPTIONS)='GATHER') THEN
1453 SELECT table_name ,
1454 partitioned BULK COLLECT
1455 INTO names,
1456 part_flag
1457 FROM dba_tables dt
1458 WHERE owner = upper(schemaname)
1459 AND
1460 (
1461 iot_type <> 'IOT_OVERFLOW'
1462 OR iot_type IS NULL
1463 )
1464 AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
1465 AND NOT EXISTS
1466 (SELECT NULL
1467 FROM fnd_stats_hist fsh
1468 WHERE dt.owner =fsh.schema_name
1469 AND fsh.REQUEST_ID = cur_request_id
1470 AND fsh.object_type ='CASCADE'
1471 AND fsh.history_mode=stathist
1472 AND dt.table_name = fsh.object_name
1473 AND LAST_GATHER_END_TIME IS NOT NULL
1474 )
1475 -- leave alone if excluded table
1476 AND NOT EXISTS
1477 (SELECT NULL
1478 FROM fnd_exclude_table_stats fets,
1479 fnd_oracle_userid fou ,
1480 fnd_product_installations fpi
1481 WHERE fou.oracle_username=upper(schemaname)
1482 AND fou.oracle_id =fpi.oracle_id
1483 AND fpi.application_id = fets.application_id
1484 AND dt.table_name = fets.table_name
1485 )
1486 AND NOT EXISTS
1487 ( SELECT NULL
1488 FROM dba_tab_statistics dts
1489 WHERE dts.stattype_locked IS NOT NULL
1490 AND dts.table_name=dt.table_name
1491 AND dts.owner =dt.owner
1492 )
1493 ORDER BY table_name;
1494
1495 num_tables := SQL%ROWCOUNT;
1496 FOR i IN 1..num_tables
1497 LOOP
1498 IF ( part_flag(i) = 'YES' ) THEN
1499 granularity := FND_STATS.ALL_GRANULARITY ;
1500 ELSE
1501 granularity := FND_STATS.STD_GRANULARITY;
1502 END IF;
1503 BEGIN
1504 FND_STATS.GATHER_TABLE_STATS(ownname => schemaname, tabname => names(i),
1505 percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1506 granularity => granularity, hmode => stathist, invalidate=> invalidate );
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 g_Errors(err_cnt) := 'ERROR: While GATHER_TABLE_STATS:
1510 object_name='
1511 ||schemaname
1512 ||'.'
1513 ||names(i)
1514 ||'***'
1515 ||SQLERRM
1516 ||'***' ;
1517 g_Errors(err_cnt+1) := NULL;
1518 err_cnt := err_cnt+1;
1519 END;
1520 END LOOP;
1521 /* end of individual tables */
1522 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1526 || 'is locked ');
1523 LOOP
1524 dbms_output.put_line('stats on table '
1525 || rec_cur.table_name
1527 dlog('stats on table '
1528 || rec_cur.table_name
1529 || ' is locked ');
1530 -- s_message := 'stats on table ' || rec_cur.table_name || ' is locked ' ;
1531 --fnd_file.put_line(FND_FILE.log,s_message);
1532 END LOOP;
1533 elsif ( (upper(OPTIONS)='GATHER AUTO') OR
1534 (
1535 upper(OPTIONS)='LIST AUTO'
1536 )
1537 ) THEN
1538 -- if db_versn > 81 then call flush, else use whatever
1539 -- data is available in dtm
1540 IF db_versn > 81 THEN
1541 IF(fm_first_flag) THEN
1542 EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO; END;' ;
1543 fm_first_flag := false;
1544 END IF;
1545 END IF;
1546 -- gather stats for stale tables/partitions. Potentially, there
1547 -- could be some redundent stats gathering, if for eg the table
1548 -- and one of its partitions, both are statle. gather_table_stats
1549 -- would get called twice, once for the table ( which would gather
1550 -- stats for the partitions too, and the partition by itself. The
1551 -- probability of this happening is small, and even if that happens
1552 -- on a rare occasion, the overhead should not be that high, so
1553 -- leaving it as it is for the moment. This can be revisited if
1554 -- tests and experience show that that is not the case.
1555 SELECT iv.table_name,
1556 iv.partition_name -- ,subpartition_name
1557 BULK COLLECT
1558 INTO names,
1559 pnames -- ,spnames
1560 FROM
1561 ( SELECT dtm.table_name,
1562 dtm.partition_name
1563 FROM sys.dba_tab_modifications dtm
1564 WHERE dtm.table_owner = upper(schemaname)
1565 AND dtm.partition_name IS NULL
1566 AND EXISTS
1567 ( SELECT NULL
1568 FROM dba_tables dt
1569 WHERE dt.owner =dtm.table_owner
1570 AND dt.table_name =dtm.table_name
1571 AND dt.partitioned='NO'
1572 AND
1573 (
1574 NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
1575 )
1576 > (modpercent*NVL(dt.num_rows,0))/100
1577 )
1578
1579 UNION ALL
1580
1581 SELECT dtm.table_name,
1582 dtm.partition_name
1583 FROM sys.dba_tab_modifications dtm
1584 WHERE dtm.table_owner = upper(schemaname)
1585 AND dtm.partition_name IS NOT NULL
1586 AND EXISTS
1587 ( SELECT NULL
1588 FROM dba_tab_partitions dtp
1589 WHERE dtp.table_owner =dtm.table_owner
1590 AND dtp.table_name =dtm.table_name
1591 AND dtp.partition_name=dtm.partition_name
1592 AND
1593 (
1597 )
1594 NVL(dtm.inserts,0)+NVL(dtm.updates,0)+NVL(dtm.deletes,0)
1595 )
1596 > (modpercent*NVL(dtp.num_rows,0))/100
1598 ) iv
1599 ORDER BY table_name;
1600
1601 num_tables := SQL%ROWCOUNT;
1602 FOR i IN 1..num_tables
1603 LOOP
1604 BEGIN
1605 IF (upper(OPTIONS)='GATHER AUTO') THEN
1606 FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1607 tabname => names(i), percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>pnames(i),
1608 CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1609 ELSE
1610 dlog('Statistics on '
1611 ||schemaname
1612 ||'.'
1613 ||names(i)
1614 ||'Partition '
1615 ||NVL(pnames(i),'n/a')
1616 ||' are Stale');
1617 END IF;
1618 EXCEPTION
1619 WHEN OTHERS THEN
1620 g_Errors(err_cnt) := 'ERROR: While GATHER_TABLE_STATS:
1621 object_name='
1622 ||schemaname
1623 ||'.'
1624 ||names(i)
1625 ||'***'
1626 ||SQLERRM
1627 ||'***' ;
1628 g_Errors(err_cnt+1) := NULL;
1629 err_cnt := err_cnt+1;
1630 END;
1631 END LOOP;
1632 /* end of individual tables */
1633 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1634 LOOP
1635 dbms_output.put_line('stats on table '
1636 || rec_cur.table_name
1637 || ' is locked ');
1638 dlog('stats on table '
1639 || rec_cur.table_name
1640 || ' is locked ');
1641 -- s_message := 'stats on table ' || rec_cur.table_name || ' is locked ' ;
1642 --fnd_file.put_line(FND_FILE.log,s_message);
1643 END LOOP;
1644 -- GATHER AUTO includes GATHER EMPTY, so gather stats
1645 -- on any unalalyzed tables and/or indexes.
1646 FOR c_rec IN empty_cur(upper(schemaname))
1647 LOOP
1648 IF c_rec.type = 'TABLE' THEN
1649 IF (upper(OPTIONS)='GATHER AUTO') THEN
1650 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,
1651 tabname => c_rec.name, percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL,
1652 CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1653 ELSE
1654 dlog('Table '
1655 ||c_rec.owner
1656 ||'.'
1657 ||c_rec.name
1658 ||' is missing statistics.');
1659 END IF;
1660 END IF;
1661 END LOOP;
1662 -- Check if there are any tables in the schema which does not have
1663 -- monitoring enabled. If yes, gather stats for them using 10% and
1664 -- enable monitoring for such tables so that we have data for them
1665 -- in dba_tab_modifications for next time.
1666 FOR c_rec IN nomon_tab(upper(schemaname))
1667 LOOP
1671 granularity => granularity, hmode => stathist, invalidate=> invalidate );
1668 IF (upper(OPTIONS)='GATHER AUTO') THEN
1669 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.table_name,
1670 percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1672 EXECUTE IMMEDIATE 'alter table '
1673 ||c_rec.owner
1674 ||'.'
1675 ||c_rec.table_name
1676 ||' monitoring';
1677 dlog('Monitoring has now been enabled for Table '
1678 ||c_rec.owner
1679 ||'.'
1680 ||c_rec.table_name
1681 ||'. Stats were gathered.' );
1682 ELSE
1683 dlog('Monitoring is not enabled for Table '
1684 ||c_rec.owner
1685 ||'.'
1686 ||c_rec.table_name );
1687 END IF;
1688 END LOOP; -- nomon_tab
1689 elsif ( (upper(OPTIONS)='GATHER EMPTY') OR
1690 (
1691 upper(OPTIONS)='LIST EMPTY'
1692 )
1693 ) THEN
1694 FOR c_rec IN empty_cur(upper(schemaname))
1695 LOOP
1696 IF c_rec.type = 'TABLE' THEN
1697 IF (upper(OPTIONS)='GATHER EMPTY') THEN
1698 FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1699 percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1700 granularity => granularity, hmode => stathist, invalidate=> invalidate );
1701 ELSE
1702 dlog('Table '
1703 ||c_rec.owner
1704 ||'.'
1705 ||c_rec.name
1706 ||' is missing statistics! ');
1707 END IF;
1708 END IF;
1709 END LOOP;
1710 FOR rec_cur IN lock_stats_tab(upper(schemaname)) -- added by saleem to display warning for tables with locked stats
1711 LOOP
1712 dbms_output.put_line('stats on table '
1713 || rec_cur.table_name
1714 || ' is locked ');
1715 dlog('stats on table '
1716 || rec_cur.table_name
1717 || ' is locked ');
1718 -- s_message := 'stats on table ' || rec_cur.table_name || ' is locked ' ;
1719 --fnd_file.put_line(FND_FILE.log,s_message);
1720 END LOOP;
1721 END IF;
1722 /* end of if upper(options)= */
1723 -- End timestamp
1724 IF(upper(stathist) <> 'NONE') THEN
1725 BEGIN
1726 FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1727 END;
1728 END IF;
1729 ELSE
1730 /* This is for ALL schema */
1731 FOR c_schema IN schema_cur
1732 LOOP
1733 --dbms_output.put_line('start of schema = '|| c_schema.sname);
1734 -- make a recursive call to gather_schema_stats
1735 GATHER_SCHEMA_STATS(schemaname=>c_schema.sname , estimate_percent=>estimate_percent , degree=>degree , internal_flag=>internal_flag ,
1736 --Errors=> Errors ,-- commented for error handling
1737 request_id=>request_id , hmode=>stathist , OPTIONS=>OPTIONS , modpercent=>modpercent , invalidate=> invalidate );
1738 END LOOP;
1739 /* schema_cur */
1740 END IF;
1741 ELSE -- schema is SYS, print message in log.
1745 END;
1742 dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');
1743 dlog('Please use DBMS_STATS package to gather stats on SYS objects.');
1744 END IF; -- end of schema<> SYS
1746 /* GATHER_SCHEMA_STATS */
1747 /************************************************************************/
1748 /* Procedure: GATHER_INDEX_STATS */
1749 /* Desciption: Gathers stats for a particular index. */
1750 /************************************************************************/
1751 PROCEDURE GATHER_INDEX_STATS(ownname IN VARCHAR2,
1752 indname IN VARCHAR2,
1753 percent IN NUMBER DEFAULT NULL,
1754 degree IN NUMBER DEFAULT NULL,
1755 partname IN VARCHAR2 DEFAULT NULL,
1756 backup_flag IN VARCHAR2 ,
1757 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
1758 invalidate IN VARCHAR2 DEFAULT 'Y' )
1759 IS
1760 num_blks NUMBER;
1761 adj_percent NUMBER ; -- adjusted percent based on table blocks.
1762 exist_insufficient EXCEPTION;
1763 pragma exception_init(exist_insufficient,-20002);
1764 degree_parallel NUMBER(4) ;
1765 BEGIN
1766 -- Set the package body variable.
1767 stathist := hmode;
1768 num_blks := fnd_stats.get_blocks(ownname,indname,'INDEX');
1769 -- In 8i, you cannot provide a degree for an index, in 9iR2 we can.
1770 IF num_blks <= SMALL_IND_FOR_PAR_THOLD THEN
1771 degree_parallel:=1;
1772 ELSE
1773 IF degree IS NULL THEN
1774 degree_parallel:=def_degree;
1775 ELSE
1776 degree_parallel :=degree;
1777 END IF;
1778 END IF;
1779 -- For better stats, indexes smaller than small_ind_for_est_thold
1780 -- should be gathered at 100%.
1781 IF num_blks <= SMALL_IND_FOR_EST_THOLD THEN
1782 IF ((db_versn>80) AND
1783 (
1784 db_versn < 90
1785 )
1786 ) THEN -- w/a for bug 1998176
1787 adj_percent:=99.99;
1788 ELSE
1789 adj_percent:=100;
1790 END IF;
1791 ELSE
1792 adj_percent:=percent;
1793 END IF;
1794 -- Insert/update the fnd_stat_hist table
1795 IF(upper(stathist) <> 'NONE') THEN
1796 BEGIN
1797 FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));
1798 END;
1799 END IF;
1800 -- backup the existing index stats
1801 IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
1802 -- First create the FND_STATTAB if it doesn't exist.
1803 BEGIN
1804 FND_STATS.CREATE_STAT_TABLE();
1805 EXCEPTION
1806 WHEN exist_insufficient THEN
1807 NULL;
1808 END;
1809 DBMS_STATS.EXPORT_INDEX_STATS( ownname, indname, NULL, fnd_stattab, NULL, fnd_statown );
1810 END IF;
1811 FND_STATS.GATHER_INDEX_STATS_PVT(ownname => ownname, indname => indname, partname => partname, estimate_percent => NVL(adj_percent,10), degree=>degree_parallel, invalidate => invalidate ) ;
1812 -- End timestamp
1813 IF(upper(stathist) <> 'NONE') THEN
1814 BEGIN
1815 -- update fnd_stats_hist for completed stats
1816 FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1817 END;
1818 END IF;
1819 END ;
1820 /* GATHER_INDEX_STATS */
1821 /************************************************************************/
1822 /* Procedure: GATHER_TABLE_STATS */
1823 /* Desciption: Gathers stats for a particular table. Concurrent program */
1824 /* version. */
1825 /************************************************************************/
1826 PROCEDURE GATHER_TABLE_STATS(errbuf OUT NOCOPY VARCHAR2,
1827 retcode OUT NOCOPY VARCHAR2,
1828 ownname IN VARCHAR2,
1829 tabname IN VARCHAR2,
1830 percent IN NUMBER,
1831 degree IN NUMBER,
1832 partname IN VARCHAR2,
1833 backup_flag IN VARCHAR2,
1834 granularity IN VARCHAR2,
1835 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
1836 invalidate IN VARCHAR2 DEFAULT 'Y' )
1837 IS
1838 exist_insufficient EXCEPTION;
1839 pragma exception_init(exist_insufficient,-20000);
1843 granularity,hmode,invalidate);
1840 l_message VARCHAR2(1000);
1841 BEGIN
1842 FND_STATS.GATHER_TABLE_STATS(ownname, tabname, percent, degree, partname, backup_flag, true,
1844 EXCEPTION
1845 WHEN exist_insufficient THEN
1846 errbuf := sqlerrm ;
1847 retcode := '2';
1848 l_message := errbuf;
1849 FND_FILE.put_line(FND_FILE.log,l_message);
1850 raise;
1851 WHEN OTHERS THEN
1852 errbuf := sqlerrm ;
1853 retcode := '2';
1854 l_message := errbuf;
1855 FND_FILE.put_line(FND_FILE.log,l_message);
1856 raise;
1857 END;
1858 /* GATHER_TABLE_STATS */
1859 /************************************************************************/
1860 /* Procedure: GATHER_TABLE_STATS */
1861 /* Desciption: Gathers stats for a particular table. Called by */
1862 /* Concurrent program version. */
1863 /************************************************************************/
1864 PROCEDURE GATHER_TABLE_STATS(ownname IN VARCHAR2,
1865 tabname IN VARCHAR2,
1866 percent IN NUMBER,
1867 degree IN NUMBER,
1868 partname IN VARCHAR2,
1869 backup_flag IN VARCHAR2,
1870 CASCADE IN BOOLEAN,
1871 granularity IN VARCHAR2,
1872 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
1873 invalidate IN VARCHAR2 DEFAULT 'Y' )
1874 IS
1875 cascade_true BOOLEAN := TRUE;
1876 approx_num_rows NUMBER ;
1877 num_blks NUMBER;
1878 adj_percent NUMBER ; -- adjusted percent based on table blocks.
1879 num_ind_rows NUMBER;
1880 obj_type VARCHAR2(7);
1881 method VARCHAR2(2000) ;
1882 exist_insufficient EXCEPTION;
1883 pragma exception_init(exist_insufficient,-20002);
1884 -- New cursor to support MVs
1885 CURSOR col_cursor (ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2)
1886 IS
1887 SELECT a.column_name,
1888 NVL(a.hsize,254) hsize
1889 FROM FND_HISTOGRAM_COLS a
1890 WHERE a.table_name = upper(tabname)
1891 AND
1892 (
1893 a.partition = upper(partname)
1894 OR partname IS NULL
1895 )
1896 ORDER BY a.column_name;
1897
1898 CURSOR ind_cursor(ownname VARCHAR2,tabname VARCHAR2)
1899 IS
1900 SELECT a.index_name indname,
1901 a.owner indowner ,
1902 a.uniqueness uniq
1903 FROM dba_indexes a
1904 WHERE table_name = upper(tabname)
1905 AND table_owner= upper(ownname)
1906 ORDER BY index_name;
1907
1908 degree_parallel NUMBER(4);
1909 BEGIN
1910 -- Set the package body variable.
1911 stathist := hmode;
1912 num_blks :=fnd_stats.get_blocks(ownname,tabname,'TABLE');
1913 -- For better performance, tables smaller than small_tab_for_par_thold should be gathered in serial.
1914 IF num_blks <= SMALL_TAB_FOR_PAR_THOLD THEN
1915 degree_parallel:=1;
1916 elsif degree IS NULL THEN -- degree will not be null when called from gather_schema_stats
1917 degree_parallel:=def_degree;
1918 ELSE
1919 degree_parallel := degree;
1920 END IF;
1921 -- For better stats, tables smaller than small_tab_for_est_thold
1922 -- should be gathered at 100%.
1923 IF num_blks <= SMALL_TAB_FOR_EST_THOLD THEN
1924 IF ((db_versn>80) AND
1925 (
1926 db_versn < 90
1927 )
1928 ) THEN -- w/a for bug 1998176
1929 adj_percent:=99.99;
1930 ELSE
1931 adj_percent:=100;
1932 END IF;
1933 ELSE
1934 adj_percent:=percent;
1935 END IF;
1936 -- Insert/update the fnd_stat_hist table
1937 -- change to call update_hist for autonomous_transaction
1938 IF (CASCADE) THEN
1939 obj_type:='CASCADE';
1940 ELSE
1941 obj_type := 'TABLE';
1942 END IF;
1943 IF(upper(stathist) <> 'NONE') THEN
1944 BEGIN
1945 -- if(cur_request_id is null) then
1946 -- cur_request_id := GET_REQUEST_ID(null); -- for gather table stats, we will not have a request_id
1947 -- end if;
1948 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));
1949 EXCEPTION
1950 WHEN OTHERS THEN
1951 raise;
1952 END;
1953 END IF;
1954 -- backup the existing table stats
1955 IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
1956 BEGIN
1960 EXCEPTION
1957 -- First create the FND_STATTAB if it doesn't exist.
1958 BEGIN
1959 FND_STATS.CREATE_STAT_TABLE();
1961 WHEN exist_insufficient THEN
1962 NULL;
1963 END;
1964 DBMS_STATS.EXPORT_TABLE_STATS(ownname, tabname, partname, fnd_stattab,NULL,CASCADE,fnd_statown );
1965 EXCEPTION
1966 WHEN OTHERS THEN
1967 raise;
1968 END;
1969 END IF;
1970 IF (db_versn >= 92) THEN
1971 --Build up the method_opt if histogram cols are present
1972 method := ' FOR COLUMNS ' ;
1973 FOR c_rec IN col_cursor(ownname,tabname,partname)
1974 LOOP
1975 method := method
1976 ||' '
1977 || c_rec.column_name
1978 ||' SIZE '
1979 || c_rec.hsize ;
1980 END LOOP;
1981 -- If no histogram cols then nullify method ;
1982 IF method = ' FOR COLUMNS ' THEN
1983 method := 'FOR ALL COLUMNS SIZE 1' ;
1984 END IF;
1985 IF (method = 'FOR ALL COLUMNS SIZE 1') THEN
1986 BEGIN
1987 --dbms_output.put_line('SINGLE:'||method||'granularity='||granularity);
1988 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
1989 partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
1990 CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 -- dbms_output.put_line('about to raise'||sqlcode||' --- '||sqlerrm);
1994 -- Error code for external table error is ora-20000 which is the same as the code
1995 -- for exist_insufficient error. Because of that, we have to resort to the following
1996 -- if check on the error message.
1997 IF(SUBSTR(sqlerrm,instr(sqlerrm,',')+2)= 'sampling on external table is not supported') THEN
1998 NULL; -- Ignore this error because apps does not use External tables.
1999 ELSE
2000 raise;
2001 END IF;
2002 END;
2003 ELSE -- call it with histogram cols.
2004 BEGIN
2005 -- dbms_output.put_line('FOR ALL COLUMNS SIZE 1 '||method);
2006 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname, partname => partname, method_opt => 'FOR ALL COLUMNS SIZE 1 '
2007 ||method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel, CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2008 EXCEPTION
2009 WHEN OTHERS THEN
2010 raise;
2011 END;
2012 END IF;
2013 ELSE -- version is pre 9.2, use the old method of calling twice.
2014 --Build up the method_opt if histogram cols are present
2015 method := ' FOR COLUMNS ' ;
2016 FOR c_rec IN col_cursor(ownname,tabname,partname)
2017 LOOP
2018 IF method <> ' FOR COLUMNS ' THEN
2019 method := method
2020 || ',' ;
2021 END IF;
2022 method := method
2023 ||' '
2024 || c_rec.column_name
2025 ||' SIZE '
2026 || c_rec.hsize ;
2027 END LOOP;
2028 -- If no histogram cols then nullify method ;
2029 IF method = ' FOR COLUMNS ' THEN
2030 method := 'FOR ALL COLUMNS SIZE 1' ;
2031 END IF;
2032 -- Due to the limitations of in DBMS_STATS in 8i we need to call
2033 -- FND_STATS.GATHER_TABLE_STATS twice, once for histogram
2034 -- and once for just the table stats.
2035 IF (method = 'FOR ALL COLUMNS SIZE 1') THEN
2036 BEGIN
2037 --dbms_output.put_line('SINGLE:'||method||'granularity='||granularity);
2038 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2039 partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
2040 CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2041 EXCEPTION
2042 WHEN OTHERS THEN
2043 raise;
2044 END;
2045 ELSE -- call it twice
2046 BEGIN
2047 --dbms_output.put_line('DOUBLE 1:'||method||'granularity='||granularity);
2048 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2049 partname => partname, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent => NVL(adj_percent,10),
2053 raise;
2050 degree => degree_parallel, CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2051 EXCEPTION
2052 WHEN OTHERS THEN
2054 END;
2055 BEGIN
2056 --dbms_output.put_line('DOUBLE 2:'||method||'granularity='||granularity);
2057 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2058 partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
2059 CASCADE => FALSE, granularity => granularity, invalidate=> invalidate );
2060 EXCEPTION
2061 WHEN OTHERS THEN
2062 raise;
2063 END;
2064 END IF;
2065 END IF; -- db_versn is 8i
2066 -- End timestamp
2067 -- change to call update_hist for autonomous_transaction
2068 IF(upper(stathist) <> 'NONE') THEN
2069 BEGIN
2070 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
2071 EXCEPTION
2072 WHEN OTHERS THEN
2073 raise;
2074 END;
2075 END IF;
2076 END ;
2077 /* GATHER_TABLE_STATS */
2078 /************************************************************************/
2079 /* Procedure: GATHER_COLUMN_STATS */
2080 /* Desciption: Gathers stats for all columns in FND_HISTOGRAM_COLS table*/
2081 /************************************************************************/
2082 PROCEDURE GATHER_COLUMN_STATS(appl_id IN NUMBER DEFAULT NULL,
2083 percent IN NUMBER DEFAULT NULL,
2084 degree IN NUMBER DEFAULT NULL,
2085 backup_flag IN VARCHAR2 ,
2086 --Errors OUT NOCOPY Error_Out, -- commented for errorhandling
2087 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
2088 invalidate IN VARCHAR2 DEFAULT 'Y' )
2089 IS
2090 -- New cursor to support MVs
2091 CURSOR tab_cursor (appl_id NUMBER)
2092 IS
2093 SELECT DISTINCT a.application_id,
2094 a.table_name ,
2095 a.partition
2096 FROM FND_HISTOGRAM_COLS a
2097 WHERE
2098 (
2099 a.application_id = appl_id
2100 OR appl_id IS NULL
2101 )
2102 ORDER BY a.application_id,
2103 a.table_name;
2104
2105 -- New cursor to support MVs
2106 CURSOR col_cursor (appl_id NUMBER, tabname VARCHAR2, partname VARCHAR2)
2107 IS
2108 SELECT a.column_name ,
2109 NVL(a.hsize,254) hsize,
2110 NVL(a.owner, upper(b.oracle_username)) ownname
2111 FROM FND_HISTOGRAM_COLS a,
2112 FND_ORACLE_USERID b ,
2113 FND_PRODUCT_INSTALLATIONS c
2114 WHERE a.application_id = appl_id
2115 AND a.application_id = c.application_id (+)
2116 AND c.oracle_id = b.oracle_id (+)
2117 AND a.table_name = upper(tabname)
2118 AND
2119 (
2120 a.partition = upper(partname)
2121 OR partname IS NULL
2122 )
2123 ORDER BY a.column_name;
2124
2125 exist_insufficient EXCEPTION;
2126 pragma exception_init(exist_insufficient,-20002);
2127 owner VARCHAR2(30);
2128 i BINARY_INTEGER := 0;
2129 method VARCHAR2(2000);
2130 degree_parallel NUMBER (4);
2131 /* defind variables for the bulk fetch */
2132 TYPE num_list
2133 IS
2134 TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
2135 TYPE char_list
2136 IS
2137 TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
2138 list_column_name char_list;
2139 list_hsize num_list;
2140 list_ownname char_list;
2141 BEGIN
2142 -- Set the package body variable.
2143 stathist := hmode;
2144 IF degree IS NULL THEN
2145 degree_parallel:=def_degree;
2146 ELSE
2147 degree_parallel := degree;
2148 END IF;
2149 -- Initialize the TABLE Errors
2150 --Errors(0) := NULL; -- commented for stopping the initialization
2151 FOR t_rec IN tab_cursor(appl_id)
2152 LOOP
2153 method := ' FOR COLUMNS ';
2154 /* initialize method_opt variable */
2155 /* Bulk fetch data from col_cursor and loop through it */
2156 OPEN col_cursor (t_rec.application_id,t_rec.table_name, t_rec.partition);
2157 FETCH col_cursor BULK COLLECT
2158 INTO list_column_name,
2159 list_hsize ,
2160 list_ownname;
2161
2162 CLOSE col_cursor;
2163 FOR i IN 1..list_column_name.last
2164 LOOP
2165 IF(upper(stathist) <> 'NONE') THEN
2166 BEGIN
2170 -- First export the col stats depending on backup-flag
2167 FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'S' );
2168 END;
2169 END IF;
2171 IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP') THEN
2172 BEGIN
2173 -- First create the FND_STATTAB if it doesn't exist.
2174 BEGIN
2175 FND_STATS.CREATE_STAT_TABLE();
2176 EXCEPTION
2177 WHEN exist_insufficient THEN
2178 NULL;
2179 END;
2180 DBMS_STATS.EXPORT_COLUMN_STATS(list_ownname(i), t_rec.table_name, list_column_name(i), t_rec.partition, fnd_stattab, NULL, fnd_statown);
2181 END;
2182 END IF;
2183 -- Build up the method_opt variable
2184 IF (method <> ' FOR COLUMNS ') THEN
2185 method := method
2186 || ',';
2187 END IF;
2188 method := method
2189 || list_column_name(i)
2190 ||' SIZE '
2191 || list_hsize(i);
2192 owner := list_ownname(i);
2193 END LOOP;
2194 /* end of c_rec */
2195 BEGIN
2196 FND_STATS.GATHER_TABLE_STATS_PVT(ownname => owner, tabname => t_rec.table_name,
2197 partname => t_rec.partition, estimate_percent => NVL(percent,10), method_opt => method,
2198 degree => degree_parallel, CASCADE => FALSE, invalidate=> invalidate, stattab => fnd_stattab,
2199 statown => fnd_statown);
2200 -- now that histograms are collected update fnd_stats_hist
2201 IF(upper(stathist) <> 'NONE') THEN
2202 FOR i IN 1..list_column_name.last
2203 LOOP
2204 FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'E' );
2205 END LOOP;
2206 END IF;
2207 EXCEPTION
2208 WHEN OTHERS THEN
2209 g_Errors(i) := 'ERROR: In GATHER_COLUMN_STATS: '
2210 ||SQLERRM ;
2211 g_Errors(i+1) := NULL;
2212 i := i+1;
2213 END;
2214 /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2215 END LOOP;
2216 /* end of t_rec */
2217 END;
2218 /* end of procedure GATHER_COLUMN_STATS */
2219 /************************************************************************/
2220 /* Procedure: GATHER_ALL_COLUMN_STATS */
2221 /* Desciption: Gathers cols stats for a given schema */
2222 /* or if ownname = 'ALL' then for ALL apps schema */
2223 /************************************************************************/
2224 PROCEDURE GATHER_ALL_COLUMN_STATS(ownname IN VARCHAR2,
2225 percent IN NUMBER DEFAULT NULL,
2226 degree IN NUMBER DEFAULT NULL,
2227 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
2228 invalidate IN VARCHAR2 DEFAULT 'Y' )
2229 IS
2230 -- New cursor for MVs
2231 CURSOR tab_cursor (ownname VARCHAR2)
2232 IS
2233 SELECT DISTINCT a.table_name,
2234 a.application_id
2235 FROM FND_HISTOGRAM_COLS a,
2236 FND_ORACLE_USERID b ,
2237 FND_PRODUCT_INSTALLATIONS c
2238 WHERE
2239 (
2240 b.oracle_username= upper(ownname)
2241 OR a.owner =upper(ownname)
2242 )
2243 AND a.application_id = c.application_id (+)
2244 AND c.oracle_id = b.oracle_id (+)
2245 ORDER BY 2 ,
2246 1;
2247
2248 CURSOR col_cursor (appl_id NUMBER, tabname VARCHAR2)
2249 IS
2250 SELECT column_name,
2251 NVL(hsize,254) hsize
2252 FROM FND_HISTOGRAM_COLS a
2253 WHERE a.application_id = appl_id
2254 AND a.table_name = upper(tabname)
2255 ORDER BY 1 ;
2256
2257 method VARCHAR2(2000) ;
2258 degree_parallel NUMBER (4);
2259 BEGIN
2260 -- Set the package body variable.
2261 stathist := hmode;
2262 IF degree IS NULL THEN
2263 degree_parallel:=def_degree;
2264 ELSE
2265 degree_parallel := degree;
2266 END IF;
2267 -- If a specific schema is given
2271 LOOP
2268 IF (upper(ownname) <> 'ALL') THEN
2269 -- get the tables for the given schema
2270 FOR t_rec IN tab_cursor(ownname)
2272 -- Insert/update the fnd_stats_hist table
2273 --dbms_output.put_line('appl_id = '||t_rec.application_id||',table='||t_rec.table_name);
2274 IF(upper(stathist) <> 'NONE') THEN
2275 BEGIN
2276 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2277 END;
2278 END IF;
2279 -- get the column list and build up the METHOD_OPT
2280 method := ' FOR COLUMNS ';
2281 FOR c_rec IN col_cursor(t_rec.application_id, t_rec.table_name)
2282 LOOP
2283 -- Build up the method_opt variable
2284 IF (method <> ' FOR COLUMNS ') THEN
2285 method := method
2286 || ',';
2287 END IF;
2288 method := method
2289 || c_rec.column_name
2290 ||' SIZE '
2291 || c_rec.hsize;
2292 END LOOP ;
2293 /* c_rec */
2294 --dbms_output.put_line(' method = '|| method);
2295 BEGIN
2296 FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => t_rec.table_name, estimate_percent => NVL(percent,10), method_opt => method, degree => degree_parallel, CASCADE => FALSE, invalidate => invalidate );
2297 EXCEPTION
2298 WHEN OTHERS THEN
2299 raise;
2300 END;
2301 /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2302 -- End timestamp
2303 IF(upper(stathist) <> 'NONE') THEN
2304 BEGIN
2305 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
2306 END;
2307 END IF;
2308 END LOOP ;
2309 /* t_rec */
2310 ELSE
2311 /* ownname = 'ALL' */
2312 FOR s_rec IN schema_cur
2313 LOOP
2314 --dbms_output.put_line('start of schema = '|| s_rec.sname);
2315 -- get the tables for the given schema
2316 FOR t_rec IN tab_cursor(s_rec.sname)
2317 LOOP
2318 -- Insert/update the fnd_stat_hist table
2319 --dbms_output.put_line('appl_id = '||t_rec.application_id||',table='||t_rec.table_name);
2320 IF(upper(stathist) <> 'NONE') THEN
2321 BEGIN
2322 FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2323 END;
2324 END IF;
2325 -- get the column list and build up the METHOD_OPT
2326 method := ' FOR COLUMNS ';
2327 FOR c_rec IN col_cursor(t_rec.application_id, t_rec.table_name)
2328 LOOP
2329 -- Build up the method_opt variable
2330 IF (method <> ' FOR COLUMNS ') THEN
2331 method := method
2332 || ',';
2333 END IF;
2334 method := method
2335 || c_rec.column_name
2336 ||' SIZE '
2337 || c_rec.hsize;
2338 END LOOP ;
2339 /* c_rec */
2340 --dbms_output.put_line(' method = '|| method);
2341 BEGIN
2342 FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => s_rec.sname,
2343 tabname => t_rec.table_name, estimate_percent => NVL(percent,10), method_opt => method,
2344 degree => degree_parallel, CASCADE => FALSE, invalidate => invalidate );
2345 EXCEPTION
2346 WHEN OTHERS THEN
2347 raise;
2348 END;
2349 /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2350 -- End timestamp
2351 IF(upper(stathist) <> 'NONE') THEN
2352 BEGIN
2353 FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2357 /* t_rec */
2354 END;
2355 END IF;
2356 END LOOP ;
2358 END LOOP ;
2359 /* s_rec */
2360 END IF ;
2361 /* end of ownname='ALL' */
2362 END;
2363 /* end of GATHER_ALL_COLUMN_STATS */
2364 /************************************************************************/
2365 /* Procedure: GATHER_ALL_COLUMN_STATS */
2366 /* Desciption: Gathers cols stats for a given schema */
2367 /* or if ownname = 'ALL' then for ALL apps schema. This the concurrent */
2368 /* program manager version */
2369 /************************************************************************/
2370 PROCEDURE GATHER_ALL_COLUMN_STATS(errbuf OUT NOCOPY VARCHAR2,
2371 retcode OUT NOCOPY VARCHAR2,
2372 ownname IN VARCHAR2,
2373 percent IN NUMBER DEFAULT NULL,
2374 degree IN NUMBER DEFAULT NULL,
2375 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
2376 invalidate IN VARCHAR2 DEFAULT 'Y' )
2377 IS
2378 l_message VARCHAR2(2000);
2379 BEGIN
2380 -- Set the package body variable.
2381 stathist := hmode;
2382 FND_STATS.GATHER_ALL_COLUMN_STATS(ownname=>ownname,percent=>percent,degree=>degree,hmode=>stathist,invalidate=>invalidate);
2383 EXCEPTION
2384 WHEN OTHERS THEN
2385 errbuf := sqlerrm ;
2386 retcode := '2';
2387 l_message := errbuf;
2388 FND_FILE.put_line(FND_FILE.log,l_message);
2389 raise;
2390 END;
2391 /* end of conc mgr GATHER_ALL_COLUMN_STATS */
2392 /************************************************************************/
2393 /* Procedure: GATHER_COLUMN_STATS */
2394 /* Desciption: Gathers cols stats This the concurrent program manager */
2395 /* version */
2396 /************************************************************************/
2397 PROCEDURE GATHER_COLUMN_STATS(errbuf OUT NOCOPY VARCHAR2,
2398 retcode OUT NOCOPY VARCHAR2,
2399 ownname IN VARCHAR2,
2400 tabname IN VARCHAR2,
2401 colname IN VARCHAR2,
2402 percent IN NUMBER DEFAULT NULL,
2403 degree IN NUMBER DEFAULT NULL,
2404 hsize IN NUMBER DEFAULT 254,
2405 backup_flag IN VARCHAR2 ,
2406 partname IN VARCHAR2 DEFAULT NULL,
2407 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
2408 invalidate IN VARCHAR2 DEFAULT 'Y' )
2409 IS
2410 exist_insufficient EXCEPTION;
2411 pragma exception_init(exist_insufficient,-20000);
2412 l_message VARCHAR2(1000);
2413 BEGIN
2414 -- Set the package body variable.
2415 stathist := hmode;
2416 l_message := 'In GATHER_COLUMN_STATS , column is '
2417 || ownname
2418 ||'.'
2419 ||tabname
2420 ||'.'
2421 ||colname
2422 ||' backup_flag= '
2423 || backup_flag ;
2424 FND_FILE.put_line(FND_FILE.log,l_message);
2425 dlog(l_message);
2426 BEGIN
2427 dlog('about to g c s');
2428 FND_STATS.GATHER_COLUMN_STATS(ownname,tabname,colname,percent,degree ,hsize,backup_flag,partname,hmode,invalidate);
2429 EXCEPTION
2430 WHEN exist_insufficient THEN
2431 errbuf := sqlerrm ;
2432 retcode := '2';
2433 l_message := errbuf;
2434 FND_FILE.put_line(FND_FILE.log,l_message);
2435 raise;
2436 WHEN OTHERS THEN
2437 errbuf := sqlerrm ;
2438 retcode := '2';
2439 l_message := errbuf;
2440 FND_FILE.put_line(FND_FILE.log,l_message);
2441 raise;
2442 END;
2443 END;
2444 /* end of GATHER_COLUMN_STATS for conc. job */
2445 /************************************************************************/
2446 /* Procedure: GATHER_COLUMN_STATS */
2447 /* Desciption: Gathers cols stats. */
2448 /************************************************************************/
2449 PROCEDURE GATHER_COLUMN_STATS(ownname IN VARCHAR2,
2450 tabname IN VARCHAR2,
2451 colname IN VARCHAR2,
2452 percent IN NUMBER DEFAULT NULL,
2453 degree IN NUMBER DEFAULT NULL,
2454 hsize IN NUMBER DEFAULT 254,
2455 backup_flag IN VARCHAR2 ,
2456 partname IN VARCHAR2 DEFAULT NULL,
2457 hmode IN VARCHAR2 DEFAULT 'LASTRUN',
2458 invalidate IN VARCHAR2 DEFAULT 'Y' )
2459 IS
2460 method VARCHAR2(200);
2461 exist_insufficient EXCEPTION;
2462 pragma exception_init(exist_insufficient,-20002);
2466 -- Set the package body variable.
2463 degree_parallel NUMBER (4);
2464 BEGIN
2465 dlog('about to g c s no cm version');
2467 stathist := hmode;
2468 IF degree IS NULL THEN
2469 degree_parallel:=def_degree;
2470 ELSE
2471 degree_parallel := degree;
2472 END IF;
2473 -- Insert/update the fnd_stat_hist table
2474 IF(upper(stathist) <> 'NONE') THEN
2475 BEGIN
2476 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>partname, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'S' );
2477 END;
2478 END IF;
2479 -- First export the col stats depending on the backup_flag
2480 IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
2481 BEGIN
2482 -- First create the FND_STATTAB if it doesn't exist.
2483 BEGIN
2484 FND_STATS.CREATE_STAT_TABLE();
2485 EXCEPTION
2486 WHEN exist_insufficient THEN
2487 NULL;
2488 END;
2489 DBMS_STATS.EXPORT_COLUMN_STATS ( ownname, tabname, colname, partname, fnd_stattab, NULL, fnd_statown );
2490 END;
2491 END IF;
2492 -- Now gather statistics
2493 method := 'FOR COLUMNS SIZE '
2494 || hsize
2495 || ' '
2496 || colname;
2497 FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => tabname, partname =>partname,
2498 estimate_percent => NVL(percent,10), method_opt => method, degree => degree_parallel, CASCADE => FALSE,
2499 stattab => fnd_stattab, statown => fnd_statown, invalidate => invalidate);
2500 -- End timestamp
2501 IF(upper(stathist) <> 'NONE') THEN
2502 BEGIN
2503 FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>NULL, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'E' );
2504 END;
2505 END IF;
2506 END;
2507 /* GATHER_COLUMN_STATS */
2508 /************************************************************************/
2509 /* Procedure: SET_TABLE_STATS */
2510 /* Desciption: Sets table stats to certain values. */
2511 /************************************************************************/
2512 PROCEDURE SET_TABLE_STATS(ownname IN VARCHAR2,
2513 tabname IN VARCHAR2,
2514 numrows IN NUMBER,
2515 numblks IN NUMBER,
2516 avgrlen IN NUMBER,
2517 partname IN VARCHAR2 DEFAULT NULL )
2518 IS
2519 -- PRAGMA AUTONOMOUS_TRANSACTION ;
2520 BEGIN
2521 DBMS_STATS.SET_TABLE_STATS(ownname, tabname, partname, NULL, NULL, numrows, numblks, avgrlen, NULL, NULL);
2522 END;
2523 /* SET_TABLE_STATS */
2524 /************************************************************************/
2525 /* Procedure: SET_INDEX_STATS */
2526 /* Desciption: Sets index stats to certain values. */
2527 /************************************************************************/
2528 PROCEDURE SET_INDEX_STATS(ownname IN VARCHAR2,
2529 indname IN VARCHAR2,
2530 numrows IN NUMBER,
2531 numlblks IN NUMBER,
2532 numdist IN NUMBER,
2533 avglblk IN NUMBER,
2534 avgdblk IN NUMBER,
2535 clstfct IN NUMBER,
2536 indlevel IN NUMBER,
2537 partname IN VARCHAR2 DEFAULT NULL)
2538 IS
2539 l_iot VARCHAR2(5):='FALSE';
2540 l_clstfct NUMBER :=clstfct;
2541 BEGIN
2542 /* add this to fix bug # .....
2543 when the index is of type IOT, set clustering factor to zero
2544 */
2545 /* added to fix bug 2239903 */
2546 SELECT DECODE(index_type,'IOT - TOP', 'TRUE', 'FALSE')
2547 INTO l_iot
2548 FROM dba_indexes
2549 WHERE owner = ownname
2550 AND index_name = indname;
2551
2552 IF (l_iot = 'TRUE') THEN
2553 l_clstfct := 0;
2554 END IF;
2555 DBMS_STATS.SET_INDEX_STATS(ownname, indname, partname, NULL, NULL, numrows, numlblks, numdist, avglblk, avgdblk, l_clstfct, indlevel, NULL, NULL);
2556 EXCEPTION
2557 WHEN OTHERS THEN
2558 NULL;
2559 END;
2560 /* SET_INDEX_STATS */
2561 /******************************************************************************/
2562 /* Procedure: LOAD_XCLUD_TAB */
2563 /* Desciption: This procedure was deprecated, but 11.5.2CU2 onwards */
2564 /* we are reuseing it for a different purpose. This procedure */
2565 /* will be used to populate fnd_exclude_table_stats table , which */
2566 /* which contains the list of tables which should be skipped */
2567 /* by the gather schema stats program. */
2568 /******************************************************************************/
2569 PROCEDURE LOAD_XCLUD_TAB(action IN VARCHAR2,
2570 appl_id IN NUMBER,
2571 tabname IN VARCHAR2)
2572 IS
2576 (
2573 exist_flag VARCHAR2(6) := NULL;
2574 BEGIN
2575 IF ((Upper(action) = 'INSERT') OR
2577 Upper(action) = 'INS'
2578 )
2579 OR
2580 (
2581 Upper(action) = 'I'
2582 )
2583 ) THEN
2584 -- Check for existence of the table first in FND Dictionary
2585 -- then in data dictionary
2586 -- break out if it doesn't exist
2587 BEGIN
2588 SELECT 'EXIST'
2589 INTO exist_flag
2590 FROM fnd_tables a
2591 WHERE a.table_name = upper(tabname)
2592 AND a.application_id = appl_id ;
2593
2594 EXCEPTION
2595 WHEN no_data_found THEN
2596 BEGIN
2597 SELECT 'EXIST'
2598 INTO exist_flag
2599 FROM dba_tables
2600 WHERE table_name = upper(tabname)
2601 AND owner =
2602 ( SELECT b.oracle_username
2603 FROM fnd_product_installations a,
2604 fnd_oracle_userid b
2605 WHERE a.application_id = appl_id
2606 AND b.oracle_id = a.oracle_id
2607 );
2608
2609 EXCEPTION
2610 WHEN no_data_found THEN
2611 raise_application_error(-20000, 'Table '
2612 || tabname
2613 || ' does not exist in fnd_tables/dba_tables for the
2614 given application ');
2615 END;
2616 END;
2617 -- Now insert
2618 INSERT
2619 INTO FND_EXCLUDE_TABLE_STATS
2620 (
2621 APPLICATION_ID ,
2622 TABLE_NAME ,
2623 CREATION_DATE ,
2624 CREATED_BY ,
2625 LAST_UPDATE_DATE,
2626 LAST_UPDATED_BY ,
2627 LAST_UPDATE_LOGIN
2628 )
2629 VALUES
2630 (
2631 appl_id ,
2632 upper(tabname),
2633 sysdate ,
2634 1 ,
2635 sysdate ,
2636 1 ,
2637 NULL
2638 ) ;
2639
2640 elsif ((Upper(action) = 'DELETE') OR
2641 (
2642 Upper(action) = 'DEL'
2643 )
2644 OR
2645 (
2646 Upper(action) = 'D'
2647 )
2648 ) THEN
2649 DELETE
2650 FROM FND_EXCLUDE_TABLE_STATS
2651 WHERE table_name = upper(tabname)
2652 AND application_id = appl_id;
2653
2654 END IF;
2655 COMMIT;
2656 END;
2657 /* LOAD_XCLUD_TAB */
2658 /************************************************************************/
2659 /* Procedure: LOAD_HISTOGRAM_COLS */
2660 /* Desciption: This is for internal purpose only. For loading into */
2661 /* SEED database */
2662 /************************************************************************/
2663 PROCEDURE LOAD_HISTOGRAM_COLS(action IN VARCHAR2,
2664 appl_id IN NUMBER,
2665 tabname IN VARCHAR2,
2666 colname IN VARCHAR2,
2667 partname IN VARCHAR2 DEFAULT NULL,
2668 hsize IN NUMBER DEFAULT 254,
2669 commit_flag IN VARCHAR2 DEFAULT 'Y')
2670 IS
2671 exist_flag VARCHAR2(5) := NULL;
2672 BEGIN
2673 IF upper(action) = 'INSERT' THEN
2674 BEGIN
2675 -- Check for existence of the table first
2676 -- break out if it doesn't exist
2677 BEGIN
2678 SELECT DISTINCT('EXIST')
2679 INTO exist_flag
2680 FROM dba_tab_columns a ,
2681 fnd_oracle_userid b,
2682 fnd_product_installations c
2683 WHERE a.table_name = upper(tabname)
2684 AND a.column_name = upper(colname)
2685 AND c.application_id = appl_id
2686 AND c.oracle_id = b.oracle_id
2687 AND a.owner = b.oracle_username;
2688
2689 EXCEPTION
2693 ||'.'
2690 WHEN no_data_found THEN
2691 raise_application_error(-20000, 'Column '
2692 || tabname
2694 || colname
2695 || ' does not exist in dba_tab_columns for the given application ');
2696 WHEN OTHERS THEN
2697 raise_application_error(-20001, 'Error in reading dictionary info. for column '
2698 || tabname
2699 ||'.'
2700 || colname );
2701 END;
2702 BEGIN
2703 INSERT
2704 INTO FND_HISTOGRAM_COLS
2705 (
2706 APPLICATION_ID ,
2707 TABLE_NAME ,
2708 COLUMN_NAME ,
2709 PARTITION ,
2710 HSIZE ,
2711 CREATION_DATE ,
2712 CREATED_BY ,
2713 LAST_UPDATE_DATE,
2714 LAST_UPDATED_BY ,
2715 LAST_UPDATE_LOGIN
2716 )
2717 VALUES
2718 (
2719 appl_id ,
2720 upper(tabname) ,
2721 upper(colname) ,
2722 upper(partname),
2723 hsize ,
2724 sysdate ,
2725 1 ,
2726 sysdate ,
2727 1 ,
2728 NULL
2729 ) ;
2730
2731 EXCEPTION
2732 WHEN DUP_VAL_ON_INDEX THEN
2733 NULL;
2734 END;
2735 END;
2736 elsif upper(action) = 'DELETE' THEN
2737 BEGIN
2738 DELETE
2739 FROM FND_HISTOGRAM_COLS
2740 WHERE application_id = appl_id
2741 AND table_name = upper(tabname)
2742 AND column_name = upper(colname)
2743 AND
2744 (
2745 partition = upper(partname)
2746 OR partition IS NULL
2747 );
2748
2749 END;
2750 END IF;
2751 IF ( commit_flag = 'Y') THEN
2752 /* for remote db operation */
2753 COMMIT;
2754 END IF;
2755 END;
2756 /* LOAD_HISTOGRAM_COLS */
2757 /************************************************************************/
2758 /* Procedure: LOAD_HISTOGRAM_COLS */
2759 /* Desciption: This is for internal purpose only. For loading into */
2760 /* SEED database */
2761 /************************************************************************/
2762 PROCEDURE LOAD_HISTOGRAM_COLS_MV(action IN VARCHAR2,
2763 ownername IN VARCHAR2,
2764 tabname IN VARCHAR2,
2765 colname IN VARCHAR2,
2766 partname IN VARCHAR2 DEFAULT NULL,
2767 hsize IN NUMBER DEFAULT 254,
2768 commit_flag IN VARCHAR2 DEFAULT 'Y')
2769 IS
2770 exist_flag VARCHAR2(5) := NULL;
2771 BEGIN
2772 IF upper(action) = 'INSERT' THEN
2773 BEGIN
2774 -- Check for existence of the table first
2775 -- break out if it doesn't exist
2776 BEGIN
2777 SELECT DISTINCT('EXIST')
2778 INTO exist_flag
2779 FROM dba_tab_columns a
2780 WHERE a.table_name = upper(tabname)
2781 AND a.column_name = upper(colname)
2782 AND a.owner = upper(ownername);
2783
2784 EXCEPTION
2785 WHEN no_data_found THEN
2786 raise_application_error(-20000, 'Column '
2787 || tabname
2788 ||'.'
2789 || colname
2790 || ' does not exist in dba_tab_columns for the given owner ');
2791 WHEN OTHERS THEN
2795 || colname );
2792 raise_application_error(-20001, 'Error in reading dictionary info. for column '
2793 || tabname
2794 ||'.'
2796 END;
2797 BEGIN
2798 INSERT
2799 INTO FND_HISTOGRAM_COLS
2800 (
2801 application_id ,
2802 OWNER ,
2803 TABLE_NAME ,
2804 COLUMN_NAME ,
2805 PARTITION ,
2806 HSIZE ,
2807 CREATION_DATE ,
2808 CREATED_BY ,
2809 LAST_UPDATE_DATE,
2810 LAST_UPDATED_BY ,
2811 LAST_UPDATE_LOGIN
2812 )
2813 VALUES
2814 (
2815 -1 ,
2816 upper(ownername),
2817 upper(tabname) ,
2818 upper(colname) ,
2819 upper(partname) ,
2820 hsize ,
2821 sysdate ,
2822 1 ,
2823 sysdate ,
2824 1 ,
2825 NULL
2826 ) ;
2827
2828 EXCEPTION
2829 WHEN DUP_VAL_ON_INDEX THEN
2830 NULL;
2831 END;
2832 END;
2833 elsif upper(action) = 'DELETE' THEN
2834 BEGIN
2835 DELETE
2836 FROM FND_HISTOGRAM_COLS
2837 WHERE owner = upper(ownername)
2838 AND table_name = upper(tabname)
2839 AND column_name = upper(colname)
2840 AND
2841 (
2842 partition = upper(partname)
2843 OR partition IS NULL
2844 );
2845
2846 END;
2847 END IF;
2848 IF ( commit_flag = 'Y') THEN
2849 /* for remote db operation */
2850 COMMIT;
2851 END IF;
2852 END;
2853 /* LOAD_HISTOGRAM_COLS_MV */
2854 /************************************************************************/
2858 /* goes for index scans instead of full table scans. The idea behind */
2855 /* Procedure: LOAD_XCLUD_STATS */
2856 /* Desciption: This will artificially pump the */
2857 /* stats with some value so that the CBO */
2859 /* this is that during a gather_schema_stats the interface tables may */
2860 /* not have data and hence the stats for such tables will be of no use */
2861 /* and hence we need to pump some artificial stats for such tables. */
2862 /* Ideally a customer has to run gather_table_stats on the interface */
2863 /* tables after populating with data. This will give them accurate data.*/
2864 /* A good methodology would be gather_table_stats once for the interface*/
2865 /* table populated with good ammount of data and for all the consecutive*/
2866 /* runs use restore_table_data procedure to restore the stats. */
2867 /* The simplified algorith for calculations are: */
2868 /* BLOCKS = num_rows*1/20, */
2869 /* AVG_ROW_LENGTH = 50% of Total max row_length */
2870 /* Clustering factor = num. of blocks */
2871 /* num. of leaf blks = */
2872 /* (cardinality)/((db_block_size -overhead 200)/key_size) */
2873 /* revised to the following as per Amozes to alway prefer index scan*/
2874 /* num. of leaf blks = 100/num of table blks */
2875 /* index_level = 1 */
2876 /* Distinct keys = num of rows */
2877 /************************************************************************/
2878 PROCEDURE LOAD_XCLUD_STATS(schemaname IN VARCHAR2)
2879 IS
2880 BEGIN
2881 -- This procedure has been deprecated. Stub is being retained for now
2882 -- so that it does not break compilation in case it is still being called.
2883 NULL;
2884 END ;
2885 /* LOAD_XCLUD_STATS */
2886 /************************************************************************/
2887 /* Procedure: LOAD_XCLUD_STATS */
2888 /* Desciption: This one is for a particular INTERFACE TABLE */
2889 /************************************************************************/
2890 PROCEDURE LOAD_XCLUD_STATS(schemaname IN VARCHAR2,
2891 tablename IN VARCHAR2)
2892 IS
2893 BEGIN
2894 -- This procedure has been deprecated. Stub is being retained for now
2895 -- so that it does not break compilation in case it is still being called.
2896 NULL;
2897 END ;
2898 /* LOAD_XCLUD_STATS */
2899 /************************************************************************/
2900 /* Procedure: CHECK_HISTOGRAM_COLS */
2901 /* Desciption: For a given list of comma seperated tables, */
2902 /* this procedure checks the */
2903 /* data in all the leading columns of all the non-unique indexes of */
2904 /* those tables and figures out if histogram needs to be created for */
2905 /* those columns. The algorithm is as follows : */
2906 /* select decode(floor(sum(tot)/(max(cnt)*75)),0,'YES','NO') HIST */
2907 /* from (select count(col) cnt , count(*) tot */
2908 /* from tab sample (S) */
2909 /* where col is not null */
2910 /* group by col); */
2911 /* The decode says whether or not a single value occupies 1/75th or */
2912 /* more of the sample. */
2913 /* If sum(cnt) is very small (a small non-null sample), the results */
2914 /* may be inaccurate. A count(*) of atleast 3000 is recommended . */
2915 /************************************************************************/
2916 PROCEDURE CHECK_HISTOGRAM_COLS(tablelist IN VARCHAR2,
2917 factor IN INTEGER,
2918 percent IN NUMBER,
2919 degree IN NUMBER DEFAULT NULL)
2920 IS
2921 BEGIN
2922 DECLARE
2923 CURSOR column_cur(tname VARCHAR2)
2924 IS
2925 SELECT DISTINCT column_name col ,
2926 b.table_name tab,
2927 b.table_owner own
2928 FROM dba_ind_columns a,
2929 dba_indexes b
2930 WHERE b.table_owner = upper(SUBSTR(tname,1,instr(tname,'.')-1))
2931 AND
2932 (
2933 b.table_name = upper(SUBSTR(tname,instr(tname,'.')+1))
2934 OR b.table_name LIKE upper(SUBSTR(tname,instr(tname,'.')+1))
2935 )
2936 AND b.uniqueness = 'NONUNIQUE'
2937 AND b.index_type = 'NORMAL'
2938 AND a.index_owner = b.owner
2939 AND a.index_name = b.index_name
2940 AND a.column_position = 1
2941 ORDER BY 3 ,
2942 2 ,
2943 1 ;
2944
2945 TYPE List
2946 IS
2947 TABLE OF VARCHAR2(62) INDEX BY BINARY_INTEGER;
2948 Table_List List;
2949 MAX_NOF_TABLES NUMBER := 32768;
2950 table_counter INTEGER := 0 ;
2951 sql_string VARCHAR2(2000);
2952 mytablelist VARCHAR2(4000);
2953 hist VARCHAR2(3);
2954 abs_tablename VARCHAR2(61);
2955 total_cnt INTEGER;
2956 max_cnt INTEGER;
2957 BEGIN
2958 -- initialize Table_list
2959 Table_List(0) := NULL;
2960 mytablelist := REPLACE(tablelist,' ','');
2961 IF (percent < 0 OR percent > 100) THEN
2962 raise_application_error(-20001,'percent must be between 0 and 100');
2963 END IF;
2964 dbms_output.put_line('Table-Name Column-Name Histogram Tot-Count Max-Count');
2965 dbms_output.put_line('==========================================================================================================');
2966 WHILE (instr(mytablelist,',') > 0)
2967 LOOP
2968 Table_List(table_counter) := SUBSTR(mytablelist,1,instr(mytablelist,',') - 1) ;
2969 Table_List(table_counter+1) := NULL;
2970 table_counter := table_counter + 1;
2971 mytablelist := SUBSTR(mytablelist,instr(mytablelist,',')+1) ;
2972 EXIT
2973 WHEN table_counter = MAX_NOF_TABLES;
2974 END LOOP;
2975 -- This gets the last table_name in a comma separated list
2976 Table_List(table_counter) := mytablelist ;
2977 Table_List(table_counter+1) := NULL;
2978 FOR i IN 0..MAX_NOF_TABLES
2979 LOOP
2980 EXIT
2981 WHEN Table_List(i) IS NULL;
2982 FOR c_rec IN column_cur(Table_List(i))
2983 LOOP
2984 --Build up the dynamic sql
2985 sql_string := 'select ';
2986 sql_string := sql_string
2987 || '/*+ PARALLEL (tab,';
2988 sql_string := sql_string
2989 || degree
2990 || ') */';
2991 sql_string := sql_string
2992 || ' decode(floor(sum(tot)/(max(cnt)*'
2993 ||factor
2994 ||')),0,''YES'',''NO'') , nvl(sum(tot),0), nvl(max(cnt),0) ';
2995 sql_string := sql_string
2996 || ' from (select count('
2997 ||c_rec.col
2998 ||') cnt, count(*) tot from ';
2999 sql_string := sql_string
3000 || c_rec.own
3001 ||'.'
3002 ||c_rec.tab
3003 || ' sample (';
3004 sql_string := sql_string
3005 || percent
3006 ||') tab ';
3007 sql_string := sql_string
3008 || ' group by '
3009 ||c_rec.col
3010 ||' )' ;
3011 BEGIN
3012 EXECUTE IMMEDIATE sql_string INTO hist,total_cnt,max_cnt;
3013 EXCEPTION
3014 WHEN zero_divide THEN
3015 hist := 'NO';
3016 END;
3017 abs_tablename := c_rec.own
3018 ||'.'
3019 ||c_rec.tab;
3020 dbms_output.put_line(rpad(upper(abs_tablename),40,' ')
3021 ||rpad(c_rec.col,30,' ')
3022 || rpad(hist,10,' ')
3023 ||lpad(TO_CHAR(total_cnt),9,' ')
3024 ||lpad(TO_CHAR(max_cnt),9,' '));
3025 END LOOP;
3026 END LOOP;
3027 END;
3028 END ;
3029 /* end of CHECK_HISTOGRAM_COLS */
3030 /************************************************************************/
3031 /* Procedure: ANALYZE_ALL_COLUMNS */
3035 PROCEDURE ANALYZE_ALL_COLUMNS(ownname IN VARCHAR2,
3032 /* Desciption: This is to create histograms on all leading cols of */
3033 /* non-unique indexes of all the tables in a given schema */
3034 /************************************************************************/
3036 percent IN NUMBER,
3037 hsize IN NUMBER,
3038 hmode IN VARCHAR2 DEFAULT 'LASTRUN' )
3039 IS
3040 BEGIN
3041 -- This procedure has been deprecated. Stub is being retained for now
3042 -- so that it does not break compilation in case it is still being called.
3043 NULL;
3044 END;
3045 /*end of ANALYZE_ALL_COLUMNS*/
3046 /************************************************************************/
3047 /* Procedure: ANALYZE_ALL_COLUMNS */
3048 /* Desciption: conc. job version of ANALYZE_ALL_COLUMNS */
3049 /************************************************************************/
3050 PROCEDURE ANALYZE_ALL_COLUMNS(errbuf OUT NOCOPY VARCHAR2,
3051 retcode OUT NOCOPY VARCHAR2,
3052 ownname IN VARCHAR2,
3053 percent IN NUMBER ,
3054 hsize IN NUMBER ,
3055 hmode IN VARCHAR2 DEFAULT 'LASTRUN' )
3056 IS
3057 BEGIN
3058 -- This procedure has been deprecated. Stub is being retained for now
3059 -- so that it does not break compilation in case it is still being called.
3060 NULL;
3061 END;
3062 /* end of ANALYZE_ALL_COLUMNS */
3063 /************************************************************************/
3064 /* Procedure: UPDATE_HIST */
3065 /* Desciption: Internal procedure to insert or update entries in table */
3066 /* fnd_stats_hist. These values are used later if restartability is */
3067 /* needed. */
3068 /************************************************************************/
3069 PROCEDURE UPDATE_HIST(schemaname VARCHAR2,
3070 objectname IN VARCHAR2,
3071 objecttype IN VARCHAR2,
3072 partname IN VARCHAR2,
3073 columntablename IN VARCHAR2,
3074 degree IN NUMBER,
3075 upd_ins_flag IN VARCHAR2,
3076 percent IN NUMBER)
3077 IS
3078 PRAGMA AUTONOMOUS_TRANSACTION ;
3079 cascade_true VARCHAR2(1);
3080 unique_constraint_detected EXCEPTION;
3081 PRAGMA EXCEPTION_INIT(unique_constraint_detected , -00001);
3082 BEGIN
3083 -- if request_id is null then we cannot do it in FULL mode, defaults to LASTRUN
3084 --- if(stathist='FULL') then
3085 --- stathist:='LASTRUN';
3086 --- end if;
3087 IF(stathist = 'LASTRUN') THEN -- retaining the old behavior as default
3088 -- S (Start) is when the entry is already in fnd_stats_hist and statistics
3089 -- were gathering is going to start for that particular object
3090 IF (upd_ins_flag = 'S') THEN
3091 UPDATE FND_STATS_HIST
3092 SET parallel = degree ,
3093 request_id = cur_request_id,
3094 request_type = request_from ,
3095 last_gather_start_time = sysdate ,
3096 last_gather_date = '' ,
3097 last_gather_end_time = '' ,
3098 est_percent =percent
3099 WHERE schema_name = upper(schemaname)
3100 AND object_name = upper(objectname)
3101 AND
3102 (
3103 partition = upper(partname)
3104 OR partname IS NULL
3105 )
3106 AND
3107 (
3108 column_table_name = upper(columntablename)
3109 OR columntablename IS NULL
3110 )
3111 AND object_type = upper(objecttype)
3112 -- and request_id=cur_request_id -- commented this line for the bug 5648754
3113 AND history_mode='L';
3114 /* Added by mo, this segment checks if an entry was updated or not.
3115 If not, a new entry will be added. */
3116 IF SQL%ROWCOUNT = 0 THEN
3117 INSERT
3118 INTO FND_STATS_HIST
3119 (
3120 SCHEMA_NAME ,
3121 OBJECT_NAME ,
3122 OBJECT_TYPE ,
3123 PARTITION ,
3124 COLUMN_TABLE_NAME ,
3125 LAST_GATHER_DATE ,
3126 LAST_GATHER_START_TIME,
3127 LAST_GATHER_END_TIME ,
3128 PARALLEL ,
3129 REQUEST_ID ,
3130 REQUEST_type ,
3131 HISTORY_MODE ,
3132 EST_PERCENT
3133 )
3137 upper(objectname),
3134 VALUES
3135 (
3136 upper(schemaname),
3138 upper(objecttype),
3139 upper(partname) ,
3140 columntablename ,
3141 '' ,
3142 sysdate ,
3143 '' ,
3144 degree ,
3145 cur_request_id ,
3146 request_from ,
3147 'L' ,
3148 percent
3149 );
3150 END IF;
3151 END IF;
3152 -- E (End) is when the entry is already in fnd_stats_hist and statistics
3153 -- gathering finished successfully for that particular object
3154 IF (upd_ins_flag = 'E') THEN
3155 UPDATE FND_STATS_HIST
3156 SET last_gather_date = sysdate,
3157 last_gather_end_time = sysdate
3158 WHERE schema_name = upper(schemaname)
3159 AND object_name = upper(objectname)
3160 AND
3161 (
3162 partition = upper(partname)
3163 OR partname IS NULL
3164 )
3165 AND
3166 (
3167 column_table_name = upper(columntablename)
3168 OR columntablename IS NULL
3169 )
3170 AND object_type = upper(objecttype)
3171 AND request_id =cur_request_id
3172 AND history_mode='L';
3173 END IF;
3174 elsif (stathist = 'FULL') THEN -- new option, old hist will not be updated
3175 IF (upd_ins_flag = 'S') THEN
3176 UPDATE FND_STATS_HIST
3177 SET parallel = degree ,
3178 request_id = cur_request_id,
3179 request_type = request_from ,
3180 last_gather_start_time = sysdate ,
3181 last_gather_date = '' ,
3182 last_gather_end_time = '' ,
3183 est_percent =percent
3184 WHERE schema_name = upper(schemaname)
3185 AND object_name = upper(objectname)
3186 AND
3187 (
3188 partition = upper(partname)
3189 OR partname IS NULL
3190 )
3191 AND
3192 (
3193 column_table_name = upper(columntablename)
3194 OR columntablename IS NULL
3195 )
3196 AND object_type = upper(objecttype)
3197 AND history_mode='F' -- F for FULL mode
3198 AND request_id =cur_request_id;
3199
3200 -- commenting out because it is not part of unique cons criteria
3201 -- and request_type=request_from;
3202 /* This segment checks if an entry was updated or not. This is still required even for
3203 FULL mode, because multiple calls for the same object from the same session will have
3204 the same cur_request_id. If not, a new entry will be added. */
3205 IF SQL%ROWCOUNT = 0 THEN
3206 INSERT
3207 INTO FND_STATS_HIST
3208 (
3209 SCHEMA_NAME ,
3210 OBJECT_NAME ,
3211 OBJECT_TYPE ,
3212 PARTITION ,
3213 COLUMN_TABLE_NAME ,
3214 LAST_GATHER_DATE ,
3215 LAST_GATHER_START_TIME,
3216 LAST_GATHER_END_TIME ,
3217 PARALLEL ,
3218 REQUEST_ID ,
3219 REQUEST_type ,
3220 HISTORY_MODE ,
3221 EST_PERCENT
3222 )
3223 VALUES
3224 (
3225 upper(schemaname),
3226 upper(objectname),
3227 upper(objecttype),
3228 upper(partname) ,
3229 columntablename ,
3230 '' ,
3231 sysdate ,
3232 '' ,
3233 degree ,
3234 cur_request_id ,
3235 request_from ,
3236 'F' ,
3237 percent
3238 );
3239
3240 END IF;
3241 END IF;
3242 -- E (End) is when the entry is already in fnd_stats_hist and statistics
3243 -- gathering finished successfully for that particular object
3244 IF (upd_ins_flag = 'E') THEN
3245 UPDATE FND_STATS_HIST
3246 SET last_gather_date = sysdate,
3247 last_gather_end_time = sysdate
3248 WHERE schema_name = upper(schemaname)
3249 AND object_name = upper(objectname)
3250 AND
3251 (
3252 partition = upper(partname)
3253 OR partname IS NULL
3254 )
3255 AND
3256 (
3257 column_table_name = upper(columntablename)
3258 OR columntablename IS NULL
3259 )
3260 AND object_type = upper(objecttype)
3261 AND history_mode='F'
3262 AND request_id =cur_request_id;
3263
3264 -- commenting out because it is not part of unique cons criteria
3265 -- and request_type=request_from;
3266 END IF;
3267 END IF;
3268 COMMIT;
3269 EXCEPTION
3270 when unique_constraint_detected then
3271 delete from fnd_stats_hist where object_name like upper(objectname) and schema_name like upper(schemaname);
3272 commit;
3273 END;
3274 /* end of UPDATE_HIST */
3275 /************************************************************************/
3276 /* Procedure: PURGE_STAT_HISTORY */
3277 /* Desciption: Purges the fnd_stat_hist table based on the FROM_REQ_ID */
3278 /* and TO_REQ_ID provided. */
3279 /************************************************************************/
3280 PROCEDURE PURGE_STAT_HISTORY(from_req_id IN NUMBER,
3284 BEGIN
3281 to_req_id IN NUMBER)
3282 IS
3283 PRAGMA AUTONOMOUS_TRANSACTION;
3285 DELETE
3286 FROM fnd_stats_hist
3287 WHERE request_id BETWEEN from_req_id AND to_req_id;
3288
3289 COMMIT;
3290 END;
3291 /************************************************************************/
3292 /* Procedure: PURGE_STAT_HISTORY */
3293 /* Desciption: Purges the fnd_stat_hist table based on the FROM_DATE */
3294 /* and TO_DATE provided. Date should be provided in DD-MM-YY format */
3295 /************************************************************************/
3296 PROCEDURE PURGE_STAT_HISTORY(purge_from_date IN VARCHAR2,
3297 purge_to_date IN VARCHAR2)
3298 IS
3299 PRAGMA AUTONOMOUS_TRANSACTION;
3300 purge_from_date_l VARCHAR2(15);
3301 purge_to_date_l VARCHAR2(15);
3302 BEGIN
3303 -- If from_date is null then from_date is sysdate-One year
3304 IF (purge_from_date IS NULL ) THEN
3305 purge_from_date_l:=TO_CHAR(sysdate-365,'DD-MM-YY');
3306 ELSE
3307 purge_from_date_l:=purge_from_date;
3308 END IF;
3309 -- If to_date is null then to_date is sysdate-One week
3310 IF (purge_to_date IS NULL ) THEN
3311 purge_to_date_l:=TO_CHAR(sysdate-7,'DD-MM-YY');
3312 ELSE
3313 purge_to_date_l:=purge_to_date;
3314 END IF;
3315 DELETE
3316 FROM fnd_stats_hist
3317 WHERE last_gather_date BETWEEN to_date(purge_from_date_l,'DD-MM-YY') AND to_date(purge_to_date_l,'DD-MM-YY');
3318
3319 COMMIT;
3320 END;
3321 /**************************************************************************/
3322 /* Procedure: PURGE_STAT_HISTORY Conc Program version */
3323 /* Desciption: Purges the fnd_stat_hist table based on the Mode parameter.*/
3324 /**************************************************************************/
3325 PROCEDURE PURGE_STAT_HISTORY(errbuf OUT NOCOPY VARCHAR2,
3326 retcode OUT NOCOPY VARCHAR2,
3327 purge_mode IN VARCHAR2,
3328 from_value IN VARCHAR2,
3329 to_value IN VARCHAR2 )
3330 IS
3331 BEGIN
3332 IF upper(purge_mode) = 'DATE' THEN
3333 PURGE_STAT_HISTORY(from_value,to_value);
3334 elsif upper(purge_mode)='REQUEST' THEN
3335 PURGE_STAT_HISTORY(to_number(from_value),to_number(to_value));
3336 END IF;
3337 EXCEPTION
3338 WHEN OTHERS THEN
3339 errbuf := sqlerrm ;
3340 retcode := '2';
3341 FND_FILE.put_line(FND_FILE.log,errbuf);
3342 raise;
3343 END;
3344 /************************************************************************/
3345 /* Procedure: table_stats */
3346 /* Desciption: Internal procedures used by verify_stats. Gets info about*/
3347 /* table stats. */
3348 /************************************************************************/
3349 PROCEDURE table_stats(schema VARCHAR2,
3350 tableName VARCHAR2)
3351 IS
3352 last_analyzed dba_tables.last_analyzed%type;
3353 sample_size dba_tables.sample_size%type;
3354 num_rows dba_tables.num_rows%type;
3355 blocks dba_tables.blocks%type;
3356 BEGIN
3357 SELECT last_analyzed ,
3358 sample_size ,
3359 TRUNC(num_rows),
3360 blocks
3361 INTO last_analyzed,
3362 sample_size ,
3363 num_rows ,
3364 blocks
3365 FROM dba_tables
3366 WHERE table_name = tableName
3367 AND owner = schema;
3368
3369 dbms_output.put_line('===================================================================================================');
3370 dbms_output.put_line(' Table '
3371 || tableName);
3372 dbms_output.put_line('===================================================================================================');
3373 dbms_output.put_line(rpad('last analyzed', 18, ' ')
3374 || rpad('sample_size', 12, ' ')
3375 ||rpad('num_rows', 20, ' ')
3376 ||rpad('blocks', 10, ' '));
3377 dbms_output.put_line(rpad(TO_CHAR(last_analyzed, 'MM-DD-YYYY hh24:mi'), 18, ' ')
3378 || rpad(sample_size, 12, ' ')
3379 || rpad(num_rows, 20, ' ')
3380 ||blocks);
3381 dbms_output.put_line(' ');
3382 EXCEPTION
3383 WHEN no_data_found THEN
3384 dbms_output.put_line('=================================================================================================');
3385 dbms_output.put_line('Table not found; Owner: '
3386 || schema
3387 ||', name: '
3388 || tableName);
3389 dbms_output.put_line('=================================================================================================');
3390 END table_stats;
3391 /************************************************************************/
3392 /* Procedure: index_stats */
3393 /* Desciption: Internal procedures used by verify_stats. Gets info about*/
3394 /* index stats. */
3395 /************************************************************************/
3396 PROCEDURE index_stats(lowner VARCHAR2,
3397 indexName VARCHAR2)
3398 IS
3399 last_analyzed dba_indexes.last_analyzed%type;
3400 num_rows dba_indexes.num_rows%type;
3401 leaf_blocks dba_indexes.leaf_blocks%type;
3402 distinct_keys dba_indexes.distinct_keys%type;
3403 avg_leaf_blocks_per_key dba_indexes.avg_leaf_blocks_per_key%type;
3407 val1 VARCHAR2(255);
3404 avg_data_blocks_per_key dba_indexes.avg_data_blocks_per_key%type;
3405 clustering_factor dba_indexes.clustering_factor%type;
3406 uniqueness dba_indexes.uniqueness%type;
3408 val2 VARCHAR2(255);
3409 val3 VARCHAR2(255);
3410 val4 VARCHAR2(255);
3411 BEGIN
3412 SELECT last_analyzed ,
3413 TRUNC(num_rows) ,
3414 leaf_blocks ,
3415 distinct_keys ,
3416 avg_leaf_blocks_per_key,
3417 avg_data_blocks_per_key,
3418 clustering_factor ,
3419 uniqueness
3420 INTO last_analyzed ,
3421 num_rows ,
3422 leaf_blocks ,
3423 distinct_keys ,
3424 avg_leaf_blocks_per_key,
3425 avg_data_blocks_per_key,
3426 clustering_factor ,
3427 uniqueness
3428 FROM dba_indexes
3429 WHERE owner = lowner
3430 AND index_name = indexName;
3431
3432 val1:= rpad(indexname, 30, ' ')
3433 || rpad(TO_CHAR(last_analyzed, 'MM-DD-YYYY hh24:mi'), 18,' ');
3434 val2:= rpad(num_rows, 10, ' ')
3435 ||rpad(leaf_blocks, 8, ' ');
3436 val3:= rpad(distinct_keys, 9, ' ')
3437 || rpad(avg_leaf_blocks_per_key, 8, ' ');
3438 val4:= rpad(avg_data_blocks_per_key, 8, ' ')
3439 || rpad(clustering_factor, 9, ' ');
3440 dbms_output.put_line(val1
3441 || val2
3442 || val3
3443 || val4);
3444 END index_stats;
3445 /************************************************************************/
3446 /* Procedure: histo_header */
3447 /* Desciption: Internal procedures used by verify_stats. Prints header */
3448 /* for histograms in the o/p file */
3449 /************************************************************************/
3450 PROCEDURE histo_header
3451 IS
3452 BEGIN
3453 dbms_output.put_line('----------------------------------------------------------------------------------------------------');
3454 dbms_output.put_line(' Histogram Stats');
3455 dbms_output.put_line(rpad('Schema', 15, ' ')
3456 ||rpad('Table Name', 31, ' ')
3457 ||rpad('Status', 12, ' ')
3458 ||rpad('last analyzed', 18, ' ')
3459 || 'Column Name');
3460 dbms_output.put_line('----------------------------------------------------------------------------------------------------');
3461 END;
3462 /************************************************************************/
3463 /* Procedure: index_header */
3464 /* Desciption: Internal procedures used by verify_stats. Prints header */
3465 /* for indexes in the o/p file */
3466 /************************************************************************/
3467 PROCEDURE index_header
3468 IS
3469 val1 VARCHAR2(255);
3470 val2 VARCHAR2(255);
3471 val3 VARCHAR2(255);
3472 val4 VARCHAR2(255);
3473 BEGIN
3474 val1 := rpad('Index name', 30, ' ')
3475 || rpad('last analyzed', 18, ' ');
3476 val2 := rpad('num_rows', 10, ' ')
3477 || rpad('LB', 8, ' ');
3478 val3 := rpad('DK', 9, ' ')
3479 || rpad('LB/key', 8, ' ');
3480 val4 := rpad('DB/key', 8, ' ')
3481 ||rpad('CF', 9, ' ');
3482 dbms_output.put_line(val1
3483 || val2
3484 || val3
3485 || val4);
3486 dbms_output.put_line('----------------------------------------------------------------------------------------------------');
3487 END;
3488 /************************************************************************/
3489 /* Procedure: histo_stats */
3490 /* Desciption: Internal procedures used by verify_stats. Gets info about*/
3491 /* about histogram stats. */
3492 /************************************************************************/
3493 PROCEDURE histo_stats(schema VARCHAR2,
3494 tableName VARCHAR2,
3495 columnName VARCHAR2)
3496 IS
3497 found0 BOOLEAN := false;
3498 found1 BOOLEAN := false;
3499 status VARCHAR2(64) := 'not present';
3500 last_analyzed dba_tab_columns.last_analyzed%type;
3501 CURSOR histo_details(schema VARCHAR2, tableName VARCHAR2, columnName VARCHAR2)
3502 IS
3503 SELECT endpoint_number,
3504 last_analyzed
3505 FROM dba_histograms a,
3506 dba_tab_columns b
3507 WHERE a.owner = schema
3508 AND a.table_name = tableName
3509 AND a.column_name = columnName
3510 AND a.owner = b.owner
3511 AND a.table_name = b.table_name
3512 AND a.column_name = b.column_name
3513 AND endpoint_number NOT IN (0,
3514 1);
3515
3516 BEGIN
3517 FOR each_histo IN histo_details(schema, tableName, columnName)
3518 LOOP
3519 last_analyzed := each_histo.last_analyzed;
3520 status := 'present';
3521 EXIT;
3522 END LOOP;
3523 dbms_output.put_line(rpad(schema, 15, ' ')
3524 || rpad(tableName, 31, ' ')
3525 || rpad(status, 12, ' ')
3526 || rpad(TO_CHAR(last_analyzed, 'DD-MM-YYYY hh24:mi'), 18, ' ')
3527 || columnName);
3528 EXCEPTION
3529 WHEN no_data_found THEN
3533 ||', name: '
3530 dbms_output.put_line('=================================================================================================');
3531 dbms_output.put_line('Histogram not found; Owner: '
3532 || schema
3534 || tableName
3535 || ', column name: '
3536 || columnName);
3537 dbms_output.put_line('=================================================================================================');
3538 END histo_stats;
3539 /************************************************************************/
3540 /* Procedure: file_tail */
3541 /* Desciption: Internal procedures used by verify_stats. Prints legend */
3542 /* in the o/p file */
3543 /************************************************************************/
3544 PROCEDURE file_tail
3545 IS
3546 BEGIN
3547 dbms_output.put_line(' ');
3548 dbms_output.put_line(' ');
3549 dbms_output.put_line('Legend:');
3550 dbms_output.put_line('LB : Leaf Blocks');
3551 dbms_output.put_line('DK : Distinct Keys');
3552 dbms_output.put_line('DB : Data Blocks');
3553 dbms_output.put_line('CF : Clustering Factor');
3554 END;
3555 /************************************************************************/
3556 /* Procedure: column_stats */
3557 /* Desciption: Internal procedures used by verify_stats. Gets info about*/
3558 /* about column stats. */
3559 /************************************************************************/
3560 PROCEDURE column_stats(column_name dba_tab_columns.column_name%type,
3561 num_distinct dba_tab_columns.num_distinct%type,
3562 num_nulls dba_tab_columns.num_nulls%type,
3563 density dba_tab_columns.density%type,
3564 sample_size dba_tab_columns.sample_size%type,
3565 last_analyzed dba_tab_columns.last_analyzed%type,
3566 first_col BOOLEAN)
3567 IS
3568 val1 VARCHAR2(255);
3569 val2 VARCHAR2(255);
3570 val3 VARCHAR2(255);
3571 BEGIN
3572 IF (first_col = true) THEN
3573 dbms_output.put_line('----------------------------------------------------------------------------------------------------');
3574 dbms_output.put_line(' Column Stats');
3575 val1 := rpad('Column name', 31, ' ')
3576 || rpad('sample_size', 12, ' ');
3577 val2 := rpad('num_distinct', 14, ' ')
3578 || rpad('num_nulls', 14, ' ');
3579 val3 := rpad('density', 12, ' ')
3580 || rpad('last analyzed', 18, ' ');
3581 dbms_output.put_line(val1
3582 ||val2
3583 ||val3);
3584 dbms_output.put_line('----------------------------------------------------------------------------------------------------');
3585 END IF;
3586 val1 := rpad(column_name, 31, ' ')
3587 || rpad(sample_size, 12, ' ');
3588 val2 := rpad(num_distinct, 14, ' ')
3589 || rpad(TRUNC(num_nulls), 14, ' ');
3590 val3 := rpad(TRUNC(density, 9), 12, ' ')
3591 || rpad(TO_CHAR(last_analyzed, 'MM-DD-YYYY hh24:mi'), 18, ' ');
3592 dbms_output.put_line(val1
3593 || val2
3594 || val3);
3595 END;
3596 /************************************************************************/
3597 /* Procedure: verify_stats */
3598 /* Desciption: Checks stats for database objects depending on input. */
3599 /* Sends its output to the screen. Should be called from SQL prompt, and*/
3600 /* o/p should be spooled to a file. Can be used to check all tables in */
3601 /* schema, or particular tables. Column stats can also be checked. */
3602 /************************************************************************/
3603 PROCEDURE verify_stats(schemaName VARCHAR2 DEFAULT NULL,
3604 tableList VARCHAR2 DEFAULT NULL,
3605 days_old NUMBER DEFAULT NULL,
3606 column_stat BOOLEAN DEFAULT false)
3607 IS
3608 CURSOR all_tables(schema VARCHAR2)
3609 IS
3610 SELECT table_name,
3611 owner
3612 FROM dba_tables dt
3613 WHERE owner = schema
3614 AND
3615 (
3616 iot_type <> 'IOT_OVERFLOW'
3617 OR iot_type IS NULL
3618 )
3619 AND
3620 (
3621 (
3622 sysdate - NVL(last_analyzed, to_date('01-01-1900', 'MM-DD-YYYY'))
3623 )
3624 >days_old
3625 OR days_old IS NULL
3626 )
3627 ORDER BY table_name;
3628
3629 CURSOR all_indexes(schema VARCHAR2, tableName VARCHAR2)
3630 IS
3631 SELECT index_name,
3632 owner
3633 FROM dba_indexes
3634 WHERE table_owner = schema
3635 AND table_name = tableName
3636 ORDER BY index_name;
3637
3638 /*cursor all_histograms(schema varchar2, tableName varchar2) is
3639 select a.column_name
3640 from fnd_histogram_cols a,
3641 fnd_oracle_userid b,
3642 fnd_product_installations c
3643 where a.application_id = c.application_id
3644 and c.oracle_id = b.oracle_id
3645 and b.oracle_username = schema
3646 and a.table_name = tableName
3647 order by a.column_name;*/
3651 FROM fnd_histogram_cols a
3648 CURSOR all_histograms(schema VARCHAR2, tableName VARCHAR2)
3649 IS
3650 SELECT a.column_name
3652 WHERE a.table_name = tableName
3653 ORDER BY a.column_name;
3654
3655 CURSOR all_columns(schema VARCHAR2, tableName VARCHAR2)
3656 IS
3657 SELECT COLUMN_NAME ,
3658 NUM_DISTINCT,
3659 NUM_NULLS ,
3660 DENSITY ,
3661 SAMPLE_SIZE ,
3662 LAST_ANALYZED
3663 FROM dba_tab_columns
3664 WHERE owner = schema
3665 AND table_name = tableName
3666 ORDER BY column_name;
3667
3668 MyTableList VARCHAR2(4000);
3669 MySchema VARCHAR2(255);
3670 TYPE List
3671 IS
3672 TABLE OF VARCHAR2(64) INDEX BY BINARY_INTEGER;
3673 Table_Name List;
3674 Table_Owner List;
3675 table_counter INTEGER := 1;
3676 MAX_NOF_TABLES NUMBER := 32768;
3677 operation VARCHAR2(64):= '';
3678 ownerIndex NUMBER(1);
3679 first_histo BOOLEAN;
3680 first_index BOOLEAN;
3681 first_col BOOLEAN;
3682 verify_stats_exception EXCEPTION;
3683 BEGIN
3684 dbms_output.enable(1000000);
3685 -- read all input params into plsql vars
3686 MySchema := upper(schemaName);
3687 MyTableList := REPLACE(upper(TableList), ' ', '');
3688 -- clean up input data
3689 -- start with the tables list
3690 IF MyTableList IS NULL THEN
3691 -- user wants to inspect all tables in schema
3692 IF MySchema IS NOT NULL THEN
3693 operation := 'schema';
3694 END IF;
3695 ELSE
3696 operation := 'table';
3697 END IF;
3698 Table_Name(1) := NULL;
3699 Table_Owner(1) := NULL;
3700 -- check operation flag and process accordingly
3701 IF operation = 'table' THEN
3702 -- initialize Table_list
3703 WHILE (instr(MyTableList,',') > 0)
3704 LOOP
3705 dbms_output.put_line('MyTableList '
3706 || mytableList);
3707 Table_Name(table_counter) := SUBSTR(mytablelist,1,instr(mytablelist,',') - 1) ;
3708 ownerIndex := instr(Table_Name(table_counter), '.');
3709 IF ownerIndex <> 0 THEN
3710 Table_Owner(table_counter):= SUBSTR(Table_Name(table_counter), 1, ownerIndex-1);
3711 Table_Name(table_counter) := SUBSTR(Table_Name(table_counter), ownerIndex+1);
3712 ELSE
3713 Table_Owner(table_counter):= MySchema;
3714 END IF;
3715 table_counter := table_counter + 1;
3716 Table_Name(table_counter) := NULL;
3717 Table_Owner(table_counter) := NULL;
3718 MyTableList := SUBSTR(MyTableList,instr(MyTableList,',')+1) ;
3719 EXIT
3720 WHEN table_counter = MAX_NOF_TABLES;
3721 END LOOP;
3722 -- This gets the last table_name in a comma separated list
3723 Table_Name(table_counter) := MyTableList ;
3724 -- check if owner is specified on command line or not
3725 OwnerIndex := instr(Table_Name(table_counter), '.');
3726 IF ownerIndex <> 0 THEN
3727 Table_Owner(table_counter):= SUBSTR(Table_Name(table_counter), 1, ownerIndex-1);
3728 Table_Name(table_counter) := SUBSTR(Table_Name(table_counter), ownerIndex+1);
3729 ELSE
3730 Table_Owner(table_counter):= MySchema;
3731 END IF;
3732 Table_Name(table_counter+1) := NULL;
3733 Table_Owner(table_counter+1) := NULL;
3734 elsif operation = 'schema' THEN
3735 -- retrieve all tables for schema and continue with processing
3736 OPEN all_tables(MySchema);
3737 FETCH all_tables BULK COLLECT
3738 INTO Table_Name,
3739 Table_Owner LIMIT MAX_NOF_TABLES;
3740
3741 CLOSE all_tables;
3742 ELSE -- error occurred
3743 raise verify_stats_exception;
3744 END IF;
3745 -- loop all the tables and check their stats and indexes
3746 FOR i IN 1..Table_Name.last
3747 LOOP
3748 EXIT
3749 WHEN Table_Name(i) IS NULL;
3750 first_histo := true;
3751 first_index := true;
3752 first_col := true;
3753 -- get table stats first
3754 table_stats(Table_Owner(i), Table_Name(i));
3755 -- do the stats for all table columns if flag is yes
3756 IF (column_stat = true) THEN
3757 FOR col_rec IN all_columns(Table_Owner(i), Table_Name(i))
3758 LOOP
3759 column_stats(col_rec.column_name, col_rec.num_distinct, col_rec.num_nulls, col_rec.density, col_rec.sample_size, col_rec.last_analyzed, first_col);
3760 first_col:= false;
3761 END LOOP;
3762 END IF;
3763 -- do the stats for all table indexes
3764 FOR index_rec IN all_indexes(Table_Owner(i), Table_Name(i))
3765 LOOP
3766 IF first_index = true THEN
3767 index_header();
3768 first_index := false;
3769 END IF;
3770 index_stats(index_rec.owner, index_rec.index_Name);
3771 END LOOP;
3772 -- do the stats for all table histograms
3773 FOR histo_rec IN all_histograms(Table_Owner(i), Table_Name(i))
3774 LOOP
3775 IF first_histo = true THEN
3776 histo_header();
3777 first_histo:= false;
3778 END IF;
3779 histo_stats(Table_Owner(i), Table_Name(i), histo_rec.column_name);
3780 END LOOP;
3781 END LOOP;
3782 file_tail();
3783 EXCEPTION
3784 WHEN verify_stats_exception THEN
3785 dbms_output.put_line('verify_stats(schema_name varchar2 default null,
3786 table_list varchar2 default null, days_old number default null,
3787 column_stats boolean defualt false)');
3788 END ;
3789 /* end of verify_stats*/
3790 BEGIN
3791 -- Get the default DOP that will be used if none is provided.
3792 GET_PARALLEL(def_degree);
3793 dummybool := fnd_installation.get_app_info('FND',dummy1,dummy2,fnd_statown);
3794 -- select substr(version,1,instr(version,'.')-1)
3795 SELECT REPLACE(SUBSTR(version,1,instr(version,'.',1,2)-1),'.')
3796 INTO db_versn
3797 FROM v$instance;
3798
3799 -- Initialize cur_request_id
3800 cur_request_id:=GET_REQUEST_ID;
3801 -- dbms_output.put_line('Database version is '||db_versn);
3802 EXCEPTION
3803 WHEN OTHERS THEN
3804 db_versn:=81; -- Just in case, default it to 8i
3805 END FND_STATS;
[Home] [Help]