Filter
Top Products
Occasionally, you may observe full table scans on some tables. It is not a cause for
concern unless the overall system performance is slow or business level SLAs are
not met.
Index and Table Statistics
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. You should 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(<schema owner>,CASCADE => TRUE, OPTIONS =>’GATHER AUTO’)
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 recommended 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.
Performance Management 31