Collect Statistics on Oracle Repository Tables

Collect Statistics on Oracle Repository Tables

Collect statistics on your Power Center repository databases this will help to improve performance of fetches and writes of repository data.

Follow below steps.

Execute below queries:

• select 'analyze table ', table_name, ' compute statistics;' from user_tables where table_name like 'OPB_%'

• select 'analyze index ', INDEX_NAME, ' compute statistics;' from user_indexes where INDEX_NAME like 'OPB_%'

Copy result to text file and remove all headers from file (if any) and execute.

Sample outputs:

'ANALYZETABLE' TABLE_NAME 'COMPUTESTATISTICS;'

-------------- ------------------------------ --------------------

analyze table OPB_ANALYZE_DEP compute statistics;

analyze table OPB_ATTR compute statistics;

analyze table OPB_BATCH_OBJECT compute statistics;

.

'ANALYZEINDEX' INDEX_NAME 'COMPUTESTATISTICS;'

-------------- ------------------------------ --------------------

analyze index OPB_DBD_IDX compute statistics;

analyze index OPB_DIM_LEVEL compute statistics;

analyze index OPB_EXPR_IDX compute statistics;

Note: Make sure to take backups and proceed with steps.