oracle统一数据收集,Oracle dbms_stats和analyze收集统计信息。

最新推荐文章于 2023-12-19 10:26:28 发布

张昕宇梁红

最新推荐文章于 2023-12-19 10:26:28 发布

阅读量682

点赞数

文章标签： oracle统一数据收集

通过对dbms_stats的概率，加上自己的一点见解，带你走进oracle收集统计信息。

dbms_stats包对段表的分析有3个层次：

1. 表自身的分析：包括表中的行数，数据块数，行长等信息。

2. 列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况(直方图)，分为频率直方图和等高直方图

3. 索引的分析：包括索引叶块的数量，索引的深度，索引的聚合因子等

4. 系统的统计信息：i/o性能和利用，cpu性能和利用

当cbo做直方图分析时，会将要分析的列上的数据分成很多数量相同的部分，每一部分称为一个bucket(桶)，这样cbo就可以非常容易知道这个列上的数值的分布情况，这种数据的分布将作为一个非常重要的因素纳入到执行成本的计算当中。

性能收集的包有如下几个存储过程：

1. gather_database_stats

2. gather_dictionary_stats 收集所有系统用户的信息

3. gather_fixed_objects_stats

4. gather_index_stats

5. gather_schema_stats

6. gather_system_stats

7. gather_table_stats

PgSQL

dbms_stats.gather_tables_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_cascase_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')), -----是否有验证游标的依存关系

force boolean default false ------强制分析，即使表被锁定

)

dbms_stats.gather_tables_stats(

ownnamevarchar2,----所有者的名字

tabnamevarchar2,-----要分析的表名

partnamevarchar2defaultnull,------要分析的分区名

estimate_percentnumberdefaultto_estimate_percent_type(get_param('ESTIMATE PERCENT')),----采样的比例

block_samplebooleandefaultfalse,----是否块分析

method_optvarchar2defaultget_param('METHOD_OPT'),------分析的样式

degreenumberdefaultto_degree_type(get_param('DEGREE')),----分析的并行度

granularityvarchar2defaultget_param('GRANULARITY'),-----分析的粒度

cascadebooleandefaultto_cascase_type(get_param('CASCADE')),---是否分析索引

stattabvarchar2defaultnull,------使用的性能表名

statidvarchar2defaultnull,------性能表的标识

statownvarchar2defaultnull,------性能表的所有者

no_invalidatebooleandefaultto_no_invalidate_type(get_param('NO_INVALIDATE')),-----是否有验证游标的依存关系

forcebooleandefaultfalse------强制分析，即使表被锁定

)

estimate_percent: 这是一个百分比值，它告诉分析包需要使用表中数据的多大比例来作分析。理论上讲，采样的数量越多，得到的信息将越接近现实，cbo作出的执行计划就越准确，但采样越多，消耗的系统资源越多，对系统的影响就越大，如果数据直方图分布比较均匀，就可以将这个参数设置成AUTO_SAMPLE_SIZE,即让oracle自己来判断采样比例，如果对于一个有1000w数据的表分区，可以设置成0.000001，这只是一个经验值。一般推荐30.

method_opt：这个参数用来定义直方图分析的一些值，参数默认值：FOR ALL COLUMNS SIZE AUTO,包含参数FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause] , FOR COLUMNS [size clause] column|attribute [size_clause] [,column|arrtibute]

所有列：FOR ALL COLUMNS

索引列：只对有索引的列进行分析：FOR ALL INDEXED COLUMNS

隐藏列：只对隐藏列进行分析，比如有函数索引时收集：FOR ALL HIDDEN COLUMNS

显示指定列：显示的指定哪些列进行分析：FOR COLUMNS columns_name

method_opt=>’for all columns size skewonly’ ORACLE会根据数据分布收集直方图，有些列不必要收集直方图，有可能会导致绑定变量窥探

method_opt=>’for all columns size repeat’ 只有以前收集过直方图，才会收集直方图信息，所以一般我们会设置method_opt 为repeat

method_opt=>’for all columns size auto’ ORACLE会根据数据分布以及列的workload来确定是否收集直方图，也就是说会根据查询语句中的where条件来判断列收集直方图

method_opt=>’for all columns size interger’ 我们自己指定一个bucket值

degree：用来指定分析时使用的并行度，有如下这些设置

1. null：设置为null，oracle将使用被分析表属性的并行度，比如表在创建时指定的并行度，或者后来使用alter table 重新设置的并行度，null是默认的参数

2. 一个数值：可以显示的指定分析时使用的并行度

3. default_degree：oracle将根据初始化参数中的相关参数的设置来决定使用的并行度。

granularity：分析的粒度，有如下几个设置，当然可以指定partition name，对指定的分区进行收集

1. ALL：将会对表的全局，分区，子分区的数据都做分析

2. AUTO：oracle根据分区的定义，自动决定做哪一种粒度的分析，默认设置

3. GLOBAL：只做全局级别的分析

4. GLOBAL AND PARTITION：只对全局和分区级别做分析，对于子分区不做分析

5. PARTITION：只对分区级别作分析

6. SUBPARTITION：只对子分区作分析

EG：

PgSQL

DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',

tabname => 'P_TEST',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'for all columns size repeat',

degree => DBMS_STATS.AUTO_DEGREE,

granularity => 'ALL',

cascade=>TRUE

);

DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT',

tabname=>'P_TEST',

estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt=>'for all columns size repeat',

degree=>DBMS_STATS.AUTO_DEGREE,

granularity=>'ALL',

cascade=>TRUE

);

在oracle10G中，在安装oracle的时候，就默认创建了一个名为gather_stats_job的job来自动收集统计信息，自动收集过期的统计信息依赖于表监控特征，在oracle10g中表监控默认是开启的，同时它也依赖statistics_level参数的值，10G中默认为typical，只有将statistics_level参数值设置成all或者typical才能让oracle识别过期的统计信息。

关闭自动收集统计信息：dbms_scheduler.disable(‘GATHER_STATS_JOB’);

收集某个schema的信息

PgSQL

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT',

estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE,

options => 'gather auto',

degree => DBMS_STATS.AUTO_DEGREE,

method_opt => 'for all columns size repeat',

cascade => TRUE

);

END;

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SCOTT',

estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,

options=>'gather auto',

degree=>DBMS_STATS.AUTO_DEGREE,

method_opt=>'for all columns size repeat',

cascade=>TRUE

);

END;

上面的例子收集SCOTT模式下所有对象的统计信息。里面值得注意的一个参数就是options。上面已经讲到过，他与表监控有关。它有四个选项

Options =>’gather’ 收集所有对象的统计信息

Options =>’gather empty’ 只收集还没被统计的表

Options =>’gather stale’ 只收集修改量超过10%的表

Options =>’gather auto’ 相当于empty+stale ，所以我们一般设置为AUTO。

=======================================================================================================================================

还需要使用Analyze收集统计信息的有：

1) 行迁移/行链接信息。不过使用dbms_stats无法统计这个信息，必须使用analyze table table_name compute statistics;或者analyze table table_name list chained rows into table_name;来收集。但是在执行analyze table table_name list chained rows into table_name的时候，必须创建CHAINED_ROWS表，然后才能查看

PgSQL

SQL> @/opt/oracle/oracle11g/rdbms/admin/utlchain.sql

Table created.

SQL> analyze table oldemp list chained rows;

Table analyzed.

SQL>@/opt/oracle/oracle11g/rdbms/admin/utlchain.sql

Tablecreated.

SQL>analyzetableoldemplistchainedrows;

Tableanalyzed.

2) Cluster table。Cluster table 必须使用analyze命令收集统计信息。

3) Validate structure。Validate structure这个命令通常用于验证索引结构信息。如果一个表上经常做delete操作，那么就需要经常对这个表上的索引进行结构验证，因为这个表上的索引可能需要重建。可以使用analyze index index_name validate structure offline;注意一定是要以offline(默认)方式收集统计信息，如果以online方式收集统计信息，那么就无法在index_stats中查找到刚才收集到的统计信息。Online方式收集统计信息不会对表加lock。而以offline方式收集统计信息会对表加lock，可能对性能有影响，一般是在系统处于空闲状态收集统计信息。

有可能需要rebuild的索引：可以查看index_stats.height ,如果高度大于等于4就可能需要rebuild，另外一个就是index_stats.del_lf_rows/index_stats.lf_rows>0.2也就是说有20%的数据被删除了，那么这个索引也需要rebuild。

行迁移：当一个行上的更新操作(原来的数据存在且没有减少)导致当前的数据不能在容纳在当前块，我们需要进行行迁移。一个行迁移意味着整

行数据将会移动，仅仅保留的是一个转移地址。因此整行数据都被移动，原始的数据块上仅仅保留的是指向新块的一个地址信息。

产生：update

行链接：当一行数据太大而不能在一个单数据块容纳时，行链接由此产生。举例来说，当你使用了4kb的Oracle 数据块大小，而你需要插入一行数据是8k，

Oracle则需要使用3个数据块分成片来存储。因此，引起行链接的情形通常是，表上行记录的大小超出了数据库Oracle块的大小。

产生：insert

表上使用了LONG 或 LONG RAW数据类型的时候容易产生行链接。其次表上多于255列时Oracle会将这些过宽的表分片而产生行链接

迁移行对索引读产生额外的I/O，对全表扫描没什么影响

行链接则影响索引读和全表扫描

在大多数情况下，行链接是无法克服的，特别是在一个表包含象LONGS, LOBs 等这样的列时。当在不同的表中有大量的链接行，并且哪些表的行的长度不是很长时，你可以通过用更大的block size重建数据库的方法来解决它。

例如：当前你的数据库的数据块的大小为4K，但是你的行的平均长度为6k，那么你可以通过用8k大小的数据块来重建数据库的办法解决行链接现象。

行迁移主要是由于设置的PCTFREE参数过小，导致没有给update操作留下足够的空闲空间引起。为了避免行迁移，所有被修改的表应该设置合适的PCTFREE 值，以便在每个数据块内为数据修改保留足够的空间。可以通过增加PCTFREE值的办法来避免行迁移，但这种解决办法是以牺牲更多的空间为代价的，这也就是我们通常所说的以空间换效率。而且通过增加PCTFREE值的办法只能缓解行迁移现象，而不能完全解决行迁移，所以较好的办法是在设置了合适的PCTFREE值的后，在发现行迁移现象比较严重时，对表的数据进行重组。

最后编辑：2014-01-18作者：Jerry

一个积极向上的小青年，热衷于分享--Focus on DB,BI,ETL

张昕宇梁红

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
oracle统一数据收集,Oracle dbms_stats和analyze收集统计信息。

通过对dbms_stats的概率，加上自己的一点见解，带你走进oracle收集统计信息。dbms_stats包对段表的分析有3个层次：1. 表自身的分析：包括表中的行数，数据块数，行长等信息。2. 列的分析：包括列值的重复数，列上的空值，数据在列上的分布情况(直方图)，分为频率直方图和等高直方图3. 索引的分析：包括索引叶块的数量，索引的深度，索引的聚合因子等4. 系统的统计信息：i/o性能和利用...
复制链接

扫一扫