TOP

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

2014-11-24 03:16:03 来源: 作者: 【大中小】浏览:4次

lock SAMPLE_SIZE : Sample defined in ESTIMATE method (0 if COMPUTE) LAST_ANALYZED : Timestamp of last analysis GLOBAL_STATS (**) : For partitioned indexes, YES means statistics are collected for the INDEX as a whole NO means statistics are estimated from statistics on underlying index partitions or subpartitions USER_STATS (**) : YES if statistics entered directly by the user PCT_DIRECT_ACCESS (**)(GP) : For secondary indexes on IOTs, percentage of rows with VALID guess（可以通过alter index index_name update block references来更新）

Column level statistics can be retrieved from:

DBA_TAB_COLUMNS - (all versions)DBA_TAB_COL_STATISTICS - (Version 8.X onwards)DBA_PART_COL_STATISTICS - (Version 8.X onwards)DBA_SUBPART_COL_STATISTICS - (Version 8.1 onwards)

The last three views extract statistics data from DBA_TAB_COLUMNS.（后三个视图是从DBA_TAB_COLUMNS获取数据）

Columns to look at are:

  NUM_DISTINCT                 : Number of distinct values 
  LOW_VALUE                    : Lowest value  
  LOW_VALUE                    : Highest value  
  DENSITY                      : Density 
  NUM_NULLS                    : Number of columns having a NULL value 
  AVG_COL_LEN                  : Average length in bytes 
  NUM_BUCKETS                  : Number of buckets in histogram for the column    
  SAMPLE_SIZE                  : Sample defined in ESTIMATE method (0 if COMPUTE) 
  LAST_ANALYZED                : Timestamp of last analysis 
  (**)GLOBAL_STATS             : For partitioned tables, YES means statistics  
                                 are collected for the TABLE as a whole 
                                 NO means statistics are estimated from statistics 
                                 on underlying table partitions or subpartitions 
  (**)USER_STATS               : YES if statistics entered directly by the user

Compute statistics vs. Estimate statistics

Both computed and estimated statistics are used by the Oracle optimizer to choose the execution plan for SQL statements that access analyzed objects. These statistics may also be useful to application developers who write such statements.

无论是采用compute还是采用estimat的方式计算统计信息，优化器都会根据这些信息来选择执行计划。程序员也可以根据这些统计信息来编写sql语句。

COMPUTE STATISTICS

COMPUTE STATISTICS instructs Oracle to compute exact statistics about the analyzed object and store them in the data dictionary.
When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.

To perform an exact computation, Oracle requires enough space to perform a scan and sort of the table. If there is not enough space in memory, then temporary space may be required. For estimations, Oracle requires enough space to perform a scan and sort of only the rows in the requested sample of the table. For indexes, computation does not take up as much time or space, so it is best to perform a full computation.

Some statistics are always computed exactly, such as the number of data blocks currently containing data in a table or the depth of an index from its root block to its leaf blocks.

Use estimation for tables and clusters rather than computation, unless you need exact values. Because estimation rarely sorts, it is often much faster than computation, especially for large tables.

当COMPUTE STATISTICS时，oracle会精确计算被分析对象的统计信息，并将其存储在数据字典中。oracle会扫描整个对象来获取数据，并根据这些数据计算统计信息。对于这种方式，基本是轻微的变化也会被计算在内。因为整个对象都会被扫描，因此对象越大就会需要越多的工作量来完成统计。

为了完成精确统计，oracle需要足够的空间来执行扫描和排序

首页上一页 2 3 4 5 6 7 8 下一页尾页 5/11/11
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：各类数据库通过sql查询表字段的注..	下一篇：为过程或函数指定了过多的参数

帐　　号:

密码: (新用户注册)

验证码:

表　　情:

内　　容: