Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2) Part Number A96612-01 |
|
With DBMS_STATS
you can view and modify optimizer statistics gathered for database objects. The statistics can reside in the dictionary or in a table created in the user's schema for this purpose. You can also collect and manage user-defined statistics for tables and domain indexes using this package. For example, if the DELETE_COLUMN_STATS
procedure is invoked on a column for which an association is defined, user-defined statistics for that column are deleted in addition to deletion of the standard statistics.
Only statistics stored in the dictionary have an impact on the cost-based optimizer. You can also use DBMS_STATS
to gather statistics in parallel.
This chapter contains the following topics:
The DBMS_STATS
subprograms perform the following general functions:
Most of the DBMS_STATS
procedures include the three parameters statown
, stattab
, and statid
. These parameters allow you to store statistics in your own tables (outside of the dictionary), which does not affect the optimizer. Therefore, you can maintain and experiment with sets of statistics.
The stattab
parameter specifies the name of a table in which to hold statistics, and it is assumed that it resides in the same schema as the object for which statistics are collected (unless the statown
parameter is specified). You can create multiple tables with different stattab
identifiers to hold separate sets of statistics.
Additionally, you can maintain different sets of statistics within a single stattab
by using the statid
parameter, which avoids cluttering the user's schema.
For the SET
and GET
procedures, if stattab
is not provided (that is, NULL
), then the operation works directly on the dictionary statistics; therefore, you do not need to create these statistics tables if they only plan to modify the dictionary directly. However, if stattab
is not NULL
, then the SET
or GET
operation works on the specified user statistics table, and not the dictionary.
When a DBMS_STATS
subprogram modifies or deletes the statistics for an object, all the dependent cursors are invalidated by default and corresponding statements are subject to recompilation next time so that the new statistics have immediate effects. This behavior can be altered with the no_invalidate
argument.
DBMS_STATS
supports operations on user-defined statistics. When a domain index or column is associated with a statistics type (using the associate
statement), operations on the index or column manipulate user-defined statistics. For example, gathering statistics for a domain index (for which an association with a statistics type exists) using the GATHER_INDEX_STATS
interface invokes the user-defined statistics collection method of the associated statistics type. Similarly, delete, transfer, import, and export operations manipulate user-defined statistics.
SET
and GET
operations for user-defined statistics are also supported using a special version of the SET
and GET
interfaces for columns and indexes.
The following procedures in this package commit the current transaction, perform the operation, and then commit again:
Types for the minimum and maximum values and histogram endpoints include:
TYPE numarray IS VARRAY(256) OF NUMBER; TYPE datearray IS VARRAY(256) OF DATE; TYPE chararray IS VARRAY(256) OF VARCHAR2(4000); TYPE rawarray IS VARRAY(256) OF RAW(2000); type StatRec is record ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY);
Types for listing stale tables include:
type ObjectElem is record ( ownname VARCHAR2(30), -- owner objtype VARCHAR2(6), -- 'TABLE' or 'INDEX' objname VARCHAR2(30), -- table/index partname VARCHAR2(30), -- partition subpartname VARCHAR2(30), -- subpartition confidence NUMBER); -- not used type ObjectTab is TABLE of ObjectElem;
Use the following constant to indicate that auto-sample size algorithms should be used:
AUTO_SAMPLE_SIZE CONSTANT NUMBER;
The constant used to determine the system default degree of parallelism, based on the initialization parameters, is:
DEFAULT_DEGREE CONSTANT NUMBER;
Use the following procedures to store and retrieve individual column-related, index-related, and table-related statistics:
PREPARE_COLUMN_VALUES SET_COLUMN_STATS SET_INDEX_STATS SET_SYSTEM_STATS SET_TABLE_STATS
In the special versions of the SET_*_STATS
procedures for setting user-defined statistics, the following, if provided, are stored in the dictionary or external statistics table:
The user-defined statistics and the corresponding statistics type are inserted into the USTATS$
dictionary table. You can specify user-defined statistics without specifying the statistics type name.
CONVERT_RAW_VALUE GET_COLUMN_STATS GET_INDEX_STATS GET_SYSTEM_STATS GET_TABLE_STATS
The special versions of the GET_*_STATS
procedures return user-defined statistics and the statistics type owner and name as OUT
arguments corresponding to the schema object specified. If user-defined statistics are not collected, NULL
values are returned.
DELETE_COLUMN_STATS DELETE_INDEX_STATS DELETE_SYSTEM_STATS DELETE_TABLE_STATS DELETE_SCHEMA_STATS DELETE_DATABASE_STATS
The DELETE_*
procedures delete user-defined statistics and the standard statistics for the given schema object.
Use the following procedures to transfer statistics from the dictionary to a user stat table (export_
*) and from a user stat table to the dictionary (import_
*):
CREATE_STAT_TABLE DROP_STAT_TABLE
CREATE_STAT_TABLE
can hold user-defined statistics and the statistics type object number.
EXPORT_COLUMN_STATS EXPORT_INDEX_STATS EXPORT_SYSTEM_STATS EXPORT_TABLE_STATS EXPORT_SCHEMA_STATS EXPORT_DATABASE_STATS IMPORT_COLUMN_STATS IMPORT_INDEX_STATS IMPORT_SYSTEM_STATS IMPORT_TABLE_STATS IMPORT_SCHEMA_STATS IMPORT_DATABASE_STATS
The IMPORT_*
procedures retrieve statistics, including user-defined statistics, from the stattab
table and store them in the dictionary. Because the SET_*_STATS
and GET_*_STATS
interfaces are supported for user-defined statistics, user-defined statistics can be copied to another database using this interface.
Use the following procedures to gather certain classes of optimizer statistics, with possible performance improvements over the ANALYZE
command:
GATHER_INDEX_STATS GATHER_TABLE_STATS GATHER_SCHEMA_STATS GATHER_DATABASE_STATS GATHER_SYSTEM_STATS
The GATHER_*
procedures also collects user-defined statistics for columns and domain indexes.
The statown
, stattab
, and statid
parameters instruct the package to back up current statistics in the specified table before gathering new statistics.
Oracle also provides the following procedure for generating statistics for derived objects when you have sufficient statistics on related objects:
GENERATE_STATS
|
Copyright © 2000, 2002 Oracle Corporation. All Rights Reserved. |
|