手动收集统计信息

ORACLE统计信息：

ORACLE的统计信息方便了我们在优化时的决策作用，比如说我们希望一个关联sql语句现在要以一个小表作为驱动表，但是我们还不能去count(*)去检验一个表的大小，我们这个时候就可以使用数据库的统计信息；

收集表的统计信息：

我们查看一个表的统计信息，可以通过视图dba_Tables;

select table_name,owner,num_rows,blocks,last_analyzed from dba_tables where table_name in ('DEPT','SALGRADE');

我们可以看到当前这两个表的信息是没有的，我们手动去让他执行统计信息

dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname='DEPT');

我们看一下官方对DBMS_STATS.GATHER_TABLE_STATS()过程的解释

GATHER_TABLE_STATS Procedure

This procedure gathers table and column (and index) statistics. It attempts to parallelize as much of the work as possible, but there are some restrictions as described in the individual parameters.

Syntax

DBMS_STATS.GATHER_TABLE_STATS (

ownname VARCHAR2,

tabname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

stattype VARCHAR2 DEFAULT 'DATA',

force BOOLEAN DEFAULT FALSE);

常用的有下面的几个，其他的自己查阅：

ownname : 表所在的用户/

tabname：表名/

partname：分区表的分区名/

estimate_percent：采样表行的百分比，null为全表分析，取值的范围在[0.000001，100]

block_sapmple：是否适用随机块采样代替随机行采样，官方指出，随机块采样更有效，如果数据不是随机分布在磁盘，那么样本值可能是和其他值有相关性，需要估计统计值。

force：即使表锁了，也要统计。

defree：设置并行度。

cascade：收集索引信息。该值默认是false。

method_opt：决定信息统计的粒度,有三个取值：

for all columns :统计所有列

for all indexed columns :统计所有索引

for all hidden columns:统计所有隐藏列

所以我们如果想要统计一个表的索引信息和表信息，那么可以

exec dbms_stats.gather_table_stats(ownname=>'schema',rabname=>'table_name',method_opt=>'for all indexed column',cascade=>true);

收集索引信息

select table_name,index_name,blevel,num_rows,leaf_blocks,last_analyze from user_index;

dbms_stats,gather_index_status(ownname=>'TEST',indname=>'NOWID');

这里说句题外话，我们看到这里我们通过user_index查到了我们的blevel，这个就是我们的索引高度，0表示根块和叶子节点块在同一个级别，每当数据有插入的时候，当叶子节点块装不下数据之后，就会产生分裂，就是我们上章说的“五五开”和“新旧分离”。

看下官方对DBMS_STATS.GATHER_TABLE_STATS()的解释：

DBMS_STATS.GET_INDEX_STATS (

ownname VARCHAR2,

indname VARCHAR2,

partname VARCHAR2 DEFAULT NULL,

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

ext_stats OUT RAW,

stattypown OUT VARCHAR2 DEFAULT NULL,

stattypname OUT VARCHAR2 DEFAULT NULL,

statown VARCHAR2 DEFAULT NULL,

cachedblk OUT NUMBER,

cachehit OUT NUMBER);

get和gather是一样的。

indname：所在表的索引名称。

partname：分区表分区表名。

stattab：用户统计标识，表示从何处检索统计信息，如果stattab为null，则直接从字典当中检索。--这个目前我还真没用过，一般都默认值。

numrows：分区表的时候会用到，要求索引的多少行

numlblks：分区表的时候会用到，要求索引的多少个块

收集全库的统计信息：

exec dbms_stats.gether_Database_stats();

官网解释：

DBMS_STATS.GATHER_DATABASE_STATS (

estimate_percent NUMBER DEFAULT to_estimate_percent_type

(get_param('ESTIMATE_PERCENT')),

block_sample BOOLEAN DEFAULT FALSE,

method_opt VARCHAR2 DEFAULT get_param('METHOD_OPT'),

degree NUMBER DEFAULT to_degree_type(get_param('DEGREE')),

granularity VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),

cascade BOOLEAN DEFAULT to_cascade_type(get_param('CASCADE')),

stattab VARCHAR2 DEFAULT NULL,

statid VARCHAR2 DEFAULT NULL,

options VARCHAR2 DEFAULT 'GATHER',

objlist OUT ObjectTab,

statown VARCHAR2 DEFAULT NULL,

gather_sys BOOLEAN DEFAULT TRUE,

no_invalidate BOOLEAN DEFAULT to_no_invalidate_type (

get_param('NO_INVALIDATE')),

obj_filter_list ObjectTab DEFAULT NULL);

options：有如下参数：

gather：收集所有对象的统计信息/

gather auto：自动收集必要的统计数据。

gather stale：收集陈旧的统计信息。看*_tab_modifications视图。

gether empty：收集当前没有统计信息的对象的统计信息。

其他：

这两个是经常我们会用到的。当然我们还会用到一些其他的：

收集某个列的统计信息：

exec dbms_stats.get_column_stats(ownname=>'',tabname=>'',colname=>'');

收集某个用户的统计信息：

exec dbms_stats.get_schema_Stats(ownname=>'',method_opt=>'');

具体参数看官方文档：

https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68577

注意：

1.我们现在讲的是手动收集，其实数据库也会自动收集统计信息，时间分别是周一到周五的10：00 pm和06：00 am都会收集，所以我们可以看出，数据库的自动收集统计信息是为了避过高峰期。但是我们有的时候会发现有的时候有些表没有自动收集统计信息，原因是因为数据库只会统计那些变化的表，而有些表即使是有变化，但是没有达到收集统计信息的程度，官方给出的变化程度是变化超过当前总记录数10%的表。所以我们会用到手动收集。

2.我们知道，还有一种analyze的方法，具体用法是：

analyze table/index tab_name/index_name cpmpute statistics;

这个和dbms_stats的区别还是有的，dbms_Stats包是8i之后出的，我么们从上面的实验和官方的解释可以看到，dbms_stats可以并行化，并且有自动分析的功能，好理解，方便分析，最麻烦的是在分析分区表的时候analyze有的时候会出现不准确的情况。

THAT'S ALL

BY CUI PEACE !!!!