The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improving performance.This package is concerned with optimizer statistics only.Given that Oracle sets automatic statistics collection of this kind on by default, this package is intended for only specialized cases.
You can also collect and manage user-defined statistics for tables and domain indexes using this package.
For example, if the 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 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 fltarray IS VARRAY(256) OF BINARY_FLOAT; TYPE dblarray IS VARRAY(256) OF BINARY_DOUBLE; TYPE Stat Rec IS RECORD ( epc NUMBER, minval RAW(2000), maxval RAW(2000), bkvals NUMARRAY, novals NUMARRAY); TYPE Object Elem 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 type Object Tab is TABLE of Object Elem; procedures using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.
Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.
Most of the procedures have a parameter, which allows you to override any lock on statistics.
Whenever statistics in dictionary are modified, old versions of statistics are saved automatically for future restoring.
Gathering Optimizer Statistics Use the following subprograms to gather certain classes of optimizer statistics, with possible performance improvements over the command: GATHER_DATABASE_STATS Procedures GATHER_DICTIONARY_STATS Procedure GATHER_FIXED_OBJECTS_STATS Procedure GATHER_INDEX_STATS Procedure GATHER_SCHEMA_STATS Procedures GATHER_SYSTEM_STATS Procedure GATHER_TABLE_STATS Procedure The 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 Procedure Setting or Getting Statistics Use the following subprograms to store and retrieve individual column-related, index-related, and table-related statistics: PREPARE_COLUMN_VALUES Procedures PREPARE_COLUMN_VALUES_NVARCHAR2 Procedure PREPARE_COLUMN_VALUES_ROWID Procedure SET_COLUMN_STATS Procedures SET_INDEX_STATS Procedures SET_SYSTEM_STATS Procedure SET_TABLE_STATS Procedure GET_COLUMN_STATS Procedures GET_INDEX_STATS Procedures GET_SYSTEM_STATS Procedure GET_TABLE_STATS Procedure In the special versions of the procedures delete both user-defined statistics and the standard statistics for the given schema object.
DELETE_COLUMN_STATS Procedure DELETE_DATABASE_STATS Procedure DELETE_DICTIONARY_STATS Procedure DELETE_FIXED_OBJECTS_STATS Procedure DELETE_INDEX_STATS Procedure DELETE_SCHEMA_STATS Procedure DELETE_SYSTEM_STATS Procedure DELETE_TABLE_STATS Procedure Transferring Statistics Use the following procedures for creating and dropping the user statistics table.
CREATE_STAT_TABLE Procedure DROP_STAT_TABLE Procedure Use the following procedures to transfer statistics EXPORT_COLUMN_STATS Procedure EXPORT_DATABASE_STATS Procedure EXPORT_DICTIONARY_STATS Procedure EXPORT_FIXED_OBJECTS_STATS Procedure EXPORT_INDEX_STATS Procedure EXPORT_SCHEMA_STATS Procedure EXPORT_SYSTEM_STATS Procedure EXPORT_TABLE_STATS Procedure IMPORT_COLUMN_STATS Procedure IMPORT_DATABASE_STATS Procedure IMPORT_DICTIONARY_STATS Procedure IMPORT_FIXED_OBJECTS_STATS Procedure IMPORT_INDEX_STATS Procedure IMPORT_SCHEMA_STATS Procedure IMPORT_SYSTEM_STATS Procedure IMPORT_TABLE_STATS Procedure Locking or Unlocking Statistics Use the following procedures to lock and unlock statistics on objects.