First page Back Continue Last page Overview Graphics
...DBMS_STATS
Many procedures available
- GATHER_INDEX_STATS, GATHER_TABLE_STATS
- GATHER_SCHEMA_STATS, GATHER_DATABASE_STATS
DBMS_STATS advantages
- Can be parallelized for better performance
- Collects complete stats for partitioned objects
- Save existing stats in user tables
- During a GATHER operation
- Explicitly with EXPORT
- Prevent cursor invalidation with NO_INVALIDATE
- Fabricate stats with SET_TABLE_STATS to see effect
Notes:
DBMS_STATS is more flexible in that there are several procedures that providing different target scope, I.e. you can collect statistics on an individual object (table or index), all objects within a schema or even the entire database.
DBMS_STATS has many advantages over the ANALYZE, including:
DBMS_STATS can be run in parallel for better performance. See the DEGREE argument for more information.
DBMS_STATS collects complete statistics for partitioned objects. The ANALYZE command does not collect complete statistics for partitions.
Existing statistics can be saved (backed up) in a user-defined table, so that they can be restored if desired. This can be done when you are gathering statistics or explicitly with EXPORT_x_STATS.
Prevent active cursor invalidation with NO_INVALIDATE=>TRUE parameter. Changes to access pate decisions will not take place until the cursor is flushed from the shared pool and the query is executed again.
Update the data dictionary (or user stats table) with the SET_x_STATS to see effect. For example, you can change the number of rows and number of blocks to see the effect on the optimizer.
Refer to the Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2) for complete documentation.