Gather Schema Statistics with LASTRUN Option does not Clean FND_STATS_HIST Table (Doc ID 745442.1)

Last updated on JULY 28, 2016

Applies to:

Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Application Object Library - Version 11.5.10.2 to 11.5.10.2 [Release 11.5]
Information in this document applies to any platform.
Oracle Application Object Library - Version: 12.0.0 to 12.0.6
This problem can occur on any platform.

Symptoms

After running Gather Schema Statistics concurrent request with (LASTRUN) option to save only the history for last run, then run the concurrent request again for 2 times after that found that (FND_STATS_HIST) tables contains the history for all the 3 requests and it is not cleaned up to keep only last run.

Sample steps to reproduce the issue are as following:

1- Login to Oracle Applications.

2- Cleaned all history found in FND_STATS_HIST table be executing (FND_STATS.PURGE_STAT_HISTORY)

3- Check that FND_STATS_HIST table is empty be executing following query which will return ZERO count as a result:

SELECT COUNT(*) FROM FND_STATS_HIST;

4- Run Gather Schema Statistics concurrent request with parameters (for example):

ABM:20::NOBACKUP::LASTRUN:GATHER::Y

5- Then run same script used in step 3 and it should return a number this time.

6- Then check if all records returned in step 5 is related to same concurrent request you submitted in step 4 by running following query:

SELECT DISTINCT REQUEST_ID FROM FND_STATS_HIST;

That script will return only one request_id.

7- Then repeat step 4 twice with the same parameters above.

8- Then run same script used in step 3 and it should higher a number or records than the one returned in step 5

9- Check now how many concurrent requests IDs do you have in the table which should be 3 by using same query in step 6

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms