You have the automatic optimizer statistics gathering enabled for a 24×7 database as part of the
automatic maintenance task. Because the automatic optimizer statistics collection runs during
maintenance window(in the night), the statistics on tables that are significantly modified throughout
the day becomes stale. This leads to poor query performance on these tables and you want to
minimize the
overhead in statistics collection. Identify a solution to this problem.
A.
Change the STALE_PERCENT preference to zero for the tables.
B.
Set the OPTIMIZER_USE_PENDING parameter statistics to TRUE.
C.
Set the OPTIMIZER_DYNAMIC_SAMPLING parameter to zero during day time.
D.
Gather statistics on the table when the tables have a representative number of rows, and then
lock the statistics.
D is correct.
C is correct
OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.