In which three situations must you collect optimizer statistics manually for database objects in addition to automatic statistics collection?

Question

Options

• AWhen substantial DML activity occurs between the nightly automatic stats gathering maintenance job
• BWhen substantial activity occurs on a partition of the partitioned table.
• CWhen a table is used for bulk loads that add 10% or more to the total size of the table
• DWhen an index is created or dropped for a column
• EWhen the degree of parallelism is explicitly defined for a table

• A
  6% (3)
• B
  75% (38)
• D
  16% (8)
• E
  4% (2)

Explanation

When to Gather Statistics When gathering statistics manually, you not only need to determine how to gather statistics, but also when and how often to gather new statistics. For an application in which tables are being incrementally modified, you may only need to gather new statistics every week or every month. The simplest way to gather statistics in these environment is to use a script or job scheduling tool to regularly run the GATHER_SCHEMA_STATS and GATHER_DATABASE_STATS procedures. The frequency of collection intervals should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process. (C) For tables which are being substantially modified in batch operations, such as with bulk loads, statistics should be gathered on those tables as part of the batch operation. The DBMS_STATS procedure should be called as soon as the load operation completes. For partitioned tables, there are often cases in which only a single partition is modified. In those cases, statistics can be gathered only on those partitions rather than gathering statistics for the entire table. However, gathering global statistics for the partitioned table may still be necessary.

No community discussion yet for this question.