Oracle数据库统计信息方法详解

select owner,
       table_name,
       partition_name,
       object_type,
       num_rows,
       blocks,
       empty_blocks,
       avg_space,
       chain_cnt,
       avg_row_len,
       to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') last_analyzed,
       user_stats
  from dba_tab_statistics
 where owner='cmxbusi'
 and table_name = 't02';

select index_name              as name,
       blevel,
       leaf_blocks             as leaf_blks,
       distinct_keys           as dst_keys,
       num_rows,
       clustering_factor       as clust_fact,
       avg_leaf_blocks_per_key as leaf_per_key,
       avg_data_blocks_per_key as data_per_key,
       last_analyzed
  from dba_ind_statistics where
  table_owner='cmxbusi' and
  table_name='t02';

select table_name,index_name,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows from dba_indexes where owner = 'scott' and table_name = 't1';

方法一：
select column_name,
      num_distinct,
      low_value,
      high_value,
      density ,
      num_nulls ,
      avg_col_len ,
      histogram,
      num_buckets
  from dba_tab_col_statistics
 where owner='cmxbusi'
 and table_name = 't02';


方法二：
a.owner ||'.'||a.table_name name ,
a.column_name,
b.num_rows,
a.num_distinct cardinality,
a.num_distinct/b.num_rows selectivity,
num_nulls,density,
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=upper('cmxbusi')
and a.table_name=upper('t02')
and a.column_name=upper('id');

方法三：
select table_name,column_name,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'scott' and table_name = 't1';

1.性能数据的收集
这个包的下面四个存储过程分别收集 index、table、schema、database 的统计信息:
dbms_stats.gather_table_stats 收集表、列和索引的统计信息(当 cascade 为true 时，分析表、列（索引）信息)；
dbms_stats.gather_schema_stats 收集 schema 下所有对象的统计信息；
dbms_stats.gather_index_stats 收集索引的统计信息；
dbms_stats.gather_system_stats 收集系统统计信息
dbms_stats.gather_dictionary_stats： 所有字典对象的统计；
dbms_stats.gather_database_stats：分析数据库信息
dbms_stats.create_stat_table 创建存放统计信息表
dbms_stats.auto_sample_size 采样值

2.性能数据的设置
设置表统计信息：dbms_stats.set_table_stats
设置索引统计信息：dbms_stats.set_index_stats
设置列统计信息：dbms_stats.set_column_stats

3.性能数据的删除
删除数据库统计信息：dbms_stats.delete_database_stats
删除用户方案统计信息：dbms_stats.delete_schema_stats
删除表统计信息：dbms_stats.delete_table_stats
删除索引统计信息：dbms_stats.delete_index_stats
删除列统计信息：dbms_stats.delete_column_stats

4.性能数据的备份和恢复
dbms_stats.export_column_stats：导出列的分析信息
dbms_stats.export_index_stats：导出索引分析信息
dbms_stats.export_system_stats：导出系统分析信息
dbms_stats.export_table_stats：导出表分析信息
dbms_stats.export_schema_stats：导出方案分析信息
dbms_stats.export_database_stats：导出数据库分析信息
dbms_stats.import_column_stats：导入列分析信息
dbms_stats.import_index_stats：导入索引分析信息
dbms_stats.import_system_stats：导入系统分析信息
dbms_stats.import_table_stats：导入表分析信息
dbms_stats.import_schema_stats：导入方案分析信息
dbms_stats.import_database_stats：导入数据库分析信息

1.检查统计信息
set linesize 300;
select owner,table_name,num_rows,last_analyzed  from dba_tables where  table_name  in (
't_s_type',
't_edf_task'
);

2.针对运行效率慢的sql情况，按表收集统计信息：
sqlplus / as sysdba <<eof
exec dbms_stats.gather_table_stats(ownname => '用户名',tabname => '表名' ,estimate_percent => 0.1 ,method_opt => 'for all indexed columns' ,cascade => true,degree => 30);
eof

ps：estimate_percent => 0.1    为采样比例

3.针对运行效率慢的sql情况，按用户收集统计信息：
sqlplus / as sysdba <<eof
exec dbms_stats.gather_schema_stats(ownname => 'ccicjy' ,estimate_percent => 0.1 ,method_opt => 'for all indexed columns' ,cascade => true,degree => 30);
eof

4.查看统计信息是否执行
select  * from dba_scheduler_jobs;
select * from dba_scheduler_jobs  where job_name='gather_stats_job';

dbms_stats.gather_table_stats的语法如下:

dbms_stats.gather_table_stats (ownname varchar2, tabname varchar2, partname  varchar2, estimate_percent number, block_sample boolean, method_opt varchar2, degree number, granularity varchar2, cascade boolean, stattab varchar2, statid varchar2, statown varchar2, no_invalidate boolean, force boolean);

参数说明:

1.ownname:要分析表的拥有者

2.tabname:要分析的表名.

3.partname:分区的名字,只对分区表或分区索引有用.

4.estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 
  常量:dbms_stats.auto_sample_size是默认值,由oracle决定最佳取采样值.

5.block_sapmple:是否用块采样代替行采样.

6.method_opt:决定histograms信息是怎样被统计的（抽样方法）
method_opt的取值如下(默认值为for all columns size auto):
  for table：只统计表
  for all columns：分析所有的列
  for all indexes：只分析统计相关索引
  for all indexed columns：只统计有索引的表列
  for all hidden columns:分析所有隐藏列（函数索引等
  for columns <list> size <n> | repeat | auto | skewonly:统计指定列的histograms.
      n的取值范围[1,254]; 
      repeat：上次统计过的histograms;
      auto：由oracle决定n的大小;
      skewonly：oracle 确定需要收集检查每个索引中每列值的分布
  例如：
  	method_opt=>'for all columns size skewonly'
  	method_opt=>'for all columns size repeat'
  	method_opt=>'for all columns size auto'
    
7.degree:决定并行度.默认值为null.
  分析索引速度加快,根据 cpu 数量来设置，一般在业务空闲的时候 degree 可设为 cpu 数量－1，繁忙的时候就再小点。

8.granularity:要收集的统计信息的粒度（仅在表已分区时才相关）
  根据将优化的 sql语句，优化器可以选择使用分区统计或全局统计，对于大多数系统这两种统计都是很
重要的，oracle 推荐将 granularity 设置为 auto 同时收集全部信息。  
			①'auto'-根据分区类型确定粒度。这是默认值。
			②'all' -收集所有（子分区，分区和全局）统计信息
			③'global' -收集全球统计数据
			④'global and partition'-收集全局和分区级别的统计信息。即使它是一个复合分区对象，也不会收集任何子分区级别统计信息。
			⑤'partition '-收集分区级别的统计信息
			⑥'subpartition' -收集子分区级别的统计信息
			⑦'default'-收集全局和分区级别的统计信息。该选项已过时，并且当前受支持，但仅出于遗留原因才包含在文档中。您应该使用“ global and partition”来实现此功能。请注意，默认值现在是'autauto'。

9.cascade:是收集索引的信息.默认为false.

10.stattab:用户统计信息表标识符，用于描述将当前统计信息保存在何处
  statid如果多个表的统计信息存储在同一个stattab中用于进行区分，标识符
  statown存储统计信息表的拥有者.包含的架构stattab（如果不同于ownname）
以上三个参数若不指定,统计信息会直接更新到数据字典.

11.no_invalidate: 通过不同的参数配置，可以实现对 oracle 失效共享游标行为的控制true，false
  如果取值为 true，表示不进行游标失效动作，原有的 shared cursor 保持原有状态。
  如果取值为 false，表示将统计量对象相关的所有 cursor 全部失效。
  如果设置为auto_invalidate，根据官方文档，oracle 自己决定 shared cursor 失效动作。
  从 10g 开始，oracle 就将 auto_invalidate 作为默认的统计量收集行为。
  select dbms_stats.get_param(pname => 'no_invalidate') from dual;

12.force:即使表锁住了也收集统计信息.

13.options 分析模式
使用 4 个预设的法子之一，这个选项能把握 oracle 统计的刷新方法：
  gather——重新分析整个（schema）。
  gather empty——只分析目前还没有统计的表。
  gather stale——只重新分析修改量超过 10%的表（这些修改包含插入、更新和删除）。
  gather auto——重新分析以前没有统计的对象，以及统计数据过期（变脏）的对象。
注意，使用 gather auto 相似于组合使用 gather stale 和 gather empty。注意，不论 gather stale 仍是 gather auto，都请求进行监视。假如你施行一个alter table xxx monitoring 命令，oracle 会用 dba_tab_modifications 视图来跟踪发生变动的表。这样一来，你就确实地知道，自从上一次剖析统计数据以来，发生了多少次插入、更新和删除操作。

14.objlist: 指定对象列表
15.obj_filter_list：a list of object filters
16.gather_sys：只收集 sys 的对象。
17.stattype：统计信息类型。允许的唯一值是data。

直方图收集方法
1. 语法格式
  对于dbms_stats包，是通过指定method_opt参数实现的，该参数可接受值如下：
1.1统计所有列的histograms(直方图).
for all [indexed | hidden] columns [size_clause]

1.2统计指定列的histograms(直方图).
for columns [size_clause] column | attribute [size_clause] [,column | attribute [size_clause]...]
  例如： 
    for columns column  size 1
    for columns column  size auto
  其中size_clause必须符合以下格式:size [整数值 | repeat | auto | skewonly]

      整数值：直方图的bucket数，范围为[1,254]，为1表示删除列上直方图信息
      repeat：只对已有直方图的列收集直方图信息
      auto：由oracle决定是否对列收集直方图，以及使用哪类直方图。oracle默认只对用过的列（where条件中出现过的列）自动收集直方图统计信息，oracle会在sys.col_usage$基表中记录各列的使用情况。在自动收集直方图统计信息时先查该表，如果列未被使用过，则不会收集。
      skewonly：只对倾斜列收集直方图 

2.距离
2.1 对表所有列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'user01',tabname => 'tab01',method_opt=> 'for all columns size auto');

2.2 对表所有有索引的列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'user01',tabname => 'tab01',method_opt=> 'for all indexed columns size auto');

2.3 对表的empno和deptno列以auto方式收集直方图
exec dbms_stats.gather_table_stats(ownname => 'user01',tabname => 'tab01',method_opt=> 'for columns size auto empno deptno');

2.4 对表的empno和deptno列收集直方图，并指定bucket数为10
exec dbms_stats.gather_table_stats(ownname => 'user01',tabname => 'tab01',method_opt=> 'for columns size 10 empno deptno');

2.5 对表的empno和deptno列收集直方图，指定empno列bucket数为10，deptno列bucket数为5
exec dbms_stats.gather_table_stats(ownname => 'user01',tabname => 'tab01',method_opt=> 'for columns empno size 10 deptno size 5')
 
3.查询直方图
select column_name,notes,histogram from dba_tab_col_statistics where table_name = 'sales2'; 

select * from user_histograms;

dbms_stats.gather_database_stats (
  estimate_percent number default to_estimate_percent_type(get_param('estimate_percent')),
  block_sample boolean default false,
  method_opt varchar2 default get_param('method_opt'),
  degree number defaultto_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  cascade boolean default
  to_cascade_type(get_param('cascade')),
  stattab varchar2 default null,
  statid varchar2 default null,
  options varchar2 default 'gather',
  objlist out objecttab,
  statown varchar2 default null,
  gather_sys boolean default true,
  no_invalidate boolean default to_no_invalidate_type (
    get_param('no_invalidate')),
  obj_filter_list objecttab default null);

sql>exec dbms_stats.gather_database_stats;
sql>exec dbms_stats.gather_database_stats(estimate_percent => 15);
sql>exec dbms_stats.gather_database_stats(estimate_percent=>10,degree=>8, cascade=>true, granularity=>'all');

sql> exec dbms_stats.gather_database_stats;
pl/sql procedure successfully completed

sql> select distinct sid, serial# from v$session_longops where opname like'%gather%';
       sid    serial#
---------- ----------
       459      20726

       select distinct sid, serial# from v$session_longops where opname like'%gather_database_stats%';

sql> alter system kill session '459,20726';
system altered

dbms_stats.gather_schema_stats (
  ownname varchar2,
  estimate_percent number default to_estimate_percent_type(get_param('estimate_percent')),
  block_sample boolean default false,
  method_opt varchar2 default get_param('method_opt'),
  degree number defaultto_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  cascade boolean defaultto_cascade_type(get_param('cascade')),
  stattab varchar2 default null,
  statid varchar2 default null,
  options varchar2 default 'gather',
  objlist out objecttab,
  statown varchar2 default null,
  no_invalidate boolean default to_no_invalidate_type (
    get_param('no_invalidate')),
  force boolean default false,
  obj_filter_list objecttab default null);	
 gather_schema_stats

begin
dbms_stats.gather_schema_stats(ownname => 'cmxbusi',
                                options => 'gather',
                                estimate_percent => 100,
                                method_opt => 'for all indexed columns',
                                degree => 2);
end;
/

begin
dbms_stats.gather_schema_stats(ownname => 'cmxbusi',
                                options => 'gather auto',
                                estimate_percent =>dbms_stats.auto_sample_size,
                                method_opt => 'for all columns size auto',
                                cascade => true,
                                degree => 2);
end;
/

begin
dbms_stats.gather_schema_stats(ownname => 'cmxbusi',
                                estimate_percent => 10,
                                cascade => true,
                                granularity => 'all',
                                degree => 2);
end;
/

dbms_stats.gather_table_stats (
  ownname varchar2,
  tabname varchar2,
  partname varchar2 default null,
  estimate_percent number default to_estimate_percent_type
  (get_param('estimate_percent')),
  block_sample boolean default false,
  method_opt varchar2 default get_param('method_opt'),
  degree number default to_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  cascade boolean defaultto_cascade_type(get_param('cascade')),
  stattab varchar2 default null,
  statid varchar2 default null,
  statown varchar2 default null,
  no_invalidate boolean default to_no_invalidate_type (
    get_param('no_invalidate')),
  force boolean default false);

sql>select table_name, blocks, empty_blocks, num_rows from sql>dba_tables where owner = 'scott' and table_name = 't1';
sql>select table_name, blocks, empty_blocks, num_rows,last_analyzed fromdba_tables where owner = 'scott' and table_name = 't1';
sql>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'scott' and table_name = 't1';
sql>select table_name,sample_size,num_rows,round(sample_size/num_rows*100,1)"%" from dba_tables where owner = 'scott' order by table_name;

sql>select table_name,column_name,last_analyzed,num_distinct,low_value,high_value,histogram,density from dba_tab_columns where owner = 'scott' and table_name ='t1';

sql>select table_name,index_name,last_analyzed,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor,num_rows fromdba_indexes where owner = 'scott' and table_name = 't1';

sql>analyze table itpux_m10 delete statistics;
sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >estimate_percent =>dbms_stats.auto_sample_size,
                               >method_opt => 'for all indexed columns',
                               >cascade => true,
                               >degree => 2);
>end;
>/

sql>analyze table itpux_m10 delete statistics;
sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >estimate_percent => 15,
                               >method_opt => 'for table',
                               >cascade => false,
                               >degree => 2);
>end;
>/

sql>analyze table t1 delete statistics;
sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >estimate_percent => 100,
                               >method_opt => 'for columns size 1 object_name status',
                               >cascade => false,
                               >degree => 2);
>end;
>/

sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >estimate_percent => 100,
                               >cascade => true,
                               >degree => 2);
>end;
>/

sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >estimate_percent => 18,
                               >cascade => true,
                               >granularity => 'all',    --全局的表，分区表，子分区表
                               >degree => 2);
>end;
>/

sql>begin
>dbms_stats.gather_table_stats(ownname => 'scott',
                               >tabname => 't1',
                               >partname => '分区名',
                               >estimate_percent => 5,
                               >cascade => true,
                               >granularity => 'partition',
                               >method_opt => 'for all indexes columns',
                               >degree => 2);
>end;
>/

sql>begin
>dbms_stats.delete_table_stats (ownname => 'scott',tabname >=>'t1');
>end;
>/

dbms_stats.gather_index_stats (
  ownname varchar2,
  indname varchar2,
  partname varchar2 default null,
  estimate_percent number default to_estimate_percent_type (get_param('estimate_percent')),
  stattab varchar2 default null,
  statid varchar2 default null,
  statown varchar2 default null,
  degree number default to_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  no_invalidate boolean default to_no_invalidate_type (get_param('no_invalidate')),
  force boolean default false);

gather_dictionary_stats：
dbms_stats.gather_dictionary_stats (
  comp_id varchar2 default null,
  estimate_percent number default to_estimate_percent_type (get_param('estimate_percent')),
  block_sample boolean default false,
  method_opt varchar2 default get_param('method_opt'),
  degree number default to_degree_type(get_param('degree')),
  granularity varchar2 default get_param('granularity'),
  cascade boolean default to_cascade_type(get_param('cascade')),
  stattab varchar2 default null,
  statid varchar2 default null,
  options varchar2 default 'gather auto',
  objlist out objecttab,
  statown varchar2 default null,
  no_invalidate boolean default to_no_invalidate_type (get_param('no_invalidate')),
  obj_filter_list objecttab default null);

sql> exec dbms_stats.gather_dictionary_stats
(estimate_percent=>100, degree=>8, cascade=>true, granularity=>'all');

动态性能表统计信息采集gather_fixed_objects_stats
dbms_stats.gather_fixed_objects_stats (
  stattab varchar2 default null,
  statid varchar2 default null,
  statown varchar2 default null,
  no_invalidate boolean default to_no_invalidate_type (get_param('no_invalidate')));

sql> exec dbms_stats.gather_fixed_objects_stats;

sql>exec dbms_stats.gather_fixed_objects_stats;

sql>exec dbms_stats.gather_table_stats ('sys', 'x$ksuse');
sql>exec dbms_stats.gather_table_stats(ownname=>'sys',tabname=>'recyclebin$');

sql>select count(*) from sys.tab_stats$;
sql>select num_rows, last_analyzed from user_tab_statistics where table_name ='x$ksuse';

sql>select view_definition from v$fixed_view_definition where view_name='v$lock';
or:
sql>select dbms_metadata.get_ddl('view', 'dba_extents') from dual;

select table_name, blocks, empty_blocks, num_rows
from dba_tables
where owner = 'cmxbusi'
and table_name = 't02';

select table_name,
column_name,
num_distinct,
low_value,
high_value,
histogram,
density
from dba_tab_columns
where owner = 'cmxbusi'
and table_name = 't02';

select * from dba_indexes
leaf_blocks,              -- 索引中叶子块数据
blevel,                   -- b 树索引等级
distinct_keys,            -- 不同索引值的数量
avg_leaf_blocks_per_key,  -- 索引中的每个值平均在多少个叶子块中，如果是主键/唯一就是 1.
avg_data_blocks_per_key,  -- 通过索引中的一个值指向表中的数据块，应数据块数量的平均值。
clustering_factor,        -- 聚集因子，越小越好，越大越不好。num_rows --索引的行数量。

select  table_name,index_name,leaf_blocks,blevel,distinct_keys,avg_leaf_blocks_per_key,avg_data_blocks_per_key,clustering_factor
from dba_indexes
where table_owner='cmxbusi'
and table_name = 't02';

analyze table t1 compute statistics for table;

analyze table t1 compute statistics for all columns;

analyze table t1 compute statistics for all indexed columns;

analyze table t1 compute statistics;

analyze table t1 compute statistics for all indexes;

analyze table t1 compute statistics for table for all indexes for all columns;

analyze table t1 delete statistics;

analyze table t1 validate structure;

analyze table cmxbusi.t01 validate structure;