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