Database Guide › Database Maintenance › Collecting Statistics

Collecting Statistics

Important! You must regularly collect statistics for all tables, columns and indexes in the CA DLP database. Failure to do so may significantly slow down searches for captured data.

For optimum performance, indexes require additional maintenance. The most important aspect of index maintenance is statistics. Both Oracle and SQL Server use a number of statistics, including ‘column value distribution’ statistics, to determine the suitability of an index for a particular database query. You must keep these statistics up to date to verify the query processor operates reliably. See your database documentation for information about maintaining database statistics.

Oracle: Implement the Wgn_Stats Package

For Oracle databases, you must deploy the wgn_stats statistics-gathering package. This ensures appropriate statistics are gathered for optimal performance. See also the Database Statistics Reference Guide, included in the CA DLP bookshelf.

We acknowledge that many DBAs prefer to use their own statistics collection scripts. If you do use your own script, it is essential that you understand what the wgn_stats package is designed to do. Specifically, wgn_stats:

• Gathers all the required global, partition and index statistics.
• Sets key table statistics that work around limitations in the Oracle optimizer.

  Without these statistics in place, the optimizer significantly underestimates certain key join cardinalities and produces inappropriate query plans that result in poor performance.

• Removes and locks statistics from global temporary tables.
• Generates histograms on key date columns to compensate for skew data generated by spam.

Note: The wgn_stats package only gathers statistics on standard CA DLP tables. If you have implemented any custom tables within the CA DLP schema, ensure that statistics are also gathered on these tables.

Typical Wgn_Stats Implementation

When you first deploy wgn_stats, you must execute wgn_stats to gather statistics on all partitions.

To gather initial statistics on all partitions

In SQL Plus, connect to the CA DLP database as the schema owner and execute the following command:

execute WGN_STATS.GATHER_ALL_STATS(
  P_def_audit_samp_pct=>50,
  P_def_non_cap_samp_pct=>100,
  P_def_cap_samp_pct=>10,
  P_num_partitions=>-1, -- All partitions
  P_degree=>8 -- or maximum available
  );