Generates a formatted display of statistical information that
describes the index structures for the database.
1 – Description
The RMU Analyze Indexes command provides a maintenance tool for
analyzing index structures and generates a formatted display
of this statistical information. Information is displayed
selectively for storage areas and logical areas, or for a range
of pages in a storage area. You can use the RMU Analyze Indexes
command to analyze the structures of both sorted (including
ranked sorted) and hashed indexes. The following shows sample
output from the RMU Analyze Index command:
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID_RANKED
----------------------------------------------------------------------------
Indices for database - RDBVMS_DISK1:[DB]MF_PERSONNEL.RDB;
----------------------------------------------------------------------------
Index JH_EMPLOYEE_ID_RANKED for relation JOB_HISTORY duplicates allowed
Max Level: 3, Nodes: 34, Used/Avail: 8693/13532 (64%), Keys: 133, Records: 0
Duplicate nodes:0, Used/Avail: 0/0 (0%), Keys: 100, Maps: 100, Records:4113
Total Comp/Uncomp IKEY Size: 600/798, Compression Ratio: .75
----------------------------------------------------------------------------
Data included in the statistics display includes the following
information:
o The first line of output identifies the database in which the
analyzed index resides.
o The second line of output:
- Specifies if the index is a hashed index. In the example,
the index is not hashed, so the term hashed does not
appear.
- The index name
- Whether or not duplicates are allowed.
o Third line of output:
- Max Level
The maximum number of levels in the index.
- Nodes
The total number of nodes in the index.
- Used/Avail (%)
The number of bytes used by the index/the number of bytes
available. (The percentage of space used by the index.)
- Keys
The sum of the dbkeys that point directly to data records
plus those that point to duplicate nodes.
- Records
The number of data records to which the Keys (in the
previous list item) point directly.
o The fourth line of output:
- Duplicate nodes
For hashed and nonranked sorted indexes, this is the number
of duplicate nodes in the index. For a ranked sorted index,
this is the number of overflow nodes. With ranked sorted
indexes, Oracle Rdb compresses duplicates using a byte-
aligned bitmap compression. It compresses the list of
dbkeys that point to duplicates and stores that list in
the index key node. Oracle Rdb creates overflow nodes when
the compressed list of duplicates does not fit in one index
key node. This overflow node contains a bitmap compressed
list of dbkeys and pointers to the next overflow node.
Therefore, for ranked sorted indexes, the duplicate nodes
count (overflow nodes) can be zero (0) if the compressed
list of dbkeys that point to duplicates fits into one node.
- Used/Avail (%)
The number of bytes used by duplicate nodes/number of bytes
available in the duplicate nodes. (The percentage of space
used within the duplicate nodes of the index.) This value
can be zero (0) for a ranked sorted index if the number of
duplicate nodes is zero.
- Keys
The total number of dbkeys that point to a duplicate node
or that point to the beginning of a duplicate node chain in
the index.
- Maps (appears only if the index is a ranked sorted index)
The number of duplicate key data record bit maps used by
ranked sorted indexes to represent the duplicate index key
data record dbkeys.
- Records
The total number of data records pointed to by duplicate
nodes. If the index is a ranked sorted index, Records
refers to the number of data records pointed to by
duplicate bit maps.
o The fifth line of output (appears only if the index is
compressed):
- Total Comp/Uncomp IKEY Size
The total byte count of the compressed leaf index keys
(level 1 nodes only)/the total byte count that would be
consumed if the index were not compressed
- Compression ratio.
The calculated ratio of Total Comp/Uncomp. A compression
ratio greater than 1.0 indicates that the compressed index
keys occupy more space than the uncompressed index keys.
For more information on RMU Analyze Indexes and the display
of index keys, refer to the Oracle Rdb7 Guide to Database
Performance and Tuning.
2 – Format
(B)0[mRMU/Analyze/Indexes root-file-spec [index-name[,...]]
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/[No]Binary_Output[=file-option-list] x /Nobinary_Output
/Exclude = Metadata x All index data displayed
/Option = {Normal | Full | Debug} x /Option=Normal
/Output = file-name x /Output=SYS$OUTPUT
/Transaction_Type=option x /Transaction_Type=Automatic
3 – Parameters
3.1 – root-file-spec
The file specification for the database root file for which
you want information. The default file extension is .rdb. This
parameter is required.
3.2 – index-name
index-name[,...]
The name of the index for which you want information. The default
is all enabled indexes. If you want information about a disabled
index, you must specify it by name. This parameter is optional.
An indirect file reference can be used. See the Indirect-Command-
Files help entry for more information.
The wildcard characters "%" and "*" can be used in the index
name specification. The following examples demonstrate various
combinations of use of the wildcard characters.
$ RMU /ANALYZE /INDEX MF_PERSONNEL EMP*
$ RMU /ANALYZE /INDEX MF_PERSONNEL *LAST%NAME
$ RMU /ANALYZE /INDEX MF_PERSONNEL EMP%LAST%NAME
$ RMU /ANALYZE /INDEX MF_PERSONNEL *HASH, *LAST*
4 – Command Qualifiers
4.1 – Binary Output
Binary_output=file-option-list
Nobinary_Output
Specifying the Binary_Output qualifier allows you to store
the summary results in a binary file, and to create a record
definition file that is compatible with the data dictionary for
the binary output file. The binary output can be loaded into
an Oracle Rdb database by using the RMU Load command with the
Record_Definition qualifier for use by a user-written management
application or procedure. The binary output can also be used
directly by the user-written application or procedure.
The valid file options are:
o File=file-spec
The File option causes the RMU Analyze Indexes command data to
be stored in an RMS file that contains a fixed-length binary
record for each index analyzed.
The default file extension for the binary output file is .unl.
The following command creates the binary output file analyze_
out.unl:
$ RMU/ANALYZE/INDEXES -
_$ /BINARY_OUTPUT=FILE=ANALYZE_OUT MF_PERSONNEL.RDB
o Record_Definition=file-spec
The Record_Definition option causes the RMU Analyze Indexes
command data record definition to be stored in an RMS file.
The output file contains the record definition in a subset of
the data dictionary command format. The default file extension
for the record definition output file is .rrd. Refer to the
rrd_file_syntax help topic for a description of the .rrd
files. The following command creates the output file analyze_
out.rrd:
$ RMU/ANALYZE/INDEXES -
_$ /BINARY_OUTPUT=RECORD_DEFINITION=ANALYZE_OUT MF_PERSONNEL.RDB
You can specify both file options in one command by separating
them with a comma and enclosing them within parentheses, as
follows:
$ RMU/ANALYZE/INDEXES/BINARY_OUTPUT= -
_$ (FILE=ANALYZE_OUT,RECORD_DEFINITION=ANALYZE_OUT) -
_$ MF_PERSONNEL.RDB
If you specify the Binary_Output qualifier, you must specify
at least one of the options. The default is the Nobinary_Output
qualifier, which does not create an output file.
4.2 – Exclude
Exclude=Metadata
Excludes information from the RMU Analyze Indexes command output.
When you specify the Exclude=Metadata qualifier, information on
the Oracle Rdb indexes (for example, the RDB$NDX_REL_NAME_NDX and
RDB$COLLATIONS_NDX indexes) is excluded from the RMU Analyze
Indexes command output. When you do not specify the Exclude
qualifier, data is provided for all indexes in the database.
Data is accumulated for the indexes excluded with the Exclude
qualifier, but the data is excluded from the RMU Analyze Indexes
command output.
You cannot specify the Exclude qualifier and one or more index
names in the same RMU Analyze Indexes command.
4.3 – Option
Option=type
Specifies the type of information and the level of detail the
analysis will include. Three types of output are available:
o Normal
Output includes only summary information. The Normal option is
the default.
o Full
Output includes histograms and summary information. This
option displays a summary line for each sorted index level.
o Debug
Output includes internal information about the data,
histograms, and summary information. Note the following when
using this option to analyze compressed index keys:
- The key lengths are from the compressed index keys.
- The hexadecimal output for the keys is that of the
uncompressed index keys.
- The output includes summary statistics about the compressed
index keys.
In general, use the Debug option for diagnostic support
purposes. You can also use the Debug option to extract data
and perform an independent analysis.
4.4 – Output
Output=file-name
Specifies the name of the file where output will be sent. The
default is SYS$OUTPUT. The default output file extension is .lis,
if you specify a file name.
4.5 – Transaction Type
Transaction_Type=option
Allows you to specify the transaction mode for the transactions
used to perform the analyze operation. 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 for the
analyze operation. If any storage area in the database (including
those not accessed for the analyze operation) has snapshots
disabled, the transactions used for the analyze operation are
set to read/write mode. Otherwise, the transactions are set to
read-only mode.
The Transaction_Type=Read_Only qualifier specifies the
transactions used to perform the analyze operation 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 that
are analyzed. Otherwise, you receive an error and the analyze
operation fails.
You might select this option if not all storage areas have
snapshots enabled and you are analyzing objects that are stored
only in storage areas with snapshots enabled. In this case, using
the Transaction_Type=Read_Only qualifier allows you to perform
the analyze operation and impose minimal locking on other users
of the database.
The Transaction_Type=Noread_Only qualifier specifies that
the transactions used to for the analyze operation 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 Analyze Indexes 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 the RMU Analyze Indexes command is issued for a closed
database, the command executes without other users being able
to attach to the database.
o The following RMU Analyze Indexes command produces an RMS
record definition file called index.rrd that can be read by
the RMU Load command and the data dictionary:
$ RMU/ANALYZE/INDEX/BINARY_OUTPUT=RECORD_DEFINITION=INDEX.RRD -
_$ MF_PERSONNEL
$!
$! Display the index.rrd file created by the previous command:
$ TYPE INDEX.RRD
DEFINE FIELD RMU$DATE DATATYPE IS DATE.
DEFINE FIELD RMU$INDEX_NAME DATATYPE IS TEXT SIZE IS 32.
DEFINE FIELD RMU$RELATION_NAME DATATYPE IS TEXT SIZE IS 32.
DEFINE FIELD RMU$LEVEL DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$FLAGS DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$USED DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$AVAILABLE DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_USED DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_AVAILABLE DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$KEY_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DATA_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_KEY_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_DATA_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_COMP_IKEY_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_IKEY_COUNT DATATYPE IS F_FLOATING.
DEFINE RECORD RMU$ANALYZE_INDEX.
o The following list describes each of the fields in the
index.rrd record definition:
- RMU$DATE
Contains the date that the analyze operation was done
- RMU$INDEX_NAME
Contains the name of the index that was analyzed
- RMU$RELATION_NAME
Contains the name of the table for which the index is
defined
- RMU$LEVEL
Contains the maximum number of index levels
- RMU$FLAGS
The eight possible values in this field have the following
meanings:
* 0-Index is sorted and not unique. A full report is not
generated.
* 1-Index is sorted and unique. A full report is not
generated.
* 2-Index is hashed and not unique. A full report is not
generated.
* 3-Index is hashed and unique. A full report is not
generated.
* 4-Index is sorted and not unique. A full report is
generated.
* 5- Index is sorted and unique. A full report is
generated.
* 6- Index is hashed and not unique. A full report is
generated.
* 7-Index is hashed and unique. A full report is
generated.
* 8-Index is sorted ranked and not unique. A full report
is not generated.
* 9-Index is sorted ranked and unique. A full report is
not generated.
* 12-Index is sorted ranked and not unique. A full report
is generated.
* 13-Index is sorted ranked and unique. A full report is
generated.
The RMU Analyze Indexes command uses the RMU$FLAGS bits
shown in RMU$FLAGS Bits Used by the RMU Analyze Indexes
Command for describing specific index information.
Table 2 RMU$FLAGS Bits Used by the RMU Analyze Indexes Command
Bit Offset Meaning
0 Unique index if true
1 Hashed index if true
2 Full report record if true
3 Ranked index if true
When RMU$FLAGS has bit 2 set it means that a full report is
generated. A full report has records for each level of the
index.
- RMU$COUNT
Contains the number of index nodes
- RMU$USED
Contains the amount of available space that is used
- RMU$AVAILABLE
Contains the amount of space available in the index records
initially
- RMU$DUPLICATE_COUNT
Contains the number of duplicate records
- RMU$DUPLICATE_USED
Contains the amount of available space used in the
duplicate records
- RMU$DUPLICATE_AVAILABLE
Contains the amount of space available in the duplicate
records initially
- RMU$KEY_COUNT
Contains the number of keys
- RMU$DATA_COUNT
Contains the number of records
- RMU$DUPLICATE_KEY_COUNT
Contains the number of duplicate keys
- RMU$DUPLICATE_DATA_COUNT
Contains the number of duplicate records
- RMU$TOTAL_COMP_IKEY_COUNT
Contains the number of compressed index key bytes
- RMU$TOTAL_IKEY_COUNT
Contains the number of bytes that would be used by index
keys, had they not been compressed
6 – Examples
Example 1
The following command analyzes the JH_EMPLOYEE_ID and SH_
EMPLOYEE_ID indexes in the mf_personnel database:
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID,SH_EMPLOYEE_ID -
_$ /OUTPUT=EMP_ID_INDEX.LIS
Example 2
The following commands demonstrate the differences you see
when you analyze a nonranked sorted index and a ranked sorted
index. Note the differences in the values for the Duplicate
nodes. The nonranked sorted index displays 80 duplicate nodes.
The ranked sorted index (before more duplicates are added)
displays 0 duplicate nodes for the same data. After hundreds
of more duplicates are added, the ranked sorted index shows only
3 duplicate nodes. The differences you see are because of the
different way duplicate records are stored for nonranked sorted
indexes and ranked sorted indexes. See the Description help entry
under this command for details on these differences.
$ ! Analyze a nonranked sorted index:
$ !
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID
----------------------------------------------------------------------------
Indices for database - USER1:[DB]MF_PERSONNEL.RDB;1
----------------------------------------------------------------------------
Index JH_EMPLOYEE_ID for relation JOB_HISTORY duplicates allowed
Max Level: 2, Nodes: 4, Used/Avail: 768/1592 (48%), Keys: 103, Records: 20
Duplicate nodes: 80, Used/Avail: 2032/4696 (43%), Keys: 80, Records: 254
----------------------------------------------------------------------------
$ ! Analyze a ranked sorted index defined on the same column as the
$ ! nonranked sorted index:
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID_RANKED
----------------------------------------------------------------------------
Indices for database - USER1:[DB]MF_PERSONNEL.RDB;1
----------------------------------------------------------------------------
Index JH_EMPLOYEE_ID_RANKED for relation JOB_HISTORY duplicates allowed
Max Level: 2, Nodes: 11, Used/Avail: 2318/4378 (53%), Keys: 110, Records: 20
Duplicate nodes: 0, Used/Avail: 0/0 (0%), Keys: 80, Maps: 80, Records: 254
----------------------------------------------------------------------------
$ !
$ ! Insert many duplicates and analyze the ranked sorted index again:
$ !
$ RMU/ANALYZE/INDEXES MF_PERSONNEL.RDB JH_EMPLOYEE_ID_RANKED
----------------------------------------------------------------------------
Indices for database - USER1:[DB]MF_PERSONNEL.RDB;1
----------------------------------------------------------------------------
Index JH_EMPLOYEE_ID_RANKED for relation JOB_HISTORY duplicates allowed
Max Level: 2, Nodes: 13, Used/Avail: 2705/5174 (52%), Keys: 112, Records: 20
Duplicate nodes:3, Used/Avail:850/1194 (71%), Keys:80, Maps: 83, Records:2964
----------------------------------------------------------------------------