How to speed up the DB maintenance process

Document ID : KB000089858
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Affects Release version(s): null

How to speed up the DB maintenance process
Resolution:
Detailed Description and Symptoms

DB maintenance can sometimes be very time-consuming, hence affecting your overall system performance. However, this negative impact can significantly be decreased by applying the advice contained in this article.

Disclaimer : the steps listed below should not be applied if you need to keep records of archived data. Please be extremely careful before applying them.


Here are a few hints to speed up the maintenance process :

1) First thing : do you use the DB Archive utility ? Because if's not mandatory to use it if you do not need to consult data after it's been removed from the DB.

2) In the DB Reorg's ini file : set "No_archive_check=1" : that parameter is used to check if the data that is being reorganized (flagged) has already been archived. Therefore, by setting it to 1, no verification will be made.

3) In the DB Unload's ini fileĀ  :

- set "No_archive_check=1"
- Add "Suppress_output=1" in the [TRANSPORT] section : output is all data that is subject to be deleted by the Reorg tool. This setting means that no output files will be generated.

3bis) If you are using an Oracle Database, BATCH.NOWAITspeeds a lot the process: it tells the Oracle DB to issuestatements that are executed faster (bulked statements instead of one by onestatements).

You may implement it by setting the connection string like in the example below :

SQLDRIVERCONNECT=ODBCVAR=NNJNIORO,DSN=UC4;UID=uc4;PWD=--1037B2E22BF022EBE2;
SP=NLS_LANGUAGE=AMERICAN,NLS_TERRITORY=AMERICA,CODESET=WE8ISO8859P15,commit_write='BATCH,NOWAIT'

4) This section is optional and not Automic related, but it can prove useful in some cases : you can perform a Database Shrink + Index rebuilding. Please align with your DBA in order to do so.