Oracle数据库统计信息

Oracle数据库统计信息

𝓓𝓸𝓷 Lv6
  2024-07-12 13:50:45 2024-07-12 13:50:45 Created   2025-03-26 19:13:46 2025-03-26 19:13:46 Updated    

Oracle Database Statistics Gathering

一、查看统计信息
1
2
3
4
5
6
7
8
9
10
11
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 = 'ADMIN'
   and a.table_name = 'TEST';
二、查看统计信息是否过期
1
2
3
4
5
6
7
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 = 'ADMIN'
   and (stale_stats = 'YES' or last_analyzed is null);
三、收集统计信息
1.非分区收集统计信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
(1)收集某个用户下某些非分区表的统计信息

begin

  dbms_stats.gather_table_stats(ownname => 'ADMIN',
                                
                                tabname => 'TEST',
                                
                                estimate_percent => 30,
                                
                                method_opt => 'for all columns size repeat',
                                
                                no_invalidate => FALSE,
                                
                                degree => 8,
                                
                                cascade => TRUE);

end;

/


(2)收集某用户下非分区表的统计信息

begin
dbms_stats.gather_schema_stats(ownname => 'ADMIN',
                                
                                estimate_percent => 30,
                                
                                method_opt => 'for all columns size repeat',
                                
                                no_invalidate => FALSE,
                                
                                degree => 8,
                                
                                cascade => TRUE);
                                
end;

/


Oracle10g为某个用户收集统计信息:

/* Formatted on 2024/09/11 14:22:01 (QP5 v5.388) */
BEGIN
    DBMS_STATS.GATHER_SCHEMA_STATS (
        ownname            => 'WHPHIS24',
        Method_opt         => 'FOR ALL COLUMNS SIZE AUTO',
        degree             => 4,
        CASCADE            => TRUE,
        estimate_percent   => 30 );
END;
/

2.分区表和非分区表收集统计信息:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
(1)收集某个用户下某些表的统计信息,包括分区表、普通表

begin

  dbms_stats.gather_table_stats(ownname => 'ADMIN',
                                
                                tabname => 'TEST',
                                
                                estimate_percent => 30,
                                
                                method_opt => 'for all columns size repeat',
                                
                                no_invalidate => FALSE,
                                
                                degree => 8,
                                
                                granularity => 'ALL',
                                
                                cascade => TRUE);

end;

/


(2)收集某用户下所有表的统计信息,包括分区表、普通表

begin
dbms_stats.gather_schema_stats(ownname => 'ADMIN',
                               
                               estimate_percent => 30,
                                
                                method_opt => 'for all columns size repeat',
                                
                                no_invalidate => FALSE,
                                
                                degree => 8,
                                
                                granularity => 'ALL',
                                
                                cascade => TRUE);

end;

/
                               


(3)收集某用户下所有分区表的子分区统计信息

begin
dbms_stats.gather_schema_stats(ownname => 'HIE',
                               
                               estimate_percent => 30,
                                
                                method_opt => 'for all columns size repeat',
                                
                                no_invalidate => FALSE,
                                
                                degree => 8,
                                
                                granularity => 'SUBPARTITION',
                                
                                cascade => TRUE);

end;

/

                               
#method_opt: 指采样比率，一般采样比率设置为30%，采取率根据segment_size来定，小表100%，大表设低点。

for all columns size skewonly 指oracle自动判断收集直方图。慎用，生产库禁用。

for all columns size auto 指如果where条件里有列，刚将此列收集直方图。生产库最好也别用。oracle11g可以，但最好不用，10g不行。

for all columns size repeat 之前某个列收集过直方图则收集，如果没有收集过就不收集。

for all columns size 1 清除直方图

刚上线的数据库先用auto,再用repeat

#no_invalidate: 指收集统计信息后，缓存的游标立即失效。一般都要设置成false

#degree:并行度。

#granularity:该值也可以设置成PARTITION,

#cascade:指收集表的统计信息时候把所有的统计信息都收集。

#如果不是分区表，granularity选项会自动忽略，所以分区表统计信息脚本都可以执行。
四、按表的大小批量收集统计信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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
              5
             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 = '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',
                                  CASCADE          => TRUE);
  END LOOP;
END;

/

五、查看统计信息收集时间
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DBA_TAB_STATS_HISTORY 可以查看表历史收集统计信息的时间，利用下面脚本分析某个表统计信息收集时间间隔:

set linesize 200
set pagesize 200
col owner format a15
col table_name format a10
col partition_name format a15
col subparttition_name format a18
col stats_update_time format a38
col interval format a28

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 = 'ADMIN'
   and table_name = 'TEST'
 order by owner, table_name, stats_update_time desc;

六、查看什么原因导致统计信息失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
查看是什么原因导致统计信息不准确失效的:

col owner for a10
col table_name for a30
col partition_name for a20
col subpartition_name for a30
set linesize 300 pagesize 300


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 <= 80  order by 1;

七、查看Oracle统计信息自动收集时间窗口
1
2
3
4
5
6
7
8
9
SET LINESIZE 500 PAGESIZE 500
COL repeat_interval FOR a75
COL duration FOR a15

SELECT t1.window_name, t1.repeat_interval, t1.duration
  FROM dba_scheduler_windows t1, dba_scheduler_wingroup_members t2
 WHERE     t1.window_name = t2.window_name
       AND t2.window_group_name IN
               ('MAINTENANCE_WINDOW_GROUP', 'BSLN_MAINTAIN_STATS_SCHED');
  • Title: Oracle数据库统计信息
  • Author: 𝓓𝓸𝓷
  • Created at : 2024-07-12 13:50:45
  • Updated at : 2025-03-26 19:13:46
  • Link: https://www.zhangdong.me/oracle-statistics.html
  • License: This work is licensed under CC BY-NC-SA 4.0.
推荐文章
查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因 查看索引是否失效 查看索引是否失效 查看Oracle用户权限及角色权限 查看Oracle用户权限及角色权限
推荐文章
查看Oracle表空间增长过快原因 查看Oracle表空间增长过快原因 查看索引是否失效 查看索引是否失效
评论
  1. 一、查看统计信息
  2. 二、查看统计信息是否过期
  3. 三、收集统计信息
    1. 1.非分区收集统计信息:
    2. 2.分区表和非分区表收集统计信息:
  4. 四、按表的大小批量收集统计信息
  5. 五、查看统计信息收集时间
  6. 六、查看什么原因导致统计信息失效
  7. 七、查看Oracle统计信息自动收集时间窗口