[Home] [Help]
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;
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 */
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
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
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;
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;
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';
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;
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';
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';
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 */
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
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);
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
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
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
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
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,
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 );
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 );
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
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: ||'.'
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
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 '
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: ||'.'
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
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
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,
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 );
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 );
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
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: ||'.'
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
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 '
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: ||'.'
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 */
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 */
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
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);
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')
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 ,
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
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
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),
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
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 '
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 '
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
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 '
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
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
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),
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
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 '
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 '
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
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 '
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 */
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 */
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
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
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;
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
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 ;
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 */
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 ;
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
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;
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;
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
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;
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);
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
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
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
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;
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;
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
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;
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
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' );
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
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 */
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' );
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 ';
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;
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' );
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 */
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 ';
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
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' );
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 */
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';
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';
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
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;
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
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 */
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,
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 ,
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 ,
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 ,
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,
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)
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 ,
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 ,
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,
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)
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: /************************************************************************/
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;
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;
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;