Displays the current values of the optimizer statistics for
tables and indexes as stored in the RDB$INDICES, RDB$RELATIONS,
and the RDB$WORKLOAD system table.
1 – Format
(B)0[mRMU/Show Optimizer_Statistics root-file
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/[No]Full x /Nofull
/[No]Indexes[=(index-list)] x /Index
/[No]Log[=file-name] x /Log
/Statistics[=(options)] x /Statistics
/[No]System_Relations x /Nosystem_Relations
/[No]Tables[=(table-list)] x /Tables
/[No]Threshold[=options] x /Nothreshold
2 – Parameters
2.1 – root-file-spec
root-file-spec
Specifies the database for which optimizer statistics are to be
displayed. The default file type is .rdb.
3 – Command Qualifiers
3.1 – Full
Full
Nofull
This qualifier can only be used if table, index, or index prefix
cardinality statistics are being displayed. If this qualifier is
specified, the following cardinality information is displayed:
o Actual cardinality
Displays the current table, index, or index prefix cardinality
value.
o Stored cardinality
Displays the table, index, or index prefix cardinality value
stored in the system relations.
o Difference between the stored and actual cardinality values
This value is negative if the stored cardinality is less than
the actual cardinality.
o Percentage cardinality difference from the actual value
This value is calculated by dividing the difference between
the stored and actual cardinality values by the actual
cardinality value. It is negative if the stored cardinality
is less than the actual cardinality.
The default value is Nofull.
3.2 – Indexes
Indexes[=(index-list)]
Noindex
Specifies the index or indexes for which statistics are to be
displayed. If you do not specify an index-list, statistics for
all indexes defined for the tables specified with the Tables
qualifier are displayed. If you specify an index-list, statistics
are displayed only for the named indexes. If you specify the
Noindex qualifier, statistics are not displayed for any indexes.
The default is the Indexes qualifier without an index-list.
3.3 – Log
Log
Nolog
Log=file-name
Specifies whether the display of 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. The Nolog qualifier is valid
syntax, but is ignored by Oracle RMU. The default is the Log
qualifier.
3.4 – Statistics
Statistics
Statistics[=(options)]
Specifies the type of statistics you want to display 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 displayed for the items
specified.
If you specify the Statistics qualifier with an options list,
Oracle RMU displays the 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 parentheses.
The Statistics qualifier options are:
o Cardinality
Displays 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.
o Workload
Displays the Column Group, Duplicity Factor, and Null Factor
workload statistics for the tables specified with the Tables
and System_Relations qualifiers.
o Storage
Displays 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.
3.5 – System Relations
System_Relations
Nosystem_Relations
The System_Relations qualifier specifies that optimizer
statistics are to be displayed 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 displayed for system tables or their associated indexes.
Specify the Noindex qualifier if you do not want statistics
displayed for indexes defined on the system tables.
The default is the Nosystem_Relations qualifier.
3.6 – Tables
Tables
Tables=(table-list)
Notables
Specifies the table or tables for which optimizer statistics
are to be displayed. If you specify a table-list, optimizer
statistics for those tables and their associated indexes are
displayed.
If you do not specify the Tables qualifier, or if you specify
the Tables qualifier but do not provide a table-list, optimizer
statistics for all tables and their associated indexes in the
database are displayed.
If you specify the Notables qualifier, optimizer statistics for
tables are not displayed.
Specify the Noindex qualifier if you do not want statistics
displayed for indexes defined on the specified tables.
The Tables qualifier is the default.
3.7 – Threshold
Threshold=options
Nothreshold
The Threshold qualifier can only be used in conjunction with
the Full qualifier. If this qualifier is used, an additional
Threshold column is added to the display. You can specify the
following options with the Threshold qualifier:
o Percent=n
The value for Percent=n can be an integer value from 0 to 99.
The default value for n is 0. If Percent=n is not specified
or if a percent value of 0 is specified, any percentage
difference from the actual cardinality value is flagged as
"*over*" in the output column. If a percent value of 1 to
99 is specified, any percentage difference from the actual
cardinality value that is greater than the percent value
specified is flagged as "*over*" in the output column. In the
report, the Threshold column displays those cardinality values
in which the percent difference exceeds the specified value.
If the threshold is not exceeded, the column is blank. If the
threshold is exceeded, the column shows the string "*over*".
o Log={All|Over_Threshold}
If Log is not specified or if Log=All is specified, all
cardinality values are displayed. If Log=Over_Threshold is
specified, only cardinality values that exceed the threshold
percentage are flagged as "*over*" in the output column.
4 – Usage Notes
o To use the RMU Show Optimizer_Statistics command for a
database, you must have the RMU$ANALYZE or RMU$SHOW privilege
in the root file access control list (ACL) for the database or
the OpenVMS SYSPRV or BYPASS privilege.
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 will see a line
such as the following in the command output:
~O: Workload and Physical statistics used
o Use the RMU Show Optimizer Statistics command with the
Statistics=Cardinality/Full/Threshold=n qualifier to identify
index prefix cardinality drift. This command identifies
indexes that need to be repaired. Use the RMU Collect
Optimizer_Statistics command to repair the stored index prefix
cardinality values.
5 – Examples
Example 1
The following command displays all optimizer statistics
previously collected for the EMPLOYEES table. See Collect_
Optimizer_Statistics for an example that demonstrates how to
collect optimizer statistics.
$ RMU/SHOW OPTIMIZER_STATISTICS MF_PERSONNEL.RDB /TABLE=(EMPLOYEES)
-------------------------------------------------------------------
Optimizer Statistics for table : EMPLOYEES
Cardinality : 100
Row clustering factor : 0.5100000
Workload Column group : EMPLOYEE_ID
Duplicity factor : 1.0000000
Null factor : 0.0000000
First created time : 3-JUL-1996 10:37:36.43
Last collected time : 3-JUL-1996 10:46:10.73
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
First created time : 3-JUL-1996 10:37:36.43
Last collected time : 3-JUL-1996 10:46:10.74
Index name : EMP_LAST_NAME
Index Cardinality : 83
Average Depth : 2.0000000
Key clustering factor : 0.0481928
Data clustering factor : 1.1686747
Segment Column Prefix cardinality
LAST_NAME 0
Index name : EMP_EMPLOYEE_ID
Index Cardinality : 0
Average Depth : 2.0000000
Key clustering factor : 0.0100000
Data clustering factor : 0.9500000
Segment Column Prefix cardinality
EMPLOYEE_ID 0
Index name : EMPLOYEES_HASH
Index Cardinality : 0
Key clustering factor : 1.0000000
Data clustering factor : 1.0000000
Example 2
The following command displays optimizer statistics for all the
tables defined in the database. Because the Noindex qualifier
is specified, no index statistics are displayed. Because the Log
qualifier is specified with a file specification, the values for
the optimizer statistics are written to the specified file.
$ RMU/SHOW OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /NOINDEX/LOG=NOINDEX-STAT.LOG
Example 3
The following example displays the output of a command when
the Full and Threshold qualifiers are used with the Cardinality
option. In the example, table XXX has three indexes. Index XXX_
IDX_FULL has index prefix cardinality collection enabled full
and the report shows no cardinality drift for this index. Index
XXX_IDX_APPROX has index prefix cardinality collection enabled,
and cardinality drift is evident. For the first segment of the
index (column C1), the stored cardinality is 20% lower than the
actual cardinality. Since the command specifies a threshold of
5%, the line is marked "*over*" in the Thresh column. There
is also cardinality drift for the second segment of the index
(column C2), index prefix (C1, C2). The third index XXX_IDX_
NONE has index prefix cardinality collection disabled. This is
indicated in the report rather than showing the index segments.
If the report were lengthy, you could write it to a disk file
and then locate the problem indexes by searching for the string
"*over*".
$ RMU/SHOW OPTIMIZER/STAT=CARD/FULL/THRESH=(percent=5,log=all) sample.rdb
Optimizer Statistics for table : XXX
(Cardinality: Diff=Stored-Actual, Percent=Diff/Actual, Thresh=Percent exceeded)
Table cardinality
Actual Stored Diff Percent Thresh
109586 109586 0 0 %
Index name : XXX_IDX_FULL
(Cardinality: Diff=Stored-Actual, Percent=Diff/Actual, Thresh=Percent exceeded)
Index cardinality
Actual Stored Diff Percent Thresh
109586 109586 0 0 %
Prefix cardinality
Actual Stored Diff Percent Thresh
Segment Column : C1
1425 1425 0 0 %
Segment Column : C2
31797 31797 0 0 %
Segment Column : C3
0 0 0 0 %
Index name : XXX_IDX_APPROX
(Cardinality: Diff=Stored-Actual, Percent=Diff/Actual, Thresh=Percent exceeded)
Index cardinality
Actual Stored Diff Percent Thresh
109586 109586 0 0 %
Prefix cardinality
Actual Stored Diff Percent Thresh
Segment Column : C1
1425 1140 -285 -20 % *over*
Segment Column : C2
31797 30526 -1271 -4 %
Segment Column : C3
0 0 0 0 %
Index name : XXX_IDX_NONE
(Cardinality: Diff=Stored-Actual, Percent=Diff/Actual, Thresh=Percent exceeded)
Index cardinality
Actual Stored Diff Percent Thresh
109586 109586 0 0 %
***Prefix cardinality collection is disabled***