oracles数据库优化-统计信息

1 统计信息分类

oracle数据库里面得统计信息可以分为一下6类

1.  表统计信息。
2.  索引统计信息。
3. 列统计信息。
4.  系统统计信息。
5. 数据字典统计信息。
6. 内部对象统计信息。

表统计信息用于描述表得详细信息，例如表得记录数、表块数量、平均行长度等。

索引统计信息描述索引得详细信息，索引层数、叶子块得数量、聚簇因子等信息。

列统计信息描述表列得详细信息，列得distinct值，列得null值，列得最小值和最大值，还有直方图。

系统统计信息，数据库服务器统计信息。

数据字典统计信息，tab$和col$等数据字典统计信息。

内部对象统计信息，包括x$BH等不占用存储空间，x$表实际是oracle自定义得内存结构。

exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

使用命令：
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

For performance reasons, the Oracle Database does not populate these views immediately when the actual modifications occur. 
Run the FLUSH_DATABASE_MONITORING_INFO procedure in the DIMS_STATS PL/SQL package to populate these views 
with the latest information.

每15分钟,smon会刷新统计结果到数据字典中,oracle使用这些表的数据去判断表的统计数据是否过期,
如果当表的数据改变超过10%,oracle就认为该表的统计数据已经过期.

Oracle 10g默认表是 monitoring,即使表在建立的时候指定是nomonitoring也是会被忽忽略的
这是由参数：statistics_level决定的， 默认值=TYPICAL
如果设置为BASIC将disable monitoring

系统将自动收集表的统计信息(由GATHER_STATS_JOB完成，
可以通过 EXEC DBMS_SCHEDULER.DISABLE('GATHER_SCHEDULER_JOB')来取消系统自动收集)，
且每3个小时由SMON写入到数据字典（USER_TABLES.NUM_ROWS）里。

2 统计信息收集方案

采集统计信息主要包括2种方案，1 analyze和dbms_stats包。1-3，5信息2种方案均可，对于系统统计信息和内部对象只能使用dbms_stats包,

10g版本以后，创建表和索引对象后，自动采集统计信息。

analyze table t1 compute statistics;

analyze table t1 compute statistics for table;  --计算模式。

analyze table t1 compute statistics for columns id1,id2,name;

analyze index idx1 compute statistics ;

analyze table t1 estimate statistics sample 15 percent for table; --估算模式。

删除统计信息

analyze table t1 delete statistics;

DBMS_STATS可以理解为analyze的增强版本。主要包括以下存储过程:

gather_table_stats、gather_index_stats、gather_schema_stats、gather_database_stats。

gather_table_stats参数介绍

参数说明:
ownname:要分析表的拥有者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引有用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle绝定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下:
for all columns:统计所有列的histograms.
for all indexed columns:统计所有indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in the data
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascace:是收集索引的信息.默认为falase.
stattab指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息 表的拥有者.以上三个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.

3 分区表统计信息-存在全局统计信息得意义

dbms_stats种参数graulatity专用于全局统计信息得采集策略。

1 GLOBAL（表）2 partition （分区）3 subpartition（子分区）4 default(表+分区，10.2以上作废），5 global+partition 表+分区。 6 all（表+分区+子分区）7 auto（自动采集）

2 graulatity指定为较低级时，则更低级别的统计信息保持不变。

11.2 新特性 统计信息

4 多列统计信息

exec dbms_stats.gather_table_stats(OWNER=>'SCOTT',TABLE_NAME=>'HR',METHOD_OPT=>'for columns(id1,id2) size auto',estimate_percent=>100);

select extension_name,extension from dba_stat_extensions;

 

5 表达式统计信息

exec dbms_stats.gather_table_stats(OWNER=>'SCOTT',TABLE_NAME=>'HR',METHOD_OPT=>'for columns(upper(id)) size auto',estimate_percent=>100);

select extension_name,extension from dba_stat_extensions;

5 表达式统计信息