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

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
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).