000038548 - How to remove unnecessary tables from database statistics analysis in RSA Identity Governance & Lifecycle

Document created by RSA Customer Support Employee on Mar 10, 2020
Version 1Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000038548
Applies ToRSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: All

 
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. RSA recommends that certain tables in the RSA Identity Governance & Lifecycle application be skipped when running the database statistics job because including them in database statistics adversely affects performance. This RSA Knowledge Base Article explains:
  • Which tables should be skipped when running database statistics.
  • How to determine what tables are being scanned by database statistics.
  • How to prevent tables from being scanned by database statistics. 
ResolutionRSA recommends that the following tables be removed from database statistics analysis:
  • Y%
  • T_DC_SOURCEDATA%
  • STX%
  • T_RAW%
  • T_DATABASE_STATISTICS_CONTROL
  • %GTT%
 

  1. To determine if any of these tables are currently being analyzed by database statistics, run the following SQL query as avuser:


select table_name,last_analyzed 
from user_tables 
where (table_name like 'Y%' or table_name like 'T_DC_SOURCEDATA%' 
or table_name like 'STX%' or table_name like 'T_RAW%' 
or table_name like 'T_DATABASE_STATISTICS_CONTROL' 
or table_name like '%GTT%') and trim(last_analyzed) is not NULL;



If this query returns no results, your database statistics are configured correctly.



If this query returns results as in the example below, continue with next steps to configure your database statistics for maximum performance benefit.
 


User-added image


  1. Execute the next query to generate SQL statements to remove the tables from scanning:


SELECT 
'execute DBMS_STATS.delete_table_stats(' || '''avuser''' || ',' || '''' || table_name || ''');' 
from user_tables 
where (table_name like 'Y%' or table_name like 'T_DC_SOURCEDATA%' 
or table_name like 'STX%' or table_name like 'T_RAW%' 
or table_name like 'T_DATABASE_STATISTICS_CONTROL' 
or table_name like '%GTT%') and trim(last_analyzed) is not NULL;



Sample output:


User-added image


  1. Run each statement generated in the previous query, followed by a commit. For example:


exec DBMS_STATS.delete_table_stats('avuser','T_RAW_ACCOUNT');
exec DBMS_STATS.delete_table_stats('avuser','T_RAW_ACCOUNT_BAD');
exec DBMS_STATS.delete_table_stats('avuser','T_RAW_ACCOUNT_MAPPING');
exec DBMS_STATS.delete_table_stats('avuser','T_RAW_ACCOUNT_MAPPING_BAD');
commit;


  1. Execute the query below to ensure no results are returned. If results are returned, repeat the steps above.


select table_name,last_analyzed 
from user_tables 
where (table_name like 'Y%' or table_name like 'T_DC_SOURCEDATA%' 
or table_name like 'STX%' or table_name like 'T_RAW%' 
or table_name like 'T_DATABASE_STATISTICS_CONTROL' 
or table_name like '%GTT%') and trim(last_analyzed) is not NULL;


 

Attachments

    Outcomes