Oracle收集和查看统计信息的方法_Oracle

一、什么是统计信息

oracle数据库里的统计信息是如下的一组数据：他们存储在数据字典里，且从多个维度描述了oracle数据库数据对象的详细信息。

oracle数据库里的统计信息主要分为以下6种情况：

（1）表的统计信息。

（2）列的统计信息。

（3）索引的统计信息。

（4）系统统计信息。

（5）数据字典统计信息。

（6）内部对象统计信息。

二、oracle收集和查看统计信息的方法

oracle数据库收集统计信息一般有以下2种方法：

（1）analyze命令。

（2）dbms_stats包。

针对以上6种统计信息，其中“表的统计信息”，“索引统计信息”，“列统计信息”，“数据字典统计信息”使用analyze或dbms_stats包收集均可以，但是“系统统计信息”和“内部对象统计信息”必须要dbms_stats包来收集才可以。

1、使用analyze命令收集统计信息

从oralce7开始，analyze命令就用来收集表、索引和列的统计信息。从oracle10g开始，创建索引后oracle会自动为您收集目标索引统计信息。analyze命令收集统计信息不会抹掉之间analyze结果。

创建测试表：

sql>create table t1 as select * from dba_objects;
sql>create index idx_t1 on t1(object_id);

（1）analyze索引统计信息：

sql>analyze index idx_t1 delete statistics;

（2）对表收集统计信息，并且以估算模式，采样比为15%：

sql>analyze table t1 estimate statistics sample 15 percent for table;

（3）对表收集统计信息，以统计模式：

sql>analyze table t1 compute statistics for table;

（4）对列收集统计信息，以计算模式：

sql>analyze table t1 compute statistics for columns object_name,object_id;

（5）以计算模式对表和列同时收集统计信息：

sql>analyze table t1 compute statistics for t1 for columns object_name,object_id;

（6）以计算模式对索引收集统计信息：

sql>analyze index idx_t1 compute statistics;

（7）删除表、表上的索引、表的所有列的统计信息：

sql>analyze table t1 delete statistics;

（8）以计算模式，同时收集表、表上的列、表上的索引的统计信息：

sql>analyze table t1 compute statistics;

2、使用dbms_stats包收集统计信息

从oracle 8.1.5开始，dbms_stats包就被广泛用于统计信息的收集，用dbms_stats包收集统计信息也是oracle官方推荐的方式。在收集cbo所需要的统计信息方面，可以简单的将dbms_stats包理解成是analyze命令的增强版。

dbms_stats包最常见的4个存储过程：

（1）dbms_stats.gather_table_stats：用于收集目标表，目标表上列及目标表上索引的统计信息。

（2）dbms_stats.gather_index_stats：用于收集指定索引的统计信息。

（3）dbms_stats.gather_schema_stats：用于收集schema下所有对象的统计信息。

（4）dbms_stats.gather_database_stats：用于收集全库统计对象的统计信息。

以下是dbms_stats包的具体用法：

（1）对表收集统计信息，并且以估算模式，采样比为15%：

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>15,method_opt=>'for table',cascade=>false);

注意：method_opt参数指定了for table不是在所有版本oracle下都是好用的。

（2）对表收集统计信息，以计算模式：

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>100,method_opt=>'for table',cascade=>false);

或

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>null,method_opt=>'for table',cascade=>false);

（3）对列收集统计信息，以计算模式：

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>100,method_opt=>'for all culumns size 1 object_name object_id',cascade=>false);

注意：以上方法收集了列objec_name、object_id的统计信息，同时也会收集表的统计信息。

（4）以计算模式对索引收集统计信息：

sql>exec dbms_stats.gather_index_stats(ownname=>'scott',indname=>'index_t1',estimate_percent=>100);

（5）删除表、表上的索引、表的所有列的统计信息：

sql>exec dbms_stats.delete_table_stats(ownname=>'scott',tabname=>'t1');

（6）以计算模式，同时收集表、表上的列、表上的索引的统计信息：

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>15 ,cascade=>true);

3、analyze和dbms_stats的区别

（1）analyze命令不能正确的收集分区表的统计信息，而dbms_stats包缺可以。

（2）analyze命令不能以并行收集统计信息，而dbms_stats包缺可以。

sql>exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',estimate_percent=>100, cascade=>false,degree=>4);

（3）dbms_stats包只能收集与cbo相关的统计信息，而与cbo无关的额外信息，比如行迁移/行链接的数量（chain_cnt），校验表和索引的结构信息等，dbms_stats包就无能为力了，而analyze命令是可以用来分析和收集上述额外信息。比如：

sql>analyze table xxx list chained rows into yyy; --用来分析和收集行迁移/行链接的数量。
sql>analyze index xxx validate structure; --用来分析索引结构。

4、查看统计信息

oracle里的统计信息存储在数据字典表中，可以通过脚本来查询对象的统计信息。

sosi.sh脚本如下（可以查看表、索引、列的统计信息）：

set echo off
set scan on
set lines 150
set pages 66
set verify off
set feedback off
set termout off
column uservar new_value table_owner noprint
select user uservar from dual;
set termout on
column table_name heading "tables owned by &table_owner" format a30
select table_name from dba_tables where owner=upper('&table_owner') order by 1
/
undefine table_name
undefine owner
prompt
accept owner prompt 'please enter name of table owner (null = &table_owner): '
accept table_name  prompt 'please enter table name to show statistics for: '
column table_name heading "table|name" format a15
column partition_name heading "partition|name" format a15
column subpartition_name heading "subpartition|name" format a15
column num_rows heading "number|of rows" format 9,999,999,990
column blocks heading "blocks" format 999,990
column empty_blocks heading "empty|blocks" format 999,999,990

column avg_space heading "average|space" format 9,990
column chain_cnt heading "chain|count" format 999,990
column avg_row_len heading "average|row len" format 990
column column_name  heading "column|name" format a25
column nullable heading null|able format a4
column num_distinct heading "distinct|values" format 999,999,990
column num_nulls heading "number|nulls" format 9,999,990
column num_buckets heading "number|buckets" format 990
column density heading "density" format 990
column index_name heading "index|name" format a15
column uniqueness heading "unique" format a9
column blev heading "b|tree|level" format 90
column leaf_blocks heading "leaf|blks" format 990
column distinct_keys heading "distinct|keys" format 9,999,999,990
column avg_leaf_blocks_per_key heading "average|leaf blocks|per key" format 99,990
column avg_data_blocks_per_key heading "average|data blocks|per key" format 99,990
column clustering_factor heading "cluster|factor" format 999,999,990
column column_position heading "col|pos" format 990
column col heading "column|details" format a24
column column_length heading "col|len" format 9,990
column global_stats heading "global|stats" format a6
column user_stats heading "user|stats" format a6
column sample_size heading "sample|size" format 9,999,999,990
column to_char(t.last_analyzed,'mm-dd-yyyy') heading "date|mm-dd-yyyy" format a10

prompt
prompt ***********
prompt table level
prompt ***********
prompt
select 
    table_name,
    num_rows,
    blocks,
    empty_blocks,
    avg_space,
    chain_cnt,
    avg_row_len,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from dba_tables t
where 
    owner = upper(nvl('&&owner',user))
and table_name = upper('&&table_name')
/
select
    column_name,
    decode(t.data_type,
           'number',t.data_type||'('||
           decode(t.data_precision,
                  null,t.data_length||')',
                  t.data_precision||','||t.data_scale||')'),
                  'date',t.data_type,
                  'long',t.data_type,
                  'long raw',t.data_type,
                  'rowid',t.data_type,
                  'mlslabel',t.data_type,
                  t.data_type||'('||t.data_length||')') ||' '||
    decode(t.nullable,
              'n','not null',
              'n','not null',
              null) col,
    num_distinct,
    density,
    num_buckets,
    num_nulls,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from dba_tab_columns t
where 
    table_name = upper('&table_name')
and owner = upper(nvl('&owner',user))
/

select 
    index_name,
    uniqueness,
    blevel blev,
    leaf_blocks,
    distinct_keys,
    num_rows,
    avg_leaf_blocks_per_key,
    avg_data_blocks_per_key,
    clustering_factor,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_indexes t
where 
    table_name = upper('&table_name')
and table_owner = upper(nvl('&owner',user))
/
break on index_name
select
    i.index_name,
    i.column_name,
    i.column_position,
    decode(t.data_type,
           'number',t.data_type||'('||
           decode(t.data_precision,
                  null,t.data_length||')',
                  t.data_precision||','||t.data_scale||')'),
                  'date',t.data_type,
                  'long',t.data_type,
                  'long raw',t.data_type,
                  'rowid',t.data_type,
                  'mlslabel',t.data_type,
                  t.data_type||'('||t.data_length||')') ||' '||
           decode(t.nullable,
                  'n','not null',
                  'n','not null',
                  null) col
from 
    dba_ind_columns i,
    dba_tab_columns t
where 
    i.table_name = upper('&table_name')
and owner = upper(nvl('&owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position
/

prompt
prompt ***************
prompt partition level
prompt ***************

select
    partition_name,
    num_rows,
    blocks,
    empty_blocks,
    avg_space,
    chain_cnt,
    avg_row_len,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_tab_partitions t
where 
    table_owner = upper(nvl('&&owner',user))
and table_name = upper('&&table_name')
order by partition_position
/


break on partition_name
select
    partition_name,
    column_name,
    num_distinct,
    density,
    num_buckets,
    num_nulls,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_part_col_statistics t
where 
    table_name = upper('&table_name')
and owner = upper(nvl('&owner',user))
/

break on partition_name
select 
    t.index_name,
    t.partition_name,
    t.blevel blev,
    t.leaf_blocks,
    t.distinct_keys,
    t.num_rows,
    t.avg_leaf_blocks_per_key,
    t.avg_data_blocks_per_key,
    t.clustering_factor,
    t.global_stats,
    t.user_stats,
    t.sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_ind_partitions t, 
    dba_indexes i
where 
    i.table_name = upper('&table_name')
and i.table_owner = upper(nvl('&owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/


prompt
prompt ***************
prompt subpartition level
prompt ***************

select 
    partition_name,
    subpartition_name,
    num_rows,
    blocks,
    empty_blocks,
    avg_space,
    chain_cnt,
    avg_row_len,
    global_stats,
    user_stats,
    sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_tab_subpartitions t
where 
    table_owner = upper(nvl('&&owner',user))
and table_name = upper('&&table_name')
order by subpartition_position
/
break on partition_name
select 
    p.partition_name,
    t.subpartition_name,
    t.column_name,
    t.num_distinct,
    t.density,
    t.num_buckets,
    t.num_nulls,
    t.global_stats,
    t.user_stats,
    t.sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_subpart_col_statistics t, 
    dba_tab_subpartitions p
where 
    t.table_name = upper('&table_name')
and t.owner = upper(nvl('&owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/

break on partition_name
select 
    t.index_name,
    t.partition_name,
    t.subpartition_name,
    t.blevel blev,
    t.leaf_blocks,
    t.distinct_keys,
    t.num_rows,
    t.avg_leaf_blocks_per_key,
    t.avg_data_blocks_per_key,
    t.clustering_factor,
    t.global_stats,
    t.user_stats,
    t.sample_size,
    to_char(t.last_analyzed,'mm-dd-yyyy')
from 
    dba_ind_subpartitions t, 
    dba_indexes i
where 
    i.table_name = upper('&table_name')
and i.table_owner = upper(nvl('&owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/

clear breaks
set echo on

附：查看表历史收集的统计信息情况

  select b.owner,
        b.object_name table_name, 
        to_char(a.analyzetime, 'yyyy-mm-dd hh24:mi:ss') last_analyzetime,
        to_char(a.savtime, 'yyyy-mm-dd hh24:mi:ss') curr_analyzetime,
        a.rowcnt
  from sys.wri$_optstat_tab_history a, dba_objects b
  where a.obj# = b.object_id
    and b.object_name ='zb_whole_orders_kafka_dispatch' and b.owner='zjopen'
  order by a.obj#, a.savtime;