What are three common reasons for SQL statements to perform poorly?

Question

Options

• AFull table scans for queries with highly selective filters
• BStale or missing optimizer statistics
• CHistograms not existing on columns with evenly distributed data
• DHigh index clustering factor
• EOPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload

• A
  73% (24)
• C
  9% (3)
• E
  18% (6)

Explanation

D: The clustering_factor measures how synchronized an index is with the data in a table. A table with a high clustering factor is out-of-sequence with the rows and large index range scans will consume lots of I/O. Conversely, an index with a low clustering_factor is closely aligned with the table and related rows reside together of each data block, making indexes very desirable for * (Not C) 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. * OPTIMIZER_MODE establishes the default behavior for choosing an optimization approach for The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

No community discussion yet for this question.