How to remove unnecessary tables from database statistics analysis in RSA Identity Governance & Lifecycle
Originally Published: 2020-03-10
Article Number
Applies To
RSA Version/Condition: 6.x, 7.0.x, 7.1.x, 7.2.x
Issue
- 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
- 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;
