Oracle 统计信息

#优化统计信息
Optimizer statistics include the following:

Table statistics

		Number of rows

		Number of blocks

		Average row length

Column statistics

		Number of distinct values (NDV) in column

		Number of nulls in column

		Data distribution (histogram)

		Extended statistics

Index statistics

		Number of leaf blocks

		Levels

		Clustering factor

System statistics

		I/O performance and utilization

		CPU performance and utilization

#enable automatic statistics collection
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
    client_name => 'auto optimizer stats collection', 
    operation => NULL, 
    window_name => NULL);
END;
/

#disable automatic statistics collection
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection', 
    operation => NULL, 
    window_name => NULL);
END;
/ 

#删除表的统计信息 和锁定统计信息
BEGIN
  DBMS_STATS.DELETE_TABLE_STATS('OE','ORDERS');
  DBMS_STATS.LOCK_TABLE_STATS('OE','ORDERS');
END;
/

#Oracle不允许对外部表进行数据操作,如果由于底层数据变化,我们可以删除统计信息,然后重新建。



#数据库的统计信息存在数据字典里面
  dbms_stats
  DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2,                                                   #user_name
   tabname          VARCHAR2,                                                   #table_name
   partname         VARCHAR2 DEFAULT NULL,                                      #分区名称
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   method_opt=>FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
               FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]
   size:{integer | REPEAT | AUTO | SKEWONLY}
   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.
   
   example:method_opt=>'for columns (empno,deptno)'
   	       method_opt=>'for columns (empno+deptno)'
   	       method_opt=>'for columns empno size auto'                                                         
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),     #当为True的时候收集索引的统计信息
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (                   #当为True的时候之前的统计信息不失效
                                     get_param('NO_INVALIDATE'))