The accuracy of the cost-based optimizer depends on the accuracy of
the statistics it uses, so updating statistics has always been a must.
Formerly, you would have used the SQL statement ANALYZE to compute
or estimate these statistics. When managing an older release,
many database administrators also used a built-in PL/SQL package,
DBMS_STATS, that contains a number of procedures that helped
automate the process of collecting statistics.
Stale statistics can lead to database performance problems, which is why
database statistics gathering has been automated by Oracle. This statistics
gathering can be quite granular. For example, as of Oracle Database
10g, youcan enable automatic statistics collection for a table, which can
be based on whether a table is either stale (which means that more than
10 percent of the objects in the table have changed) or empty.
Table 4-1. Database statistics
Data structure Type of statistics
Table Number of rows
Number of blocks
Number of unused blocks
Average available free space per block
Number of chained rows
Average row length
Column Number of distinct values per column
Second-lowest column value
Second-highest column value
Column density factor
Index Depth of index B*-tree structure
Number of leaf blocks
Number of distinct values
Average number of leaf blocks per key
Average number of data blocks per key
Clustering factor
Query Optimization | 113
The use of statistics makes it possible for the cost-based optimizer to make a much
more well-informed choice of the optimal execution plan.
Pages:
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263