Oracle9i Supplied PL/SQL Packages and Types Reference Release 1 (9.0.1) Part Number A8985202 

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 btree and bitmap indexes.
DBMS_STATS.GENERATE_STATS ( ownname VARCHAR2, objname VARCHAR2, organized NUMBER DEFAULT 7);
ORA20000
: Unsupported object type of object does not exist.
ORA20001
: 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 costbased 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: emp
.savestats
.
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;

Copyright © 19962001, Oracle Corporation. All Rights Reserved. 
