我们发现有很多dba对
oracle
的统计分析都存在一些误解,认为这个是oracle后台自动运行的,我们不用去关心,我觉得统计分析有很深的学问。
我想问大家的是:
1、你们开启了的每天收集统计信息,每天窗口运行正常吗?
2、所有的表都使用oracle的自动窗口收集?大表是不是要考虑定制收集?分区表是不是要增量收集?
3、在不影响业务的情况下,什么时间段收集统计信息?
4、如何判断统计信息是否过期,如何判断哪些字段需要收集直方图,收集的规则是什么?
下面我来一一解答:
1、
查询自动任务是否开启
SELECT OPERATION_NAME,STATUS FROM DBA_AUTOTASK_OPERATION WHERE CLIENT_NAME LIKE '%stats%';
STATUS为ENABLED表示任务开启
如果为DISABLE exec dbms_auto_task_admin.ENABLE(client_name => 'auto optimizer stats collection',operation =>'auto optimizer stats job',window_name=> null);
查看后台的窗口信息:
select * from dba_scheduler_windows
查看每天的统计JOB是否成功 racle 11g 调用窗口会自动生成以ORA$AT_OS_OPT开头的JOB
select * from dba_scheduler_job_run_details where job_name LIKE '%ORA$AT_OS_OPT%' ORDER BY LOG_DATE DESC
如果stoped要查看具体的内容:我这边有两次发生stop.
(1) 执行总共收集的时间,超出了收集窗口的时间(默认是周一到周五 22:00~02:00 4个小时 周六和周日06:00~02:00 20个小时)
解决办法:可以开启并行收集(默认是串行)
EXEC DBMS_STATS.SET_PARAM('DEGREE',4);
或者将窗口时间调长
begin
dbms_scheduler.set_attribute(name => 'TUESDAY_WINDOW', attribute => 'duration', value =>numtdosinterval(480,'minute'));
end;
/
(2) job执行到一半的时候,由于其它窗口启动导致收集终止。
解决办法:将收集的窗口优先级调高
BEGIN
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'window_priority',
value => 'HIGH');
END;
/
---备注:调整窗口的时间点收集:
BEGIN
dbms_scheduler.disable(
name => 'TUESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=TUE;byhour=03;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'TUESDAY_WINDOW');
END;
/
2、表是否都是oracle自动收集
(1) 获取对象级别的统计信息设置选型
SELECT * FROM DBA_TAB_STAT_PREFS
(2)大表我们要定制化收集,调整收集的采样率(例如我们有张表是800G,如果你使用oracle的默认收集,收集时间保守估计在800分钟)
可以定制收集
exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',100);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XX',
tabname => 'XX',
estimate_percent => 30,
degree => 4,
no_invalidate => FALSE,
method_opt => 'for all columns size auto',
cascade=>TRUE
);
END;
具体什么样的表需要定制,这个根据你们的数据库实际情况,找出具体的表手工收集,下面有个脚本参考一下:
declare
cursor stale_table is
select owner, segment_name,
case when size_gb<0.5 then 30
when size_gb>=0.5 and size_gb<1 then 20
when size_gb >=1 and size_gb<5 then 10
when size_gb>=5 and size_gb<10 then
when size_gb>=10 then 1
end as percent,
8 as degree
from (select owner,segment_name,sum(bytes/1024/1024/1024)size_gb
from dba_segments where owner='' AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE
(LAST_ANALYZED IS NULL OR STALE_STATS='YES') AND OWNER='')
GROUP BY OWNER,SEGMENT_NAME);
BEGIN
FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>STALE.OWNER,
TABNAME=>STALE.SEGMENT_NAME,
ESTIMATE_PERCENT =>STALE.PERCENT,
METHOD_OPT =>'for all colums size skewonly',---默认oracle 是for all colums size auto
degree =>8,
granularity =>'ALL',
cascade =>true);
end 1oop;
end;
(3) 分区表的增量收集:
exec dbms_stats.set_table_prefs('username','TABLENAME','INCREMENTAL','TRUE');
为什么要增量收集,我们这边有一个log表,记录用户下的所有源表的增删改查,这个表用于用户程序的增量同步的判断。
每天的数据量很大,按周的rang分区,查询的时间都是在最近时间。
----我们暂且不用考虑分区表的设计问题和增量的断点问题。
3、第三点上面已经解答。
4、
(1)判断是否过期
(stale_stats='YES' or last_analyzed is null)可以说明统计信息过期:
select owner,table_name ,object_type,stale_stats,last_analyzed from dba_tab_statistics where owner ='XX'
and table_name in('XX')
(2)oracle默认表的变化量在10%收集统计信息。
有时我们发现有些表数据量变化不大,很久没有收集统计信息,导致执行计划问题(oracle 估算基数处在一个临界点)我们可以根据表的变化率进行微调,从而制定合理的阀值。
查看表的变化率:
select *
from (select t2.table_name,
t2.partition_name,
t2.subpartition_name,
t2.inserts,
t2.updates,
t2.deletes,
t1.NUM_ROWS,
case
when (t1.NUM_ROWS = 0 or t1.NUM_ROWS is null) then
100
else
trunc((t2.inserts + t2.updates + t2.deletes) / t1.NUM_ROWS * 100,
2)
end "mod_pct(%)",
case
when t1.LAST_ANALYZED is null then
'未分析'
else
to_char(t1.LAST_ANALYZED)
end LAST_ANALYZED,
t2.timestamp,
'begin dbms_stats.gather_table_stats('||'ownname=>'''|| t2.table_owner ||
''','||'tabname=>''' || t2.table_name ||''','||'estimate_percent=>100'||','||'degree=>4'||',cascade => TRUE); end;' EXEC_STATS
from dba_tab_statistics t1, dba_tab_modifications t2
where t1.OWNER = t2.table_owner
and t1.TABLE_NAME = t2.table_name
and DECODE(t1.PARTITION_NAME, t2.partition_name, 0, 1) = 0
and DECODE(t1.SUBPARTITION_NAME, t2.subpartition_name, 0, 1) = 0
and t2.table_owner = '' AND T2.table_name='')
where "mod_pct(%)" >= 0
order by "mod_pct(%)" desc;
调整阀值:
exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',1); 这样表变化率在1%会自动收集
(3) 直方图什么情况下收集:
oracle默认是for all colums size auto
oracle文档中说(文档 ID 338926.1)中有描述
Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
其实这句话有两层含义
一是必须这个字段是被条件筛选到就是where a=xx
select object_id from dba_objects where owner='XX' and object_name='XX';
select name,intcol# from sys.col$ where obj#=XX and name='XX';
select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=XX;
这个字段equality_preds要有值
二是 字段的数据有倾斜
select count(distinct a) from xx 和select count(*) from xx 数据量不能相等并且数据量大于254,会以HEIGHT BALANCED收集。
小于254会以FREQUENCY方式收集,当然我们也可以指定桶的大小。
5 具体什么情况下用 auto 、repeat 和 skewonly 方式收集统计信息,这个要理解这三个参数的含义。我个人的见解
auto 使用于系统上线的初期,系统上线稳定了使用repeat方式,如果要自己定制收集使用skewonly方式。
如果发现不足之处,望指出。
我想问大家的是:
1、你们开启了的每天收集统计信息,每天窗口运行正常吗?
2、所有的表都使用oracle的自动窗口收集?大表是不是要考虑定制收集?分区表是不是要增量收集?
3、在不影响业务的情况下,什么时间段收集统计信息?
4、如何判断统计信息是否过期,如何判断哪些字段需要收集直方图,收集的规则是什么?
下面我来一一解答:
1、
查询自动任务是否开启
SELECT OPERATION_NAME,STATUS FROM DBA_AUTOTASK_OPERATION WHERE CLIENT_NAME LIKE '%stats%';
STATUS为ENABLED表示任务开启
如果为DISABLE exec dbms_auto_task_admin.ENABLE(client_name => 'auto optimizer stats collection',operation =>'auto optimizer stats job',window_name=> null);
查看后台的窗口信息:
select * from dba_scheduler_windows
查看每天的统计JOB是否成功 racle 11g 调用窗口会自动生成以ORA$AT_OS_OPT开头的JOB
select * from dba_scheduler_job_run_details where job_name LIKE '%ORA$AT_OS_OPT%' ORDER BY LOG_DATE DESC
如果stoped要查看具体的内容:我这边有两次发生stop.
(1) 执行总共收集的时间,超出了收集窗口的时间(默认是周一到周五 22:00~02:00 4个小时 周六和周日06:00~02:00 20个小时)
解决办法:可以开启并行收集(默认是串行)
EXEC DBMS_STATS.SET_PARAM('DEGREE',4);
或者将窗口时间调长
begin
dbms_scheduler.set_attribute(name => 'TUESDAY_WINDOW', attribute => 'duration', value =>numtdosinterval(480,'minute'));
end;
/
(2) job执行到一半的时候,由于其它窗口启动导致收集终止。
解决办法:将收集的窗口优先级调高
BEGIN
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'window_priority',
value => 'HIGH');
END;
/
---备注:调整窗口的时间点收集:
BEGIN
dbms_scheduler.disable(
name => 'TUESDAY_WINDOW');
dbms_scheduler.set_attribute(
name => 'TUESDAY_WINDOW',
attribute => 'repeat_interval',
value => 'freq=daily;byday=TUE;byhour=03;byminute=0;bysecond=0');
dbms_scheduler.enable(
name => 'TUESDAY_WINDOW');
END;
/
2、表是否都是oracle自动收集
(1) 获取对象级别的统计信息设置选型
SELECT * FROM DBA_TAB_STAT_PREFS
(2)大表我们要定制化收集,调整收集的采样率(例如我们有张表是800G,如果你使用oracle的默认收集,收集时间保守估计在800分钟)
可以定制收集
exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',100);
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XX',
tabname => 'XX',
estimate_percent => 30,
degree => 4,
no_invalidate => FALSE,
method_opt => 'for all columns size auto',
cascade=>TRUE
);
END;
具体什么样的表需要定制,这个根据你们的数据库实际情况,找出具体的表手工收集,下面有个脚本参考一下:
declare
cursor stale_table is
select owner, segment_name,
case when size_gb<0.5 then 30
when size_gb>=0.5 and size_gb<1 then 20
when size_gb >=1 and size_gb<5 then 10
when size_gb>=5 and size_gb<10 then
when size_gb>=10 then 1
end as percent,
8 as degree
from (select owner,segment_name,sum(bytes/1024/1024/1024)size_gb
from dba_segments where owner='' AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE
(LAST_ANALYZED IS NULL OR STALE_STATS='YES') AND OWNER='')
GROUP BY OWNER,SEGMENT_NAME);
BEGIN
FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>STALE.OWNER,
TABNAME=>STALE.SEGMENT_NAME,
ESTIMATE_PERCENT =>STALE.PERCENT,
METHOD_OPT =>'for all colums size skewonly',---默认oracle 是for all colums size auto
degree =>8,
granularity =>'ALL',
cascade =>true);
end 1oop;
end;
(3) 分区表的增量收集:
exec dbms_stats.set_table_prefs('username','TABLENAME','INCREMENTAL','TRUE');
为什么要增量收集,我们这边有一个log表,记录用户下的所有源表的增删改查,这个表用于用户程序的增量同步的判断。
每天的数据量很大,按周的rang分区,查询的时间都是在最近时间。
----我们暂且不用考虑分区表的设计问题和增量的断点问题。
3、第三点上面已经解答。
4、
(1)判断是否过期
(stale_stats='YES' or last_analyzed is null)可以说明统计信息过期:
select owner,table_name ,object_type,stale_stats,last_analyzed from dba_tab_statistics where owner ='XX'
and table_name in('XX')
(2)oracle默认表的变化量在10%收集统计信息。
有时我们发现有些表数据量变化不大,很久没有收集统计信息,导致执行计划问题(oracle 估算基数处在一个临界点)我们可以根据表的变化率进行微调,从而制定合理的阀值。
查看表的变化率:
select *
from (select t2.table_name,
t2.partition_name,
t2.subpartition_name,
t2.inserts,
t2.updates,
t2.deletes,
t1.NUM_ROWS,
case
when (t1.NUM_ROWS = 0 or t1.NUM_ROWS is null) then
100
else
trunc((t2.inserts + t2.updates + t2.deletes) / t1.NUM_ROWS * 100,
2)
end "mod_pct(%)",
case
when t1.LAST_ANALYZED is null then
'未分析'
else
to_char(t1.LAST_ANALYZED)
end LAST_ANALYZED,
t2.timestamp,
'begin dbms_stats.gather_table_stats('||'ownname=>'''|| t2.table_owner ||
''','||'tabname=>''' || t2.table_name ||''','||'estimate_percent=>100'||','||'degree=>4'||',cascade => TRUE); end;' EXEC_STATS
from dba_tab_statistics t1, dba_tab_modifications t2
where t1.OWNER = t2.table_owner
and t1.TABLE_NAME = t2.table_name
and DECODE(t1.PARTITION_NAME, t2.partition_name, 0, 1) = 0
and DECODE(t1.SUBPARTITION_NAME, t2.subpartition_name, 0, 1) = 0
and t2.table_owner = '' AND T2.table_name='')
where "mod_pct(%)" >= 0
order by "mod_pct(%)" desc;
调整阀值:
exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',1); 这样表变化率在1%会自动收集
(3) 直方图什么情况下收集:
oracle默认是for all colums size auto
oracle文档中说(文档 ID 338926.1)中有描述
Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
其实这句话有两层含义
一是必须这个字段是被条件筛选到就是where a=xx
select object_id from dba_objects where owner='XX' and object_name='XX';
select name,intcol# from sys.col$ where obj#=XX and name='XX';
select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=XX;
这个字段equality_preds要有值
二是 字段的数据有倾斜
select count(distinct a) from xx 和select count(*) from xx 数据量不能相等并且数据量大于254,会以HEIGHT BALANCED收集。
小于254会以FREQUENCY方式收集,当然我们也可以指定桶的大小。
5 具体什么情况下用 auto 、repeat 和 skewonly 方式收集统计信息,这个要理解这三个参数的含义。我个人的见解
auto 使用于系统上线的初期,系统上线稳定了使用repeat方式,如果要自己定制收集使用skewonly方式。
如果发现不足之处,望指出。