Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1011461
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-03-08 20:39:52

原文地址:dbms_stats相关 作者:hxl

1.分析整个schema,分析整个schema下的表和索引
  1. Begin
  2.   Dbms_Stats.Gather_Schema_Stats(Ownname => 'HXL',
  3.                                  Estimate_Percent => Dbms_Stats.Auto_Sample_Size,
  4.                                  Method_Opt => 'for all columns size repeat',
  5.                                  Degree => 2,
  6.                                  Cascade => True);
  7. End;

A.参数method_opt

METHOD_OPT - The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:
FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column is defined as column := column_name | extension name | extension

integer : Number of histogram buckets. Must be in the range [1,254].
REPEAT : Collects histograms only on the columns that already have histograms
AUTO : Oracle determines the columns to collect histograms based on data distribution and the workload of the columns
SKEWONLY : Oracle determines the columns to collect histograms based on the data distribution of the columns
column_name : name of a column
extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression
The default is FOR ALL COLUMNS SIZE AUTO.

字段收集信息后再如下视图中有体现

USER_TAB_COL_STATISTICS
USER_PART_COL_STATISTICS
USER_SUBPART_COL_STATISTICS

B.参数cascade

The value determines whether or not index statistics are collected as part of gathering table statistics

2.删除schema下的统计信息

  1. Begin
  2.   Dbms_Stats.Delete_Schema_Stats(Ownname => 'HXL');
  3. End;
阅读(175) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~