B Statistics

To enhance performance, gather statistics on schemas CDM, ODB, ENT, APP, and CGA. If you have uneven data distribution among the schemas, you may want to contact Oracle Support for additional performance recommendations.

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'