Various options are available when performing a manual database diagnosis. We have already discussed option Erweiterte Diagnose. Today’s article focuses on option Schlüsselanalyse und -reparatur
When should an analysis be carried out
Key analysis should be selected if problems repeatedly occur in a database relating to keys that cannot be solved by reorganizing the key(s) in question. Key errors are expressed in messages such as “Key without data set“, “Key not available” or the diagnosis detects deviations in key trees.
How the key analysis works
With Key analysis and repair, all data records in the database are read sequentially. An integrity check of the key values is carried out for each data record. The system also checks whether a corresponding data record exists for each key. Key errors are automatically corrected during the analysis. In addition, incomplete data records are automatically removed from the database.
What must be considered
Due to the fact that the entire database is run through, the duration of the analysis depends on the following factors:
- Number of data records in the database
- Size of the database buffer used
- Performance of the storage system
Depending on the environment, the analysis can take several hours or even days.
Recommendations
As the duration of an analysis can only be estimated very roughly and it is not possible to work in the database during the process, the measure should first be carried out on a copy of the database.
Ideally, the database should be located on a different storage system than the production database, as a high I/O load is generated during the execution. To ensure that the analysis can be carried out within a reasonable period of time, the use of a high-performance storage system is a prerequisite. From a database size of more than 10 GB, the database should be located on an SSD storage system. In addition, a sufficiently large database buffer should be selected. This should be at least ?? % of the database size.
The further procedure depends on the result of the analysis. If differences occur in only a few keys, a correction by reorganizing the relevant keys may be faster than an analysis on the productive database. It is also possible to work in the database during the reorganization. A sufficiently large database buffer must also be ensured for this measure.