B Statistics

This section discusses Oracle statistics.

B.1 Options Used

Following are the options used while collecting statistics:

Auto sample size
Statistics collected only at object level, partition statistics are not collected
Block sample is used to get a good spread
Histogram considerations left for Oracle to decide

DBMS_SCHEMA_STATS used. You may also consider DBMS_TABLE_STATS similar options used as follows:

exec dbms_stats.gather_schema_stats (ownname=> '<OWNER>', method_opt=> 'for all columns size auto', cascade=>true, block_sample => true, granularity => 'GLOBAL', estimate_percent=> dbms_stats.auto_sample_size, options => 'GATHER STALE')

Note:

In a few cases AUTO_SAMPLE_SIZE considers 100% sample even for large tables. If the statistics are running long, consider setting proper table preferences to lower sample size considerations. Use the following command to set preferences for a specific table.
- EXEC DBMS_STATS.SET_TABLE_PREFS(ownname=> '<OWNER>', tabname=>'<TABLE_NAME>', pname => 'ESTIMATE_PERCENT', pvalue => '<sample size>');
- After setting proper preferences for all the large or small tables, omit the option or clause estimate_percent=> dbms_stats.auto_sample_size” from above DBMS_STATS.GATHER_SCHEMA_STATS
Perform one successful statistics collection before using the option options => 'GATHER STALE'

B.2 Script to Find Tables for Statistics

Use the following script to find all the tables in the ODB schema that need to have statistics collected for them.

Note:

Any table that is excluded from this script should have the statistics deleted and locked if they were collected by some other step.

select table_name
from user_tables
where iot_type is null
and substr(table_name,1,9) <> 'W_EHA_STG'
and substr(table_name,-4) <> '_STG'