Configure Database Index Optimizations
Several system parameters can be configured to modify the default database index settings:
Modify the Database Index Rebuild Frequency
In large environments, if report generation performance begins to degrade, database indexes can be built more frequently. The default is to rebuild indexes every 60 days.
Use the following procedure to configure the frequency of index builds in the system parameters.
1. Log in to the Portal Server as user aptare.
2. Type the following command:
sqlplus portal/<portal_password>
3. Locate the default value.
SELECT param_value
FROM ptl_system_parameter
WHERE param_name='DAYS_BETWEEN_INDEX_REBUILDS';
4. Update the value for the DAYS_BETWEEN_INDEX_REBUILDS system parameter.
UPDATE ptl_system_parameter set param_value=30 WHERE param_name='DAYS_BETWEEN_INDEX_REBUILDS';
commit;
Define a Large Database Index
In large environments, to improve the performance of index builds, a system parameter can be configured to define the number of leaf blocks for a large index. The default value is 10,000 leaf blocks.
Use the following procedure to the number of leaf blocks in a database index.
1. Log in to the Portal Server as user aptare.
2. Type the following command:
sqlplus portal/<portal_password>
3. Update the value for the LARGE_INDEX_NUM_LEAF_BLOCKS system parameter.
UPDATE ptl_system_parameter set param_value=<NEW_VALUE> WHERE param_name='LARGE_INDEX_NUM_LEAF_BLOCKS';
commit;
Modify the Database Index Rebuild Run Time
To improve the performance of index recreation, a system parameter can be configured to define the number of minutes the index rebuild should run. The default value is 10 minutes. If the rebuild takes longer than this time, the job will stop. In very large environments, it may be necessary to increase this time to accommodate large indexes.
Use the following procedure to the number of leaf blocks in a database index.
1. Log in to the Portal Server as user aptare.
2. Type the following command:
sqlplus portal/<portal_password>
3. Update the value for MAX_TOT_INDX_JOB_RUN_TIME_MINS system parameter.
UPDATE ptl_system_parameter set param_value=<NEW_VALUE> WHERE param_name='MAX_TOT_INDX_JOB_RUN_TIME_MINS';
commit;
Modify the Maximum Number of Large Database Indexes
To improve the performance of index recreation, a system parameter can be modified to change the number of large database indexes that will be processed in a single run. The default value is 10.
Use the following procedure to the number of leaf blocks in a database index.
1. Log in to the Portal Server as user aptare.
2. Type the following command:
sqlplus portal/<portal_password>
3. Update the value for the MAX_TOT_INDX_JOB_RUN_TIME_MINS system parameter.
UPDATE ptl_system_parameter set param_value=<NEW_VALUE> WHERE param_name='MAX_TOT_INDX_JOB_RUN_TIME_MINS';
commit;