【调优篇基本原理】对象统计信息

对象统计信息用来描述数据是如何在数据库中存储的，有表统计，列统计，索引统计。先不看分区相关的内容，分区相关内容在分区专题中分析和实验。

Oracle中有一些表或者视图的可以查看这些信息：

User_tab或all_tab或dba_tab打头的表相关的，user_tab_col或all_tab_col或dba_tab_col打头的列相关的，user_ind,all_ind,dba_ind打头的索引相关的，比如

User_tab_statistics

User_tables

User_tab_col_statistics

User_tab_histograms

User_ind_statistics

User_indexes

简单看看相关的字段信息：

创建一个表：

create table t_test_statistics1  as select * from dba_tables ;

SQL> set linesize 200

SQL> select a.object_type,

  2         a.num_rows,

  3         a.blocks,

  4         a.empty_blocks,

  5         a.avg_space,

  6         a.chain_cnt,

  7         a.avg_row_len,

  8         a.last_analyzed,

  9         a.stale_stats

 10    from user_tab_statistics a

 11   where a.table_name = upper('t_test_statistics1');

 

OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN LAST_ANAL STA

------------ ---------- ---------- ------------ ---------- ---------- ----------- --------- ---

TABLE

 

SQL>

SQL> select a.table_name,a.column_name,a.num_distinct

  2    from user_tab_col_statistics a

  3   where a.table_name = upper('t_test_statistics1');

 

no rows selected

 

SQL>

可以看到新创建的表并没有相关的统计信息，比如：

行数num_rows，

块数blocks，

空块数empty_block,

平均行的空间avg_space，

行链接数量chain_cnt，

平均行长度avg_row_len,

最后分析时间last_analyzed,

统计信息是否陈旧stale_stats.

 

同时也发现并没有相应列的统计信息。

 

接着在表上创建一个索引：

SQL>  create index idx_test_stat_name on t_test_statistics1(table_name);

 

Index created

 

 

SQL> select a.blevel, 

  2         a.leaf_blocks,

  3         a.distinct_keys,

  4         a.avg_leaf_blocks_per_key,

  5         a.avg_data_blocks_per_key,

  6         a.avg_cached_blocks,

  7         a.avg_cache_hit_ratio,

  8         a.clustering_factor,

  9         a.num_rows,

 10         a.last_analyzed,

 11         a.stale_stats

 12    from user_ind_statistics a

 13   where a.index_name = upper('idx_test_stat_name');

 

 

    BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO CLUSTERING_FACTOR   NUM_ROWS LAST_ANAL STA

---------- ----------- ------------- ----------------------- ----------------------- ----------------- ------------------- ----------------- ---------- --------- ---

         1          10          2298                       1                       1                                               1103        2309 22-JUL-10

 

SQL>

可以看到，在创建索引的时候便产生了索引相关的统计信息，包括：

Blevel   索引高度

Leaf_blocks   叶块数

Distinct_keys 不同的键值数

avg_leaf_blocks_per_key 键值平均叶块数

avg_data_blocks_per_key 键值平均数据块数

avg_cached_blocks       平均缓存的块数

avg_cache_hit_ratio    平均缓存的命中率

clustering_factor  索引集群因子

num_rows 行数

last_analyzed 最后分析时间

stale_stats   是否陈旧

 

 

可以使用analyze table xx compute statistics来分析表，也可以使用dbms_stats包中的一些方法，通过设置一些参数来具体的分析表。dbms_stats包的一些过程如下：具体可参见oralce官方文档 performance tuning guide.

GATHER_INDEX_STATS	Index statistics
GATHER_TABLE_STATS	Table, column, and index statistics
GATHER_SCHEMA_STATS	Statistics for all objects in a schema
GATHER_DATABASE_STATS	Statistics for all objects in a database
GATHER_SYSTEM_STATS	CPU and I/O statistics for the system

 

以gather_table_stats为例：

SQL> begin

  2    dbms_stats.gather_table_stats(ownname => 'TEST', --拥有者用户名

  3                                  tabname => upper('t_test_statistics1'),--表名

  4                                  degree  => 2, --并行度，也可以设置为默认值

  5                                  --DBMS_STATS.AUTO_DEGREE

  6                                  cascade          => true, --是否手机索引统计信息

  7                                  method_opt       => '',

  8                                  estimate_percent => 100, --是否采样收集，值为采样百分比

  9                                  --或者取常量：DBMS_STATS.AUTO_SAMPLE_SIZE为0

 10                                  block_sample => true,--采用数据行或者块采样，true为块

 11                                  granularity  => 'AUTO', --分区粒度级别，取值为

 12                                  --AUTO， ALL， GLOBAL， PARTITION，

 13                                  --SUBPARTITION， GLOBAL AND PARTITION，10g默认AUTO

 14                                  force => false);

 15  end;

 16 

 17  /

 

PL/SQL procedure successfully completed.

此外还有gather_temp是否收集临时表统计信息，gather_sys是否收集sys用户的统计信息等

 

或者也可以使用 analyze table 来收集统计信息：

SQL> analyze table t_test_statistics1 compute statistics;

 

Table analyzed.

 

Oracle 10g默认是设置每天的10点自动收集统计信息，可以在下面的查询中看到最近一次job的执行情况：

SQL> select max(to_char(a.log_date, 'yyyy-mm-dd hh24:mi:ss')) datetime,

  2         a.job_name,

  3         a.job_subname,

  4         a.status

  5    from dba_scheduler_job_run_details a

  6   where a.job_name = 'GATHER_STATS_JOB'

  7   group by a.job_name, a.job_subname, a.status;

 

DATETIME            JOB_NAME              JOB_SUBNAME           STATUS

------------------- --------------------- --------------------- ------

2010-07-22 22:00:41 GATHER_STATS_JOB                            SUCCEEDED

 

SQL>

由于操作系统时间问题，这里看到的07-22其实已经过了，也即已经自动分析了，注意到10g这个自动收集统计信息可能会给一些在晚间业务繁忙的系统带来性能隐患，比如一些在线交易的系统，应该选取合适的时间点和时间间隔来做收集。

 

使用dbms_stats.lock_schema_stats来锁定或解锁对象统计信息

同样以table对象为例子：

锁定：

SQL> begin

  2    dbms_stats.lock_table_stats(ownname => 'TEST',

  3                                 tabname => upper('t_test_statistics1'));

  4  end;

  5  /

 

PL/SQL procedure successfully completed

 

解锁：

SQL> begin

  2    dbms_stats.unlock_table_stats(ownname => 'TEST',

  3                                 tabname => upper('t_test_statistics1'));

  4  end;

  5  /

 

PL/SQL procedure successfully completed

 

当然也可以删除统计信息，随后的一些测试可能会用到，不过一般情况下，不会去delete统计信息。

例如：

SQL> begin

  2    dbms_stats.delete_table_stats(ownname => 'TEST',

  3                                 tabname => upper('t_test_statistics1'));

  4  end;

  5  /

 

PL/SQL procedure successfully completed

 

SQL>

SQL> select a.object_type,

  2         a.num_rows,

  3         a.blocks,

  4         a.empty_blocks,

  5         a.avg_space,

  6         a.chain_cnt,

  7         a.avg_row_len,

  8         a.last_analyzed,

  9         a.stale_stats

 10    from user_tab_statistics a

 11   where a.table_name = upper('t_test_statistics1');

 

OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN LAST_ANALYZED STALE_STATS

------------ ---------- ---------- ------------ ---------- ---------- ----------- ------------- -----------

TABLE                                                                                          

 

SQL>

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-668649/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16179598/viewspace-668649/