James's Blog

Oracle: Generation statistics

Oracle use following techniques to generates statistics:
- Estimation based on random data sampling
- Exact computation
- User-defined statistics collection methods

To collection statistics on schema
- execute DBMS_STATS.GATHER_SCHEMA_STATS('OE',DBMS_STATS.AUTO_SAMPLE_SIZE);

You can use DBMS_STATS to gather, modify, view, export, import and delete statistics.

DBMS_STATS Package procedures:
- GATHER_INDEX_STATS : Index statistics
- GATHER_TABLE_STATS : Table, Columns and index statistics
- GATHER_SCHEMA_STATS : Statistics for all objects in a schema
- GATHER_DATABASE_STATS : Statistics for all objects in a database
- GATHER_SYSTEM_STATS : CPU and I/O statistics for the system

Gathering statistics on table/col/index will invalidate parsed SQL statements.
Gathering system statistics will not invalidate parsed SQL.


- Gather statistics. Gathering ends after 720 mins and is stored in mystats table
** day time **
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab = 'mystats',
statid => 'OLTP');
end;
/

** night time **
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab = 'mystats',
statid => 'OLAP');
end;
/

- to import OLAP statistics for nighttime

BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATIS.IMPORT_SYSTEM_STATS(''mysstats'',''OLAP'');'
SYSDATE +0.5, 'SYSDATE + 1');
COMMIT;
END;
/

posted by James Pei @ 2:47 PM