Oracle性能：统计信息statistics

统计信息

 

 

1. 查看 表的 统计信息

1.1 sql

user_tables

dba_tables

说明: 

1) 字段 last_analyzed 值就是 表最后一次的统计更新的日期，如果为 null 则表示 该表没有被分析过，即没有统计信息。

2) 新建的表是没有统计信息的。

2.2 plsql

desc dbms_stats.get_table_stats --Default Y表示有默认值，则可以不给IN参数，OUT参数总是要给变量的

desc dbms_stats.get_column_stats

desc dbms_stats.get_index_stats

 

set serveroutput on;

declare

  iv_OWNNAME varchar2(30);

  iv_TABNAME varchar2(30);

  ov_NUMROWS number;

  ov_NUMBLKS number;

  ov_AVGRLEN number;

begin

  iv_OWNNAME := upper('zzhtest');

  iv_TABNAME := upper('t_emotion');

  dbms_stats.get_table_stats(OWNNME => iv_ownname,TABNAME => iv_tabname,NUMROWS => ov_numrows,NUMBLKS => ov_numblks,AVGRLEN => ov_avgrlen);

dbms_output.put_line('用户:'||iv_ownname||chr(10)||'表:'||iv_tabname||chr(10)||'统计信息: ov_numrows='||ov_numrows||',ov_numblks='||ov_numblks||',ov_avgrlen='||ov_avgrlen);

end;

/

 

2. 设置 表的 统计信息

desc dbms_stats.set_table_stats

desc dbms_stats.set_column_stats

desc dbms_stats.set_index_stats

 

set serveroutput on;

declare

  iv_OWNNAME varchar2(30);

  iv_TABNAME varchar2(30);

  iv_NUMROWS number;

  iv_NUMBLKS number;

  iv_AVGRLEN number;

begin

  iv_OWNNAME := upper('zzhtest');

  iv_TABNAME := upper('t_emotion');

  iv_NUMROWS := 20;

  iv_NUMBLKS := 9;

  iv_AVGRLEN := 6;

  dbms_stats.set_table_stats(OWNNME => iv_ownname,TABNAME => iv_tabname,NUMROWS => iv_numrows,NUMBLKS => iv_numblks,AVGRLEN => iv_avgrlen);

end;

/

 

 

3. 收集|计算  表的 统计信息

3.1 sql

analyze table t_emotion estimate statistics sample 20 percent;  --抽样估算法（20%）

analyze table t_emotion compute statistics;  --完全计算法

 

3.2 plsql

desc dbms_stats.gather_table_stats

 

set serveroutput on;

execute dbms_stats.gather_table_stats(OWNNAME => upper('zzhtest'),TABNAME => upper('t_emotion'),cascade => true);  --完全计算法

说明:

1.  完全计算法 与 抽样估算法 的比较

2.  analyze 与 dbms_stats.gather_table_stats 的区别

当使用 dbms_stats 来分析表的时候 global_stats是YES，而用 analyze 来分析表的时候就是 No，而且 num_rows 的结果也会有些不同。

【实验】

步骤:

step1. 设置统计信息(dbms_stats.set_table_stats)

step2. 计算统计信息(analyze table),查看统计信息(user_tables)

step3. 计算统计信息(dbms_stats.gather_table_stats), 查看统计信息(user_tables

注意: 观察步骤1,2,3中的 字段 user_users.global_stats

user_users.user_stats

或

dba_users.global_stats

dba_users.user_stats

的变化。

 

4. 收集 表的 统计信息 对 列的统计信息的影响(列的柱状图，列的基本统计信息)

计算 表的 统计信息，可以活得 列的 柱状图和基本统计信息。

1. 查看 列的统计信息

1.1 列的 柱状图信息

1.2 列的 基本统计信息

2. 删除 列的柱状图信息

2.1 使用 dbms_stats.gather_table_stats 的 选项 method_opt

select user from dual;

exec dbms_stats.gather_table_stats(user,upper('t_user_tables'),method_opt =>'for all columns size 1');

method_opt的取值有:

1) FOR ALL COLUMNS SIZE AUTO:这一选项oracle 9i不收集 列的柱状图信息，在oracle 10g中则会根据数据库的选项选择是否收集柱状图，缺省的会为列收集基本信息。

2) FOR ALL COLUMNS SIZE 1:清除 所有字段的柱状图，保留字段的基本统计信息。

3) FOR COLUMNS <column_name> SIZE 1:清除 单列的柱状图，保留单列的基本统计信息。   <column_name> 为 分析表 的字段名。

2.2 使用 dbms_stats.delete_column_stats :可以彻底删除 单列的 柱状图信息

exec dbms_stats.delete_column_stats(user,upper('t_user_table'),upper('table_name'));  --彻底删除 表zzhtest.t_user_table的 单列table_name 的柱状图

 

【实验1】 使用 dbms_stats.gather_table_stats 的 选项 method_opt 对 列的统计信息的影响

step1. 使用 method_opt 的缺省值 收集表的统计信息，并查看 列的柱状图和基本统计信息。

exec dbms_stats.gather_table_stats(user,upper('t_user_tables'));  --收集

step2. 使用 指定的 method_opt  收集表的统计信息，并查看 列的柱状图和基本统计信息。

exec dbms_stats.gather_table_stats(user,upper('t_user_tables'),method_opy => 'for all columns size 1');  --收集: 所有列

exec dbms_stats.gather_table_stats(user,upper('t_user_tables'),method_opt => 'for columns table_name size 1');  --收集:单列 table_name

小结:在统计信息收集时，必须注意到这些选项和后台动作，否则就可能出现莫名其妙的问题。

 

【实验2】 清除 单列的柱状图 与  彻底删除 单列的柱状图 有什么区别

step1. 使用dbms_stats.gather_table_stats 的 选项 method_opt => 'for columns <column_name> size 1' ,并查看 列的柱状图

exec dbms_stats.gather_table_stats(user,upper('t_user_tables'),method_opt => 'for columns table_name size 1');

select * from dba_tab_histograms where = upper('zzhtest') and table_name =upper('t_user_tables') and column_name = upper('table_name') order by 3;

step2. 使用 dbms_stats.delete_column_stats，并查看 列的柱状图

现象:使用 dbms_stats.delete_column_stats 会删除行，而 method_opt => 'for columns <column_name> size 1' 不会删除行。

exec dbms_stats.delete_table_stats(user,upper('t_user_tables')); 

select * from dba_tab_histograms where = upper('zzhtest') and table_name =upper('t_user_tables') and column_name = upper('table_name') order by 3;

小结:通常推荐使用 method_opt => 'for columns <column_name> size 1'  而不是使用 dbms_stats.delete_column_stats 去清楚 列的统计信息，完全删除列的基本统计信息在某些bug作用下可能会导致优化器计算的异常。