oracle 定时 analyze,ORACLE-ANALYZE命令分析

1.analyze table t1 compute statistics for table;  --&gtuser_tables

(只对表的总体信息进行统计，比如行数多少等，不涉及到表字段)

2.analyze table t2 compute statistics for all columns;  --&gtuser_tab_columns

(只会收集表字段信息)

3.analyze table t3 compute statistics for all indexed columns; --&gtuser_tab_columns

(只会收集表中索引所在的字段信息)

4.analyze table t4 compute statistics for all indexes;à user_indexes

(只收集表索引的信息)

5.analyze table t5 compute statistics;

(收集表，表字段，索引的信息)

另外，可以删除分析数据：

SQL> analyze table my_table delete statistics;

SQL> analyze table my_table delete statistics for table for all indexes for all indexed columns;

例子：

create table t1 as select * from user_objects;

create table t2 as select * from user_objects;

create table t3 as select * from user_objects;

create table t4 as select * from user_objects;

create table t5 as select * from user_objects;

create table t6 as select * from user_objects;

create unique index pk_t1_idx on t1(object_id);

create unique index pk_t2_idx on t2(object_id);

create unique index pk_t3_idx on t3(object_id);

create unique index pk_t4_idx on t4(object_id);

create unique index pk_t5_idx on t5(object_id);

create unique index pk_t6_idx on t6(object_id);

(1) 查看表的统计信息

select table_name, num_rows, blocks, empty_blocks

from user_tables

where table_name in ('T1', 'T2', 'T3', 'T4', 'T5','T6');

(2) 查看字段的统计信息

select table_name,

column_name,

num_distinct,

low_value,

high_value,

density

from user_tab_columns

where table_name in ('T1', 'T2', 'T3', 'T4','T5','T6');

(3) 查看索引的统计信息

select table_name,

index_name,

blevel,

leaf_blocks,

distinct_keys,

avg_leaf_blocks_per_key avg_leaf_blocks,

avg_data_blocks_per_key avg_data_blocks,

clustering_factor,

num_rows

from user_indexes

where table_name in ('T1', 'T2', 'T3', 'T4', 'T5', 'T6');

二．执行analyze命令

analyze table t1 compute statistics for table;  --针对表收集信息

analyze table t2 compute statistics for all columns;  --针对表字段收集信息

analyze table t3 compute statistics for all indexes columns;  --收集索引字段信息

analyze table t4 compute statistics;        --收集表，表字段，索引信息

analyze table t5 compute statistics for all indexes;          --收集索引信息

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

--收集表，索引，表字段信息

(1) 表的统计信息

select table_name, num_rows, blocks, empty_blocks

from user_tables

where table_name in ('T1', 'T2', 'T3', 'T4', 'T5','T6');

(2) 表中字段的统计信息

select table_name,

column_name,

num_distinct,

low_value,

high_value,

density

from user_tab_columns

where table_name in ('T1', 'T2', 'T3', 'T4','T5','T6');

(3) 索引的统计信息

没有变化，说明在创建索引的时候就ORACLE就已经收集相关信息

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