Table Analysis

SQL databases use statistical data to further optimize query planning. In order to work correctly periodic statistic analysis must run on database tables. The frequency and trigger conditions of this depends on the vendor in question. Usually there are some user configurable values to fine tune the behavior.

To diagnose issues which might caused by invalid statistics analysis configuration, codebeamer provides some administrative tools to check the current state of the database.

Note: these tools are not available for all database engines. Currently only Postgres and Oracle are supported.

Postgres statistics

In case running codebeamer with the Postgres database engine, the System Admin > Database Performance monitoring job page has an additional "Table Analysis" link available in the action bar. Clicking this link leads to the following page:

The table shows all application tables, the last execution of statistics analysis, and whether the statistics collection process is enabled on the table. This table is also present in the Excel file exported with the System Admin > Database Performance monitoring job > Export report action on a separate sheet tab.

Note: even if most cells under "Last Analysis" column is empty, it does not necessarily means that the statistics collection is configured improperly. Postgres creates various metadata about table changes which is then used to decide when the next analysis needs to be run. In case of small or seldom modified tables, Postgres might not even schedule analysis. For more details please see the Postgres 12 user manual page: Routine Vacuuming - Updating planner statistics

Oracle statistics

Please visit Updating Oracle schema statistics periodically to read about how to configure the automatic statistics update process on Oracle databases.

The status of this can be examined by clicking the System Admin > Database Performance monitoring job > Export report link and opening the resulting Excel file (the Oracle statistics data is visible on a separate sheet tab).