ORACL 统计信息包-DBMS_STATS.docVIP

ORACLE 统计信息包-DBMS_STATS ORACLE统计信息包-DBMS_STATS 1.什么是统计信息 统计信息主要是描述数据库中表，索引的大小，规模，数据分布状况等的一类信息。比如，表的行数，块数，平均每行的大小，索引的leaf blocks，索引字段的行数，不同值的大小等，都属于统计信息。CBO正是根据这些统计信息数据，计算出不同访问路径下，不同join 方式下，各种计划的成本，最后选择出成本最小的计划。 统计信息是存放在数据字段表中的，如tab$。一般我们从数据字段视图中察看统计信息状况，如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS 等。 列举下DBA_TABLES,DBA_INDEXES 视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值，否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间，大家可以根据这个字段，快速的了解最近一次统计信息搜集的时间。 --dba_tables NUM_ROWS* NUMBER Number of rows in the table BLOCKS* NUMBER Number of used data blocks in the table EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table CHAIN_CNT* NUMBER Number of rows in the table that are chained from one data block to another, or which have migrated to a new block, requiring a link to preserve the old ROWID AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes LAST_ANALYZED DATE Date on which this table was most recently analyzed --dba_indexes BLEVEL* NUMBER B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the same. LEAF_BLOCKS* NUMBER Number of leaf blocks in the index DISTINCT_KEYS* NUMBER Number of distinct indexed values. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is the same as the number of rows in the table (USER_TABLES.NUM_ROWS) AVG_LEAF_BLOCKS_PER_KEY* NUMBER Average number of leaf blocks in which each distinct value in the index appears, rounded to the nearest integer. For indexes that enforce UNIQUE and PRIMARY KEY constraints, this value is always 1. AVG_DATA_BLOCKS_PER_KEY* NUMBER Average number of data blocks in the table that are pointed to by a distinct value in the index rounded to the nearest integer. This statistic is the average number of data blocks that contain rows that contain a given value for the indexed colum