oracle 统计信息是什么
上一篇 / 下一篇 2009-01-04 22:52:55 / 个人分类:Oracle
- 文件版本: V1.0
- 开发商: 本站原创
- 文件来源: 本地
- 界面语言: 简体中文
- 授权方式: 免费
- 运行平台: Win9X/Win2000/WinXP
1. 什么是统计信息
-maE){$_0统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最后选择出成本最小的计划。
7pP/zr*?E4o0统计信息是存放在数据字段表中的,如tab$。一般我们从数据字段视图中察看统计信息状况,如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS 等。51Testing软件测试网W$e0]jP#h0ns
列举下DBA_TABLES,DBA_INDEXES 视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值,否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间,大家可以根据这个字段,快速的了解最近一次统计信息搜集的时间。51Testing软件测试网.\ G%i(e![DnpX4IQH
51Testing软件测试网Xjj {i WIU
--dba_tables51Testing软件测试网!Mr,N,d4Wb
NUM_ROWS* NUMBER Number of rows in the table
i2A6XLut{4V-_0BLOCKS* NUMBER Number of used data blocks in the table51Testing软件测试网(y%R.x&t{PfSp
EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table51Testing软件测试网 ZhI|#X@/a@l
AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table
*r5^1B0vs/r0CHAIN_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
UV6GrU7G)U;y0AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes
yb1W#Y3u/n4m0LAST_ANALYZED DATE Date on which this table was most recently analyzed51Testing软件测试网 { _ Dmcq/q&wIx
51Testing软件测试网5_Bl-rn
51Testing软件测试网-eIUv(b
--dba_indexes
,N#\3B ?Y ] E0BLEVEL* 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.51Testing软件测试网4f0D'u8Eq'amy
LEAF_BLOCKS* NUMBER Number of leaf blocks in the index51Testing软件测试网'u x*Q2x)fl!O
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)
rz\,T0m0AVG_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.51Testing软件测试网 B8k*yu-[6Yw)[N F)W?v
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 columns.
K4E c4nq F b%krJ0CLUSTERING_FACTOR* NUMBER Indicates the amount of order of the rows in the table based on the values of the index.51Testing软件测试网ed/gXleu
• If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.51Testing软件测试网5wH,~(J{g'J]Y
• If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks51Testing软件测试网Hu E'b'?8z5P_"}
NUM_ROWS NUMBER Number of rows in the index
-maE){$_0统计信息主要是描述数据库中表,索引的大小,规模,数据分布状况等的一类信息。比如,表的行数,块数,平均每行的大小,索引的leaf blocks,索引字段的行数,不同值的大小等,都属于统计信息。CBO正是根据这些统计信息数据,计算出不同访问路径下,不同join 方式下,各种计划的成本,最后选择出成本最小的计划。
7pP/zr*?E4o0统计信息是存放在数据字段表中的,如tab$。一般我们从数据字段视图中察看统计信息状况,如DBA_TABLES,DBA_INDEXES,DBA_TAB_COL_STATISTICS, DBA_TAB_HISTOGRAMS 等。51Testing软件测试网W$e0]jP#h0ns
列举下DBA_TABLES,DBA_INDEXES 视图中表示统计信息的一些字段。这些字段只有搜集过统计信息之后才有值,否则是空的。这些字段中last_analyzed 字段表示上次统计信息搜集的时间,大家可以根据这个字段,快速的了解最近一次统计信息搜集的时间。51Testing软件测试网.\ G%i(e![DnpX4IQH
51Testing软件测试网Xjj {i WIU
--dba_tables51Testing软件测试网!Mr,N,d4Wb
NUM_ROWS* NUMBER Number of rows in the table
i2A6XLut{4V-_0BLOCKS* NUMBER Number of used data blocks in the table51Testing软件测试网(y%R.x&t{PfSp
EMPTY_BLOCKS* NUMBER Number of empty (never used) data blocks in the table51Testing软件测试网 ZhI|#X@/a@l
AVG_SPACE* NUMBER Average amount of free space, in bytes, in a data block allocated to the table
*r5^1B0vs/r0CHAIN_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
UV6GrU7G)U;y0AVG_ROW_LEN* NUMBER Average length of a row in the table in bytes
yb1W#Y3u/n4m0LAST_ANALYZED DATE Date on which this table was most recently analyzed51Testing软件测试网 { _ Dmcq/q&wIx
51Testing软件测试网5_Bl-rn
51Testing软件测试网-eIUv(b
--dba_indexes
,N#\3B ?Y ] E0BLEVEL* 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.51Testing软件测试网4f0D'u8Eq'amy
LEAF_BLOCKS* NUMBER Number of leaf blocks in the index51Testing软件测试网'u x*Q2x)fl!O
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)
rz\,T0m0AVG_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.51Testing软件测试网 B8k*yu-[6Yw)[N F)W?v
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 columns.
K4E c4nq F b%krJ0CLUSTERING_FACTOR* NUMBER Indicates the amount of order of the rows in the table based on the values of the index.51Testing软件测试网ed/gXleu
• If the value is near the number of blocks, then the table is very well ordered. In this case, the index entries in a single leaf block tend to point to rows in the same data blocks.51Testing软件测试网5wH,~(J{g'J]Y
• If the value is near the number of rows, then the table is very randomly ordered. In this case, it is unlikely that index entries in the same leaf block point to rows in the same data blocks51Testing软件测试网Hu E'b'?8z5P_"}
NUM_ROWS NUMBER Number of rows in the index