Question & Answer
Question
How often do the database statistics need to be updated when using IBM Engineering Lifecycle Management?
Cause
Engineering Lifecycle Management (ELM) uses a common off the shelf (COTS) database as its data store. These databases use sophisticated optimization calculations to provide the most expedient way of executing ELM's often complicated queries.
If the database statistics are not up to date, the database query optimizer is choosing an execution plan that is potentially not optimum for the current data.
Updating the statistics too often is also not recommended. Oracle, for example, will invalidate existing execution plans after updating statistics, and will generate new ones over time (although not all at once). Oracle makes the best decisions when statistics are up to date.
How to tell if statistics are stale?
Statistics might not be up to date, but are not stale. Administrators have a wide array of options available to them from the database vendor.
For example, an Oracle database might rely on a percentage of stale data to run the statistics.
This can be appropriate on smaller tables. It might be less appropriate on larger tables, such as REPOSITORY_VERSION.
If you are using a percentage or algorithm to determine the frequency of statistics and experiencing degrading performance, use the advice in the Answer section.
Answer
In general, we recommend that the statistics are updated at least once a week or after significant changes to the data.
Some examples of significant changes are batch deletes; large imports (ReqIf for IBM Engineering Requirements Management; Excel importer for IBM Engineering Test Management; API-driven changes for any of the ELM applications.
Additional information:
For IBM DB2 we recommend disabling auto run stats to minimize hard parses.
Auto stmt stats will update statistics after statement execution if DB2 thinks it needs to, which could be more frequently than daily.
When we did tests with stmt stats enabled, we would see response time spikes because of the cost of statistics collection.
Instead, updating the statistics at least weekly is recommended. This can be achieved through a cron job.
Auto stmt stats will update statistics after statement execution if DB2 thinks it needs to, which could be more frequently than daily.
When we did tests with stmt stats enabled, we would see response time spikes because of the cost of statistics collection.
Instead, updating the statistics at least weekly is recommended. This can be achieved through a cron job.
For more information, see: 7.0 performance: IBM Engineering Requirements Management DOORS Next
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB59","label":"Sustainability Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSF34G","label":"IBM Engineering Lifecycle Management Suite"},"ARM Category":[{"code":"a8m0z000000CbPsAAK","label":"Jazz Team Server-\u003EBest Practices"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
19 October 2022
UID
ibm16827565