This article will provide details of the automatic stats collector job in PostgreSQL and Oracle
POSTGRESQL ORACLE
| Parameters: Auto analyze/vacuum in PostgreSQL Default: ON AUTOVACUUM=ON TRACK_COUNTS = ON | Parameters: Auto gather statistic in Oracle Default: TYPICAL STATISTICS_LEVEL=’TYPICAL’ |
| Metadata The background process “stats collector” will collect data about inserts, updates and deletes all the time. PG_STAT_ALL_TABLES will have the data about modified records or tuples modified (update/insert/delete) | Metadata The information about the inserts, updates and deletes on tables will be in SGA. Every 15 minutes SMON will push this data into data-dictionary tables. Or you can update manually to flush stats. Exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO(); DBA_TAB_MODIFICATIONS will have the data about modified records or tuples(update/insert/delete) |
| Frequency of the auto analyze AUTOVACUUM_NAPTIME helps to schedule the frequency of the tables that are required to be analyzed/vacuumed Default:60 sec You can verify parameter after connecting to server command line show autovacuum_naptime; This is parameter can be modified in postgresql.conf and can be reloaded without database restart For example, connect as super user Select pg_reload_conf(); | Frequency of the gather stats Job scheduler helps to modify the frequency of the statistics collection Below sql’s will help for verifying default settings, window name and how to modify the job scheduler select client_name, status, window_group from DBA_AUTOTASK_CLIENT ; select window_name from DBA_SCHEDULER_WINGROUP_MEMBERS where window_group_name = ‘ORA$AT_WGRP_OS’; EXECUTE DBMS_SCHEDULER.SET_ATTRIBUTE(- ‘MONDAY_WINDOW’,’repeat_interval’,’freq=daily; byday=MON; byhour=1;byminute=0;bysecond=0′) |
| Stale Percentage Below parameters will calculate when a table should be analyzed depending no. of DML’s(Insert/update/delete) or stale percent AUTOVACUUM_ANALYZE_SCALE_FACTOR=0.1(Default stale percentage is 0.1) 0.1 is 10% AUTOVACUUM_ANALYZE_THRESHOLD=50(No. of records modified) Parameters for reusing space. AUTOVACUUM_VACUUM_ SCALE_FACTOR =0.2 AUTOVACUUM_VACUUM_ THRESHOLD =50 Note: PostgreSQL will consider both percentage of modified records and also no. of modified records or can set either one of them depending upon the table size and other factors. | Stale Percentage By default, oracle gathers stats when the stale percentage is >=10 Use GLOBAL_PREFS to modify at global level Exec DBMS_STATS.SET_GLOBAL_PREFS(‘STALE_PERCENT’, ’10’); Can be modified at table level as follows exec DBMS_STATS.SET_TABLE_PREFS(null,’EMP’, ‘STALE_PERCENT’,15) |