[Home] [Help]
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;
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);
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
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
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);
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
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
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 ;
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' );
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' );
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;