当前位置：首页 -> AI编程基础 -> 数据库编程

使用DBMS_STATS来收集统计信息(三)

2014-11-24 03:16:03 · 作者: · 浏览: 29
标签: 使用 DBMS_STATS 收集 统计 信息
bled, any change (insert, update, delete, direct load, truncate, etc.) that occurs on a table will be tracked in the SGA. This information is incorporated into the data dictionary by the SMON process at a pre-set interval (every 3 hours in Oracle 8.1.x, and every 15 minutes in Oracle 9i). The information collected by this monitoring can be seen in the DBA_TAB_MODIFICATIONS view. Oracle 9i introduced a new function in the DBMS_STATS package called: FLUSH_DATABASE_MONITORING_INFO. The DBA can make use of this function to flush the monitored table data more frequently. Oracle 9i will also automatically call this procedure prior to executing DBMS_STATS for statistics gathering purposes. Note that this function is not included with Oracle 8i.（使用DBMS_STATS，DBA可以指定某张表，或者整个用户，或者这个数据库自动监视数据的变化。当发生任何变化时（增删改查，装载，truncate等），oracle会在sga中自动记录数据的变化，随后SMON进程会将这些变化与已有的统计信息进行合并（oracle8每3个小时合并一次，oracle9之后没15分钟合并一次）。我们可以通过DBA_TAB_MODIFICATIONS视图来查看已经发生的变化。我们也可以直接使用9i引入的新函数FLUSH_DATABASE_MONITORING_INFO来将信息手动合并到已有统计信息中。在9i中，oracle会在每次调用DBMS_STATS时，首先调用FLASH_DATABASE_MONITORING_INFO函数。）DBMS_STATS provides a more efficient, scalable solution for statistics gathering and should be used over the traditional ANALYZE command which does not support features such as parallelism and stale statistics collection.（DBMS_STAS提供了一种更高效，可伸缩的信息统计方式，我们优先使用DBMS_STATS,而不使用ANNLYZE）Use of table monitoring in conjunction with DBMS_STATS stale object statistics generation is highly recommended for environments with large, random and/or sporadic data changes. These features allow the database to more efficiently determine which tables should be re-analyzed versus the DBA having to force statistics collection for all tables. Including those that have not changed enough to merit a re-scan)（优先使用dbms_stats）

What gets collected

Table Statistics

Oracle collects the following statistics for a table. Statistics marked with an asterisk are always computed exactly. Table statistics, including the status of domain indexes, appear in the data dictionary views USER_TABLES, ALL_TABLES, and DBA_TABLES in the columns shown in parentheses.

oracle可以为表收集如下的统计信息，部分统计信息始终是准确的（带*）。表的统计信息（包括domain index）都可以在 USER_TABLES, ALL_TABLES, and DBA_TABLES等视图的如下字段中可以查看到。

Number of rows (NUM_ROWS)记录数量

* Number of data blocks below the high water mark (that is, the number of data blocks that have been formatted to receive data, regardless whether they currently contain data or are empty) (BLOCKS) 位于高水位线之下的数据块数量（在mssm中，oracle通过freelist管理段，当段空间不足时，oracle会分配新的数据块到高水位线下，并进行格式化后放到freelist上以备后用，此时高水位线下的块都是格式化的，但可能并没有被使用。在ASSM下，段的管理模式发生了变化，当空间不足时，oracle会分配数据块到高水位线下，但是并不会立即格式化，而是在使用时才格式化，此时引入了另一个概念low 高水位线，lowhwm下的块都是格式化的，lowhwm和hwm之间的数据库可能是格式化也可能并未格式化，当lowhwm和hwm之间的数据块全部格式化时，lowhwm上移到hwm的位置），

* Number of data blocks allocated to the table that have never been used (EMPTY_BLOCKS)空闲数据块的数量,HWM之上的数据块

Average available free space in each data block in bytes (AVG_SPACE) 平均每个数据上的空闲空间，blocks+empty_blocks

Number of chained rows. [Not collected by DBMS_STATS] (CHAIN_COUNT)发生chainrow的记录数量

Average row length, including the row's overhead, in bytes (AVG_ROW_LEN)平均每行的长度，包含overhead信息

Index Statistics

Oracle collects the following statistics for an index. Statistics marked with an asterisk are always computed exactly. For conventional indexes, the statistics appear in the data dictiona