Database Guide › Database Maintenance › Oracle: Implement the Wgn_Stats Package

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.

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 DataMinder tables. If you have implemented any custom tables within the CA DataMinder schema, ensure that statistics are also gathered on these tables.