Home » Oracle » 1z0-117 » What are three common reasons for SQL statements to perform poorly?
What are three common reasons for SQL statements to perform poorly?
A. Full table scans for queries with highly selective filters
B. Stale or missing optimizer statistics
C. Histograms not existing on columns with evenly distributed data
D. High index clustering factor
E. OPTIMIZER_MODE parameter set to ALL_ROWS for DSS workload
Correct Answer: ABD
Explanation/Reference:
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 optimal access.
Note:
* (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 instance. all_rows
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).
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
