在 Oracle 11g,系统自带的 JOB 里面有收集统计信息的任务,但并不一定每天每天都会收集。
在 DBMS_STATS 包中有一个 STALE_PERCENT 参数,这个参数的意义是:当表中的数据量修改超过总数据量的一定比例数,会再次触发统计信息收集,默认值为10%。
这样对于大表来说就有一个问题,收集的时间不会很及时,可能会隔好几天才会收集一次,这样会导致表的统计信息不准确,对数据库的 SQL 解析产生影响。
大表通常都是分区表,下面的收集方案主要是针对分区表,也涵盖了普通表。
--方案一
对于非分区表,使用默认的统计信息采集方法;对于分区表,使用增量采集方法,只对数据有变动的分区做收集。
BEGIN
--采集非分区表的统计信息
FOR i IN (select s.TABLE_NAME, s.num_rows
from dba_tables s
where s.OWNER = 'ACCT'
and not exists
(select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT'
and p.table_name = s.TABLE_NAME)) LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
i.TABLE_NAME,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
--采集分区表的统计信息
FOR j IN (select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
tabname => j.table_name,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
GRANULARITY => 'AUTO',
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
END;
--对于分区表,设置 INCREMENTAL 参数只对数据有变动的分区做收集
在对分区表收集统计信息的时候,如果想让 Oracle 只扫描发生数据改变的分区,避免全表扫描,需要满足以下条件:
(1) 指定分区表的 INCREMENTAL 参数设置为 TRUE
(2) 指定分区表的 PUBLISH 设置为 TRUE
(3) ESTIMATE_PERCENT 参数设为 AUTO_SAMPLE_SIZE,GRANULARITY 参数设为 AUTO
--统计信息收集设置选项
CASCADE 索引的统计信息是否和表一起收集。
DEGREE 收集的并行度。
ESTIMATE_PERCENT 决定收集的比例,范围是[0.000001,100]。可以通过DBMS_STATS.AUTO_SAMPLE_SIZE来让oracle自动决定收集的比例。这是默认值。
METHOD_OPT 控制列和柱状图的收集方法。
GRANULARITY 收集的粒度。
PUBLISH 当统计信息收集JOB执行完毕后,是否更新新的统计信息。
INCREMENTAL 决定是否维持分区表的全局统计信息,而不做全表扫描。当开启这个参数的时候,Oracle仅需扫描分区表中有数据变化的分区来更新分区表的统计信息,而无需扫描整个分区表。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。
--查询相关参数
select dbms_stats.get_prefs('PUBLISH') from dual;
select dbms_stats.get_prefs('method_opt') from dual;
select dbms_stats.get_prefs('GRANULARITY') from dual;
select dbms_stats.get_prefs('INCREMENTAL') from dual;
--查看分区表INCREMENTAL、PUBLISH的值
select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;
select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;
--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');
--只收集数据变动的分区
BEGIN
FOR j IN (select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
END LOOP;
END;
--方案二
只修改 STALE_PERCENT 参数,不创建 JOB。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。
select dbms_stats.get_prefs('STALE_PERCENT') from dual;
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
END;
--方案三
对于非分区表,使用默认的统计信息采集方法;对于分区表,由于现有分区每天会产生一个新的分区,在保证所有分区的统计信息都收集的基础上,每天只对新生成的分区做收集。
BEGIN
--采集非分区表的统计信息
FOR i IN (select s.TABLE_NAME, s.num_rows
from dba_tables s
where s.OWNER = 'ACCT'
and not exists
(select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT'
and p.table_name = s.TABLE_NAME)) LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
i.TABLE_NAME,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
--采集分区表的统计信息
FOR j IN (select distinct table_name,
last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
tabname => j.table_name,
partname => j.partition_name,
estimate_percent => 0.000001,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
END;
在 DBMS_STATS 包中有一个 STALE_PERCENT 参数,这个参数的意义是:当表中的数据量修改超过总数据量的一定比例数,会再次触发统计信息收集,默认值为10%。
这样对于大表来说就有一个问题,收集的时间不会很及时,可能会隔好几天才会收集一次,这样会导致表的统计信息不准确,对数据库的 SQL 解析产生影响。
大表通常都是分区表,下面的收集方案主要是针对分区表,也涵盖了普通表。
--方案一
对于非分区表,使用默认的统计信息采集方法;对于分区表,使用增量采集方法,只对数据有变动的分区做收集。
BEGIN
--采集非分区表的统计信息
FOR i IN (select s.TABLE_NAME, s.num_rows
from dba_tables s
where s.OWNER = 'ACCT'
and not exists
(select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT'
and p.table_name = s.TABLE_NAME)) LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
i.TABLE_NAME,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
--采集分区表的统计信息
FOR j IN (select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
tabname => j.table_name,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
GRANULARITY => 'AUTO',
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
END;
--对于分区表,设置 INCREMENTAL 参数只对数据有变动的分区做收集
在对分区表收集统计信息的时候,如果想让 Oracle 只扫描发生数据改变的分区,避免全表扫描,需要满足以下条件:
(1) 指定分区表的 INCREMENTAL 参数设置为 TRUE
(2) 指定分区表的 PUBLISH 设置为 TRUE
(3) ESTIMATE_PERCENT 参数设为 AUTO_SAMPLE_SIZE,GRANULARITY 参数设为 AUTO
--统计信息收集设置选项
CASCADE 索引的统计信息是否和表一起收集。
DEGREE 收集的并行度。
ESTIMATE_PERCENT 决定收集的比例,范围是[0.000001,100]。可以通过DBMS_STATS.AUTO_SAMPLE_SIZE来让oracle自动决定收集的比例。这是默认值。
METHOD_OPT 控制列和柱状图的收集方法。
GRANULARITY 收集的粒度。
PUBLISH 当统计信息收集JOB执行完毕后,是否更新新的统计信息。
INCREMENTAL 决定是否维持分区表的全局统计信息,而不做全表扫描。当开启这个参数的时候,Oracle仅需扫描分区表中有数据变化的分区来更新分区表的统计信息,而无需扫描整个分区表。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。
--查询相关参数
select dbms_stats.get_prefs('PUBLISH') from dual;
select dbms_stats.get_prefs('method_opt') from dual;
select dbms_stats.get_prefs('GRANULARITY') from dual;
select dbms_stats.get_prefs('INCREMENTAL') from dual;
--查看分区表INCREMENTAL、PUBLISH的值
select dbms_stats.get_prefs('INCREMENTAL','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;
select dbms_stats.get_prefs('PUBLISH','ACCT','ELMP_TRANS_CUSTOMACCOUNTFLOW') from dual;
--exec DBMS_STATS.SET_PARAM('INCREMENTAL','TRUE');
--只收集数据变动的分区
BEGIN
FOR j IN (select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.SET_TABLE_PREFS('ACCT', j.table_name, 'INCREMENTAL', 'TRUE');
END LOOP;
END;
--方案二
只修改 STALE_PERCENT 参数,不创建 JOB。
STALE_PERCENT 当表的数据量修改超过总数据量的一定比例数,再次触发统计信息收集,默认值为10%。
select dbms_stats.get_prefs('STALE_PERCENT') from dual;
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');
END;
--方案三
对于非分区表,使用默认的统计信息采集方法;对于分区表,由于现有分区每天会产生一个新的分区,在保证所有分区的统计信息都收集的基础上,每天只对新生成的分区做收集。
BEGIN
--采集非分区表的统计信息
FOR i IN (select s.TABLE_NAME, s.num_rows
from dba_tables s
where s.OWNER = 'ACCT'
and not exists
(select distinct TABLE_NAME
from dba_tab_partitions p
where table_owner = 'ACCT'
and p.table_name = s.TABLE_NAME)) LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
i.TABLE_NAME,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
--采集分区表的统计信息
FOR j IN (select distinct table_name,
last_value(partition_name) over(partition by table_name order by partition_position ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) partition_name
from dba_tab_partitions p
where table_owner = 'ACCT') LOOP
DBMS_STATS.GATHER_TABLE_STATS('ACCT',
tabname => j.table_name,
partname => j.partition_name,
estimate_percent => 0.000001,
DEGREE => 5,
CASCADE => TRUE);
END LOOP;
END;