7.8 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 event-related archived data (in _ARC tables) that the Behavior Detection UI uses to display events. It is recommended that you do not modify this table. The Behavior Detection Historical Data Copy procedures uses this table to archive event-related data.
  • DLY_PRE_HDC: Use this value to update statistics of the Mantas related tables that contain the event-related information. It is recommended that you do not modify this table. The Historical Data Copy procedures uses this table to archive event-related data.
  • DLY_POST_LINK: Use this value to update statistics of the Mantas 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.
  • EST_PART_STATS: Estimates statistics on only the newest partition in the table.
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.
  • 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 the following properties:
  • 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, we have to manage the Oracle database statistics periodically. Following are the sample commands.

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

Example: ANALYZE table KDD_CASES compute statistics;

We can also perform whole schema analyze periodically.