Index and Table Statistics

Edit online

Database optimizers rely on relatively up-to-date table and index statistics to generate optimal access plans.

Oracle does not require statistics to be absolutely correct or current, just relatively correct and representative. Sterling B2B Integrator requires a refresh statistics periodically (several times in a day or at least one time every day). You can refresh statistics by using the default job that is provided in Oracle or by creating the custom jobs. Ensure that you update statistics when there is a significant change in data (20% or more).

Oracle database gathers statistics automatically during its maintenance window (10 p.m. to 2 a.m. on week nights and all day on weekends) for tables with sufficient changes. Oracle bypasses statistics generation for tables that did not change significantly.

To manually refresh statistics, use the DBMS_STATS package. This sample invocation refreshes the statistics for the entire database:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNNAME => <schema owner>, CASCADE => TRUE

where <schema owner> = Sterling B2B Integrator schema owner.

One way to determine the update frequency for statistics is when the data for one customer changes by approximately 20%. The update frequency is governed by the document's lifespan.

Speak with the Sterling B2B Integrator Administrator to find out more about document lifespans. Generally, updating statistics one time every 24 hours is sufficient.

Index Rebuilds

Index rebuilds are also required for the Sterling B2B Integrator schema when the data in the tables changes by approximately 20%. However, the rebuild must be validated by looking at the system performance as a whole. Online index rebuilds, which cause minimal impact to the system, is possible.

The Sterling B2B Integrator system administrator must work with the DBA to identify the indexes that must be rebuilt. You must always rebuild indexes at schema level.

Tip:

Sterling B2B Integrator tables in the database turn over quite often between deletion and inserts. With a typical sample 7 day life span in the application, key tables in the database are turning over 28% in just one day.

Sterling B2B Integrator also has a large number of serially increasing indexes and date indexes that are never re-used. Data is constantly removed from the tables in both large blocks of the B-tree and in various areas of the B-tree. This constant purging creates a lot of empty leaf blocks that are never reused or reclaimed and the indexes become imbalanced because the growth is only on one side of the B-tree. This leads to index broadening and increase of the indexes clustering factor.

The only way to mitigate database growth and reclaim the space in these fragmented index leaf blocks is through the rebuilding of indexes.

Sterling B2B Integrator is a high-volume OLTP application and there might be times when you have to shrink the tables. For more information on shrinking database segments and index rebuilds, refer to the Oracle documentation listed below: