Database Reference
In-Depth Information
Chapter 8
Object Statistics
Object statistics describe the data stored in the database. For example, they tell the query optimizer how many rows
are stored in tables. Without this quantitative information, the query optimizer could never make right decisions, such
as finding the right join method for small or large tables (result sets). To illustrate this, consider the following example.
Let's say I ask you what's the fastest method of transportation for me to get home from a particular place. Would it
be by car, by train, or by plane? Why not by bike? The point is, without considering my actual position and where my
home is, you can't arrive at a meaningful answer. Without object statistics, the query optimizer has the same problem.
It simply can't generate optimal execution plans.
This chapter begins by describing which object statistics are available and where to find them in the data
dictionary. Then it presents the features of the
dbms_stats
package used to gather, restore, lock, compare, and delete
statistics. Finally, it describes a few strategies that I use to manage object statistics, making full use of the available
features. What the query optimizer does with object statistics is described here for only a few cases. The purpose of
most statistics is explained in Chapter 9. Because the query optimizer uses statistics and initialization parameters at
the same time, it makes sense to describe them together in the same chapter.
■
The database engine, through the
ASSOCIATE STATISTICS
statement, allows user-defined statistics to be
associated with columns, functions, packages, types, domain indexes, and index types. When needed, this SQL statement
is a very powerful feature, although in practice this technique is rarely used. For this reason,
ASSOCIATE STATISTICS
isn't covered here. For information about it, refer to the
Oracle Database Data Cartridge Developer's Guide
manual and to
Chapter 7 of
Expert Oracle Practices
(Apress, 2010).
Note
The dbms_stats Package
It used to be that object statistics were gathered with the
ANALYZE
statement. This is no longer the case. For gathering
object statistics, the
ANALYZE
statement is available, but only for purposes of backward compatibility. As of Oracle9
i
,
it's recommended that you use the
dbms_stats
package. In fact, not only does the
dbms_stats
package provide many
more features, but in some situations it provides better statistics as well. For example, the
ANALYZE
statement provides
less control over the gathering of statistics, doesn't support external tables, and for partitioned objects, gathers
statistics only for each segment and derives (usually poorly) the statistics at the table/index level. For these reasons,
I don't cover the
ANALYZE
statement in this chapter.
It's important to recognize that the
dbms_stats
package provides a comprehensive set of procedures and
functions to manage object statistics. Because there are a lot of objects in a database, it's important to be able to
manage their statistics at different granularities. You have the choice between managing the object statistics for the
