eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FND_STATS dependencies on FND_STATS

Line 1: PACKAGE body FND_STATS AS

1: PACKAGE body FND_STATS AS
2: /* $Header: AFSTATSB.pls 120.12.12010000.10 2009/02/25 11:05:02 suchauha ship $ */
3: db_versn NUMBER :=81;
4: request_from VARCHAR2(7) DEFAULT 'U';
5: MAX_ERRORS_PRINTED NUMBER := 20 ; -- The max nof. allowable errors.

Line 63: RETURN fnd_stats.SMALL_TAB_FOR_PAR_THOLD+1000;

59: WHEN OTHERS THEN
60: -- For partitioned tables, we will get an exception as it unused space
61: -- expects a partition spec. If table is partitioned, we definitely
62: -- do not want to do serial, so will return thold+1000.
63: RETURN fnd_stats.SMALL_TAB_FOR_PAR_THOLD+1000;
64: END;
65: /************************************************************************/
66: /* Procedure: SCHEMA_MONITORING */
67: /* Desciption: Non Public procedure that is called by */

Line 99: FND_STATS.SCHEMA_MONITORING(mmode,c_schema.sname);

95: -- therefore this has to be taken care of manually.
96: IF schemaname ='ALL' THEN -- call itself with the schema name
97: FOR c_schema IN schema_cur
98: LOOP
99: FND_STATS.SCHEMA_MONITORING(mmode,c_schema.sname);
100: END LOOP;
101: /* schema_cur */
102: ELSE -- schemaname<>'ALL'
103: SELECT table_name BULK COLLECT

Line 215: SELECT fnd_stats_hist_s.nextval

211: request_id_l := FND_GLOBAL.CONC_REQUEST_ID; -- set request id to conc request id
212: elsif ( FND_GLOBAL.USER_ID > 0) THEN -- check if call from apps program
213: request_from :='P'; -- P for PROG , cal by program
214: -- generate it from sequence
215: SELECT fnd_stats_hist_s.nextval
216: INTO request_id_l
217: FROM dual;
218:
219: ELSE -- call not from within apps context, maybe sqlplus

Line 222: SELECT fnd_stats_hist_s.nextval

218:
219: ELSE -- call not from within apps context, maybe sqlplus
220: request_from:='U'; -- U for USER, called from sqlplus etc
221: -- generate it from sequence
222: SELECT fnd_stats_hist_s.nextval
223: INTO request_id_l
224: FROM dual;
225:
226: END IF;

Line 348: FND_STATS.CREATE_STAT_TABLE();

344: pragma exception_init(exist_insufficient,-20002);
345: BEGIN
346: -- First create the FND_STATTAB if it doesn't exist.
347: BEGIN
348: FND_STATS.CREATE_STAT_TABLE();
349: EXCEPTION
350: WHEN exist_insufficient THEN
351: NULL;
352: END;

Line 382: FND_STATS.BACKUP_TABLE_STATS(schemaname, tabname, statid, partname, CASCADE);

378: exist_insufficient EXCEPTION;
379: pragma exception_init(exist_insufficient,-20000);
380: l_message VARCHAR2(1000);
381: BEGIN
382: FND_STATS.BACKUP_TABLE_STATS(schemaname, tabname, statid, partname, CASCADE);
383: EXCEPTION
384: WHEN exist_insufficient THEN
385: errbuf := sqlerrm ;
386: retcode := '2';

Line 416: FND_STATS.CREATE_STAT_TABLE();

412: pragma exception_init(exist_insufficient,-20002);
413: BEGIN
414: -- First create the FND_STATTAB if it doesn't exist.
415: BEGIN
416: FND_STATS.CREATE_STAT_TABLE();
417: EXCEPTION
418: WHEN exist_insufficient THEN
419: NULL;
420: END;

Line 465: FND_STATS.RESTORE_TABLE_STATS(ownname,tabname,statid,partname,CASCADE);

461: pragma exception_init(exist_insufficient,-20000);
462: pragma exception_init(exist_invalid,-20001);
463: l_message VARCHAR2(1000);
464: BEGIN
465: FND_STATS.RESTORE_TABLE_STATS(ownname,tabname,statid,partname,CASCADE);
466: EXCEPTION
467: WHEN exist_insufficient THEN
468: errbuf := sqlerrm;
469: retcode := '2';

Line 761: FND_STATS.GATHER_SCHEMA_STATS(schemaname, estimate_percent, degree_parallel, internal_flag , request_id,stathist, OPTIONS,modpercent,invalidate); -- removed errors parameter for error handling

757: || ' internal_flag= '
758: || internal_flag ;
759: FND_FILE.put_line(FND_FILE.log,l_message);
760: BEGIN
761: FND_STATS.GATHER_SCHEMA_STATS(schemaname, estimate_percent, degree_parallel, internal_flag , request_id,stathist, OPTIONS,modpercent,invalidate); -- removed errors parameter for error handling
762: EXCEPTION
763: WHEN exist_insufficient THEN
764: errbuf := sqlerrm ;
765: retcode := '2';

Line 815: FND_STATS.GATHER_SCHEMA_STATS_SQLPLUS(schemaname, estimate_percent, degree,internal_flag, Errors, request_id,hmode,OPTIONS ,modpercent,invalidate);

811: IS
812: Errors Error_Out;
813: BEGIN
814: call_from_sqlplus:=true;
815: FND_STATS.GATHER_SCHEMA_STATS_SQLPLUS(schemaname, estimate_percent, degree,internal_flag, Errors, request_id,hmode,OPTIONS ,modpercent,invalidate);
816: END;
817: /* end of GATHER_SCHEMA_STATISTICS */
818: /************************************************************************/
819: /* Procedure: GATHER_SCHEMA_STATS_SQLPLUS */

Line 945: granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables

941: degree_parallel := degree;
942: END IF;
943: -- Initialize the TABLE Errors
944: Errors(0) := NULL;
945: granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables
946: err_cnt := 0;
947: -- If a specific schema is given
948: IF (upper(schemaname) <> 'SYS') THEN
949: IF (upper(schemaname) <> 'ALL') THEN

Line 950: -- Insert/update the fnd_stats_hist table

946: err_cnt := 0;
947: -- If a specific schema is given
948: IF (upper(schemaname) <> 'SYS') THEN
949: IF (upper(schemaname) <> 'ALL') THEN
950: -- Insert/update the fnd_stats_hist table
951: IF(upper(stathist)<> 'NONE') THEN
952: BEGIN
953: -- if(cur_request_id is null) then
954: -- cur_request_id := GET_REQUEST_ID(request_id);

Line 956: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(estimate_percent,10));

952: BEGIN
953: -- if(cur_request_id is null) then
954: -- cur_request_id := GET_REQUEST_ID(request_id);
955: -- end if;
956: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(estimate_percent,10));
957: END;
958: END IF; --if(upper(stathist)<> 'NONE')
959: -- backup the existing schema stats
960: IF ( (upper(internal_flag) = 'BACKUP') ) THEN

Line 961: FND_STATS.BACKUP_SCHEMA_STATS( schemaname );

957: END;
958: END IF; --if(upper(stathist)<> 'NONE')
959: -- backup the existing schema stats
960: IF ( (upper(internal_flag) = 'BACKUP') ) THEN
961: FND_STATS.BACKUP_SCHEMA_STATS( schemaname );
962: END IF;
963: IF(upper(OPTIONS)='GATHER') THEN
964: SELECT table_name ,
965: partitioned BULK COLLECT

Line 978: FROM fnd_stats_hist fsh

974: )
975: AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
976: AND NOT EXISTS
977: (SELECT NULL
978: FROM fnd_stats_hist fsh
979: WHERE dt.owner =fsh.schema_name
980: AND fsh.REQUEST_ID = cur_request_id
981: AND fsh.object_type ='CASCADE'
982: AND fsh.history_mode=stathist

Line 1009: granularity := FND_STATS.ALL_GRANULARITY ;

1005: num_tables := SQL%ROWCOUNT;
1006: FOR i IN 1..num_tables
1007: LOOP
1008: IF ( part_flag(i) = 'YES' ) THEN
1009: granularity := FND_STATS.ALL_GRANULARITY ;
1010: ELSE
1011: granularity := FND_STATS.STD_GRANULARITY;
1012: END IF;
1013: BEGIN

Line 1011: granularity := FND_STATS.STD_GRANULARITY;

1007: LOOP
1008: IF ( part_flag(i) = 'YES' ) THEN
1009: granularity := FND_STATS.ALL_GRANULARITY ;
1010: ELSE
1011: granularity := FND_STATS.STD_GRANULARITY;
1012: END IF;
1013: BEGIN
1014: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1015: tabname => names(i), percent => NVL(estimate_percent,10),

Line 1014: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,

1010: ELSE
1011: granularity := FND_STATS.STD_GRANULARITY;
1012: END IF;
1013: BEGIN
1014: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1015: tabname => names(i), percent => NVL(estimate_percent,10),
1016: degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1017: granularity => granularity, hmode => stathist,
1018: invalidate=> invalidate );

Line 1116: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,

1112: FOR i IN 1..num_tables
1113: LOOP
1114: BEGIN
1115: IF (upper(OPTIONS)='GATHER AUTO') THEN
1116: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1117: tabname => names(i), percent => NVL(estimate_percent,10),
1118: degree => degree_parallel, partname=>pnames(i),
1119: CASCADE => TRUE, granularity => granularity,
1120: hmode => stathist, invalidate=> invalidate );

Line 1161: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,

1157: FOR c_rec IN empty_cur(upper(schemaname))
1158: LOOP
1159: IF c_rec.type = 'TABLE' THEN
1160: IF (upper(OPTIONS)='GATHER AUTO') THEN
1161: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1162: percent => NVL(estimate_percent,10),
1163: degree => degree_parallel, partname=>NULL, CASCADE => TRUE, granularity => granularity,
1164: hmode => stathist, invalidate=> invalidate );
1165: ELSE

Line 1174: fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);

1170: ||' is missing statistics.');
1171: END IF;
1172: elsif c_rec.type ='INDEX' THEN
1173: IF (upper(OPTIONS)='GATHER AUTO') THEN
1174: fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);
1175: ELSE
1176: dlog('Index '
1177: ||c_rec.owner
1178: ||'.'

Line 1191: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,

1187: -- in dba_tab_modifications for next time.
1188: FOR c_rec IN nomon_tab(upper(schemaname))
1189: LOOP
1190: IF (upper(OPTIONS)='GATHER AUTO') THEN
1191: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,
1192: tabname => c_rec.table_name, percent => NVL(estimate_percent,10), degree => degree_parallel,
1193: partname=>NULL, CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1194: EXECUTE IMMEDIATE 'alter table '
1195: ||c_rec.owner

Line 1220: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,

1216: FOR c_rec IN empty_cur(upper(schemaname))
1217: LOOP
1218: IF c_rec.type = 'TABLE' THEN
1219: IF (upper(OPTIONS)='GATHER EMPTY') THEN
1220: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1221: percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1222: granularity => granularity, hmode => stathist, invalidate=> invalidate );
1223: ELSE
1224: dlog('Table '

Line 1232: fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);

1228: ||' is missing statistics! ');
1229: END IF;
1230: elsif c_rec.type ='INDEX' THEN
1231: IF (upper(OPTIONS)='GATHER EMPTY') THEN
1232: fnd_stats.gather_index_stats(ownname=>c_rec.owner, indname=>c_rec.name, percent=>NVL(estimate_percent,10), invalidate=>invalidate);
1233: ELSE
1234: dlog('Statistics for Index '
1235: ||c_rec.owner
1236: ||'.'

Line 1257: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

1253: /* end of if upper(options)= */
1254: -- End timestamp
1255: IF(upper(stathist) <> 'NONE') THEN
1256: BEGIN
1257: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1258: END;
1259: END IF;
1260: ELSE
1261: /* This is for ALL schema */

Line 1280: dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');

1276: END LOOP;
1277: /* schema_cur */
1278: END IF;
1279: ELSE -- schema is SYS, print message in log.
1280: dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');
1281: dlog('Please use DBMS_STATS package to gather stats on SYS objects.');
1282: END IF; -- end of schema<> SYS
1283: END;
1284: /* GATHER_SCHEMA_STATS_SQLPLUS */

Line 1432: granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables

1428: degree_parallel := degree;
1429: END IF;
1430: -- Initialize the TABLE Errors
1431: --Errors(0) := NULL; -- commented the initialization so that the errors will not be cleared
1432: granularity := FND_STATS.ALL_GRANULARITY; -- granularity will be ALL for all tables
1433: err_cnt := 0;
1434: -- If a specific schema is given
1435: IF (upper(schemaname) <> 'SYS') THEN
1436: IF (upper(schemaname) <> 'ALL') THEN

Line 1437: -- Insert/update the fnd_stats_hist table

1433: err_cnt := 0;
1434: -- If a specific schema is given
1435: IF (upper(schemaname) <> 'SYS') THEN
1436: IF (upper(schemaname) <> 'ALL') THEN
1437: -- Insert/update the fnd_stats_hist table
1438: IF(upper(stathist)<> 'NONE') THEN
1439: BEGIN
1440: -- if(cur_request_id is null) then
1441: -- cur_request_id := GET_REQUEST_ID(request_id);

Line 1443: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname,

1439: BEGIN
1440: -- if(cur_request_id is null) then
1441: -- cur_request_id := GET_REQUEST_ID(request_id);
1442: -- end if;
1443: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname,
1444: objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S',
1445: percent=>NVL(estimate_percent,10));
1446: END;
1447: END IF; --if(upper(stathist)<> 'NONE')

Line 1450: FND_STATS.BACKUP_SCHEMA_STATS( schemaname );

1446: END;
1447: END IF; --if(upper(stathist)<> 'NONE')
1448: -- backup the existing schema stats
1449: IF ( (upper(internal_flag) = 'BACKUP') ) THEN
1450: FND_STATS.BACKUP_SCHEMA_STATS( schemaname );
1451: END IF;
1452: IF(upper(OPTIONS)='GATHER') THEN
1453: SELECT table_name ,
1454: partitioned BULK COLLECT

Line 1467: FROM fnd_stats_hist fsh

1463: )
1464: AND TEMPORARY <> 'Y' -- Bypass if temporary tables for bug#1108002
1465: AND NOT EXISTS
1466: (SELECT NULL
1467: FROM fnd_stats_hist fsh
1468: WHERE dt.owner =fsh.schema_name
1469: AND fsh.REQUEST_ID = cur_request_id
1470: AND fsh.object_type ='CASCADE'
1471: AND fsh.history_mode=stathist

Line 1499: granularity := FND_STATS.ALL_GRANULARITY ;

1495: num_tables := SQL%ROWCOUNT;
1496: FOR i IN 1..num_tables
1497: LOOP
1498: IF ( part_flag(i) = 'YES' ) THEN
1499: granularity := FND_STATS.ALL_GRANULARITY ;
1500: ELSE
1501: granularity := FND_STATS.STD_GRANULARITY;
1502: END IF;
1503: BEGIN

Line 1501: granularity := FND_STATS.STD_GRANULARITY;

1497: LOOP
1498: IF ( part_flag(i) = 'YES' ) THEN
1499: granularity := FND_STATS.ALL_GRANULARITY ;
1500: ELSE
1501: granularity := FND_STATS.STD_GRANULARITY;
1502: END IF;
1503: BEGIN
1504: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname, tabname => names(i),
1505: percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,

Line 1504: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname, tabname => names(i),

1500: ELSE
1501: granularity := FND_STATS.STD_GRANULARITY;
1502: END IF;
1503: BEGIN
1504: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname, tabname => names(i),
1505: percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1506: granularity => granularity, hmode => stathist, invalidate=> invalidate );
1507: EXCEPTION
1508: WHEN OTHERS THEN

Line 1606: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,

1602: FOR i IN 1..num_tables
1603: LOOP
1604: BEGIN
1605: IF (upper(OPTIONS)='GATHER AUTO') THEN
1606: FND_STATS.GATHER_TABLE_STATS(ownname => schemaname,
1607: tabname => names(i), percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>pnames(i),
1608: CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1609: ELSE
1610: dlog('Statistics on '

Line 1650: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,

1646: FOR c_rec IN empty_cur(upper(schemaname))
1647: LOOP
1648: IF c_rec.type = 'TABLE' THEN
1649: IF (upper(OPTIONS)='GATHER AUTO') THEN
1650: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner,
1651: tabname => c_rec.name, percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL,
1652: CASCADE => TRUE, granularity => granularity, hmode => stathist, invalidate=> invalidate );
1653: ELSE
1654: dlog('Table '

Line 1669: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.table_name,

1665: -- in dba_tab_modifications for next time.
1666: FOR c_rec IN nomon_tab(upper(schemaname))
1667: LOOP
1668: IF (upper(OPTIONS)='GATHER AUTO') THEN
1669: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.table_name,
1670: percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1671: granularity => granularity, hmode => stathist, invalidate=> invalidate );
1672: EXECUTE IMMEDIATE 'alter table '
1673: ||c_rec.owner

Line 1698: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,

1694: FOR c_rec IN empty_cur(upper(schemaname))
1695: LOOP
1696: IF c_rec.type = 'TABLE' THEN
1697: IF (upper(OPTIONS)='GATHER EMPTY') THEN
1698: FND_STATS.GATHER_TABLE_STATS(ownname => c_rec.owner, tabname => c_rec.name,
1699: percent => NVL(estimate_percent,10), degree => degree_parallel, partname=>NULL, CASCADE => TRUE,
1700: granularity => granularity, hmode => stathist, invalidate=> invalidate );
1701: ELSE
1702: dlog('Table '

Line 1726: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

1722: /* end of if upper(options)= */
1723: -- End timestamp
1724: IF(upper(stathist) <> 'NONE') THEN
1725: BEGIN
1726: FND_STATS.UPDATE_HIST(schemaname=>schemaname, objectname=>schemaname, objecttype=>'SCHEMA', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1727: END;
1728: END IF;
1729: ELSE
1730: /* This is for ALL schema */

Line 1742: dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');

1738: END LOOP;
1739: /* schema_cur */
1740: END IF;
1741: ELSE -- schema is SYS, print message in log.
1742: dlog('Gathering statistics on the SYS schema using FND_STATS is not allowed.');
1743: dlog('Please use DBMS_STATS package to gather stats on SYS objects.');
1744: END IF; -- end of schema<> SYS
1745: END;
1746: /* GATHER_SCHEMA_STATS */

Line 1768: num_blks := fnd_stats.get_blocks(ownname,indname,'INDEX');

1764: degree_parallel NUMBER(4) ;
1765: BEGIN
1766: -- Set the package body variable.
1767: stathist := hmode;
1768: num_blks := fnd_stats.get_blocks(ownname,indname,'INDEX');
1769: -- In 8i, you cannot provide a degree for an index, in 9iR2 we can.
1770: IF num_blks <= SMALL_IND_FOR_PAR_THOLD THEN
1771: degree_parallel:=1;
1772: ELSE

Line 1797: FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));

1793: END IF;
1794: -- Insert/update the fnd_stat_hist table
1795: IF(upper(stathist) <> 'NONE') THEN
1796: BEGIN
1797: FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));
1798: END;
1799: END IF;
1800: -- backup the existing index stats
1801: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN

Line 1804: FND_STATS.CREATE_STAT_TABLE();

1800: -- backup the existing index stats
1801: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
1802: -- First create the FND_STATTAB if it doesn't exist.
1803: BEGIN
1804: FND_STATS.CREATE_STAT_TABLE();
1805: EXCEPTION
1806: WHEN exist_insufficient THEN
1807: NULL;
1808: END;

Line 1811: FND_STATS.GATHER_INDEX_STATS_PVT(ownname => ownname, indname => indname, partname => partname, estimate_percent => NVL(adj_percent,10), degree=>degree_parallel, invalidate => invalidate ) ;

1807: NULL;
1808: END;
1809: DBMS_STATS.EXPORT_INDEX_STATS( ownname, indname, NULL, fnd_stattab, NULL, fnd_statown );
1810: END IF;
1811: FND_STATS.GATHER_INDEX_STATS_PVT(ownname => ownname, indname => indname, partname => partname, estimate_percent => NVL(adj_percent,10), degree=>degree_parallel, invalidate => invalidate ) ;
1812: -- End timestamp
1813: IF(upper(stathist) <> 'NONE') THEN
1814: BEGIN
1815: -- update fnd_stats_hist for completed stats

Line 1815: -- update fnd_stats_hist for completed stats

1811: FND_STATS.GATHER_INDEX_STATS_PVT(ownname => ownname, indname => indname, partname => partname, estimate_percent => NVL(adj_percent,10), degree=>degree_parallel, invalidate => invalidate ) ;
1812: -- End timestamp
1813: IF(upper(stathist) <> 'NONE') THEN
1814: BEGIN
1815: -- update fnd_stats_hist for completed stats
1816: FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1817: END;
1818: END IF;
1819: END ;

Line 1816: FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

1812: -- End timestamp
1813: IF(upper(stathist) <> 'NONE') THEN
1814: BEGIN
1815: -- update fnd_stats_hist for completed stats
1816: FND_STATS.UPDATE_HIST(schemaname=>upper(ownname), objectname=>upper(indname), objecttype=>'INDEX', partname=>upper(partname), columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
1817: END;
1818: END IF;
1819: END ;
1820: /* GATHER_INDEX_STATS */

Line 1842: FND_STATS.GATHER_TABLE_STATS(ownname, tabname, percent, degree, partname, backup_flag, true,

1838: exist_insufficient EXCEPTION;
1839: pragma exception_init(exist_insufficient,-20000);
1840: l_message VARCHAR2(1000);
1841: BEGIN
1842: FND_STATS.GATHER_TABLE_STATS(ownname, tabname, percent, degree, partname, backup_flag, true,
1843: granularity,hmode,invalidate);
1844: EXCEPTION
1845: WHEN exist_insufficient THEN
1846: errbuf := sqlerrm ;

Line 1912: num_blks :=fnd_stats.get_blocks(ownname,tabname,'TABLE');

1908: degree_parallel NUMBER(4);
1909: BEGIN
1910: -- Set the package body variable.
1911: stathist := hmode;
1912: num_blks :=fnd_stats.get_blocks(ownname,tabname,'TABLE');
1913: -- For better performance, tables smaller than small_tab_for_par_thold should be gathered in serial.
1914: IF num_blks <= SMALL_TAB_FOR_PAR_THOLD THEN
1915: degree_parallel:=1;
1916: elsif degree IS NULL THEN -- degree will not be null when called from gather_schema_stats

Line 1948: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));

1944: BEGIN
1945: -- if(cur_request_id is null) then
1946: -- cur_request_id := GET_REQUEST_ID(null); -- for gather table stats, we will not have a request_id
1947: -- end if;
1948: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S', percent=>NVL(adj_percent,10));
1949: EXCEPTION
1950: WHEN OTHERS THEN
1951: raise;
1952: END;

Line 1959: FND_STATS.CREATE_STAT_TABLE();

1955: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
1956: BEGIN
1957: -- First create the FND_STATTAB if it doesn't exist.
1958: BEGIN
1959: FND_STATS.CREATE_STAT_TABLE();
1960: EXCEPTION
1961: WHEN exist_insufficient THEN
1962: NULL;
1963: END;

Line 1988: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,

1984: END IF;
1985: IF (method = 'FOR ALL COLUMNS SIZE 1') THEN
1986: BEGIN
1987: --dbms_output.put_line('SINGLE:'||method||'granularity='||granularity);
1988: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
1989: partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
1990: CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
1991: EXCEPTION
1992: WHEN OTHERS THEN

Line 2006: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname, partname => partname, method_opt => 'FOR ALL COLUMNS SIZE 1 '

2002: END;
2003: ELSE -- call it with histogram cols.
2004: BEGIN
2005: -- dbms_output.put_line('FOR ALL COLUMNS SIZE 1 '||method);
2006: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname, partname => partname, method_opt => 'FOR ALL COLUMNS SIZE 1 '
2007: ||method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel, CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2008: EXCEPTION
2009: WHEN OTHERS THEN
2010: raise;

Line 2033: -- FND_STATS.GATHER_TABLE_STATS twice, once for histogram

2029: IF method = ' FOR COLUMNS ' THEN
2030: method := 'FOR ALL COLUMNS SIZE 1' ;
2031: END IF;
2032: -- Due to the limitations of in DBMS_STATS in 8i we need to call
2033: -- FND_STATS.GATHER_TABLE_STATS twice, once for histogram
2034: -- and once for just the table stats.
2035: IF (method = 'FOR ALL COLUMNS SIZE 1') THEN
2036: BEGIN
2037: --dbms_output.put_line('SINGLE:'||method||'granularity='||granularity);

Line 2038: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,

2034: -- and once for just the table stats.
2035: IF (method = 'FOR ALL COLUMNS SIZE 1') THEN
2036: BEGIN
2037: --dbms_output.put_line('SINGLE:'||method||'granularity='||granularity);
2038: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2039: partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
2040: CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2041: EXCEPTION
2042: WHEN OTHERS THEN

Line 2048: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,

2044: END;
2045: ELSE -- call it twice
2046: BEGIN
2047: --dbms_output.put_line('DOUBLE 1:'||method||'granularity='||granularity);
2048: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2049: partname => partname, method_opt => 'FOR ALL COLUMNS SIZE 1', estimate_percent => NVL(adj_percent,10),
2050: degree => degree_parallel, CASCADE => CASCADE, granularity => granularity, invalidate=> invalidate );
2051: EXCEPTION
2052: WHEN OTHERS THEN

Line 2057: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,

2053: raise;
2054: END;
2055: BEGIN
2056: --dbms_output.put_line('DOUBLE 2:'||method||'granularity='||granularity);
2057: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => ownname, tabname => tabname,
2058: partname => partname, method_opt => method, estimate_percent => NVL(adj_percent,10), degree => degree_parallel,
2059: CASCADE => FALSE, granularity => granularity, invalidate=> invalidate );
2060: EXCEPTION
2061: WHEN OTHERS THEN

Line 2070: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

2066: -- End timestamp
2067: -- change to call update_hist for autonomous_transaction
2068: IF(upper(stathist) <> 'NONE') THEN
2069: BEGIN
2070: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>tabname, objecttype=>obj_type, partname=>partname, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
2071: EXCEPTION
2072: WHEN OTHERS THEN
2073: raise;
2074: END;

Line 2167: FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'S' );

2163: FOR i IN 1..list_column_name.last
2164: LOOP
2165: IF(upper(stathist) <> 'NONE') THEN
2166: BEGIN
2167: FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'S' );
2168: END;
2169: END IF;
2170: -- First export the col stats depending on backup-flag
2171: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP') THEN

Line 2175: FND_STATS.CREATE_STAT_TABLE();

2171: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP') THEN
2172: BEGIN
2173: -- First create the FND_STATTAB if it doesn't exist.
2174: BEGIN
2175: FND_STATS.CREATE_STAT_TABLE();
2176: EXCEPTION
2177: WHEN exist_insufficient THEN
2178: NULL;
2179: END;

Line 2196: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => owner, tabname => t_rec.table_name,

2192: owner := list_ownname(i);
2193: END LOOP;
2194: /* end of c_rec */
2195: BEGIN
2196: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => owner, tabname => t_rec.table_name,
2197: partname => t_rec.partition, estimate_percent => NVL(percent,10), method_opt => method,
2198: degree => degree_parallel, CASCADE => FALSE, invalidate=> invalidate, stattab => fnd_stattab,
2199: statown => fnd_statown);
2200: -- now that histograms are collected update fnd_stats_hist

Line 2200: -- now that histograms are collected update fnd_stats_hist

2196: FND_STATS.GATHER_TABLE_STATS_PVT(ownname => owner, tabname => t_rec.table_name,
2197: partname => t_rec.partition, estimate_percent => NVL(percent,10), method_opt => method,
2198: degree => degree_parallel, CASCADE => FALSE, invalidate=> invalidate, stattab => fnd_stattab,
2199: statown => fnd_statown);
2200: -- now that histograms are collected update fnd_stats_hist
2201: IF(upper(stathist) <> 'NONE') THEN
2202: FOR i IN 1..list_column_name.last
2203: LOOP
2204: FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'E' );

Line 2204: FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'E' );

2200: -- now that histograms are collected update fnd_stats_hist
2201: IF(upper(stathist) <> 'NONE') THEN
2202: FOR i IN 1..list_column_name.last
2203: LOOP
2204: FND_STATS.UPDATE_HIST(schemaname=>list_ownname(i), objectname=>list_column_name(i), objecttype=>'COLUMN', partname=>t_rec.partition, columntablename=>t_rec.table_name, degree=>degree_parallel, upd_ins_flag=>'E' );
2205: END LOOP;
2206: END IF;
2207: EXCEPTION
2208: WHEN OTHERS THEN

Line 2214: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */

2210: ||SQLERRM ;
2211: g_Errors(i+1) := NULL;
2212: i := i+1;
2213: END;
2214: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2215: END LOOP;
2216: /* end of t_rec */
2217: END;
2218: /* end of procedure GATHER_COLUMN_STATS */

Line 2272: -- Insert/update the fnd_stats_hist table

2268: IF (upper(ownname) <> 'ALL') THEN
2269: -- get the tables for the given schema
2270: FOR t_rec IN tab_cursor(ownname)
2271: LOOP
2272: -- Insert/update the fnd_stats_hist table
2273: --dbms_output.put_line('appl_id = '||t_rec.application_id||',table='||t_rec.table_name);
2274: IF(upper(stathist) <> 'NONE') THEN
2275: BEGIN
2276: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );

Line 2276: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );

2272: -- Insert/update the fnd_stats_hist table
2273: --dbms_output.put_line('appl_id = '||t_rec.application_id||',table='||t_rec.table_name);
2274: IF(upper(stathist) <> 'NONE') THEN
2275: BEGIN
2276: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2277: END;
2278: END IF;
2279: -- get the column list and build up the METHOD_OPT
2280: method := ' FOR COLUMNS ';

Line 2296: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => t_rec.table_name, estimate_percent => NVL(percent,10), method_opt => method, degree => degree_parallel, CASCADE => FALSE, invalidate => invalidate );

2292: END LOOP ;
2293: /* c_rec */
2294: --dbms_output.put_line(' method = '|| method);
2295: BEGIN
2296: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => t_rec.table_name, estimate_percent => NVL(percent,10), method_opt => method, degree => degree_parallel, CASCADE => FALSE, invalidate => invalidate );
2297: EXCEPTION
2298: WHEN OTHERS THEN
2299: raise;
2300: END;

Line 2301: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */

2297: EXCEPTION
2298: WHEN OTHERS THEN
2299: raise;
2300: END;
2301: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2302: -- End timestamp
2303: IF(upper(stathist) <> 'NONE') THEN
2304: BEGIN
2305: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

Line 2305: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );

2301: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2302: -- End timestamp
2303: IF(upper(stathist) <> 'NONE') THEN
2304: BEGIN
2305: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>ownname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'E' );
2306: END;
2307: END IF;
2308: END LOOP ;
2309: /* t_rec */

Line 2322: FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );

2318: -- Insert/update the fnd_stat_hist table
2319: --dbms_output.put_line('appl_id = '||t_rec.application_id||',table='||t_rec.table_name);
2320: IF(upper(stathist) <> 'NONE') THEN
2321: BEGIN
2322: FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2323: END;
2324: END IF;
2325: -- get the column list and build up the METHOD_OPT
2326: method := ' FOR COLUMNS ';

Line 2342: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => s_rec.sname,

2338: END LOOP ;
2339: /* c_rec */
2340: --dbms_output.put_line(' method = '|| method);
2341: BEGIN
2342: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => s_rec.sname,
2343: tabname => t_rec.table_name, estimate_percent => NVL(percent,10), method_opt => method,
2344: degree => degree_parallel, CASCADE => FALSE, invalidate => invalidate );
2345: EXCEPTION
2346: WHEN OTHERS THEN

Line 2349: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */

2345: EXCEPTION
2346: WHEN OTHERS THEN
2347: raise;
2348: END;
2349: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2350: -- End timestamp
2351: IF(upper(stathist) <> 'NONE') THEN
2352: BEGIN
2353: FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );

Line 2353: FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );

2349: /* end of FND_STATS.GATHER_TABLE_STATS_PVT call */
2350: -- End timestamp
2351: IF(upper(stathist) <> 'NONE') THEN
2352: BEGIN
2353: FND_STATS.UPDATE_HIST(schemaname=>s_rec.sname, objectname=>s_rec.sname, objecttype=>'HIST', partname=>NULL, columntablename=>NULL, degree=>degree_parallel, upd_ins_flag=>'S' );
2354: END;
2355: END IF;
2356: END LOOP ;
2357: /* t_rec */

Line 2382: FND_STATS.GATHER_ALL_COLUMN_STATS(ownname=>ownname,percent=>percent,degree=>degree,hmode=>stathist,invalidate=>invalidate);

2378: l_message VARCHAR2(2000);
2379: BEGIN
2380: -- Set the package body variable.
2381: stathist := hmode;
2382: FND_STATS.GATHER_ALL_COLUMN_STATS(ownname=>ownname,percent=>percent,degree=>degree,hmode=>stathist,invalidate=>invalidate);
2383: EXCEPTION
2384: WHEN OTHERS THEN
2385: errbuf := sqlerrm ;
2386: retcode := '2';

Line 2428: FND_STATS.GATHER_COLUMN_STATS(ownname,tabname,colname,percent,degree ,hsize,backup_flag,partname,hmode,invalidate);

2424: FND_FILE.put_line(FND_FILE.log,l_message);
2425: dlog(l_message);
2426: BEGIN
2427: dlog('about to g c s');
2428: FND_STATS.GATHER_COLUMN_STATS(ownname,tabname,colname,percent,degree ,hsize,backup_flag,partname,hmode,invalidate);
2429: EXCEPTION
2430: WHEN exist_insufficient THEN
2431: errbuf := sqlerrm ;
2432: retcode := '2';

Line 2476: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>partname, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'S' );

2472: END IF;
2473: -- Insert/update the fnd_stat_hist table
2474: IF(upper(stathist) <> 'NONE') THEN
2475: BEGIN
2476: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>partname, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'S' );
2477: END;
2478: END IF;
2479: -- First export the col stats depending on the backup_flag
2480: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN

Line 2484: FND_STATS.CREATE_STAT_TABLE();

2480: IF ( upper(NVL(backup_flag,'NOBACKUP')) = 'BACKUP' ) THEN
2481: BEGIN
2482: -- First create the FND_STATTAB if it doesn't exist.
2483: BEGIN
2484: FND_STATS.CREATE_STAT_TABLE();
2485: EXCEPTION
2486: WHEN exist_insufficient THEN
2487: NULL;
2488: END;

Line 2497: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => tabname, partname =>partname,

2493: method := 'FOR COLUMNS SIZE '
2494: || hsize
2495: || ' '
2496: || colname;
2497: FND_STATS.GATHER_TABLE_STATS_PVT ( ownname => ownname, tabname => tabname, partname =>partname,
2498: estimate_percent => NVL(percent,10), method_opt => method, degree => degree_parallel, CASCADE => FALSE,
2499: stattab => fnd_stattab, statown => fnd_statown, invalidate => invalidate);
2500: -- End timestamp
2501: IF(upper(stathist) <> 'NONE') THEN

Line 2503: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>NULL, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'E' );

2499: stattab => fnd_stattab, statown => fnd_statown, invalidate => invalidate);
2500: -- End timestamp
2501: IF(upper(stathist) <> 'NONE') THEN
2502: BEGIN
2503: FND_STATS.UPDATE_HIST(schemaname=>ownname, objectname=>colname, objecttype=>'COLUMN', partname=>NULL, columntablename=>tabname, degree=>degree_parallel, upd_ins_flag=>'E' );
2504: END;
2505: END IF;
2506: END;
2507: /* GATHER_COLUMN_STATS */

Line 3066: /* fnd_stats_hist. These values are used later if restartability is */

3062: /* end of ANALYZE_ALL_COLUMNS */
3063: /************************************************************************/
3064: /* Procedure: UPDATE_HIST */
3065: /* Desciption: Internal procedure to insert or update entries in table */
3066: /* fnd_stats_hist. These values are used later if restartability is */
3067: /* needed. */
3068: /************************************************************************/
3069: PROCEDURE UPDATE_HIST(schemaname VARCHAR2,
3070: objectname IN VARCHAR2,

Line 3088: -- S (Start) is when the entry is already in fnd_stats_hist and statistics

3084: --- if(stathist='FULL') then
3085: --- stathist:='LASTRUN';
3086: --- end if;
3087: IF(stathist = 'LASTRUN') THEN -- retaining the old behavior as default
3088: -- S (Start) is when the entry is already in fnd_stats_hist and statistics
3089: -- were gathering is going to start for that particular object
3090: IF (upd_ins_flag = 'S') THEN
3091: UPDATE FND_STATS_HIST
3092: SET parallel = degree ,

Line 3091: UPDATE FND_STATS_HIST

3087: IF(stathist = 'LASTRUN') THEN -- retaining the old behavior as default
3088: -- S (Start) is when the entry is already in fnd_stats_hist and statistics
3089: -- were gathering is going to start for that particular object
3090: IF (upd_ins_flag = 'S') THEN
3091: UPDATE FND_STATS_HIST
3092: SET parallel = degree ,
3093: request_id = cur_request_id,
3094: request_type = request_from ,
3095: last_gather_start_time = sysdate ,

Line 3118: INTO FND_STATS_HIST

3114: /* Added by mo, this segment checks if an entry was updated or not.
3115: If not, a new entry will be added. */
3116: IF SQL%ROWCOUNT = 0 THEN
3117: INSERT
3118: INTO FND_STATS_HIST
3119: (
3120: SCHEMA_NAME ,
3121: OBJECT_NAME ,
3122: OBJECT_TYPE ,

Line 3152: -- E (End) is when the entry is already in fnd_stats_hist and statistics

3148: percent
3149: );
3150: END IF;
3151: END IF;
3152: -- E (End) is when the entry is already in fnd_stats_hist and statistics
3153: -- gathering finished successfully for that particular object
3154: IF (upd_ins_flag = 'E') THEN
3155: UPDATE FND_STATS_HIST
3156: SET last_gather_date = sysdate,

Line 3155: UPDATE FND_STATS_HIST

3151: END IF;
3152: -- E (End) is when the entry is already in fnd_stats_hist and statistics
3153: -- gathering finished successfully for that particular object
3154: IF (upd_ins_flag = 'E') THEN
3155: UPDATE FND_STATS_HIST
3156: SET last_gather_date = sysdate,
3157: last_gather_end_time = sysdate
3158: WHERE schema_name = upper(schemaname)
3159: AND object_name = upper(objectname)

Line 3176: UPDATE FND_STATS_HIST

3172: AND history_mode='L';
3173: END IF;
3174: elsif (stathist = 'FULL') THEN -- new option, old hist will not be updated
3175: IF (upd_ins_flag = 'S') THEN
3176: UPDATE FND_STATS_HIST
3177: SET parallel = degree ,
3178: request_id = cur_request_id,
3179: request_type = request_from ,
3180: last_gather_start_time = sysdate ,

Line 3207: INTO FND_STATS_HIST

3203: FULL mode, because multiple calls for the same object from the same session will have
3204: the same cur_request_id. If not, a new entry will be added. */
3205: IF SQL%ROWCOUNT = 0 THEN
3206: INSERT
3207: INTO FND_STATS_HIST
3208: (
3209: SCHEMA_NAME ,
3210: OBJECT_NAME ,
3211: OBJECT_TYPE ,

Line 3242: -- E (End) is when the entry is already in fnd_stats_hist and statistics

3238: );
3239:
3240: END IF;
3241: END IF;
3242: -- E (End) is when the entry is already in fnd_stats_hist and statistics
3243: -- gathering finished successfully for that particular object
3244: IF (upd_ins_flag = 'E') THEN
3245: UPDATE FND_STATS_HIST
3246: SET last_gather_date = sysdate,

Line 3245: UPDATE FND_STATS_HIST

3241: END IF;
3242: -- E (End) is when the entry is already in fnd_stats_hist and statistics
3243: -- gathering finished successfully for that particular object
3244: IF (upd_ins_flag = 'E') THEN
3245: UPDATE FND_STATS_HIST
3246: SET last_gather_date = sysdate,
3247: last_gather_end_time = sysdate
3248: WHERE schema_name = upper(schemaname)
3249: AND object_name = upper(objectname)

Line 3271: delete from fnd_stats_hist where object_name like upper(objectname) and schema_name like upper(schemaname);

3267: END IF;
3268: COMMIT;
3269: EXCEPTION
3270: when unique_constraint_detected then
3271: delete from fnd_stats_hist where object_name like upper(objectname) and schema_name like upper(schemaname);
3272: commit;
3273: END;
3274: /* end of UPDATE_HIST */
3275: /************************************************************************/

Line 3286: FROM fnd_stats_hist

3282: IS
3283: PRAGMA AUTONOMOUS_TRANSACTION;
3284: BEGIN
3285: DELETE
3286: FROM fnd_stats_hist
3287: WHERE request_id BETWEEN from_req_id AND to_req_id;
3288:
3289: COMMIT;
3290: END;

Line 3316: FROM fnd_stats_hist

3312: ELSE
3313: purge_to_date_l:=purge_to_date;
3314: END IF;
3315: DELETE
3316: FROM fnd_stats_hist
3317: WHERE last_gather_date BETWEEN to_date(purge_from_date_l,'DD-MM-YY') AND to_date(purge_to_date_l,'DD-MM-YY');
3318:
3319: COMMIT;
3320: END;

Line 3805: END FND_STATS;

3801: -- dbms_output.put_line('Database version is '||db_versn);
3802: EXCEPTION
3803: WHEN OTHERS THEN
3804: db_versn:=81; -- Just in case, default it to 8i
3805: END FND_STATS;