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