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.
Answer:D
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.