Deletes records from the RDB$WORKLOAD system table.
1 – Description
When you enable and collect workload statistics, the system
table, RDB$WORKLOAD, is created and populated. (See Collect_
Optimizer_Statistics for details.) If you are knowledgeable
about the data in your database, or if workload statistics were
gathered for queries that are no longer in use, you might decide
that you no longer want Oracle RMU to collect statistics for
particular column groups. The RMU Delete Optimizer_Statistics
gives you the ability to selectively delete records for column
groups in the RDB$WORKLOAD system table.
When you use the RMU Delete Optimizer_Statistics command, both
the optimizer statistics themselves and the reference to the
column duplicity factor and the null factor are deleted from the
RDB$WORKLOAD system table.
If you issue an RMU Collect Optimizer_Statistics command after
having issued an RMU Delete Optimizer_Statistics command,
statistics for the specified column group are not collected.
2 – Format
(B)0[mRMU/Delete Optimizer_Statistics root-file-spec
[4mCommand[m [4mQualifiers[m x [4mDefaults[m
x
/Column_Group=(column-list) x See description
/[No]Log[=file-name] x See description
/Tables=(table-list) x None - Required Qualifier
3 – Parameters
3.1 – root-file-spec
root-file-spec
Specifies the database from which optimizer statistics are to be
deleted. 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 single column group.
The columns specified must be a valid column group for a table
specified with the Tables=(table-list) qualifier. (Use the RMU
Show Optimizer_Statistics command to display a valid column
groups.) When you specify the Column_Group qualifier, the entire
record in the RDB$WORKLOAD system table that holds data for the
specified column group is deleted. Therefore, the next time you
issue the RMU Collect Optimizer_Statistics command, statistics
for the specified column-group are not collected.
4.2 – Log
Log
Nolog
Log=file-name
Specifies whether the statistics deleted from 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 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.3 – Tables
Tables=(table-list)
Specifies the table or tables for which column group entries are
to be deleted, as follows:
o If you specify the Tables=(table-list) qualifier, but do
not specify the Column_Group qualifier, then all column
group entries for the listed tables are deleted from the
RDB$WORKLOAD system table.
o If you specify the Tables=(table-list) qualifier, and you
specify the Column_Group=(column-list) qualifier, then the
workload statistics entries for the specified tables that
have exactly the specified column group are deleted from the
RDB$WORKLOAD system table.
o If you use an asterisk (*) with the Tables qualifier
(Tables=*), all tables registered in the RDB$WORKLOAD table
are deleted. This allows the RDB$WORKLOAD table to be purged.
If you issue an RMU Collect Optimizer_Statistics command after
you have deleted a workload column group from the RDB$WORKLOAD
system table, those statistics are no longer collected.
The Tables=(table-list) qualifier is a required qualifier; you
cannot issue an RMU Delete Optimizer_Statistics command without
the Tables=(table-list) qualifier.
5 – Usage Notes
o To use the RMU Delete 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 Oracle Corporation recommends that you execute an RMU Show
Optimizer_Statistics command with the Output qualifier prior
to executing an RMU Delete Optimizer_Statistics command. If
you accidentally delete statistics, you can replace them
by issuing an RMU Insert Optimizer_Statistics command and
specifying the statistical values contained in the output
file.
6 – Examples
Example 1
The following example issues commands to do the following:
1. Display optimizer statistics for the EMPLOYEES and JOB_HISTORY
tables and their indexes
2. Delete the entries for the column group (EMPLOYEE_ID, JOB_
CODE, JOB_START, JOB_END, DEPARTMENT_CODE, SUPERVISOR_ID) in
JOB_HISTORY
$ RMU/SHOW OPTIMIZER_STATISTICS MF_PERSONNEL.RDB -
_$ /TABLES=(EMPLOYEES, JOB_HISTORY)/STATISTICS=(WORKLOAD)
-----------------------------------------------------------------------
Optimizer Statistics for table : EMPLOYEES
Workload Column group : EMPLOYEE_ID
Duplicity factor : 1.0000000
Null factor : 0.0000000
First created time : 3-JUL-1996 10:37:36.43
Last collected time : 3-JUL-1996 10:46:10.73
Workload Column group : LAST_NAME, FIRST_NAME, MIDDLE_INITIAL,
ADDRESS_DATA_1, ADDRESS_DATA_2, CITY, STATE, POSTAL_CODE, SEX,
BIRTHDAY, STATUS_CODE
Duplicity factor : 1.5625000
Null factor : 0.3600000
First created time : 3-JUL-1996 10:37:36.43
Last collected time : 3-JUL-1996 10:46:10.74
----------------------------------------------------------------------
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
$ !
$ ! Delete one of the entries for JOB_HISTORY
$ !
$ 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