How to change the default Oracle Statistics History Retention period for RSA Identity Governance & Lifecycle

[#1]

Article Number

000029937

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle 
RSA Version/Condition: All
Platform (Other): Oracle 11, 12
 

Issue

The RSA Identity Governance & Lifecycle DB Statistics Refresh job under the Collectors > Scheduling > Tasks tab runs nightly database statistics optimized for the RSA Identity Governance & Lifecycle application. Database statistics results are saved for a certain number of days defined by Oracle's statistics history retention value.

The Oracle statistics history retention value is set to 31 days by default. Some customers have found that the SYSAUX tablespace has space issues due to the large amounts of space consumed by 31 days of database statistics. The purpose of this RSA Knowledge Base Article is to provide instructions for reducing the default statistics history retention setting. However, the same steps may be used to increase the default statistics history retention setting if desired.
 

Resolution

Here are the steps to determine the existing value for the Oracle statistics history retention setting and how to modify that value.

1. Login to SQL as avuser:
2. Run the following query to determine how many days of statistics Oracle is currently retaining on your system. Unless this has been previously changed by someone at your site, this value will be 31. 

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

Sample output:

Image description

3. Run the following two queries to confirm the oldest date retained. For example, if your system is new and installed less than 31 days ago, you will have less than 31 days of history retained.

select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY, 
(systimestamp - DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) 
as NUM_DAYS_RET from dual;

Sample output:

Image description

select (DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY)
, to_date(to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd-MON-YY hh:mi:ss AM')
,'dd-MON-YY hh:mi:ss AM') as STATS_AVAIL_FROM
, to_date(to_char(systimestamp,'dd-MON-YY hh:mi:ss AM'),'dd-MON-YY hh:mi:ss AM') as TODAY
, round(to_date(to_char(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'dd-MON-YY hh:mi:ss AM')
,'dd-MON-YY hh:mi:ss AM')
- to_date(to_char(systimestamp,'dd-MON-YY hh:mi:ss AM'),'dd-MON-YY hh:mi:ss AM')) as NUM_RET_DAYS_RD
, (systimestamp - DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY) as NUM_DAYS_RET_PRECISE from dual;

Sample output:

Image description

4. Log out of SQL
5. Login as sysdba or the SYS user.
6. Execute the following SQL where xx is the number of days you want to retain database statistics history.

exec dbms_stats.alter_stats_history_retention(xx);

For example, to change the history retention to 15 days execute:

exec dbms_stats.alter_stats_history_retention(15);

7. Log in as avuser.
8. Run the following query to confirm that the Oracle statistics history retention has been adjusted down from 31 to 15 days:

select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

Sample output:
Image description