This approach avoids the error
Query Optimization | 111
discussed in the previous section. After all, the cost-based optimizer would know
which table was bigger and would select the right table to begin the query, regardless
of the syntax of the SQL statement.
Oracle8 and later versions, by default, use the cost-based optimizer to identify the
optimal execution plan. And, since Oracle Database 10g, the cost-based optimizer is
the only supported optimizer. To properly evaluate the cost of any particular execution
plan, the cost-based optimizer uses statistics about the composition of the
relevant data structures. These statistics are automatically gathered by default since
the Oracle Database 10g release into the Automatic Workload Repository (AWR).
Among the statistics gathered in the AWR are database segment access and usage
statistics, time model statistics, system and session statistics, SQL statements that
produce the greatest loads, and Active Session History (ASH) statistics.
How statistics are used
The cost-based optimizer finds the optimal execution plan by assigning an optimization
score for each of the potential execution plans using its own internal rules and
logic along with statistics that reflect the state of the data structures in the database.
Pages:
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261