You are administering a database that supports an OLTP workload. Automatic optimizer statistics collection is scheduled in the night maintenance window. Some of the tables get updated frequently…

Question

Options

• ASet the global statistics preference STALE_PERCENT to 0.
• BUse dynamically sampling hint for the queries on frequently updated tables.
• CCreate histogram for the columns used frequently in the WHERE clause.
• DGather statistics with global statistics preference NO_VALIDATE to TRUE.
• ESet the OPTIMZER_USE_PENDING_STATISTICS parameter to TRUE.

• A
  11% (6)
• B
  78% (43)
• D
  4% (2)
• E
  7% (4)

Explanation

B: Dynamic sampling first became available in Oracle9i Database Release 2. It is the ability of the cost-based optimizer (CBO) to sample the tables a query references during a hard parse, to determine better default statistics for unanalyzed segments, and to verify its "guesses." This sampling takes place only at hard parse time and is used to dynamically generate better statistics for the optimizer to use, hence the name dynamic sampling. There are two ways to use dynamic sampling: The OPTIMIZER_DYNAMIC_SAMPLING parameter can be set at the database instance level and can also be overridden at the session level with the ALTER SESSION command. The DYNAMIC_SAMPLING query hint can be added to specific queries. C: A histogram is a collection of information about the distribution of values within a column. In some cases, the distribution of values within a column of a table will affect the optimizer's decision to use an index vs. perform a full-table scan. This scenario occurs when the value with a where clause has a disproportional amount of values, making a full-table scan cheaper than index Histograms are also important for determinine the optimal table join order. A: Too much statistics would be gathered. Note: STALE_PERCENT - This value determines the percentage of rows in a table that have to change before the statistics on that table are deemed stale and should be regathered. The default D: In Oracle PL/SQL, the VALIDATE keyword defines the state of a constraint on a column in a E: OPTIMZER_USE_PENDING_STATISTICS specifies whether or not the optimizer uses pending statistics when compiling SQL statements.

No community discussion yet for this question.