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