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

3 years ago

Originally Published: 2020-03-10

Article Number

000049682

Applies To

RSA Product Set: RSA Identity Governance & Lifecycle
RSA Version/Condition: 6.x, 7.0.x, 7.1.x, 7.2.x

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

Resolution

RSA recommends that the following tables be removed from database statistics analysis:

Y%
T_DC_SOURCEDATA%
STX%
T_RAW%
T_DATABASE_STATISTICS_CONTROL
%GTT%

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.

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:

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;

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;

Don't see what you're looking for?

Related Articles

Trending Articles