PeopleSoft Learning

The advantage of DBMS_STAT is
- easier to automate
- can analyze external tables.
- can gather system statistics ( 9i onwards )
- DBMS_STATS gathers statistics only for cost-based optimization; it does
not
gather other statistics. For example, the table statistics gathered by
DBMS_STATS include the number of rows, number of blocks currently containing
data, and average row length but not the number of chained rows, average
free space, or number of unused data blocks.

ANALYZE calculates global statistics for partitioned tables and indexes
instead
of gathering them directly. This can lead to inaccuracies for some
statistics,
such as the number of distinct values. DBMS_Stats won't do that.

People code has a meta tag that can be used to update the stats on the record which will improve the performance of the record.

For Oracle Database.

%UpdateStats - It will expand to DBMS_STATS.GATHER_STATS. This can be used in the Application Engine and COBAL

%UpdateStats(record name ,[HIGH/LOW])
Default is LOW.

pecifying LOW produces the statement

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT8468908', tabname=>'PSSTATUS', estimate_percent=>20, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE)

Specifying HIGH produces the statement

execute DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'PT848908', tabname=>'PSSTATUS', estimate_percent=> dbms_stats.auto_sample_size, method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1',cascade=>TRUE)

Oracle has an implicit commit after the %UpdateStats statement executes.