Database Guide › Database Maintenance › Oracle: Implement the Wgn_Stats Package › Recommended Weekly and Daily Statistics-gathering Configurations

Recommended Weekly and Daily Statistics-gathering Configurations

You must set up daily and weekly statistics gathering. This section suggests suitable wgn_stats configurations, depending on the partitioning scheme implemented. For further information see the Database Statistics Reference Guide.

Daily Partitions

Daily wgn_stats call

execute
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>2, --Last 2 partitions
P_degree=>1
);

Weekly wgn_stats call

execute 
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>7, -- Last 7 partitions 
P_degree=>8 -- or maximum available
);

Weekly Partitions

Daily wgn_stats call

execute 
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>1, --Last partition
P_degree=>1
);

Weekly wgn_stats call

execute 
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>2, -- Last 2 partitions
P_degree=>8 -- or maximum available
);

Monthly or longer, or non-partitioned

Daily wgn_stats call

execute
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>1, --Last partition
P_degree=>1
);

Weekly wgn_stats call

execute
WGN_STATS.GATHER_ALL_STATS(
P_def_audit_samp_pct=>100,
P_def_non_cap_samp_pct=>100,
P_def_cap_samp_pct=>10,
P_num_partitions=>2, -- Last 2 partitions
P_degree=>8 -- or maximum available
);

Note: If statistics gathering takes too long for larger installations, reduce sample percentage P_def_cap_samp_pct.