Examine the initializing parameters: An index exists on the column used in the WHERE of a query. You execute the query for the first time today and notice that the query is not using the index. The…

Question

Exhibits

Options

• AThe value of the OPTIMIZER_INDEX_COST_ADJ parameter is set to a low value.
• BThe blocks fetched by the query are greater than the value specified by the DB_FILE_MULTIBLOCK_
• CThe statistics for the CUSTOMERS table and the indexes stale.
• DThe OPTIMIZER_MODE parameter is set to ALL_ROWS.
• EHistogram statistics for CUST_CITY_ID are missing.
• FAverage number of rows per block for the CUSTOMERS table is low.

• A
  9% (4)
• B
  2% (1)
• C
  80% (35)
• E
  7% (3)
• F
  2% (1)

Explanation

C: Old statistics could cause this problem. D: Histograms are feature in CBO and it helps to optimizer to determine how data are skewed(distributed) with in the column. Histogram is good to create for the column which are included in the WHERE clause where the column is highly skewed. Histogram helps to optimizer to decide whether to use an index or full-table scan or help the optimizer determine the fastest table join order. A: 100 is the maximum value of OPTIMIZER_INDEX_COST_ADJ Note: OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly--that is, to make the optimizer more or less prone to selecting an index access path over a full table scan. The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal. B: DB_FILE_MULTIBLOCK_READ_COUNT does not apply: DB_FILE_MULTIBLOCK_READ_COUNT is one of the parameters you can use to minimize I/O during table scans. It specifies the maximum number of blocks read in one I/O operation during a sequential scan. The total number of I/Os needed to perform a full table scan depends on such factors as the size of the table, the multiblock read count, and whether parallel execution is being utilized for the operation. F: High (not low) row per block could make a table scan preferable.

No community discussion yet for this question.