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