1.收集统计信息的方式
- for all columns size skewonly
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
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 = 'SCOTT'
and a.table_name = 'TEST';
method_opt设置成for all columns size skewnoly时,让oracle自动判断是否需要收集直方图,除了主键列和唯一值列之外的列都收集直方图。这不符合我们收集直方图的规则,只有在where关键字之后出现的列才收集直方图。
- for all columns size auto
创建一个新表,使用auto的方式对表test收集直方图。
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
查看统计信息
可以看到,当对一个新表使用auto方式收集直方图的时候,num_buckets数为1,说明没有收集直方图
接下来运行sql,在收集直方图
SQL> select count(*) from test where owner='SB';
COUNT(*)
----------
0
exec dbms_stats.flush_database_monitoring_info; --刷新内存
查看where关键字后列值进行过滤操作的信息
select
r.name owner,
o.name table_name ,
c.name column_name,
equality_preds, ---等值过滤
equijoin_preds, ---等值JOIN过滤 比如where a.id=b.id
nonequijoin_preds, ----不等JOIN过滤
range_preds, ----范围过滤 > >= < <= between and
like_preds, ----LIKE过滤
null_preds, ----NULL 过滤
timestamp
from
sys.col_usage$ u,
sys.obj$ o,
sys.col$ c,
sys.user$ r
where
o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name='SCOTT' and o.name='TEST';
通过显示结果可以知道,用户scott下test表中的owner列进行了一次等值连接
再次执行auto模式收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size auto',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
查询统计信息
可以看出对owner列收集了直方图,如果有sql运行,有where列,就收集直方图。
使用auto方式收集统计信息也是不可用的,在oracle11g里用没有问题,因为解决了绑定变量窥探的问题,在11g之前使用for all columns size 1方式,遇到需要收集的列单独收集。
- for all columns size repeat
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
这种方式是推荐的方式,对以前收集过的列,进行收集,以前没收集过的,就不收集。
当系统运行稳定的时候采用这种方式收集统计信息。第一次上线的时候先使用for all columns size 1方式收集,在发现运行慢的sql对单独列进行收集统计信息,之后等系统运行稳定,使用repeat方式收集统计信息。
- for columns <column name> size skewonly
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for columns object_name size skewonly',
no_invalidate => FALSE,
degree => 8,
cascade => TRUE);
END;
/
查看统计信息
可以看到对object_name列单独收集了直方图。
常用的统计信息相关的脚本
1.查看收集统计信息的采样率
SELECT owner,
table_name,
num_rows,
sample_size,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner='SCOTT' AND table_name='TEST';
采样率是否合适,根据表的大小而定,根究dba_segments查看表的大小,制定采样率
2G以内采样率为100%
2G-5G采样率为50%
5G-10G采样率为30%
10G-20G采样率为10%
20G-30G采样率为5%
30G以上采样率为1%
2.查看统计信息是否过期
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 = 'SCOTT'
and (stale_stats = 'YES' or last_analyzed is null);
统计信息过期的标准是,变化率超过10%
如果一个表一年都没有DML操作,是否对他重新收集统计信息呢?不需要,因为统计信息根本没有过期。
3.定制收集统计信息的脚本
DECLARE
CURSOR STALE_TABLE IS
SELECT OWNER,
SEGMENT_NAME,
CASE
WHEN SIZE_GB < 2 THEN
100
WHEN SIZE_GB >= 2 AND SIZE_GB < 5 THEN
50
WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN
30
WHEN SIZE_GB >= 10 AND SIZE_GB < 30 THEN
10
WHEN SIZE_GB >= 30 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 = 'ADWU_OPTIMA_AP11'
AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
AND OWNER = 'ADWU_OPTIMA_AP11')
GROUP BY OWNER, SEGMENT_NAME);
BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
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 columns size repeat',
DEGREE => 8,
GRANULARITY => 'ALL',--这个是针对分区表,非分区表要进行改写
NO_INVALIDATE => FALSE,
CASCADE => TRUE);
END LOOP;
END;
/
4.DBA_TAB_STATS_HISTORY 可以查看表历史收集统计信息的时间,利用下面脚本分析某个表统计信息收集时间间隔
select owner,
table_name,
partition_name,
subpartition_name,
stats_update_time,
stats_update_time - lag(stats_update_time, 1, null) over(partition by owner, table_name order by stats_update_time) interval
from DBA_TAB_STATS_HISTORY
where owner = 'SCOTT'
and table_name = 'TEST'
order by owner, table_name, stats_update_time desc;
5.下面SQL是查询 TOP 50 INSERTS,UPDATES,DELETES 的表
select * from
(
select * from
(
select * from
(
select u.name owner, o.name table_name, null partition_name, null subpartition_name,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like 'XDB'
union all
select * from
(
select u.name owner, o.name table_name, null partition_name, null subpartition_name,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO') truncated,
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1,'YES','NO'),
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like '%SYS%' and owner not like '%XDB%'
) order by inserts desc
) where rownum<=50;
方法一:
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
方法二:
alter system set "_optimizer_autostats_job"=false scope=spfile;
alter system set "_optimizer_autostats_job"=true scope=spfile;
Pfile可以直接修改初始化参数文件,重新启动数据库。