overview Oracles cost-based optimizer (COB) uses statistics to calculate the selectivity (the fraction of rows in a table that the SQL statements predicate chooses) of predicates and to estimate the cost of each execution plan. The COB wil

overview

Oracle's cost-based optimi本文来源gaodai$ma#com搞$$代**码)网@zer (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

ORACLE COB使用统计信息来计算查询谓词的选择性，并借此评估执行计划的成本。然后COB会使用谓词的选择性来评估特定的访问路径的成本并确定最优的连接顺序。

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。

统计信息被用来量化表、列、索引和分区的数据分布特征和存储特征。COB使用统计信息来评估SQL语句采用某特定执行计划时的内存和输入输出量。统计信息存储在数据字典视图中，它们可以被导出和导入，例如，我们可以将生产环境的统计信息导入到测试环境中以便模拟真环境，即使测试环境具有较小的数据采样。

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.

为了给ORACLE COB提供最新的关于模式对象的信息（从而可以选择最优执行计划），所有被访问的应用表和索引都需要被分析。如果对象的统计信息已经过时，我们需要更新统计信息，例如，在进行大量的装载或者删除数据后，或者对表数据进行了大量的更新操作。

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.

统计信息可以通过ANALYZE命令或者DBMS_STATS包来收集。在COB模式下，DBMS_STATS包是DBA管理统计信息的有力工具。DBMS_STATS包允许管理员以调用过程的方式创建，编辑，查看和删除统计信息。它可以收集表、索引、列、分区和模式的统计信息，但是它不可以生成cluster的统计信息；

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

dbms_stats包为我们提供了查看和编辑统计信息的机制。统计信息可以存储在2个不同的位置：数据字典视图和用户自定义的表中。

喜欢 (0)赏

[搞代码]

分享 (0)