|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
Part Number A89852-02
DBMS_STATS, 37 of 41
This procedure generates object statistics from previously collected statistics of related objects. For fully populated schemas, the gather procedures should be used instead when more accurate statistics are desired.The currently supported objects are b-tree and bitmap indexes.
Schema of object.
Name of object.
Amount of ordering associated between the index and its underlying table. A heavily organized index would have consecutive index keys referring to consecutive rows on disk for the table (the same block). A heavily disorganized index would have consecutive keys referencing different table blocks on disk.
This parameter is only used for b-tree indexes. The number can be in the range of 0-10, with 0 representing a completely organized index and 10 a completely disorganized one.
ORA-20000: Unsupported object type of object does not exist.
ORA-20001: Invalid option or invalid statistics.
Assume many modifications have been made to the
emp table since the last time statistics were gathered. To ensure that the cost-based optimizer is still picking the best plan, statistics should be gathered once again; however, the user is concerned that new statistics will cause the optimizer to choose bad plans when the current ones are acceptable. The user can do the following:
BEGIN DBMS_STATS.CREATE_STAT_TABLE ('scott', 'savestats'); DBMS_STATS.GATHER_TABLE_STATS ('scott', 'emp', 5, stattab => 'savestats'); END;
This operation gathers new statistics on
emp, but first saves the original statistics in a user stat table:
If the user believes that the new statistics are causing the optimizer to generate poor plans, then the original stats can be restored as follows:
BEGIN DBMS_STATS.DELETE_TABLE_STATS ('scott', 'emp'); DBMS_STATS.IMPORT_TABLE_STATS ('scott', 'emp', stattab => 'savestats'); END;
Assume that you want to perform database application processing OLTP transactions during the day and run reports at night.
To collect daytime system statistics, gather statistics for 720 minutes. Store the stats in the MYSTATS table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLTP'); END;
To collect nighttime system statistics, gather statistics for 720 minutes. Store the stats in the MYSTATS table.
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS ( interval => 720, stattab => 'mystats', statid => 'OLAP'); END;
Update the dictionary with the gathered statistics.
VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS
(''mystats'',''OLTP'');' sysdate, 'sysdate + 1'); COMMIT; END; BEGIN DBMS_JOB.SUBMIT (:jobno, 'DBMS_STATS.IMPORT_SYSTEM_STATS
(''mystats'',''OLAP'');' sysdate + 0.5, 'sysdate + 1'); COMMIT; END;