数据库高效优化：架构、规范与SQL技巧
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

4.2　统计信息操作

常见的统计信息操作包括查看、收集、修改、删除、锁定等。下面针对每种操作，简单说明一下。

统计信息相关的操作中，最常见的就是查看操作。常见的场景是需要判断统计信息是否准确，进而排除可能因为统计信息失真导致优化器制定出低效执行计划的情况。统计信息保存在数据字典中，我们可以通过视图去查询相关统计信息。

收集统计信息也非常重要。在一般情况下，系统自动收集的统计信息是可以满足我们需要的，但也有些情况是需要人为干预、手工收集统计信息。这部分比较复杂，相关命令的选项也比较多，下面会针对常见的一些情况加以说明。

修改统计信息操作很少见。常见的情况是对象非常大，做收集动作非常慢，才采取人为修改统计信息的方式；或者是在测试环境中，为了模拟某些操作行为，而又没有那么大数据量而采取手工修改来欺骗优化器。不建议进行修改操作，除非是对各种统计信息指标非常了解。

删除统计信息的操作，相对用得较少。常见的情况是现有统计信息有问题，只需要重新收集然后覆盖就可以了，基本不需要删除。一般只有在直方图中，因错误地收集了直方图信息导致问题，才需要手工删除直方图统计信息。

锁定统计信息也不太常用。它主要是为了避免因统计信息变化导致执行计划发生变化。一般我们是相信系统对统计信息的处理的，不需要锁定处理。

下面针对不同类别的统计信息的主要操作分别加以说明。

4.2.1　系统统计信息

1.收集统计信息

我们知道，系统统计信息分为两种，一种是非工作量统计信息（noworkload statistics）和工作量统计信息（workload statistics）。从10g开始，非工作量的统计信息总是可用的。对于工作量统计信息，则可以按照下面步骤进行收集：

exec dbms_stats.gather_system_stats('start')      //开始收集系统统计信息
运行一段时间                                    //最好是以系统典型负载运行一段时间
exec dbms_stats.gather_system_stats('stop')      //停止收集系统统计信息

除了上面的方法外，也可以用下面的方法，其中interval参数为间隔时长（单位分钟）：

dbms_stats.gather_system_stats(gathering_mode=>'interval', interval=>N);

2.查看统计信息

系统统计信息放在aux_stats$表里面。Oracle没有提供数据字典视图来供外部访问。根据sname不同，可以将统计信息划分为三个结果集（不同类别的信息），分别如下：

·SYSSTATS_INFO：系统统计信息的状态和收集时间。

·SYSSTATS_MAIN：系统统计信息结果集。

·SYSSTATS_TEMP：用来计算系统统计信息，只有收集工作量统计信息时才可用。

查看系统统计信息的方法如下：

select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

也可以使用dbms_stats.get_system_stats过程获得统计信息。

3.修改统计信息

手工设置统计信息，大致操作如下：

begin
dbms_stats.set_system_stats(pname=>'CPUSPEED',pvalue=>772);
dbms_stats.set_system_stats(pname=>'SREADTIM',pvalue=>5.5);
dbms_stats.set_system_stats(pname=>'MREADTIM',pvalue=>19.4);
dbms_stats.set_system_stats(pname=>'MBRC',pvalue=>53);
dbms_stats.set_system_stats(pname=>'MAXTHR',pvalue=>1243434334);
dbms_stats.set_system_stats(pname=>'SLAVETHR',pvalue=>1212121);
end;
*pname为指定统计信息参数，pvalue为统计信息的值

4.删除统计信息

删除统计信息的方法如下：

exec dbms_stats.delete_system_stats;//调用这个过程不需要参数

4.2.2　对象统计信息

对象统计信息的相关操作是日常使用最多的。

1.收集统计信息

收集统计信息主要由analyze命令和dbms_stats包实现。一般建议使用dbms_stats代替analyze命令。当然这两者不是完全等价的，有些情况必须要使用analyze，例如分析索引的结构信息。作为重点，下面主要介绍一下dbms_stats包的用法。

dbms_stats包本身很复杂，涉及统计信息方方面面的操作都可以通过它完成。下面主要针对对象统计信息的收集动作，通过几个例子说明一下它的用法及主要参数。

收集整个库中对象的统计信息，采样率通过estimate_percent指定，这里为15%，命令如下：

exec dbms_stats.gather_database_stats(estimate_percent => 15);

收集指定Schema的统计信息，命令如下：

exec dbms_stats.gather_schema_stats('scott', estimate_percent => 15);

收集指定表的统计信息，命令如下：

exec dbms_stats.gather_table_stats('scott', 'employees', estimate_percent => 15);

通过method_opt指定是否收集直方图，例子中说明为所有有索引的列收集且只会为现有的直方图重新分析，不再搜索其他直方图。通过granularity指定如何处理分区对象的统计信息，这里指定all代表收集对象、分区、子分区统计信息。通过cascade选项指定是否收集索引的统计信息。

exec dbms_stats.gather_table_stats(ownname => 'prd_user',tabname => 'prd_syi_search',method_opt => 'for all indexed columns size repeat',granularity=>'all',cascade => true);
exec dbms_stats.gather_index_stats('scott', 'employees_pk', estimate_percent => 15);

2.查看统计信息

根据对象的不同，其统计信息可到不同的视图中查看。这部分涉及的数据字典比较多，如表4-2所示。

表4-2　对象统计信息数据字典

带有星号的表主要在9i之前使用。这是因为视图user_tab_statistics和user_ind_statistics只能在10g上使用。

下面分别针对表、列和索引进行举例说明。

1）查看表的统计信息：

select table_name,num_rows,blocks,empty_blocks,avg_space,chain_cnt,avg_row_len,
      global_stats,user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_tables t
where owner='xxx' and table_name='xxx';

主要字段说明：

·num_row：数据的行数。

·blocks：高水位下的数据块个数。

·empty_block：高水位以上的数据块个数。dbms_stats不计算这个值，被设置为0。

·avg_space：数据块中平均空余空间（字节）。dbms_stats不计算这个值，被设置为0。

·chain_cnt：行链接和行迁移的数目。dbms_stats不计算这个值，被设置为0。

·avg_row_len：行平均长度（字节）。

·last_analyzed：最后收集统计信息时间。

2）查看索引的统计信息：

select index_name,uniqueness,blevel,leaf_blocks,distinct_keys,num_rows,
      avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,global_stats,
      user_stats,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_indexes t
where table_owner='xx' and table_name='xx';

主要字段说明：

·num_rows：索引行。

·leaf_blocks：索引叶块数。

·distinct_keys：索引不同键数。

·blevel：索引的blevel分支层数（btree的深度，从root节点到leaf节点的深度。如果root节点也是leaf节点，那么这个深度就是0）。

·avg_leaf_blocks_per_key：每个键值的平均索引leaf块数（每个键值的平均索引leaf块数（近似取整），如果是unique index或pk，这个值总是1）。

·avg_data_blocks_per_key：每个键值的平均索引数据（表）块数。

·clustering_factor：索引的群集因子（索引集群因子 一个度量标准, 用于索引的有序度和表混乱度之间的比较。）。

3）查看列的统计信息：

select column_name,num_distinct,density,num_buckets,num_nulls,global_stats,user_stats,
histogram,num_buckets,sample_size,to_char(t.last_analyzed,'yyyy-mm-dd')
from dba_tab_cols t
where owner='xx' and table_name='xx';

主要字段说明：

·num_distinct：不同值的数目。

·num_nulls：字段值为null的数目。

·density：选择率。

·histogram：是否有直方图统计信息。如果有，是哪种类型。10g以后才提供。

·NONE：没有。

·FREQUENCY：频率类型。

·HEIGHT BALANCED：基于高度类型。

·num_buckets：直方图的桶数。

3.修改统计信息

可以直接通过dbms_stats包的相关方法设置对象的统计信息。下面举例说明。

exec dbms_stats.set_table_stats(ownname=>'HF',tabname=>'EMP',
      numrows=>10000000,no_invalidate=>false);
/*
这个例子设置了HF.EMP表的统计项numrows为1000万,参数no_invalidate表示不会设置相关的SQL游标失效
*/
exec dbms_stats.set_index_stats(ownname=>'HF',indname=>'IDX_EMP',
      numlblks=>100000,no_invalidate=>false);
/*
这个例子设置了HF.IDX_EMP索引的统计项numlblks为10万，参数no_invalidate表示不会设置相关的SQL游标失效
*/

4.删除统计信息

类似上面收集的方法，每一个gather_xxx_stats就对应一个delete_xxx_stats方法。此外还多一个delete_column_stats方法，专门用来删除列的统计信息。下面举例说明。

exec dbms_stats.delete_table_stats('scott', 'employees');
//上面例子删除了scott用户下employees表的对象统计信息
exec dbms_stats.delete_index_stats('scott', 'employees_pk');
//上面例子删除了scott用户下employees_pk索引的对象统计信息
exec dbms_stats.delete_column_stats(
ownname => user,
tabname => 'T',
colname => 'VAL',
col_stat_type => 'HISTOGRAM');
//上面例子删除了当前用户下，T表的VAL字段的直方图信息

5.锁定统计信息

从10g以后，可以明确锁定对象的统计信息。相关的操作包括锁定、解锁、查看锁定状态。下面举例说明。

锁定对象的统计信息：

dbms_stats.lock_schema_stats(ownname=>user);
//上面例子锁定了当前用户的所有对象统计信息
dbms_stats.lock_table_stats(ownname=>user,tabname=>'T');
//上面例子锁定了当前用户的T表统计信息

解锁对象的统计信息：

dbms_stats.unlock_schema_stats(ownname=>user);
//上面例子对当前用户的对象统计信息取消锁定
dbms_stats.unlock_table_stats(ownname=>user,tabname=>'T');
//上面例子对当前用户的T表统计信息取消锁定

查看对象是否锁定了统计信息：

select table_name from user_tab_statistics where stattype_locked is not null;
//查看当前用户下所有表中有锁定统计信息的对象名称

[1] 一个度量标准, 用于索引的有序度和表混乱度之间的比较。

4.2.3　数据字典统计信息

1.收集统计信息

对数据字典统计信息，可以用专门的方法收集，也可以按普通表的方式收集。下面举例说明。

exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'TAB$',estimate_percent=> 100,cascade=>true);

2.删除统计信息

类似收集的方法，删除统计信息的方法也有两种。

exec dbms_stats.delete_dictionary_stats;
//上面例子删除字典对象统计信息

exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'TAB$');
//上面例子删除SYS用户TAB$表的统计信息

3.查看统计信息

查看统计信息就按照普通对象进行查询即可，这里就不具体介绍了。

4.2.4　内部对象统计信息

对内部对象统计信息的收集，可以用专门的方法进行，也可以按普通表的方法进行。删除也类似。下面举例说明。

exec dbms_stats.gather_fixed_objects_stats();
//收集内部对象的统计信息

exec dbms_stats.gather_table_stats(ownname=>'SYS',tabname=>'X$KCCRSR',
     estimate_percent=>100,cascade=>true);
//收集某个内部对象(SYS.X$KCCRSR)的统计信息

exec dbms_stats.delete_fixed_objects_stats();
//删除内部对象的统计信息

exec dbms_stats.delete_table_stats(ownname=>'SYS',tabname=>'X$KCCRSR');
//删除某个内部对象(SYS.X$KCCRSR)的统计信息
上一章目录下一章