Starting with 8i, Oracle recommends the use of dbms_stats to gather statistics for the CBO.
Some of the advantages:
1. dbms_stats can be done in parallel
2. Monitoring can be done and stale statistics can be collected for changed rows using dbms_stats.
3. You can import/export/set statistics directly with dbms_stats
4. It is easier to automate with dbms_stats (it is procedural, analyze is just a command)
5. dbms_stats is the stated, preferred method of collecting statisttics.
6. dbms_stats can analyze external tables, analyze cannot.
7. dbms_stats gathers statistics only for costbased 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.
8. dbms_stats (in 9i) can gather system stats (new)
9. 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.
10. all new enhancements are included in dbms_stats
11. Most importantly, in the future, ANALYZE will not collect statistics needed by
the costbased optimizer.
We recommend to implement dbms_stats in the following way:
1. Enable table monitoring
2. Gather stats at the schema level for all schemas except SYS and SYSTEM.
3. Set OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ = 25. These are typically appropriate for many transactional/OLTP systems. For OLAP and data warehouses, keep defaults.
. Enable table monitoring for all tables
alter table monitoring;
Use this procedure to enable monitoring on tables that don't have monitoring turnedon:
set serveroutput on
BEGIN
FOR t in (select owner,table_name
from dba_tables
where owner not in ('SYS','SYSTEM')
and monitoring = 'NO'
)
LOOP
dbms_output.put_line('Enable monitoring for table: 't.owner'.'t.table_name);
execute immediate 'alter table 't.owner'.'t.table_name' monitoring';
End Loop;
END;
/
. Gather statistic at the schema level (don't gather statistics for sys and system)
BEGIN
FOR s in (select distinct(owner) schema
from dba_tables
where owner not in ('SYS','SYSTEM')
LOOP
dbms_output.put_line('Analyzing the entire 's.schema' schema');
dbms_stats.gather_schema_stats(ownname => s.schema,
estimate_percent => 20,
method_opt => 'FOR ALL COLUMNS SIZE 1',
degree => 8,
cascade => true);
End Loop;
END;
/
explain parameters
compute
savestats in a table
explain histograms and buckets
. Schedule job to list stale objects and gather new stats for stale objects
.
Examples:
 Gather stats for a schema
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS(
ownname => 'sysadm',
estimate_percent => 1,
block_sample =>TRUE,
cascade => TRUE,
stattab => 'savestats',
statown => 'system');
END;
/
 Gather stats for a schema
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SYSADM',
tabname => 'PS_PERSONAL_DATA',
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
degree => 2,
cascade => TRUE
);
END;
/
The settings of OPTIMIZER_INDEX_CACHING = 90 and OPTIMIZER_INDEX_COST_ADJ =
25. These are typically appropriate for many transactional/OLTP systems.
