This is one of the technical infrastructure data gathering methodologies and performance recommendations for server machines. The full list is defined in the Server Environment Recommendations topic here.
After upgrade, the Oracle data statistics should be renewed for STEP in order to collect a complete new set of statistics for all indexes / tables in STEPSYS schema.
The following information is about STEP and the way database statistics are maintained for the STEPSYS schema:
- STEP has its own database job (GATHER_STATS_STEP) which, by default, is executed (executing the function stepsys.adminpck.table_manage) every night at 23.30h. This job collects new statistics on indexes / tables in STEPSYS schema, where more than 10 percent of rows has been inserted / updated / deleted since last time, statistics were collected on the given object.
- The normal Oracle built-in scheduled job for collecting statistics on all indexes / tables in the database must be configured to only collect statistics for Oracle’s own objects (SYS schema). Therefore, the parameter AUTOSTATS_TARGET, must be set to ORACLE.
- Some core tables in the STEPSYS schema are locked and without any statistics. This because the number of rows in the tables change too frequently, and the statistics would therefore never be up to date. In order to make Oracle choose the right path of execution, when executing queries against these tables, STEP instead uses the built-in Oracle feature 'dynamic samplic' (Oracle init parameter DYNAMIC_SAMPLING=2), which makes Oracle generate some statistics for the given object at runtime.
Collect a complete new set of statistics for all indexes / tables in STEPSYS schema as follows:
- Log onto the database as user STEPSYS (or sqlplus, or the like).
- Execute the following command:
Exec adminpck.analyze(true);