oracle统计信息

1. 查看表的统计信息

1.建表
SQL> create table test as select * from dba_objects;

2.查看表的统计信息
select owner, table_name, num_rows, blocks, avg_row_len
  from dba_tables
 where owner = 'SCOTT'
   and table_name = 'TEST';
OWNER			       TABLE_NAME			NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT			       TEST

3. 收集统计信息
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
     ownname => 'SCOTT',
 tabname => 'TEST',
 estimate_percent => 100,
 method_opt   => 'for all columns size auto',
 degree => 30,
 cascade => TRUE);
  END;
 /
 

4.再次查看统计信息
select owner, table_name, num_rows, blocks, avg_row_len
  from dba_tables
 where owner = 'SCOTT'
      and table_name = 'TEST';

OWNER			       TABLE_NAME			NUM_ROWS     BLOCKS AVG_ROW_LEN
------------------------------ ------------------------------ ---------- ---------- -----------
SCOTT			       TEST				   86262       1260	     98


5. 查看test表的直方图
select sta.column_name,
       sta.num_distinct,
       sta.num_nulls,
       sta.num_buckets,
       sta.HISTOGRAM
  from dba_tab_col_statistics sta
 where sta.owner = 'SCOTT'
   and sta.table_name = 'TEST'



6.查看表和列的统计信息
select sta.column_name,
       tab.num_rows,
       sta.num_nulls,
       sta.num_distinct cardinality,
       round(sta.num_distinct / tab.num_rows * 100, 2) selectivity,
       sta.HISTOGRAM,
       sta.num_buckets
  from dba_tab_col_statistics sta, dba_tables tab
 where sta.owner = tab.owner
   and sta.table_name = tab.table_name
   and sta.owner = 'SCOTT'
   and sta.table_name = 'TEST';

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

estimate_percent:采样率，0.0000001~100，一般设置为30
degree ：设置cpu负载
method_opt ：直方图收集策略（for all columns size 1:所有列不收集，for all columns size skewonly：对所有列自动判断是否收集，for all columns size auto:对出现在where条件中的列自动判断是否收集，for all columns size repeat:当前哪些列收集统计信息，还对那些列收集）

2.查看索引的统计信息

1.创建索引（会自动收集统计信息）
SQL> create index idx_id on test(object_id);

2.查看索引统计信息
select idx.blevel, idx.leaf_blocks, idx.clustering_factor, idx.status
  from dba_indexes idx
 where idx.index_name = 'IDX_ID';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- --------
	 1	   191		    1304 VALID

3. 单独对索引收集统计信息
begin
  dbms_stats.gather_index_stats( 
  ownname => 'SCOTT',
  indname => 'IDX_ID');
 end;
/ 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.查看统计信息状态

1.查看统计信息是否过期
select s.owner, s.table_name, s.object_type, s.stale_stats, s.last_analyzed
  from dba_tab_statistics s
 where s.owner = 'SCOTT'
   AND S.table_name = 'EMP';
PS：stale_stats为yes表示统计信息过期
   

2.查看统计信息过期原因
select alm.table_owner,
       alm.table_name,
       alm.inserts,
       alm.updates,
       alm.deletes,
       alm.timestamp
  from all_tab_modifications alm
 where alm.table_owner = 'SCOTT'
   and alm.table_name = 'TEST';
PS：当表中有10%的数据发生改变，就会引起统计信息过期


3.检查表统计信息过期sql
select owner, table_name, object_type, stale_stats, last_analyzed
  from dba_tab_statistics
 where (owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select idx.table_owner, idx.table_name
          from dba_indexes idx
         where (idx.owner, idx.table_name) in
               (select plt.object_owner, plt.object_name
                  from plan_table plt
                 where plt.object_type = '%INDEX%'))



4.统计过期原因
select *
  from all_tab_modifications
 where (table_owner, table_name) in
       (select object_owner, object_name
          from plan_table
         where object_type like '%TABLE%'
        union
        select table_owner, table_name
          from dba_indexes
         where (owner, index_name) in
               (select object_owner, object_name
                  from plan_table
                 where object_type like '%INDEX%'));

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

索引统计信息

select owner,index_name,num_rows,blevel,leaf_blocks,clustering_factor,status,last_analyzed 
  from dba_indexes 
 where owner = 'SYS'
   and index_name = 'test'; 

--判断统计信息是否陈旧
select owner, table_name, object_type, stale_stats, num_rows, last_analyzed
 from dba_tab_statistics
 where table_name = 'TEST3'
 and owner = 'SCOTT';
 tips：stale_stats 若为 yes 则陈旧 ，  no不陈旧 


查看统计信息过期的原因（回收高水位、经常要rebuild的索引都可以用这个判断）： 
当表中有超过10%的数据发生变化（insert、update、delete），就会引起统计信息过去 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15