1 – Exclude Tables
Exclude_Tables
Exclude_Tables=(table-list)
Specifies a list of database tables to be excluded from
statistics collection and update for statistics used by the Rdb
query optimizer. You must specify at least one list. You can
specify an options file in place of a list of tables.
If the Exclude_Tables qualifier is used with the Tables qualifier
in the same RMU Collect Optimizer command, the Exclude_Tables
qualifier takes precedence. If the same table is specified in the
table list for both qualifiers, that table is excluded from the
statistics collection and update.
2 – Indexes
Indexes
Indexes[=(index-list)]
Noindex
Specifies the index or indexes for which statistics are to be
collected. If you do not specify an index-list, statistics for
all indexes defined for the tables specified with the Tables
qualifier are collected. If you specify an index-list, statistics
are collected only for the named indexes. If you specify
the Noindex qualifier, statistics for the index cardinality,
average index depth, index key clustering factor, and index data
clustering factor are not collected.
Specify the Notable qualifier if you do not want statistics
collected for tables. (Remember, the Tables qualifier without
a table-list is the default.)
The default is the Indexes qualifier without an index-list.
3 – Log
Log
Log=file-name
Nolog
Specifies how the values calculated for the statistics are to
be logged. Specify the Log qualifier to have the information
displayed to SYS$OUTPUT. Specify the Log=file-spec qualifier
to have the information written to a file. Specify the Nolog
qualifier to prevent display of the information. If you do not
specify any of variation of the Log qualifier, the default is
the current setting of the DCL verify switch. (The DCL SET VERIFY
command controls the DCL verify switch.)
4 – Row Count
Row_Count=n
Specifies the number of rows that are sent in a single I/O
request when Workload Statistics are collected. You can
experiment to find the value for n that provides the best
performance and memory usage for your database and environment.
As you increase the value of n, you see an increase in
performance at the expense of additional memory and overhead.
The minimum value you can specify for n is 1. The default value
for n is 100.
5 – Statistics
Statistics
Statistics[=(options)]
Specifies the type of statistics you want to collect for the
items specified with the Tables, System_Relations, and Indexes
qualifiers. If you specify the Statistics qualifier without
an options list, all statistics are collected for the items
specified.
If you specify the Statistics qualifier with an options list,
Oracle RMU collects types of statistics described in the
following list. If you specify more than one option, separate the
options with commas and enclose the options within parenthesis.
The Statistics qualifier options are:
o Cardinality
Collects the table cardinality for the tables specified with
the Tables and System_Relations qualifiers and the index and
index prefix cardinalities for the indexes specified with the
Indexes qualifier. Because cardinalities are automatically
maintained by Oracle Rdb, it is usually not necessary
to collect cardinality statistics using the RMU Collect
Optimizer_Statistics command unless you have previously
explicitly disabled cardinality updates.
o Workload
Collects the Column Group, Duplicity Factor, and Null Factor
workload statistics for the tables specified with the Tables
and System_Relations qualifiers.
o Storage
Collects the following statistics:
- Table Row Clustering Factor for the tables specified with
the Tables qualifier
- Index Key Clustering Factor, the Index Data Clustering
Factor, and the Average Index Depth for the indexes
specified with the Indexes qualifier
See System Tables Used to Store Optimizer Statistics in the
Usage_Notes entry for this command for information on the
columns and tables used in the system relations to store these
statistics.
6 – System Relations
System_Relations
Nosystem_Relations
Specifies that optimizer statistics are to be collected for
system tables (relations) and their associated indexes.
If you do not specify the System_Relations qualifier, or if you
specify the Nosystem_Relations qualifier, optimizer statistics
are not collected for system tables or their associated indexes.
Specify the Noindex qualifier if you do not want statistics
collected for indexes defined on the system tables.
The default is the Nosystem_Relations qualifier.
7 – Tables
Tables
Tables[=(table-list)]
Notables
Specifies the table or tables for which statistics are to be
collected. If you specify a table-list, statistics for those
tables and their associated indexes are collected. If you do
not specify a table-list, statistics for all tables and their
associated indexes in the database are collected. If you do
not specify the Table qualifier, statistics for all tables are
collected. If you specify the Notables qualifier, statistics for
for the table cardinality, table row clustering factor, column
group duplicity factor, and column group null factor are not
collected.
Specify the Noindex qualifier if you do not want statistics
collected for indexes.
The Tables qualifier without a table-list is the default.
8 – Transaction Type
Transaction_Type=option
Allows you to specify the transaction mode for the transactions
used to collect statistics. Valid options are:
o Automatic
o Read_Only
o Noread_Only
You must specify an option if you use this qualifier.
If you do not use any form of this qualifier, the Transaction_
Type=Automatic qualifier is the default. This qualifier specifies
that Oracle RMU is to determine the transaction mode used
to collect statistics. If any storage area in the database
(including those not accessed for collecting statistics) has
snapshots disabled, the transactions used to collect data are set
to read/write mode. Otherwise, the transactions to collect data
are set to read-only mode.
The Transaction_Type=Read_Only qualifier specifies the
transactions used to collect statistics be set to read-only
mode. When you explicitly set the transaction type to read-
only, snapshots need not be enabled for all storage areas in
the database, but must be enabled for those storage areas from
which statistics are collected. Otherwise, you receive an error
and the collect optimizer statistics operation fails.
You might select this option if not all storage areas have
snapshots enabled and you are collecting statistics on objects
that are stored only in storage areas with snapshots enabled. In
this case, using the Transaction_Type=Read_Only qualifier allows
you to collect statistics and impose minimal locking on other
users of the database.
The Transaction_Type=Noread_Only qualifier specifies that the
transactions used to collect statistics be set to read/write
mode. You might select this option if you want to eradicate
the growth of snapshot files that occurs during a read-only
transaction and are willing to incur the cost of increased
locking that occurs during a read/write transaction.