Database Reference
In-Depth Information
Figure 8-6. Range-hash partitioned table with 16 segments
For partitioned objects, the database engine is able to handle all object statistics discussed in the previous
sections (in other words, table statistics, column statistics, histograms and index statistics) at the table/index-level as
well as at the partition and subpartition levels. Having object statistics at all levels is useful because, depending on
the SQL statement to be processed, the query optimizer considers the object statistics that most closely describe the
segments to be accessed. Simply put, the query optimizer uses the partition and subpartition statistics only when,
during the parse phase, it can determine whether a specific partition or subpartition is accessed. Otherwise, the query
optimizer generally uses the table/index-level statistics. (There are some situations in which the query optimizer uses,
at the same time, table/index statistics as well as partition and subpartition statistics).
Gathering Object Statistics
To gather object statistics, the dbms_stats package contains several procedures. There are several procedures
because, depending on the situation, the process of gathering object statistics should occur for the whole database,
for the data dictionary, for a schema, or for a single object:
gather_database_stats gathers object statistics for a whole database.
gather_dictionary_stats gathers object statistics for the data dictionary. Note that the data
dictionary isn't only composed of the objects stored in the sys schema, but also includes the
other schemas installed by Oracle for optional components.
gather_fixed_objects_stats gathers object statistics for particular objects called fixed
tables (also known as x$ tables ) and fixed indexes that are part of the data dictionary. The fixed
tables, which are commonly used in dynamic performance views, are in-memory structures
only. For this reason, they require special handling. To know which tables are relevant for
this procedure, you can use the following query. Note that object statistics aren't gathered for
every fixed table, though:
SELECT name
FROM v$fixed_table
WHERE type = 'TABLE'
gather_schema_stats gathers object statistics for a whole schema.
gather_table_stats gathers object statistics for one table including its columns and,
optionally, for its indexes.
 
Search WWH ::




Custom Search