Generates a formatted display of statistical information
describing the row placement relative to the index structures
for the database.
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.
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
3 – Parameters
3.1 – root-file-spec
The file specification for the database root file to be analyzed.
The default file extension is .rdb.
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 – Command Qualifiers
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.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.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 – 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.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.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.
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
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