7.7 Database Statistics Management

The system uses a script to manage Oracle database statistics. These statistics determine the appropriate execution path for each database query.

Logs

The log.category.RUN_STORED_PROCEDURE property controls logging for the process.location entry in the <OFSAAI Installed Directory>/database/db_tools/mantas_cfg/categories.cfg file.

Using Database Statistics Management

The system calls the script as part of nightly processing at the appropriate time and with the appropriate parameters.
analyze_mantas.sh <analysis_type> [TABLE_NAME]
The <analysis_type> parameter can have one of the following values:
  • DLY_POST_LOAD: Use this value to update statistics on tables that the system just loaded (for BUSINESS and MARKET related tables).
  • ALL: Use this once per week on all schemas.
  • DLY_POST_HDC: Use this value to update statistics of the alert-related archived data (in _ARC tables) that the Behavior Detection UI uses to display alerts. It is recommended that you do not modify this table. The Behavior Detection Historical Data Copy procedures uses this table to archive alert-related data.
  • DLY_PRE_HDC: Use this value to update statistics of the Oracle-related tables that contain the alert-related information. It is recommended that you do not modify this table. The Behavior Detection Historical Data Copy procedures uses this table to archive alert-related data.
  • DLY_POST_LINK: Use this value to update statistics of the Oracle- related tables that contain network analysis information. Run this option at the conclusion of the network analysis batch process.

The [TABLE_NAME] parameter optionally enables you to analyze one table at a time. This allows scheduling of the batch at a more granular level, analyzing each table as processing completes instead of waiting for all tables to complete before running the analysis process.

The metadata in the KDD_ANALYZE_PARAM table drive these processes. For each table this table provides information about the method of updating the statistics that you should use for each analysis type. Valid methods include:
  • EST_STATS: Performs a standard statistics estimate on the table.

    Note:

    For the EST_STATS and EST_PART_STATS parameters, the default sample size that the analyze procedure uses is now based on DBMS_STATS.AUTO_SAMPLE_SIZE.
  • EST_PART_STATS: Estimates statistics on only the newest partition in the table.
  • IMP_STATS: Imports statistics that were previously calculated. When running an ALL analysis, the system exports statistics for the tables for later use.

Failure to run the statistics estimates can result in significant database performance degradation. These scripts connect to the database using the user that the utils.database.username property specifies, in the <OFSAAI Installed Directory>/ database/db_tools/mantas_cfg/install.cfg file.

The install.cfg file also contains schema.mantas.owner. The system derives schema name from this property.

For the ATOMIC Schema, there is no separate script for managing Oracle database statistics. But for improved query performance, you must manage the Oracle database statistics periodically.

To analyze table wise use, use the following commands: ANALYZE table <Table name> compute statistics;

ANALYZE table KDD_ACCOUNT compute statistics;

You can also perform whole schema analyze periodically.