Collects cardinality and storage statistics for the Oracle
Rdb optimizer. Also collects workload statistics if a workload
profile has been generated.
1 – Description
The purpose of collecting optimizer statistics is to maintain
up-to-date statistics that the Oracle Rdb optimizer uses
to determine solution costs and cardinalities during query
optimization.
You can collect cardinality and storage statistics by issuing the
RMU Collect Optimizer_Statistics command. You can direct Oracle
RMU to collect these statistics for particular tables or indexes
by using the Tables, System_Relations, or Indexes qualifiers.
Before you can collect workload statistics, you must first
generate a workload profile with SQL. The following list
describes the general procedure for generating a workload profile
and collecting workload statistics:
1. Enable workload profiling with the WORKLOAD COLLECTION
IS ENABLED clause of the SQL ALTER DATABASE or SQL CREATE
DATABASE statement.
SQL creates a new system table called RDB$WORKLOAD.
2. Execute the queries for which you want the Oracle Rdb
optimizer to have the best possible statistics.
When you execute the queries, the optimizer determines which
groups of columns are important for optimal processing of the
query. These groups of columns are referred to as workload
column groups. Note that a workload column group may actually
contain only one column.
Each set of workload column groups is entered as a row in the
RDB$WORKLOAD system table.
At this point, the only data in the RDB$WORKLOAD system table
are the workload column groups, the tables with which the
column group is associated, and the date they were entered
into the table. No statistics are currently recorded in the
RDB$WORKLOAD system table.
3. In most cases, now you disable workload profiling with the SQL
ALTER DATABASE WORKLOAD COLLECTION IS DISABLED clause.
Queries executed after you disable workload profiling are
not scanned by the Oracle Rdb optimizer for workload column
groups. You can leave the workload profiling enabled if the
same queries are always executed. In such a case, no new rows
are entered into the RDB$WORKLOAD system table. However, if
you anticipate that queries will be executed for which you do
not want workload profiling to be enabled, you need to disable
workload collection.
4. Execute an RMU Collect Optimizer_Statistics command with the
Statistics=(Workload) qualifier.
Oracle RMU reads the RDB$WORKLOAD system table to determine
for which column groups it should collect statistics, and then
collects the statistics.
5. Execute the queries previously profiled again.
The optimizer uses the statistics gathered by Oracle RMU to
make a best effort at optimizing the profiled queries.
The following list provides some guidelines on when to issue the
RMU Collect Optimizer_Statistics command and which Statistics
qualifier options you should use:
o You should enable workload profiling and execute the
RMU Collect Optimizer_Statistics command with the
Statistics=(Workload) qualifier when you introduce new,
complex, frequently used queries as part of your regular work.
o You should execute the RMU Collect Optimizer_Statistics
command with the Statistics=(Storage) qualifier after you
add metadata, such as new tables or indexes, to the database.
In this case, you do not need to reenable workload profiling.
o You should execute the RMU Collect Optimizer_Statistics
command with the Statistics=(Storage, Workload) qualifier
when the data in the database has significantly increased,
decreased, or changed. In this case, you do not need to
reenable workload profiling.
The statistics you can gather with the RMU Collect Optimizer_
Statistics command and a description of how the optimizer uses
these statistics are summarized in Statistics Gathered by the RMU
Collect Optimizer_Statistics Command.
Table 6 Statistics Gathered by the RMU Collect Optimizer_
Statistics Command
Cardinality Statistics
Statistic
Gathered: Definition: Used by Optimizer to:
Table Number of rows in Determine solution cardinality.
Cardinality a table.
Index Number of Estimate the number of index
Cardinality distinct key keys returned.
values in an
index.
Index Prefix Number of Estimate the number of index
Cardinality distinct key keys returned based on a sorted
values in leading index range.
parts of a multi-
segmented B-tree
index.
Workload Statistics
Statistic
Gathered: Definition: Used by Optimizer to:
Column Group Average number Determine strategies for
Duplicity of duplicates equiselections (selections
Factor per distinct with the IS NULL predicate
value in a column or selection predicates with
group. This is an the equals (=) operator),
estimated value. equijoins, grouped aggregation
(for example, the SQL GROUP
BY clause), or projection
operations (for example, the
SQL DISTINCT clause).
Column Group Number of table Estimate the effects of
Null Factor rows with a NULL NULL data on equijoins and
value in at least equiselections (because they
one column of a imply the removal of rows with
column group. NULL values). Also used for
This is an estimating the cardinality of
estimated value. an outer join result.
Storage Statistics
Statistic
Gathered: Definition: Used by Optimizer to:
Average Average number Estimate the cost of descending
Index Depth of levels to the B-tree. (A cross join with
(sorted traverse on a an inner table that is accessed
indexes B-tree descent. by a sorted index involves
only) repetitive B-tree descents.)
Index Key Average number of Improve the cost estimate
Clustering I/Os required of performing an index-only
Factor to read one retrieval for hashed and sorted
index key and indexes.
all associated
dbkeys during
a hashed key
lookup or a B-
tree index scan,
excluding the
B-tree descent.
Index Data Average number Estimate the cost for fetching
Clustering of I/Os required data rows from a sorted index
Factor to fetch data scan or from a hash bucket.
rows using dbkeys
associated with
an index key.
Table Row The average Estimate the cost of performing
Clustering number of I/Os a sequential scan of a table.
Factor required to read
one row during a
sequential scan
of a table.
2 – Format
(B)0[mRMU/Collect Optimizer_Statistics root-file
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Exclude_Tables=(table-list) x None
/[No]Indexes[=(index-list)] x /Indexes
/[No]Log[=file-name] x Current DCL verify value
/Row_Count=n x /Row_Count=100
/Statistics[=(options)] x /Statistics
/[No]System_Relations x /Nosystem_Relations
/[No]Tables[=(table-list)] x /Tables
/Transaction_Type=option x /Transaction_Type=Automatic
3 – Parameters
3.1 – root-file-spec
root-file-spec
Specifies the database for which statistics are to be collected.
The default file type is .rdb.
4 – Command Qualifiers
4.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.
4.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.
4.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.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.
4.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.
4.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.
4.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.
4.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.
5 – Usage Notes
o To use the RMU Collect Optimizer_Statistics command for a
database, you must have the RMU$ANALYZE privilege in the root
file access control list (ACL) for the database or the OpenVMS
SYSPRV or BYPASS privilege.
o When you use the SQL ALTER DATABASE statement to set the
RDB$SYSTEM storage area to read-only access for your database,
the Oracle Rdb system tables in the RDB$SYSTEM storage area
are also set to read-only access. When the Oracle Rdb system
tables are set to read-only access:
o Automatic updates to table and index cardinality are
disabled.
o Manual changes made to the cardinalities to influence the
optimizer are not allowed.
o The I/O associated with the cardinality update is
eliminated.
o For indexes, the cardinality value is the number of unique
entries for an index that allows duplicates. If the index is
unique, Oracle Rdb stores zero for the cardinality, and uses
the table cardinality instead. For tables, the cardinality
value is the number of rows in the table. Oracle Rdb uses
the cardinality values of indexes and tables to influence
decisions made by the optimizer. If the actual cardinality
values of tables and indexes are different from the stored
cardinality values, the optimizer's performance can be
adversely affected.
o As Oracle RMU performs the collect operation, it displays
the maximum memory required to perform the operation. If
the maximum amount required is not available, Oracle RMU
makes adjustments to try to make use of the memory that is
available. However, if after making these adjustments, memory
is still insufficient, the collect operation skips the updates
for the table causing the problem and continues with the
operation. The skipped table is noted in the log file with the
message, "Unable to allocate memory for <table-name>; default
statistics values used."
To avoid this problem, use the OpenVMS System Generation
Utility (SYSGEN) to increase the VIRTUALPAGECNT parameter.
o If you prefer not to update optimizer statistics all at
once, you can divide the work into separate commands. Oracle
Corporation recommends that you collect Cardinality and
Storage statistics in one RMU Collect Optimizer_Statistics
command; and collect Workload statistics in a second command.
o You must decide if the improved performance provided by
enabling and maintaining the workload profile is worth the
cost. Generally speaking, it is worth the cost of maintaining
this table for a stable set of queries that are run on a
regular basis; it is not worth the cost of maintaining this
table when the majority of your queries are ad hoc queries,
each of which uses different access strategies.
For example, if the majority of queries that access the
EMPLOYEES table use the EMPLOYEE_ID as the selection criteria
and the queries are using the same access strategy, you might
want to maintain a workload profile for the EMPLOYEES table.
However, if some queries access the EMPLOYEES table through
the EMPLOYEE_ID, some through the LAST_NAME, and others
through the STATE, in an unpredictable manner, the queries
are using different access strategies for which you probably
do not want to maintain a workload profile.
o Index prefix cardinalities are cumulative values. For example,
suppose an index contains three segments and the first segment
has a cardinality of A; the second has a cardinality of B;
and the third has a cardinality of C. Then the index prefix
cardinality for the first segment is A; the index prefix
cardinality for the second segment is A concatenated with
B (A|B); and the index prefix cardinality for the third
segment is A concatenated with B concatenated with C (A|B|C).
Therefore, the prefix cardinality for last segment in an
index is always equal to the total cardinality for the index.
Likewise, if the index only contains one segment, the index
prefix cardinality is equal to the total cardinality for the
index. In these cases, because the index prefix cardinality
is the same as the total index cardinality, Oracle RMU does
not calculate an index prefix cardinality. Instead, Oracle
RMU stores a value of "0" for the index prefix cardinality
and the optimizer uses the value stored for the total index
cardinality.
o Cardinality statistics are automatically maintained by
Oracle Rdb. Physical storage and Workload statistics are only
collected when you issue an RMU Collect Optimizer_Statistics
command. To get information about the usage of Physical
storage and Workload statistics for a given query, define
the RDMS$DEBUG_FLAGS logical name to be "O". For example:
$ DEFINE RDMS$DEBUG_FLAGS "O"
When you execute a query, if workload and physical statistics
have been used in optimizing the query, you see a line such as
the following in the command output:
~O: Workload and Physical statistics used
o Detected asynchronous prefetch should be enabled to achieve
the best performance of this command. Beginning with Oracle
Rdb V7.0, by default, detected asynchronous prefetch is
enabled for databases created under Oracle Rdb V7.0 or
converted to V7.0. You can determine the setting for your
database by issuing the RMU Dump command with the Header
qualifier.
If detected asynchronous prefetch is disabled, and you do not
want to enable it for the database, you can enable it for your
Oracle RMU operations by defining the following logicals at
the process level:
$ DEFINE RDM$BIND_DAPF_ENABLED 1
$ DEFINE RDM$BIND_DAPF_DEPTH_BUF_CNT P1
P1 is a value between 10 and 20 percent of the user buffer
count.
o You can delete entries from the workload profile with the RMU
Delete Optimizer_Statistics command. See Delete_Optimizer_
Statistics for details.
o You can display entries from the workload profile with the
RMU Show Optimizer_Statistics command. See Show Optimizer_
Statistics for details.
o System Tables Used to Store Optimizer Statistics provides a
summary of the system tables in which statistics gathered by
the RMU Collect Optimizer_Statistics command are stored.
Table 7 System Tables Used to Store Optimizer Statistics
Statistic System Table Name Column Name
Table RDB$RELATIONS RDB$CARDINALITY
Cardinality
Table Row RDB$RELATIONS RDB$ROW_CLUSTER_FACTOR
Clustering
Factor
Column Group RDB$WORKLOAD RDB$DUPLICITY_FACTOR
Duplicity
Factor
Column Group RDB$WORKLOAD RDB$NULL_FACTOR
Null Factor
Index RDB$INDICES RDB$CARDINALITY
Cardinality
Index Prefix RDB$INDEX_ RDB$CARDINALITY
Cardinality SEGMENTS
Average RDB$INDICES RDB$INDEX_DEPTH
Index Depth
(B-Trees
only)
Index Key RDB$INDICES RDB$KEY_CLUSTER_FACTOR
Clustering
Factor
Index Data RDB$INDICES RDB$DATA_CLUSTER_FACTOR
Clustering
Factor
6 – Examples
Example 1
The following example collects cardinality statistics for the
EMPLOYEES and JOB_HISTORY tables and their associated indexes.
See the Usage Notes for an explanation for the value "0" for the
index prefix cardinality.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /STATISTICS=(CARDINALITY)/TABLES=(EMPLOYEES, JOB_HISTORY) -
_$ /INDEXES=(EMP_LAST_NAME,EMP_EMPLOYEE_ID, EMPLOYEES_HASH, -
_$ JH_EMPLOYEE_ID, JOB_HISTORY_HASH)/LOG
Start loading tables... at 3-JUL-1996 09:35:25.19
Done loading tables.... at 3-JUL-1996 09:35:25.91
Start loading indexes... at 3-JUL-1996 09:35:25.92
Done loading indexes.... at 3-JUL-1996 09:35:26.49
Start collecting btree index stats... at 3-JUL-1996 09:35:28.17
Done collecting btree index stats.... at 3-JUL-1996 09:35:28.23
Start collecting table & hash index stats... at 3-JUL-1996 09:35:28.23
Done collecting table & hash index stats.... at 3-JUL-1996 09:35:28.52
Start calculating stats... at 3-JUL-1996 09:35:28.76
Done calculating stats.... at 3-JUL-1996 09:35:28.76
Start writing stats... at 3-JUL-1996 09:35:30.16
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Cardinality : 100
Index name : EMP_LAST_NAME
Index Cardinality : 83
Segment Column Prefix cardinality
LAST_NAME 0
Index name : EMP_EMPLOYEE_ID
Index Cardinality : 100
Segment Column Prefix cardinality
EMPLOYEE_ID 0
Index name : EMPLOYEES_HASH
Index Cardinality : 100
----------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Cardinality : 274
Index name : JH_EMPLOYEE_ID
Index Cardinality : 100
Segment Column Prefix cardinality
EMPLOYEE_ID 0
Index name : JOB_HISTORY_HASH
Index Cardinality : 100
Done writing stats.... at 3-JUL-1996 09:35:30.83
Example 2
The following example collects storage statistics for the
EMPLOYEES and JOB_HISTORY TABLES and their associated indexes:
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel -
_$ /STATISTICS=(STORAGE)/TABLES=(EMPLOYEES, JOB_HISTORY) -
_$ /INDEXES=(EMP_LAST_NAME,EMP_EMPLOYEE_ID, EMPLOYEES_HASH, -
_$ JH_EMPLOYEE_ID, JOB_HISTORY_HASH)/LOG
Start loading tables... at 3-JUL-1996 10:28:49.39
Done loading tables.... at 3-JUL-1996 10:28:50.30
Start loading indexes... at 3-JUL-1996 10:28:50.30
Done loading indexes.... at 3-JUL-1996 10:28:51.03
Start collecting btree index stats... at 3-JUL-1996 10:28:53.27
Done collecting btree index stats.... at 3-JUL-1996 10:28:53.37
Start collecting table & hash index stats... at 3-JUL-1996 10:28:53.38
Done collecting table & hash index stats.... at 3-JUL-1996 10:28:53.80
Start calculating stats... at 3-JUL-1996 10:28:54.07
Done calculating stats.... at 3-JUL-1996 10:28:54.07
Start writing stats... at 3-JUL-1996 10:28:55.61
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Row clustering factor : 0.2550000
Index name : EMP_LAST_NAME
Average Depth : 2.0000000
Key clustering factor : 0.0481928
Data clustering factor : 1.1686747
Index name : EMP_EMPLOYEE_ID
Average Depth : 2.0000000
Key clustering factor : 0.0100000
Data clustering factor : 0.9500000
Index name : EMPLOYEES_HASH
Key clustering factor : 1.0000000
Data clustering factor : 1.0000000
--------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Row clustering factor : 0.0930657
Index name : JH_EMPLOYEE_ID
Average Depth : 2.0000000
Key clustering factor : 0.0500000
Data clustering factor : 0.9500000
Index name : JOB_HISTORY_HASH
Key clustering factor : 1.0000000
Data clustering factor : 1.0000000
Done writing stats.... at 3-JUL-1996 10:28:56.41
Example 3
The following example enables workload collection with an SQL
ALTER DATABASE statement, executes frequently run queries to
generate a workload profile, collects workload statistics for
the EMPLOYEES and JOB_HISTORY tables (along with their associated
indexes), and then displays the statistics gathered.
The SQL natural left outer join causes the first and third
workload column groups to be created. The SQL DISTINCT clause
causes the second and fourth workload column groups to be
created.
$ ! Enable workload collection:
$ SQL
SQL> ALTER DATABASE FILENAME mf_personnel.rdb
cont> WORKLOAD COLLECTION IS ENABLED;
SQL> --
SQL> -- Execute frequently run SQL queries.
SQL> --
SQL> ATTACH 'FILENAME mf_personnel.rdb';
SQL> SELECT DISTINCT *
cont> FROM JOB_HISTORY NATURAL LEFT OUTER JOIN EMPLOYEES;
.
.
.
SQL> DISCONNECT DEFAULT;
SQL> -- Disable workload collection:
SQL> ALTER DATABASE FILENAME mf_personnel.rdb
cont> WORKLOAD COLLECTION IS DISABLED;
SQL> EXIT;
$
$ ! Direct Oracle RMU to collect statistics for the EMPLOYEES and
$ ! JOB_HISTORY tables.
$ !
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /TABLE=(EMPLOYEES, JOB_HISTORY)/STATISTICS=(WORKLOAD)/LOG
Start loading tables... at 3-JUL-1996 10:40:00.22
Done loading tables.... at 3-JUL-1996 10:40:00.90
Start collecting workload stats... at 3-JUL-1996 10:40:03.43
Maximum memory required (bytes) = 6810
Done collecting workload stats.... at 3-JUL-1996 10:40:05.03
Start calculating stats... at 3-JUL-1996 10:40:05.32
Done calculating stats.... at 3-JUL-1996 10:40:05.32
Start writing stats... at 3-JUL-1996 10:40:06.91
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Workload Column group : EMPLOYEE_ID
Duplicity factor : 1.0000000
Null factor : 0.0000000
Workload Column group : LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
ADDRESS_DATA_1, ADDRESS_DATA_2, CITY, STATE, POSTAL_CODE, SEX,
BIRTHDAY, STATUS_CODE
Duplicity factor : 1.5625000
Null factor : 0.3600000
----------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Workload Column group : EMPLOYEE_ID
Duplicity factor : 2.7040000
Null factor : 0.0000000
Workload Column group : EMPLOYEE_ID, JOB_CODE, JOB_START,
JOB_END, DEPARTMENT_CODE, SUPERVISOR_ID
Duplicity factor : 1.5420582
Null factor : 0.3649635
Done writing stats.... at 3-JUL-1996 10:40:07.46
Example 4
The following example collects all statistics (cardinality,
workload, and storage) for all tables and indexes in the database
except system relations. Output is written to the file stats_
nosys.log.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /LOG=stats_nosys.log
Example 5
The following example collects all statistics (cardinality,
workload, and storage) for all tables, indexes, and system
relations. Output is written to the file stats_all.log.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb/SYSTEM_RELATIONS -
_$ /LOG=stats_all.log
Example 6
In the following example the Employees and Departments tables are
excluded from statistics collection.
$ RMU/COLLECT OPTIMIZER_STATISTICS MF_PERSONNEL /LOG -
_$ /EXCLUDE_TABLES=(EMPLOYEES,DEPARTMENTS)