devan05

CA DLP Tuesday Tip: Collecting Oracle Stats with CA DLP (DataMinder)

Discussion created by devan05 Employee on Feb 28, 2012
Latest reply on Feb 28, 2012 by Chris_Hackett
CA DLP Tuesday Tip for 28 February 2012 published by Andrew Devine Snr. Support Engineer.

In order for the Oracle CBO (Cost Based Optimizer) to make an intelligent decision about the best execution plan for your SQL, it must have information about the table and indexes that participate in the query. When the CBO knows the size of the tables and the distribution, cardinality, and selectivity of column values, the CBO can make an informed decision and almost always generates the best execution plan. CA DLP Ships with its own CBO (Cost Based Optimizer) statistics gathering package, called "WGN_STATS".

WGN_STATS is a standard pl/sql package, which installs automatically in the CA DLP r14.0 schema to provide a standard framework to gather Oracle cost based optimizer (CBO) statistics. This package is available DBA's to call as part of the normal operational procedures for maintaining the database, removing the necessity of designing and implementing a process for CA DLP. It also allows CA to improve customer support by having a single point of reference when discovering how a customer is gathering CBO statistics.

Note: The statistics gathered by this package are specific to CA DLP and do not replace the gathering of system statistics (that is, Workload Statistics for system performance) or the Oracle Data Dictionary statistics.

The wgn_stats package provides comprehensive configuration options, which reduce the need for package customizations. However, provision has still been made for customization if required.

For full implementation details please refer to the "Database Statistics for Database Schema 3.50 Reference Guide Release 14.0" (DLP_DBStats_ENU.pdf) available to download from the CA DLP r14.0 Bookshelf on the CA Support Portal (Support.ca.com)

Outcomes