DBA成长随笔---oracle11g,性能优化之数据分析

目录数据分析为什么要进行数据分析？CBO的数据来源一部分来自数据分析当分析信息不充分时CBO的数据来源DBMS_STATS包oracle信息收集表数据的收集数据分析为什么要进行数据分析？尽量提供最准确的数据给数据模型，尽可能的让CBO‘理解’数据。CBO的数据来源一部分来自数据分析CBO是一个数学模型；需要准确的传入数据；通过精确的数据计算出精确的执行计划；当没有进行数据分析时create tab

hello world857

1649人浏览 · 2022-04-19 17:28:04

hello world857 · 2022-04-19 17:28:04 发布

数据分析

为什么要进行数据分析？

尽量提供最准确的数据给数据模型，尽可能的让CBO‘理解’数据。

CBO的数据来源一部分来自数据分析

CBO是一个数学模型；

需要准确的传入数据；

通过精确的数据计算出精确的执行计划；

当没有进行数据分析时

create table t10 as select * from dba_objects;

select extents,blocks from user_segments where segment_name='T10';

select num_rows,blocks from user_tables where table_name='T10';

当新建的表未进行数据分析时候，CBO知道的信息非常少，此时Oracle通过动态采样分析一些数据。

select /*+ dynanic_sampling(t10 0) */ count(*) from t10;

而实际列行数为

select count(*) from t10;

当分析信息不充分时

update t10 set object_id =1 where object_id <1001; --使其数据不均衡分布

Commit;

exec dbms_stats.gather_table_stats(user,'T10',method_opt=>'for all columns size 1');

--for all columns size 1 ；用一个桶做直方图，就跟没分析一样

select num_rows,blocks from user_tables where table_name='T10';

select * from t10 where object_id=1;

显然 1是错误的，实际上应该是943；

当数据足够多的时候

exec dbms_stats.gather_table_stats(user,'T10',method_opt=>'for all columns size 254');

CBO的数据来源

初始化参数

优化参数

CPU

数据块大小

多块读的大小

....

数据字典

user_tables;user_tab_partitions

user_indexes,user_ind_partitions

User_tab_col_statistics

...

DBMS_STATS包

专门为CBO提供信息来源

可以进行数据分析多种组合

可以对分区进行分析

可以对分区数据进行管理

备份、恢复、删除、设置...

oracle信息收集

Oracle的自动信息收集

Oracle11g的默认一个设置

User_tab_modification记录表的修改

当对象修改数据超过10%，Oracle会重新分析

定时任务GATHER_STATS_JOB 负责重新定时收集过旧数据的信息

是否完全依赖于自动分析？

当数据执行计划不错时，可以依赖自动分析

比如，OLTP系统

否则，需要手工介入

比如，OLAP系统

表数据的收集

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

DBMS_STATS.GATHER_TABLE_STATS

字段名	字段类型	默认值	注释
ownname	varchar2		要分析表的拥有者
tabname	varchar2		要分析的表名
partname	varchar2	null	分区的名字,只对分区表或分区索引有用
estimate_percent	number	null	采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值。大表建议手工设置数值。
block_sample	boolean	FALSE	是否用块采样代替行采样.
method_opt	varchar2	default 'FOR ALL COLUMNS SIZE 1'	决定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	number	null	决定并行度.默认值为null.
granularity	varchar2	default	数据分析粒度，(全局层面)global、partition、(子分区)subpartition
cascade	boolean	FALSE	是收集索引的信息.默认为FALSE.
stattab	varchar2	null	指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥有者.
statid	varchar2	null	指定要存储统计信息的表的ID
statown	varchar2	null	指定要存储统计信息的表的拥有者
no_invalidate	boolean	FALSE	如果设置为 TRUE，则不会使从属游标失效。如果设置为 FALSE，则该过程会立即使从属游标失效
stattype	varchar2
force	boolean		即使表锁住了也收集统计信息.

直方图

概念：Oracle对列上数据分布进行统计分析，对数据倾斜分布是很有用

CBO的数据来源

表未分析	数据块，默认值，动态采样...
表已分析（未生成直方图）	Num_rows,NDV,BLOCKS...
表已分析（已生成直方图）	列上的数据分布
？？？？	列的相关性

直方图示例

create table test_height as select object_id col1,trunc(rownum/1000) col2

from dba_objects where rownum < 10000;

Begin dbms_stats.gather_table_stats(user,'TEST_HEIGHT',method_opt => 'for all columns size 12'); end;

Select column_name,num_distinct,num_buckets,histogram

from user_tab_col_statistics s where table_name='TEST_HEIGHT' and column_name ='COL1' order by 1;

select s.endpoint_number,s.endpoint_value

from user_tab_histograms s where table_name='TEST_HEIGHT' and column_name ='COL1' order by 1;

select column_name,num_distinct,num_buckets,histogram

from user_tab_col_statistics where table_name='TEST_HEIGHT' and column_name ='COL2' order by 1;

select s.endpoint_number,s.endpoint_value

from user_tab_histograms s where table_name='TEST_HEIGHT' and column_name ='COL2' order by 1;

全局分析和分区分析比较

create table test_globa_or_partition_stats (id int,name varchar2(1000))

partition by range(id)(

partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(maxvalue)

);

begin

--dbms_stats.delete_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS');

dbms_stats.gather_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS' );

end;

select num_rows,blocks,global_stats

from user_tables where table_name='TEST_GLOBA_OR_PARTITION_STATS';

--globa;l_stats 为yes，说明全局已经分析了

select table_name,partition_name,blocks,global_stats

from user_tab_partitions where table_name='TEST_GLOBA_OR_PARTITION_STATS';

--globa;l_stats 为yes，说明分区已经分析了

begin

--dbms_stats.delete_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS');

dbms_stats.gather_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS',granularity => 'partition' );

end;

-- granularity => 'partition' 只对分区做分析

select num_rows,blocks,global_stats

from user_tables where table_name='TEST_GLOBA_OR_PARTITION_STATS';

-- 全局分析还是和之前一样

select table_name,partition_name,blocks,global_stats

from user_tab_partitions where table_name='TEST_GLOBA_OR_PARTITION_STATS';

--分区的分析已经重新分析了

--进行执行计划测试

select count(*) from TEST_GLOBA_OR_PARTITION_STATS where id <10000;

select count(*) from TEST_GLOBA_OR_PARTITION_STATS where id <30000;

--小结，前面分区时 id<10000是属于p1分区

Id<10000时，使用分区统计信息

Id>10000时，使用全局统计信息

导致在分区里面数据是准确的，在全局里面数据是错误的。

/删除全局统计，不删除分区统计/

Begin bms_stats.delete_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS',cascade_parts => false); end;

select num_rows,blocks,global_stats

from user_tables where table_name='TEST_GLOBA_OR_PARTITION_STATS';

-- 此时全局分析为NO

select num_rows,blocks,global_stats

from user_tables where table_name='TEST_GLOBA_OR_PARTITION_STATS';

--再次进行分区统计

begin

dbms_stats.gather_table_stats(user,'TEST_GLOBA_OR_PARTITION_STATS',granularity => 'partition' );

end;

--再次查看全局统计

select num_rows,blocks,global_stats

from user_tables where table_name='TEST_GLOBA_OR_PARTITION_STATS';

总结

当统计信息不包含在一个分区里面，Oracle会使用全局分析
当表上已经有全局统计信息时，单独对分区分析，不会更新全局信息
当表上未有全局统计信息时，单独对分区进行分析，会跟新全局统计信息(合并分区信息)。

分区和全局信息

由于在生产环境中，表的数据是十分庞大的，如果每次都进行全局分析十分浪费系统资源。

在Oracle11g开始得到了改善，可以以增量的收集分区信息来跟新全局信息。

将修改或更新过的内容进行比较，将差异的信息单独存放；然后再结合之前的全局信息进行分析。其结果准确率肯定不如直接全局分析，但效率上得到了大大的改善。

--设置表格属性全局信息采用增量形式跟新信息

begin dbms_stats.set_table_prefs(user,'TEST_GLOBA_OR_PARTITION_STATS','INCREMENTAL','TRUE'); end;

如何设置该参数

granularity

在一个很大的分区表（OLAP），全局分析代价是非常昂贵的。

OLAP系统下，除了新加入的数据外，旧的数据基本是没什么变化的，全局分析很浪费时间。

对于很大的分区表，将granularity设置成partition或者incremental是很有意义的。

对应不大的分区，可以使用默认设置；

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 只收集非均匀分布的直方图，系统自动决定桶数

列的相关性分析

得出其分析误差比较大

begin dbms_stats.gather_table_stats('SH','CUSTOMERS',METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (SUST_STATE_PROVINCE,COUNTRY_ID) SIZE SKEWONLY')end;