统计信息及DBMS_STATS.GATHER_TABLE_STATS学习

╰+哭是因爲堅強的太久メ 2022-08-11 13:28 288阅读 0赞

一、统计信息的收集

Gather\_stats\_job 调用 dbms\_stats.gather\_database\_stats\_job\_proc 过程来收集statistics的信息。该过程收集对象statistics 的条件如下：  
（1）对象的统计信息之前没有收集过。  
（2）当对象有超过10%的 rows 被修改，此时对象的统计信息也称为stale statistics。  
select job\_name,program\_name,enabled,stop\_on\_window\_close from dba\_scheduler\_jobs where job\_name = 'gather\_stats\_job';  
job\_name              program\_name           enabl  stop\_on\_window\_close  
\-------------------- --------------------    ----- --------------------  
gather\_stats\_job      gather\_stats\_prog      true    true  
  
停止自动统计信息的收集  
BEGIN  
DBMS\_SCHEDULER.DISABLE('GATHER\_STATS\_JOB');  
END;  
/  
自动统计信息的收集参数可以查看DBA\_TAB\_STAT\_PREFS，可以通过dbms\_stats.set\_XX\_prefs设置

二、如何查看统计信息是否过期

查看表的数据变动情况请使用SQL语句：select \* from user\_tab\_modifications;  
注意以及使用方法：  
1、但这张表不是实时更新的，默认情况15分钟更新一次，所以你更新一张表，可能在 user\_tab\_modifications里不能立刻体现出来。  
2、想要实时查看也有办法实现，那就是在执行select \* from user\_tab\_modifications之前先执行

exec DBMS\_STATS.FLUSH\_DATABASE\_MONITORING\_INFO;这个存储过程目的就是立刻刷新异动信息的

EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
    SELECT OWNER,TABLE_NAME,OBJECT_TYPE,STALE_STATS,LAST_ANALYZED FROM
    DBA_TAB_STATISTICS WHERE (STALE_STATS='YES' OR LAST_ANALYZED IS NULL);

三、优化器收集的统计信息包括内容：

1）Table statistics  
Number of rows  
Number of blocks  
Average row length  
2）Column statistics  
Number of distinct values (NDV) in column  
Number of nulls in column  
Data distribution (histogram)  
3）Index statistics  
Number of leaf blocks  
Levels  
Clustering factor  
4）System statistics  
I/O performance and utilization  
CPU performance and utilization

表的统计信息
    包含表行数，使用的块数，空的块数，块的使用率，行迁移和链接的数量，pctfree，pctused 的数据，行的平均大小：
    
    SELECT NUM_ROWS, --表中的记录数
    BLOCKS, --表中数据所占的数据块数
    EMPTY_BLOCKS, --表中的空块数
    AVG_SPACE, --数据块中平均的使用空间
    CHAIN_CNT, --表中行连接和行迁移的数量
    AVG_ROW_LEN --每条记录的平均长度
    FROM USER_TABLES 
    
    索引列的统计信息  
    包含 索引的深度（ B-Tree 的级别），索引叶级的块数量，集群因子（clustering_factor),  唯一值的个数。
    
    SELECT BLEVEL, --索引的层数
    LEAF_BLOCKS, --叶子结点的个数
    DISTINCT_KEYS, --唯一值的个数
    AVG_LEAF_BLOCKS_PER_KEY, --每个 KEY 的平均叶块个数
    AVG_DATA_BLOCKS_PER_KEY, --每个 KEY 的平均数据块个数
    CLUSTERING_FACTOR --群集因子
    FROM USER_INDEXES
    
    列的统计信息 
    包含  唯一的值个数，列最大小值， 密度（选择率），数据分布（直方图信息），NUll 值个数
    
    SELECT NUM_DISTINCT, --唯一值的个数
    LOW_VALUE, --列上的最小值
    HIGH_VALUE, --列上的最大值
    DENSITY, --选择率因子（密度）
    NUM_NULLS, --空值的个数
    NUM_BUCKETS, --直方图的 BUCKET 个数
    HISTOGRAM --直方图的类型
    FROM USER_TAB_COLUMNS

四、DBMS\_STATS.GATHER\_TABLE\_STATS详解  
   
作用:DBMS\_STATS.GATHER\_TABLE\_STATS统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行  
长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）.  
   
DBMS\_STATS.GATHER\_TABLE\_STATS的语法如下:

DBMS\_STATS.GATHER\_TABLE\_STATS (ownname VARCHAR2, tabname  VARCHAR2, partname VARCHAR2,estimate\_percent NUMBER,block\_sample BOOLEAN,method\_opt  VARCHAR2,degree  NUMBER, granularity VARCHAR2, cascade  BOOLEAN,stattab VARCHAR2,statid VARCHAR2,statown VARCHAR2,no\_invalidate BOOLEAN, force BOOLEAN);

参数说明:  
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 SIZE AUTO):  
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 选项会耗费大量处理时间，因为它要检查每个索引中的每个列的值的分布情况。  
degree:决定并行度.默认值为null.  
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.  
cascade:是收集索引的信息.默认为FALSE.  
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:即使表锁住了也收集统计信息.  
  
例子:  
execute dbms\_stats.gather\_table\_stats(ownname => 'owner',tabname => 'table\_name' ,estimate\_percent => null ,method\_opt => 'for all indexed columns' ,cascade => true);  
如何使用dbms\_stats分析统计信息？  
\--创建统计信息历史保留表   
sql> exec dbms\_stats.create\_stat\_table(ownname => 'scott',stattab => 'stat\_t') ;   
\--导出整个scheme的统计信息   
sql> exec dbms\_stats.export\_schema\_stats(ownname => 'scott',stattab => 'stat\_t') ;   
\--分析scheme  
Exec dbms\_stats.gather\_schema\_stats(   
ownname => 'scott',   
options => 'GATHER AUTO',   
estimate\_percent => dbms\_stats.auto\_sample\_size,   
method\_opt => 'for all indexed columns ',   
degree => 6 )   
\--分析表  
sql> exec dbms\_stats.gather\_table\_stats(ownname => 'scott',tabname => 'work\_list',estimate\_percent => 10,method\_opt=> 'for all indexed columns') ;   
\--分析索引  
SQL> exec dbms\_stats.gather\_index\_stats(ownname => 'crm2',indname => 'IDX\_ADM\_PERMISSION\_PID\_MID',estimate\_percent => '10',degree => '4') ;  
\--如果发现执行计划走错，删除表的统计信息  
SQL>dbms\_stats.delete\_table\_stats(ownname => 'scott',tabname => 'work\_list') ;  
\--导入表的历史统计信息  
sql> exec dbms\_stats.import\_table\_stats(ownname => 'scott',tabname => 'work\_list',stattab => 'stat\_table') ;   
\--如果进行分析后，大部分表的执行计划都走错，需要导回整个scheme的统计信息  
sql> exec dbms\_stats.import\_schema\_stats(ownname => 'scott',stattab => 'stat\_table');  
\--导入索引的统计信息  
SQL> exec dbms\_stats.import\_index\_stats(ownname => 'crm2',indname => 'IDX\_ADM\_PERMISSION\_PID\_MID',stattab => 'stat\_table')   
\--检查是否导入成功  
SQL> select table\_name,num\_rows,a.blocks,a.last\_analyzed from all\_tables a where a.table\_name='WORK\_LIST';

分析数据库（包括所有的用户对象和系统对象）：gather\_database\_stats   
分析用户所有的对象（包括表、索引、簇）：gather\_schema\_stats  
分析表：gather\_table\_stats  
分析索引：gather\_index\_stats   
删除数据库统计信息：delete\_database\_stats  
删除用户方案统计信息：delete\_schema\_stats  
删除表统计信息：delete\_table\_stats   
删除索引统计信息：delete\_index\_stats  
删除列统计信息：delete\_column\_stats   
设置表统计信息：set\_table\_stats  
设置索引统计信息：set\_index\_stats  
设置列统计信息：set\_column\_stats   
  
这是对命令与工具包的一些总结  
1、对于分区表，建议使用DBMS\_STATS，而不是使用Analyze语句。  
a) 可以并行进行，对多个用户，多个Table  
b) 可以得到整个分区表的数据和单个分区的数据。  
c) 可以在不同级别上Compute Statistics：单个分区，子分区，全表，所有分区  
d) 可以倒出统计信息  
e) 可以用户自动收集统计信息  
2、DBMS\_STATS的缺点  
a) 不能Validate Structure  
b) 不能收集CHAINED ROWS, 不能收集CLUSTER TABLE的信息，这两个仍旧需要使用Analyze语句。  
c) DBMS\_STATS 默认不对索引进行Analyze，因为默认Cascade是False，需要手工指定为True  
3、对于oracle 9里面的External Table，Analyze不能使用，只能使用DBMS\_STATS来收集信息。