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

Previous Topic: Collecting Statistics

Next Topic: Do not Collect Statistics for Temporary Tables

Recommended Weekly and Daily Statistics-gathering Configurations

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

Partitions

Daily wgn_stats call

Weekly wgn_stats call

Daily

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

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

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

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

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

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: You may need to reduce sample percentage P_def_cap_samp_pct for larger installations to prevent statistics gathering taking too long.