Displays information about stored and actual cardinality values
for tables and indexes, database space utilization in the
database, index structures for the database, or the accessibility
through indexes of data records in the database.
1 – Database
Gathers and displays statistics on how the database uses storage,
logical area, or page space.
1.1 – Description
The RMU Analyze command provides a maintenance tool for database
administrators. It generates a formatted display of statistical
information that describes storage utilization in the database.
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 command to analyze the following:
o Space utilization for database pages
o Space utilization for storage areas
o Space utilization for logical areas
1.2 – Format
(B)0[mRMU/Analyze root-file-spec
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Areas[=storage-area-list] x /Areas
/[No]Binary_Output=file-option-list x /Nobinary_Output
/End=integer x /End=last-page
/Exclude=(options) x No logical areas excluded
/[No]Lareas[=logical-area-list] x /Lareas
/Option = {Normal | Full | Debug} x /Option=Normal
/Output=file-name x /Output=SYS$OUTPUT
/Start = integer x /Start=first-page
1.3 – Parameters
1.3.1 – root-file-spec
The file specification for the database root file to be analyzed.
The default file extension is .rdb.
1.4 – Command Qualifiers
1.4.1 – Areas
Areas[=storage-area-list]
Areas=*
Specifies the storage areas to be analyzed. You can specify each
storage area by name or by the area's ID number.
The default, the Areas qualifier, results in analysis of all
storage areas. You can also specify the Areas=* qualifier to
analyze all storage areas. If you specify more than one storage
area, separate the storage area names or ID numbers in the
storage-area-list parameter with a comma and enclose the list
in parentheses. If you omit the Areas qualifier, information for
all the storage areas is displayed.
You can use the Start and End qualifiers with the Areas
qualifier to analyze specific pages. If you use the Start and
End qualifiers when you specify more than one storage area in the
storage-area-list parameter, the same specified range of pages
are analyzed in each specified storage area.
The Areas qualifier can be used with an indirect command file.
See the Indirect-Command-Files help entry for more information.
1.4.2 – Binary Output
Binary_Output=file-option-list
Nobinary_Output
Allows you to direct the summary results to 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
file 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 Analyze command data to be stored
in an RMS file that contains a fixed-length binary record for
each storage area and logical area 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/BINARY_OUTPUT=FILE=ANALYZE_OUT MF_PERSONNEL.RDB
o Record_Definition=file-spec
The Record_Definition option causes the Analyze command
data record definition to be stored in an RMS file. The
output file contains the definition in a subset of the data
dictionary command format, a format very similar to RDO field
and relation definitions. The default file extension for the
record definition output file is .rrd. The following command
creates the output file analyze_out.rrd:
$ RMU/ANALYZE/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, for
example:
$ RMU/ANALYZE/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.
1.4.3 – End
End=integer
Specifies the ending page number for the analysis. The default is
the end of the storage area file.
1.4.4 – Exclude
Exclude=System_Records
Exclude=Metadata
Exclude=(System_Records, Metadata)
Excludes information from the RMU Analyze command output. You
can specify Exclude=System_Records or Exclude=Metadata, or both.
If you specify both options, enclose them within parentheses and
separate each option with a comma.
When you do not specify the Exclude qualifier, data is provided
for all the logical areas in the database.
The options are as follows:
o System_Records
Information on the RDB$SYSTEM_RECORDS logical areas is
excluded from the Analyze command output.
o Metadata
Information on all the Oracle Rdb logical areas (for example,
the RDB$SYSTEM_RECORDS and RDB$COLLATIONS_NDX logical areas)
is excluded from the RMU Analyze command output.
Data is accumulated for the logical areas excluded with the
Exclude qualifier, but the data is excluded from the Analyze
output.
You cannot use the Exclude qualifier and the Lareas qualifier in
the same RMU Analyze command.
1.4.5 – Lareas
Lareas[=logical-area-list]
Lareas=*
Nolareas
Specifies the logical areas to be analyzed. Each table in the
database is associated with a logical area name. The default, the
Lareas qualifier, results in analysis of all logical areas. You
can also specify the Lareas=* qualifier to analyze all logical
areas. If you specify more than one logical area name, separate
the logical area names in the logical-area-list with a comma and
enclose the list in parentheses.
The Lareas qualifier can be used with indirect command files. See
the Indirect-Command-Files help entry for more information.
1.4.6 – Option
Option=Normal
Option=Full
Option=Debug
Specifies the type of information and 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.
o Debug
Output includes internal information about the data, as well
as histograms and summary information. 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.
1.4.7 – Output
Output=file-name
Specifies the name of the file where output will be sent. The
default file extension is .lis. If you do not specify the Output
qualifier, the output is sent to SYS$OUTPUT.
1.4.8 – Start
Start=integer
Specifies the starting page number for the analysis. The default
is 1.
1.5 – Usage Notes
o To use the RMU Analyze command for a database, you must
have the RMU$ANALYZE privilege in the root file ACL for the
database or the OpenVMS SYSPRV or BYPASS privilege.
o When the RMU Analyze command is issued for a closed database,
the command executes without other users being able to attach
to the database.
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. 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 The following RMU Analyze command directs the results into a
record definition file called db.rrd. This file is compatible
with the syntax for creating new columns and tables in the
data dictionary.
$ RMU/ANALYZE/BINARY_OUTPUT=RECORD_DEFINITION=DB.RRD MF_PERSONNEL
$! Display the db.rrd file created by the previous command:
$ TYPE DB.RRD
DEFINE FIELD RMU$DATE DATATYPE IS DATE.
DEFINE FIELD RMU$AREA_NAME DATATYPE IS TEXT SIZE IS 32.
DEFINE FIELD RMU$STORAGE_AREA_ID DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$FLAGS DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$TOTAL_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$EXPANDED_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$FRAGMENTED_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$EXPANDED_FRAGMENT_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$FRAGMENTED_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$FRAGMENT_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$PAGE_LENGTH DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$MAX_PAGE_NUMBER DATATYPE IS SIGNED LONGWORD.
DEFINE FIELD RMU$FREE_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$OVERHEAD_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$AIP_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$ABM_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$SPAM_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$INDEX_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$BTREE_NODE_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$HASH_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATES_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$OVERFLOW_BYTES DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$LOGICAL_AREA_ID DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$RELATION_ID DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$RECORD_ALLOCATION_SIZE DATATYPE IS SIGNED WORD.
DEFINE FIELD RMU$TOTAL_SPACE DATATYPE IS F_FLOATING.
DEFINE RECORD RMU$ANALYZE_AREA.
.
.
.
o The following list describes each of the fields in the db.rrd
record definition:
- RMU$DATE
Contains the date that the Analyze operation was done
- RMU$AREA_NAME
Contains the name of the storage area that was analyzed
- RMU$STORAGE_AREA_ID
Contains the area ID of the storage area that was analyzed
- RMU$FLAGS
The three possible values in this field have the following
meanings:
* 0-Indicates that the record is a storage area record,
not a logical area record
* 1-Indicates that data compression is not enabled for the
logical area
* 3-Indicates that data compression is enabled for the
logical area
- RMU$TOTAL_BYTES
Contains the total size of the data stored in the logical
area
- RMU$EXPANDED_BYTES
Contains the total size of the stored data in the logical
area after decompression
- RMU$FRAGMENTED_BYTES
Contains the number of bytes in the stored fragments
- RMU$EXPANDED_FRAGMENT_BYTES
Contains the number of bytes in the stored fragments after
decompression
- RMU$TOTAL_COUNT
Contains the total number of records stored
- RMU$FRAGMENTED_COUNT
- Contains the number of fragmented records
- RMU$FRAGMENT_COUNT
Contains the number of stored fragments
- RMU$PAGE_LENGTH
Contains the length in bytes of a database page in the
storage area
- RMU$MAX_PAGE_NUMBER
Contains the page number of the last initialized page in
the storage area
- RMU$FREE_BYTES
Contains the number of free bytes in the storage area
- RMU$OVERHEAD_BYTES
Contains the number of bytes used for overhead in the
storage area
- RMU$AIP_COUNT
Contains the number of the area inventory pages (AIPs) in
the storage area
- RMU$ABM_COUNT
Contains the number of area bit map (ABM) pages in the
storage area
- RMU$SPAM_COUNT
Contains the number of space area management (SPAM) pages
in the storage area
- RMU$INDEX_COUNT
Contains the number of index records in the storage area
- RMU$BTREE_NODE_BYTES
Contains the number of bytes for sorted indexes in the
storage area
- RMU$HASH_BYTES
Contains the number of bytes for hashed indexes in the
storage area
- RMU$DUPLICATES_BYTES
Contains the number of bytes for duplicate key values for
sorted indexes in the storage area
- RMU$OVERFLOW_BYTES
Contains the number of bytes for hash bucket overflow
records in the storage area
- RMU$LOGICAL_AREA_ID
Contains the logical area ID of the logical area that was
analyzed
- RMU$RELATION_ID
Contains the record type of the row in the logical area
that was analyzed
- RMU$RECORD_ALLOCATION_SIZE
Contains the size of a row when the table was initially
defined
- RMU$TOTAL_SPACE
Contains the number of bytes available for storing user
data in the logical area (used space + free space +
overhead)
1.6 – Examples
Example 1
The following command analyzes the EMPIDS_LOW and EMP_INFO
storage areas in the mf_personnel database:
$ RMU/ANALYZE/AREAS=(EMPIDS_LOW,EMP_INFO)/OUTPUT=EMP.OUT -
_$ MF_PERSONNEL.RDB
Example 2
Both of the following commands analyze the DEPARTMENTS and
SALARY_HISTORY storage areas in the mf_personnel database:
$! Using storage area names to specify storage areas
$ RMU/ANALYZE/AREAS=(DEPARTMENTS,SALARY_HISTORY) MF_PERSONNEL.RDB -
$ /OUTPUT=DEP_SAL.OUT
$!
$! Using storage area ID numbers to specify storage areas
$ RMU/ANALYZE/AREAS=(2,9) MF_PERSONNEL.RDB /OUTPUT=DEP_SAL.OUT
2 – Cardinality
Generates a formatted display of the actual and stored
cardinality values for specified tables and indexes. Also, if
the stored cardinality values are different from the actual
cardinality values, the RMU Analyze Cardinality command allows
you to update the stored cardinality values.
NOTE
Beginning in Oracle Rdb Version 7.0, the RMU Analyze
Cardinality command has been deprecated and might be removed
in future versions of Oracle Rdb. The features available
through this command are now available through the RMU
Collect Optimizer_Statistics command and the RMU Show
Optimizer_Statistics command.
In addition, updating cardinality information for indexes
using the RMU Analyze Cardinality command may cause poor
performance because the prefix cardinality information is
not collected.
Therefore, Oracle Corporation recommends that you use the
RMU Collect Optimizer_Statistics and RMU Show Optimizer_
Statistics commands instead of the RMU Analyze Cardinality
command.
See Collect_Optimizer_Statistics and Show Optimizer_
Statistics for information on the RMU Collect Optimizer_
Statistics and the RMU Show Optimizer_Statistics commands.
2.1 – Description
The actual cardinality values for tables and indexes can be
different from the stored cardinality values in your database's
RDB$SYSTEM storage area if RDB$SYSTEM has been set to read-
only access. When rows are added to or deleted from tables and
indexes after the RDB$SYSTEM storage area has been set to read-
only access, the cardinality values for these tables and indexes
are not updated.
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.
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.
With the RMU Analyze Cardinality command, you can:
o Display the stored and actual cardinality values for the
specified tables and indexes.
o Update the stored cardinality value for a specified table
or index with either the actual value or an alternative
value of your own choosing. Oracle Corporation recommends
that you update the stored cardinality value with the actual
cardinality value. Specifying a value other than the actual
cardinality value can result in poor database performance.
2.2 – Format
(B)0[mRMU Analyze/Cardinality root-file-spec [table-or-index-name[,...]]
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/[No]Confirm x /Noconfirm
/Output = file-name x /Output = SYS$OUTPUT
/Transaction_Type=option x /Transaction_Type=Automatic
/[No]Update x /Noupdate
2.3 – Parameters
2.3.1 – root-file-spec
The name of the database root file for which you want
information. The default file extension is .rdb. This parameter
is required.
2.3.2 – table-or-index-name
table-or-index-name[,...]
The name of the table or index for which you want information
about cardinality. The default is all tables and all enabled
indexes. If you want information about a disabled index, you must
specify it by name.
If you do not accept the default and instead specify a table
name, the RMU Analyze Cardinality command and any qualifiers
you specify will affect only the named table; the command will
not result in a display or update (if the Update qualifier is
specified) of the indexes associated with the table.
This parameter is optional. An indirect file reference can
be used. See the Indirect-Command-Files help entry for more
information.
2.4 – Command Qualifiers
2.4.1 – Confirm
Confirm
Noconfirm
Specify the Confirm qualifier with the Update qualifier to
gain more control over the update function. When you specify
the Confirm qualifier, you are asked whether the update should
be performed for each selected table or index whose stored
cardinality value is different from its actual cardinality value.
You can respond with YES, NO, QUIT, or an alternative value for
the stored cardinality.
Specifying YES means that you want to update the stored
cardinality with the actual cardinality value. Specifying NO
means that you do not want to update the stored cardinality
value. Specifying QUIT aborts the RMU Analyze Cardinality
command, rolls back any changes you made to stored cardinalities,
and returns you to the operating system prompt. Specifying an
alternative value updates the stored cardinality value with the
alternative value.
When you specify the Noconfirm qualifier, you are not given the
option of updating stored cardinality values with an alternative
value of your own choosing. Instead, the stored cardinality
values that differ from the actual cardinality values are
automatically updated with the actual cardinality values.
The default is the Noconfirm qualifier.
The Confirm and Noconfirm qualifiers are meaningless and are
ignored if they are specified without the Update qualifier.
2.4.2 – Output
Output=file-name
Specifies the name of the file where output will be sent. The
default is SYS$OUTPUT. The default output file type is .lis, if
you specify a file name.
2.4.3 – 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 specify 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.
2.4.4 – Update
Update
Noupdate
Specify the Update qualifier to update the stored cardinality
values of tables and indexes. You can perform an update only when
the stored cardinality values differ from the actual cardinality
values. When updating cardinality values, Oracle Corporation
recommends that you update the stored cardinality values with
the actual cardinality values, not with an alternative value
of your own choosing. Specifying a value other than the actual
cardinality value can result in poor database performance. The
default is the Noupdate qualifier.
Using the Update qualifier allows you to update the stored
cardinality values of the specified tables and indexes even when
the RDB$SYSTEM storage area is designated for read-only access.
If you have set the RDB$SYSTEM storage area to read-only access,
Oracle RMU sets it to read/write during execution of the RMU
Analyze Cardinality command with the Update qualifier. Oracle RMU
resets the area to read-only when the operation completes.
If you are updating the stored cardinality for a table or index,
and a system failure occurs before the RDB$SYSTEM storage area is
changed back to read-only access, use the SQL ALTER DATABASE
statement to manually change the database back to read-only
access.
However, note that if you have set the area to read-only, the
update operation specified with the Update qualifier commences
only if the database is off line or the database is quiescent.
If you specify a table name parameter with an RMU Analyze
Cardinality command that includes the Update qualifier, the
associated indexes are not updated; you must specify each table
and index you want to be updated or accept the default (by not
specifying any table or index names) and have all items updated.
Oracle Corporation recommends that you use the Update qualifier
during offline operations or during a period of low update
activity. If you update a cardinality while it is changing
(as a result of current database activity), the end result is
unpredictable.
Specify the Noupdate qualifier when you want to display the
stored and actual cardinality values only for the specified
tables and indexes.
2.5 – Usage Notes
o To use the RMU Analyze Cardinality command for a database, you
must have the RMU$ANALYZE privilege in the root file ACL for
the database or the OpenVMS SYSPRV or BYPASS privilege.
o You must have the SQL ALTER privilege for the database to
update a read-only RDB$SYSTEM storage area.
o If you specify a name for the table-or-index-name parameter
that is both an index name and a table name, the RMU Analyze
Cardinality command performs the requested operation for both
the table and index.
o Although you can alter the cardinality of a unique index
using the RMU Analyze Cardinality command, it has no effect.
(A unique index has only unique keys and does not have any
duplicate keys.) Because the cardinality of a unique index and
the table it indexes are the same, Oracle Rdb uses the table
cardinality value when performing operations that involve
the cardinality of a unique index. Oracle Rdb does not use
the cardinality value stored for a unique index, nor does it
attempt to update this value as rows are stored or deleted.
o When the RMU Analyze Cardinality command is issued for a
closed database, the command executes without other users
being able to attach to the database.
2.6 – Examples
Example 1
The following command provides information on the cardinality for
all indexes and tables in the sample mf_personnel database:
$ RMU/ANALYZE/CARDINALITY/NOUPDATE MF_PERSONNEL.RDB /OUTPUT=CARD.LIS
Example 2
The following command provides information on the cardinality for
the EMPLOYEES table in the mf_personnel database:
$ RMU/ANALYZE/CARDINALITY/NOUPDATE MF_PERSONNEL.RDB EMPLOYEES -
_$ /OUTPUT=EMP.LIS
3 – Indexes
Generates a formatted display of statistical information that
describes the index structures for the database.
3.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.
3.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.3 – Parameters
3.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.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*
3.4 – Command Qualifiers
3.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.
3.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.
3.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.
3.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.
3.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.
3.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
3.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
----------------------------------------------------------------------------
4 – Placement
Generates a formatted display of statistical information
describing the row placement relative to the index structures
for the database.
4.1 – Description
The RMU Analyze Placement command provides a maintenance tool
for analyzing row placement relative to index structures and
generates a formatted display of this statistical information.
Information is displayed selectively for any specified storage
area.
You can use the RMU Analyze Placement command to determine:
o The maximum and average path length to a data record. (The
maximum and average number of records touched to reach a data
record.)
o The estimated maximum I/O path length to a data record.
o The estimated minimum I/O path length to a data record.
o The frequency distributions for the database key (dbkey)
path lengths, maximum I/O path lengths, and minimum I/O path
lengths for specified indexes.
o The distribution of data records on data pages in a storage
area by logical area identifier (ID) and dbkey, the number
of dbkeys needed to reach each data record, the maximum and
minimum I/O path lengths needed to reach the data record, and
the specific dbkey for the data record.
4.2 – Format
(B)0[mRMU/Analyze/Placement root-file-spec [index-name[,...]]
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Areas[=storage-area-list] x /Areas
/[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
4.3 – Parameters
4.3.1 – root-file-spec
The file specification for the database root file to be analyzed.
The default file extension is .rdb.
4.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.
4.4 – Command Qualifiers
4.4.1 – Areas
Areas[=storage-area-list]
Areas=*
Specifies the storage areas to be analyzed. You can specify each
storage area by name or by the area's ID number.
If you are interested in the placement information for a
particular index, specify the area where the data resides, not
where the index resides. For example, if you are interested in
the placement information for the SH_EMPLOYEE_ID index of the
mf_personnel database, you should specify SALARY_HISTORY as the
storage area (which is where the data resides), not RDB$SYSTEM
(which is where the index resides).
If you do not specify the Areas qualifier, or if you specify
the Areas qualifier but do not provide a storage-area-list,
information for all the storage areas is displayed.
If you specify more than one storage area, separate the storage
area names or ID numbers in the storage-area-list with a comma
and enclose the list within parentheses.
If you specify more than one storage area with the Areas
qualifier, the analysis Oracle RMU provides is a summary for
all the specified areas. The analysis is not broken out into
separate sections for each specified storage area. To get index
information for a specific storage area, issue the RMU Analyze
Placement command, specifying only that area with the Areas
qualifier.
The Areas qualifier can be used with an indirect file reference.
See the Indirect-Command-Files help entry for more information.
The Areas qualifier (without a storage-area-list) is the default.
4.4.2 – 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 file can be loaded
into an Oracle Rdb database by using the RMU Load command with
the Record_Definition qualifier that can then be used 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 Placement 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/PLACEMENT -
_$ /BINARY_OUTPUT=FILE=ANALYZE_OUT MF_PERSONNEL.RDB
o Record_Definition=file-spec
The Record_Definition option causes the RMU Analyze Placement
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 .rrd files.
The following command creates the output file analyze_out.rrd:
$ RMU/ANALYZE/PLACEMENT -
_$ /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/PLACEMENT/BINARY_OUTPUT= -
_$ (FILE=ANALYZE_OUT,RECORD_DEFINITION=ANALYZE_OUT) -
_$ MF_PERSONNEL.RDB
The default is the Nobinary_Output qualifier, which does not
create an output file.
4.4.3 – Exclude
Exclude=Metadata
Excludes information from the RMU Analyze Placement command data.
When you specify the Exclude=Metadata qualifier, information on
all the Oracle Rdb indexes (for example, the RDB$NDX_REL_NAME_NDX
and RDB$COLLATIONS_NDX indexes) is excluded from the RMU Analyze
Placement 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
Placement command output.
You cannot specify the Exclude qualifier and one or more index
names in the same RMU Analyze Placement command.
4.4.4 – Option
Option=type
Specifies the type of information and level of detail the
analysis will include. Three types of output are available:
o Normal
Output includes only summary information. Normal is the
default.
o Full
Output includes histograms and summary information.
o Debug
Output includes internal information about the data,
histograms, and summary information. Output also displays
uncompressed index keys from compressed indexes. The
hexadecimal output is that of the uncompressed index key.
However, the lengths shown are of the compressed index key.
For more information on RMU Analyze Placement and the display
of index keys, refer to the Oracle Rdb7 Guide to Database
Performance and Tuning.
4.4.5 – Output
Output=file-name
Specifies the name of the file where output will be sent. The
default file type is .lis. If you do not specify the Output
qualifier, the default output is SYS$OUTPUT.
4.4.6 – 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 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.
4.5 – Usage Notes
o To use the RMU Analyze Placement command for a database, you
must have the RMU$ANALYZE privilege in the root file ACL for
the database or the OpenVMS SYSPRV or BYPASS privilege.
o When the RMU Analyze Placement 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 Placement command directs
the results into an RMS record definition file called
placement.rrd that is compatible with the data dictionary:
$ RMU/ANALYZE/PLACEMENT/BINARY_OUTPUT=RECORD_DEFINITION=PLACEMENT.RRD -
_$ MF_PERSONNEL
$!
$! Display the placement.rrd file created by the previous command:
$ TYPE PLACEMENT.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$DUPLICATE_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$KEY_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_KEY_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DATA_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$DUPLICATE_DATA_COUNT DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_KEY_PATH DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_PAGE_PATH DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$TOTAL_BUFFER_PATH DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$MAX_KEY_PATH DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$MAX_PAGE_PATH DATATYPE IS F_FLOATING.
DEFINE FIELD RMU$MIN_BUF_PATH DATATYPE IS F_FLOATING.
DEFINE RECORD RMU$ANALYZE_PLACEMENT.
o The following list describes each of the fields in the
placement.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 six possible values in this field have the following
meanings:
* 0-Index is a sorted and not unique index
* 1-Index is sorted and unique
* 2-Index is hashed and not unique
* 3-Index is hashed and unique
* 4-Index is ranked sorted and not unique
* 5-Index is ranked sorted and unique
The RMU Analyze Placement command uses the RMU$FLAGS bits
shown in RMU$FLAGS Bits Used by the RMU Analyze Placement
Command for describing specific index information.
Table 3 RMU$FLAGS Bits Used by the RMU Analyze Placement Command
Bit Offset Meaning
0 Unique index if true
1 Hashed index if true
2 Ranked sorted index if true
- RMU$COUNT
Contains the number of index nodes
- RMU$DUPLICATE_COUNT
Contains the number of duplicate records
- RMU$KEY_COUNT
Contains the number of keys
- RMU$DUPLICATE_KEY_COUNT
Contains the number of duplicate keys
- RMU$DATA_COUNT
Contains the number of records
- RMU$DUPLICATE_DATA_COUNT
Contains the number of duplicate records
- RMU$TOTAL_KEY_PATH
Contains the total number of keys touched to access all the
records
- RMU$TOTAL_PAGE_PATH
Contains the total number of pages touched to access all
the records
- RMU$TOTAL_BUFFER_PATH
Contains the total number of buffers touched to access all
the records
- RMU$MAX_KEY_PATH
Contains the largest number of keys touched to access any
of the records
- RMU$MAX_PAGE_PATH
Contains the largest number of pages touched to access any
of the records
- RMU$MIN_BUF_PATH
Contains the smallest number of buffers touched to access
any of the records
4.6 – Examples
Example 1
The following command provides information on row storage
relative to the DEPARTMENTS_INDEX index of the sample personnel
database:
$ RMU/ANALYZE/PLACEMENT MF_PERSONNEL.RDB DEPARTMENTS_INDEX