在OLTP系统中,一定要收集统计信息
一般OLAP系统都不收集,直接用HINT固定
因为OLAP系统I/O压力大,表也非常大,收集统计信息很耗费资源
并且OLAP系统表的数据随时都在变化(因为实时在入库),那么就需要经常收集统计信息
所以OLAP系统收集统计信息不现实
在OLTP系统中要优化一个SQL语句,首先要保证SQL语句中所有的表统计是准确的,如果统计信息都不准确,那么SQL优化就无从谈起,在OLAP系统中,如果数据量特别大,一般不会去收集统计信息,会直接利用HINT把执行计划给固定住。
常用的统计信息收集脚本:
非分区表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size repeat',
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
对分区表收集统计信息:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'ROBINSON',
tabname =>'P_TEST',
estimate_percent =>30,
no_invalidate =>FALSE,
degree =>8,
granularity =>'ALL',
cascade =>TRUE);
END;
/
estimate_percent表示采样率,采样率设置太大,也没必要,如果表非常大,采样率过高会导致收集统计信息跑很长,增加了系统压力。采样率设置过小,统计的信息就不能很完整的体现表中数据的分布,这样CBO在进行执行计划的选择上,很可能选择错误的执行计划。根所工作经验:
表小于1GB采样率可以设置50%-100%
表大于1GB小于5GB可以设置30%
表大于5GB这类表都应该进行分区,采样率可以设置为30%
用segmet_size来计算一个表是大表还是小表
method_opt:表示收集的方法,一个稳定的系统收集统计推荐使用
method_opt:'for all columns size repeat'
repeat表示以前收集过直方图,现在收集统计信息的时候就收集直方图,如果以前没收集过直方图,现在收集统计信息的时候就不收集
method_opt:'for all columns size auto'
auto表示oracle根据谓词过滤信息,自动判断该列是否收集直方图,一个稳定的系统,不应该让Oracle去自动判断,自动判断很可能会出事,比如某列不该收集直方图,设置auto过后它自己去收集直方图了,从而导致系统不稳定。
degree:表示收集统计信息的时候并行度,并行度根据系统配置以及当前系统可用资源自行调协,一般degree设置4-8
一般设置为4
cascade:表示收集表的统计信息时候同时收集索引的统计信息
no_invalidate:表示收集统计信息之后在共享池中引用了相关的SQL游标是否失败。这个一定要设置为FALSE,不然你可能在做SQL优化的时候,你发现明明更新了统计信息,但是执行计划还是没改变
----实验1----
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size skewonly',--》这个会收集直方图
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
select a.column_name,
b.num_raws,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows*100,2) selectivity,
num_nulls,
density,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner=upper('&table_name')
and a.table_name=upper('&table_name')
and a.column_name=upper('&column_name');
---实验2----
drop table test purge;
create table test as select * from dba_objects;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size auto',
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
select a.column_name,
b.num_raws,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows*100,2) selectivity,
num_nulls,
density,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner=upper('&table_name')
and a.table_name=upper('&table_name')
and a.column_name=upper('&column_name');
用auto会根据where子句后的字段来自动创建直方图
例如在创建了表之后 执行 select * from test where owner=‘SCOTT';之后在统计信息的话
这时会在owner列收集直方图
exec dbms_stats.flush_database_monitoring_info;
select owner,table_name name,object_type,stale_stats,last_analyzed
from dba_tab_statistics
where table_name in ('TEST')
and owner='OWNER_NAME'
and (stale_stats='YES' or last_analyzed is null);
如果没有返回数据则表示没有过期
delete from test where rownum<=83499*0.11;
这时再查看则会返回数据,则表示已经过期
在做优化的时候,如果发现统计信息没过期,则要检查采样率
select owner,
table_name,
num_rows,
sample_size,
trunc(sample_size/num_row*100) estimate_percent
from dba_tab_statistics
where owner='SCOTT' and table_name='TEST';
以下语句处理查询大批量的表的统计信息是否过期
select '''' || object_owner || '''','''' || object_name || ''','
from plan_table
where object_type='TABLE'
union
---table in the index------
select '''' || table_owner || '''','''' || table_name || '*'','
from dba_indexes
where owner in
(select distinct object_owner from plan_table where rownum>0)
and index_name in
(select distinct object_name from plan_table where rownum>0)
order by 2;
----再把上面的结果贴入下面的代码--------
select owner,table_name name,object_type,stale_stats,last_analyzed
from dba_tab_statistics
where table_name in
(
table_name
)
and owner='owner'
and (stale_stats='SYS' or last_analyzed is null);
一般OLAP系统都不收集,直接用HINT固定
因为OLAP系统I/O压力大,表也非常大,收集统计信息很耗费资源
并且OLAP系统表的数据随时都在变化(因为实时在入库),那么就需要经常收集统计信息
所以OLAP系统收集统计信息不现实
在OLTP系统中要优化一个SQL语句,首先要保证SQL语句中所有的表统计是准确的,如果统计信息都不准确,那么SQL优化就无从谈起,在OLAP系统中,如果数据量特别大,一般不会去收集统计信息,会直接利用HINT把执行计划给固定住。
常用的统计信息收集脚本:
非分区表:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size repeat',
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
对分区表收集统计信息:
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'ROBINSON',
tabname =>'P_TEST',
estimate_percent =>30,
no_invalidate =>FALSE,
degree =>8,
granularity =>'ALL',
cascade =>TRUE);
END;
/
estimate_percent表示采样率,采样率设置太大,也没必要,如果表非常大,采样率过高会导致收集统计信息跑很长,增加了系统压力。采样率设置过小,统计的信息就不能很完整的体现表中数据的分布,这样CBO在进行执行计划的选择上,很可能选择错误的执行计划。根所工作经验:
表小于1GB采样率可以设置50%-100%
表大于1GB小于5GB可以设置30%
表大于5GB这类表都应该进行分区,采样率可以设置为30%
用segmet_size来计算一个表是大表还是小表
method_opt:表示收集的方法,一个稳定的系统收集统计推荐使用
method_opt:'for all columns size repeat'
repeat表示以前收集过直方图,现在收集统计信息的时候就收集直方图,如果以前没收集过直方图,现在收集统计信息的时候就不收集
method_opt:'for all columns size auto'
auto表示oracle根据谓词过滤信息,自动判断该列是否收集直方图,一个稳定的系统,不应该让Oracle去自动判断,自动判断很可能会出事,比如某列不该收集直方图,设置auto过后它自己去收集直方图了,从而导致系统不稳定。
degree:表示收集统计信息的时候并行度,并行度根据系统配置以及当前系统可用资源自行调协,一般degree设置4-8
一般设置为4
cascade:表示收集表的统计信息时候同时收集索引的统计信息
no_invalidate:表示收集统计信息之后在共享池中引用了相关的SQL游标是否失败。这个一定要设置为FALSE,不然你可能在做SQL优化的时候,你发现明明更新了统计信息,但是执行计划还是没改变
----实验1----
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size skewonly',--》这个会收集直方图
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
select a.column_name,
b.num_raws,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows*100,2) selectivity,
num_nulls,
density,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner=upper('&table_name')
and a.table_name=upper('&table_name')
and a.column_name=upper('&column_name');
---实验2----
drop table test purge;
create table test as select * from dba_objects;
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname =>'SCOTT',
tabname =>'DEPT',
estimate_percent =>30,
method_opt =>'for all columns size auto',
no_invalidate =>FALSE,
degree =>8,
cascade =>TRUE);
END;
/
select a.column_name,
b.num_raws,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows*100,2) selectivity,
num_nulls,
density,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a,dba_tables b
where a.owner=b.owner
and a.table_name=b.table_name
and a.owner=upper('&table_name')
and a.table_name=upper('&table_name')
and a.column_name=upper('&column_name');
用auto会根据where子句后的字段来自动创建直方图
例如在创建了表之后 执行 select * from test where owner=‘SCOTT';之后在统计信息的话
这时会在owner列收集直方图
exec dbms_stats.flush_database_monitoring_info;
select owner,table_name name,object_type,stale_stats,last_analyzed
from dba_tab_statistics
where table_name in ('TEST')
and owner='OWNER_NAME'
and (stale_stats='YES' or last_analyzed is null);
如果没有返回数据则表示没有过期
delete from test where rownum<=83499*0.11;
这时再查看则会返回数据,则表示已经过期
在做优化的时候,如果发现统计信息没过期,则要检查采样率
select owner,
table_name,
num_rows,
sample_size,
trunc(sample_size/num_row*100) estimate_percent
from dba_tab_statistics
where owner='SCOTT' and table_name='TEST';
以下语句处理查询大批量的表的统计信息是否过期
select '''' || object_owner || '''','''' || object_name || ''','
from plan_table
where object_type='TABLE'
union
---table in the index------
select '''' || table_owner || '''','''' || table_name || '*'','
from dba_indexes
where owner in
(select distinct object_owner from plan_table where rownum>0)
and index_name in
(select distinct object_name from plan_table where rownum>0)
order by 2;
----再把上面的结果贴入下面的代码--------
select owner,table_name name,object_type,stale_stats,last_analyzed
from dba_tab_statistics
where table_name in
(
table_name
)
and owner='owner'
and (stale_stats='SYS' or last_analyzed is null);
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1816099/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30109892/viewspace-1816099/