dbms_stats相关

1.分析整个schema,分析整个schema下的表和索引

Begin
  Dbms_Stats.Gather_Schema_Stats(Ownname => 'HXL',
                                 Estimate_Percent => Dbms_Stats.Auto_Sample_Size,
                                 Method_Opt => 'for all columns size repeat',
                                 Degree => 2,
                                 Cascade => True);
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下的统计信息

Begin
  Dbms_Stats.Delete_Schema_Stats(Ownname => 'HXL');
End;