Inserts workload records into the RDB$WORKLOAD system relation.
1 – Description
When you enable and collect workload statistics, the system
table RDB$WORKLOAD is created and populated. (See Collect_
Optimizer_Statistics for details.) You can update or delete these
statistics using the RMU Collect Optimizer_Statistics command or
the RMU Delete Optimizer_Statistics command, respectively.
You might delete entries in the RDB$WORKLOAD table by accident
or you might delete them to test how effective it is to maintain
those particular workload statistics. If you decide that you
want to maintain those deleted statistics, you can insert them
with the RMU Insert Optimizer_Statistics command. To ensure that
you insert accurate values, always issue an RMU Show Optimizer_
Statistics command with the Log qualifier before you issue an
RMU Delete Optimizer_Statistics command. Refer to your generated
log file for the values you should specify with the RMU Insert
Optimizer_Statistics command.
In addition you can use the RMU Insert Optimizer_Statistics
command to create workload statistics in a copy of your master
database.
If you issue an RMU Collect Optimizer_Statistics command after
having issued an RMU Insert Optimizer_Statistics command,
statistics for the specified column groups are updated.
2 – Format
(B)0[mRMU Insert Optimizer_Statistics root-file-spec
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Column_Group=(Column-list) x None - Required Qualifier
/Duplicity_Factor=(floating-number) x /Duplicity_Factor=(1.0)
/[No]Log[=file-spec] x See description
/Null_Factor=(floating-number) x /Null_Factor=(0.0)
/Tables=(table-list) x None - Required Qualifier
3 – Parameters
3.1 – root-file-spec
root-file-spec
Specifies the database into which optimizer statistics are to be
inserted. The default file type is .rdb.
4 – Command Qualifiers
4.1 – Column Group
Column_Group=(column-list)
Specifies a list of columns that comprise a column group. You
must use the Tables qualifier to specify the table or tables with
which the columns are associated.
The Column_Group=(column-list) qualifier is a required qualifier.
4.2 – Duplicity Factor
Duplicity_Factor=(floating-number)
Specifies the value to be inserted in the RDB$DUPLICITY_FACTOR
column in the RDB$WORKLOAD table for the specified column group
and table (or tables). The minimum value is 1.0 and the maximum
value is the cardinality of the specified table. The default is
the Duplicity_Factor=(1.0) qualifier.
4.3 – Log
Log
Log=file-spec
Nolog
Specifies how the statistics inserted into the RDB$WORKLOAD
system table 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. Specify
the Nolog qualifier to prevent display of the information. If you
do not specify any of variation of the Log qualifier, the default
is the current setting of the DCL verify switch. (The DCL SET
VERIFY command controls the DCL verify switch.)
4.4 – Null Factor
Null_Factor=floating-number
Specifies the value to be inserted in the RDB$NULL_FACTOR column
in the RDB$WORKLOAD table for the specified column group and
table (or tables). The minimum value is 0.0 and the maximum value
is 1.0. The default is the Null_Factor=(0.0) qualifier.
4.5 – Tables
Table
Tables=(table-list)
Specifies the table or tables for which column group entries are
to be inserted.
If you issue an RMU Collect Optimizer_Statistics command after
you have inserted a workload column group into the RDB$WORKLOAD
system table, those statistics are collected.
The Tables=(table-list) qualifier is a required qualifier.
5 – Usage Notes
o To use the RMU Insert Optimizer_Statistics 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 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 The Insert Optimizer_Statistics command modifies the
RDB$LAST_ALTERED date of the RDB$WORKLOAD row so that it is
activated for use by the optimizer.
6 – Examples
Example 1
The following example:
1. Collects workload statistics for the JOB_HISTORY table using
the RMU Collect Optimizer_Statistics command
2. Deletes the statistics for one of the JOB_HISTORY workload
column groups
3. Inserts the statistics that were just deleted into the
RDB$WORKLOAD system table using the RMU Insert Optimizer_
Statistics command
4. Displays the current data stored in the RDB$WORKLOAD table for
the JOB_HISTORY table using the RMU Show Optimizer_Statistics
command
$ RMU/COLLECT OPTIMIZER_STATISTICS MF_PERSONNEL.RDB -
_$ /TABLE=(JOB_HISTORY)/STATISTICS=(WORKLOAD)/LOG
Start loading tables... at 3-JUL-1996 10:54:04.16
Done loading tables.... at 3-JUL-1996 10:54:04.69
Start collecting workload stats... at 3-JUL-1996 10:54:06.76
Maximum memory required (bytes) = 6810
Done collecting workload stats.... at 3-JUL-1996 10:54:07.64
Start calculating stats... at 3-JUL-1996 10:54:07.84
Done calculating stats.... at 3-JUL-1996 10:54:07.86
Start writing stats... at 3-JUL-1996 10:54:09.34
---------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Workload Column group : EMPLOYEE_ID
Duplicity factor : 2.7400000
Null factor : 0.0000000
Workload Column group : EMPLOYEE_ID, JOB_CODE, JOB_START,
JOB_END, DEPARTMENT_CODE, SUPERVISOR_ID
Duplicity factor : 1.5930233
Null factor : 0.3649635
Done writing stats.... at 3-JUL-1996 10:54:09.90
$ RMU/DELETE OPTIMIZER_STATISTICS MF_PERSONNEL.RDB -
_$ /TABLE=(JOB_HISTORY)/COLUMN_GROUP=(EMPLOYEE_ID,JOB_CODE, -
_$ JOB_START,JOB_END,DEPARTMENT_CODE,SUPERVISOR_ID)/LOG
Changing RDB$SYSTEM area to READ_WRITE.
Workload column group deleted for JOB_HISTORY : EMPLOYEE_ID,
JOB_CODE, JOB_START, JOB_END, DEPARTMENT_CODE,
SUPERVISOR_ID
$ !
$ RMU/INSERT OPTIMIZER_STATISTICS MF_PERSONNEL.RDB -
_$ /TABLE=(JOB_HISTORY) /COLUMN_GROUP=(EMPLOYEE_ID,JOB_CODE, -
_$ JOB_START,JOB_END,DEPARTMENT_CODE,SUPERVISOR_ID) -
_$ /DUPLICITY_FACTOR=(1.5930233)/NULL_FACTOR=(0.3649635)/LOG
Changing RDB$SYSTEM area to READ_WRITE.
Workload column group inserted for JOB_HISTORY : EMPLOYEE_ID,
JOB_CODE, JOB_START, JOB_END, DEPARTMENT_CODE,
SUPERVISOR_ID
$ !
$ RMU/SHOW OPTIMIZER_STATISTICS MF_PERSONNEL.RDB -
_$ /TABLE=(JOB_HISTORY)/STATISTICS=(WORKLOAD)/LOG
--------------------------------------------------------------------
Optimizer Statistics for table : JOB_HISTORY
Workload Column group : EMPLOYEE_ID
Duplicity factor : 2.7400000
Null factor : 0.0000000
First created time : 3-JUL-1996 10:37:36.43
Last collected time : 3-JUL-1996 10:54:09.62
Workload Column group : EMPLOYEE_ID, JOB_CODE, JOB_START,
JOB_END, DEPARTMENT_CODE, SUPERVISOR_ID
Duplicity factor : 1.5930233
Null factor : 0.3649635
First created time : 3-JUL-1996 10:57:47.65
Last collected time : 3-JUL-1996 10:57:47.65