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***