Gather Statistics with DBMS_STATS

http://www.idevelopment.info/data/Oracle/DBA_tips/Tuning/TUNING_17.shtml

Gather Statistics with DBMS_STATS

by Jeff Hunter, Sr. Database Administrator

Contents

Overview Missing statistics Analyze vs. DBMS_STATS What gets collected? Where are the statistics stored? Compute statistics vs. Estimate statistics DBMS_STATS functions and variable definitions DBMS_STATS in action (Examples) Automated table monitoring and stale statistics gathering example How to determine if dictionary statistics are RDBMS-generated or user-defined

Overview

Oracle's cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statement's predicate chooses) of predicates and to estimate the "cost" of each execution plan. The COB will use the selectivity of a predicate to estimate the cost of a particular access method and to determin the optimal join order.

Statistics are used to quantify the data distribution and storage characteristics of tables, columns, indexes and partitions. The COB uses these statistics to estimate how much I/O and memory are required to execute a SQL statement using a particular execution plan. Statistics are stored in the data dictionary, and they can be exported from one database and imported into another. Situations in where you would want to perform. this, might be to transfer production statistics to a test system to simulate the real environment, even though the test system may only have small samples of the data.

In order to give the Oracle cost-based optimizer the most up-to-date information about schema objects (and the best chance for choosing a good execution plan) all application tables and indexes to be accessed must be analyzed. New statistics should be gathered on schema objects that are out of date. After loading or deleting large amounts of data would obviously change the number of rows. Other changes like updating a large amount of rows would not effect the number of rows, but may effect the average row length.

Statistics can be generated with the ANALYZE statement or with the package DBMS_STATS (introduced in Oracle8i). The DBMS_STATS package is great for DBA's in managing database statistics only for use by the COB. The package itself allows the DBA to create, modify, view and delete statistics from a standard, well-defined set of package procedures. The statistics can be gathered on tables, indexes, columns, partitions and schemas, but note that it does not generate statistics for clusters.

DBMS_STATS provides a mechanism for you to view and modify optimizer statistics gathered for database objects.The statistics can reside in two different locations:

The dictionary. A table created in the user's schema for this purpose.

Only statistics stored in the dictionary itself have an impact on the cost-based optimizer.

When you generate statistics for a table, column, or index, if the data dictionary already contains statistics for the object, then Oracle updates the existing statistics. Oracle also invalidates any currently parsed SQL statements that access the object.

The next time such a statement executes, the optimizer automatically chooses a new execution plan based on the new statistics. Distributed statements issued on remote databases that access the analyzed objects use the new statistics the next time Oracle parses them.

When you associate a statistics type with a column or domain index, Oracle calls the statistics collection method in the statistics type if you analyze the column or domain index.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25243263/viewspace-696201/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25243263/viewspace-696201/