Improving Performance at Repository level

Tuning Repository Performance

The PowerMart and PowerCenter repository has more than 80 tables and almost all tables use one or more indexes to speed up queries. Most databases keep and use column distribution statistics to determine which index to use to execute SQL queries optimally. Database servers do not update these statistics continuously.

In frequently-used repositories, these statistics can become outdated very quickly and SQL query optimizers may choose a less than optimal query plan. In large repositories, the impact of choosing a sub-optimal query plan can affect performance drastically. Over time, the repository becomes slower and slower.

To optimize SQL queries, you might update these statistics regularly. The frequency of updating statistics depends on how heavily the repository is used. Updating statistics is done table by table. The database administrator can create scripts to automate the task.

You can use the following information to generate scripts to update distribution statistics.

Note: All PowerMart/PowerCenter repository tables and index names begin with “OPB_”.

Oracle Database

You can generate scripts to update distribution statistics for an Oracle repository.

To generate scripts for an Oracle repository:

1. Run the following 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_%'

This produces an output like the following:

'ANALYZETABLE'         TABLE_NAME                           'COMPUTESTATISTICS;'

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

analyze table                 OPB_ANALYZE_DEP                compute statistics;

analyze table                 OPB_ATTR                               compute statistics;

analyze table                 OPB_BATCH_OBJECT              compute statistics;

2. Save the output to a file.

3. Edit the file and remove all the headers.

Headers are like the following:

'ANALYZETABLE' TABLE_NAME       'COMPUTESTATISTICS;'

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

4. Run this as an SQL script. This updates repository table statistics.

Microsoft SQL Server

You can generate scripts to update distribution statistics for a Microsoft SQL Server repository.

To generate scripts for a Microsoft SQL Server repository:

1. Run the following query:

select 'update statistics ', name from sysobjects where name like 'OPB_%'

This produces an output like the following:

                   name

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

update statistics  OPB_ANALYZE_DEP

update statistics  OPB_ATTR

update statistics  OPB_BATCH_OBJECT

2. Save the output to a file.

3. Edit the file and remove the header information.

Headers are like the following:

                   name

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

4. Add a go at the end of the file.

5. Run this as a sql script. This updates repository table statistics.