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

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support on Mar 10, 2020
Version 6Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000029937
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle 
RSA Version/Condition: All
Platform (Other): Oracle 11, 12
 
IssueThe 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.
 
ResolutionHere 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:


User-added image


  1. 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:


User-added image



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:


User-added image


  1. Log out of SQL
  2. Login as sysdba or the SYS user.
  3. 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);


  1. Log in as avuser.
  2. 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:
User-added image

Attachments

    Outcomes