Example 1
The following example collects cardinality statistics for the
EMPLOYEES and JOB_HISTORY tables and their associated indexes.
See the Usage Notes for an explanation for the value "0" for the
index prefix cardinality.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /STATISTICS=(CARDINALITY)/TABLES=(EMPLOYEES, JOB_HISTORY) -
_$ /INDEXES=(EMP_LAST_NAME,EMP_EMPLOYEE_ID, EMPLOYEES_HASH, -
_$ JH_EMPLOYEE_ID, JOB_HISTORY_HASH)/LOG
Start loading tables... at 3-JUL-1996 09:35:25.19
Done loading tables.... at 3-JUL-1996 09:35:25.91
Start loading indexes... at 3-JUL-1996 09:35:25.92
Done loading indexes.... at 3-JUL-1996 09:35:26.49
Start collecting btree index stats... at 3-JUL-1996 09:35:28.17
Done collecting btree index stats.... at 3-JUL-1996 09:35:28.23
Start collecting table & hash index stats... at 3-JUL-1996 09:35:28.23
Done collecting table & hash index stats.... at 3-JUL-1996 09:35:28.52
Start calculating stats... at 3-JUL-1996 09:35:28.76
Done calculating stats.... at 3-JUL-1996 09:35:28.76
Start writing stats... at 3-JUL-1996 09:35:30.16
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Cardinality : 100
Index name : EMP_LAST_NAME
Index Cardinality : 83
Segment Column Prefix cardinality
LAST_NAME 0
Index name : EMP_EMPLOYEE_ID
Index Cardinality : 100
Segment Column Prefix cardinality
EMPLOYEE_ID 0
Index name : EMPLOYEES_HASH
Index Cardinality : 100
----------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Cardinality : 274
Index name : JH_EMPLOYEE_ID
Index Cardinality : 100
Segment Column Prefix cardinality
EMPLOYEE_ID 0
Index name : JOB_HISTORY_HASH
Index Cardinality : 100
Done writing stats.... at 3-JUL-1996 09:35:30.83
Example 2
The following example collects storage statistics for the
EMPLOYEES and JOB_HISTORY TABLES and their associated indexes:
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel -
_$ /STATISTICS=(STORAGE)/TABLES=(EMPLOYEES, JOB_HISTORY) -
_$ /INDEXES=(EMP_LAST_NAME,EMP_EMPLOYEE_ID, EMPLOYEES_HASH, -
_$ JH_EMPLOYEE_ID, JOB_HISTORY_HASH)/LOG
Start loading tables... at 3-JUL-1996 10:28:49.39
Done loading tables.... at 3-JUL-1996 10:28:50.30
Start loading indexes... at 3-JUL-1996 10:28:50.30
Done loading indexes.... at 3-JUL-1996 10:28:51.03
Start collecting btree index stats... at 3-JUL-1996 10:28:53.27
Done collecting btree index stats.... at 3-JUL-1996 10:28:53.37
Start collecting table & hash index stats... at 3-JUL-1996 10:28:53.38
Done collecting table & hash index stats.... at 3-JUL-1996 10:28:53.80
Start calculating stats... at 3-JUL-1996 10:28:54.07
Done calculating stats.... at 3-JUL-1996 10:28:54.07
Start writing stats... at 3-JUL-1996 10:28:55.61
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Row clustering factor : 0.2550000
Index name : EMP_LAST_NAME
Average Depth : 2.0000000
Key clustering factor : 0.0481928
Data clustering factor : 1.1686747
Index name : EMP_EMPLOYEE_ID
Average Depth : 2.0000000
Key clustering factor : 0.0100000
Data clustering factor : 0.9500000
Index name : EMPLOYEES_HASH
Key clustering factor : 1.0000000
Data clustering factor : 1.0000000
--------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Row clustering factor : 0.0930657
Index name : JH_EMPLOYEE_ID
Average Depth : 2.0000000
Key clustering factor : 0.0500000
Data clustering factor : 0.9500000
Index name : JOB_HISTORY_HASH
Key clustering factor : 1.0000000
Data clustering factor : 1.0000000
Done writing stats.... at 3-JUL-1996 10:28:56.41
Example 3
The following example enables workload collection with an SQL
ALTER DATABASE statement, executes frequently run queries to
generate a workload profile, collects workload statistics for
the EMPLOYEES and JOB_HISTORY tables (along with their associated
indexes), and then displays the statistics gathered.
The SQL natural left outer join causes the first and third
workload column groups to be created. The SQL DISTINCT clause
causes the second and fourth workload column groups to be
created.
$ ! Enable workload collection:
$ SQL
SQL> ALTER DATABASE FILENAME mf_personnel.rdb
cont> WORKLOAD COLLECTION IS ENABLED;
SQL> --
SQL> -- Execute frequently run SQL queries.
SQL> --
SQL> ATTACH 'FILENAME mf_personnel.rdb';
SQL> SELECT DISTINCT *
cont> FROM JOB_HISTORY NATURAL LEFT OUTER JOIN EMPLOYEES;
.
.
.
SQL> DISCONNECT DEFAULT;
SQL> -- Disable workload collection:
SQL> ALTER DATABASE FILENAME mf_personnel.rdb
cont> WORKLOAD COLLECTION IS DISABLED;
SQL> EXIT;
$
$ ! Direct Oracle RMU to collect statistics for the EMPLOYEES and
$ ! JOB_HISTORY tables.
$ !
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /TABLE=(EMPLOYEES, JOB_HISTORY)/STATISTICS=(WORKLOAD)/LOG
Start loading tables... at 3-JUL-1996 10:40:00.22
Done loading tables.... at 3-JUL-1996 10:40:00.90
Start collecting workload stats... at 3-JUL-1996 10:40:03.43
Maximum memory required (bytes) = 6810
Done collecting workload stats.... at 3-JUL-1996 10:40:05.03
Start calculating stats... at 3-JUL-1996 10:40:05.32
Done calculating stats.... at 3-JUL-1996 10:40:05.32
Start writing stats... at 3-JUL-1996 10:40:06.91
----------------------------------------------------------------------
Optimizer Statistics collected for table : EMPLOYEES
Workload Column group : EMPLOYEE_ID
Duplicity factor : 1.0000000
Null factor : 0.0000000
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
----------------------------------------------------------------------
Optimizer Statistics collected for table : JOB_HISTORY
Workload Column group : EMPLOYEE_ID
Duplicity factor : 2.7040000
Null factor : 0.0000000
Workload Column group : EMPLOYEE_ID, JOB_CODE, JOB_START,
JOB_END, DEPARTMENT_CODE, SUPERVISOR_ID
Duplicity factor : 1.5420582
Null factor : 0.3649635
Done writing stats.... at 3-JUL-1996 10:40:07.46
Example 4
The following example collects all statistics (cardinality,
workload, and storage) for all tables and indexes in the database
except system relations. Output is written to the file stats_
nosys.log.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb -
_$ /LOG=stats_nosys.log
Example 5
The following example collects all statistics (cardinality,
workload, and storage) for all tables, indexes, and system
relations. Output is written to the file stats_all.log.
$ RMU/COLLECT OPTIMIZER_STATISTICS mf_personnel.rdb/SYSTEM_RELATIONS -
_$ /LOG=stats_all.log
Example 6
In the following example the Employees and Departments tables are
excluded from statistics collection.
$ RMU/COLLECT OPTIMIZER_STATISTICS MF_PERSONNEL /LOG -
_$ /EXCLUDE_TABLES=(EMPLOYEES,DEPARTMENTS)