Dowemo
0 0 0 0

The online documentation of oracle describes the effect of analytics:
Use the analysis statement to collect non-optimizer statistics, for example, to:

Collect or delete statistics about an index or index partition, table or table partition, index-organized table, cluster, or scalar object attribute.
Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference ( ref ).
Identify migrated and chained of a table or cluster.

The role of the dbms stats mainly is to replace the collection statistics of analysis, and make a considerable amount of enhancements on this one.

Take your analysis table abc compute statistics;
For example, the generated statistics will exist in the user tables view, and look at the select * from user_tables where table_name = 'abc ';
Look at the num rows, blocks, avg_space, avg row len columns, you'll understand that this is a change.

The purpose of collecting statistics is to make the execution plan more accurate.

对于Oracle analyze table的使用总结. 对于Oracle analyze table的使用总结. 
analyze table 一般可以指定分析: 表,所有字段,所有索引字段,所有索引。 若不指定则全部都分析。
SQL> analyze table my_table compute statistics; 
SQL> analyze table my_table compute statistics fortablefor all indexes for all columns; 
SQL> analyze table my_table compute statistics fortablefor all indexes for all indexed columns; 
其中:
SQL> analyze table my_table compute statistics; 
等价于:
SQL> analyze table my_table compute statistics fortablefor all indexes for all columns; 
sample:
analyze table t1 compute statistics fortable;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t5 compute statistics for all indexes; 
analyze table t4 compute statistics; (不指定)
另外,可以删除分析数据:
SQL> analyze table my_table delete statistics;
SQL> analyze table my_table delete statistics fortablefor all indexes for all indexed columns; 
http://wfly2004.blog.163.com/blog/static/1176427201042891042233/
首先创建四个临时表t1,t2,t3,t4,和他们相对应的索引 
复制内容到剪贴板 
代码:
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 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);
查看这个时候各个表对应的数据库统计信息(表,字段,索引) 
复制内容到剪贴板 
代码:--查看表的统计信息select table_name,num_rows,blocks,empty_blocks from user_table where table_names in ('T1','T2','T3','T4');
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
T1 
T2 
T3 
T4 --查看字段的统计信息select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
T1 OBJECT_NAME 
T1 SUBOBJECT_NAME 
T1 OBJECT_ID 
T1 DATA_OBJECT_ID 
T1 OBJECT_TYPE 
T1 CREATED 
T1 LAST_DDL_TIME 
T1 TIMESTAMP 
T1 STATUS 
T1 TEMPORARY 
T1 GENERATED 
T1 SECONDARY 
T2 OBJECT_NAME 
T2 SUBOBJECT_NAME 
T2 OBJECT_ID 
T2 DATA_OBJECT_ID 
T2 OBJECT_TYPE 
T2 CREATED 
T2 LAST_DDL_TIME 
T2 TIMESTAMP 
T2 STATUS 
T2 TEMPORARY 
T2 GENERATED 
T2 SECONDARY 
T3 OBJECT_NAME 
T3 SUBOBJECT_NAME 
T3 OBJECT_ID 
T3 DATA_OBJECT_ID 
T3 OBJECT_TYPE 
T3 CREATED 
T3 LAST_DDL_TIME 
T3 TIMESTAMP 
T3 STATUS 
T3 TEMPORARY 
T3 GENERATED 
T3 SECONDARY 
T4 OBJECT_NAME 
T4 SUBOBJECT_NAME 
T4 OBJECT_ID 
T4 DATA_OBJECT_ID 
T4 OBJECT_TYPE 
T4 CREATED 
T4 LAST_DDL_TIME 
T4 TIMESTAMP 
T4 STATUS 
T4 TEMPORARY 
T4 GENERATED 
T4 SECONDARY --查看索引的统计信息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');
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1 PK_T1_IDX 
T2 PK_T2_IDX 
T3 PK_T3_IDX 
T4 PK_T4_IDX 
现在我们分别对这个表做不同形式的analyze table处理 
复制内容到剪贴板
代码:
analyze table t1 compute statistics fortable;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;
我们再回头看看这是的oracle数据库对于各种统计信息 
复制内容到剪贴板 
代码:--这是对于表的统计信息select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
T1 3930551T2 
T3 
T4 3933551--我们可以据此得出结论,只有我们在analyze table命令中指定了for table或者不指定任何参数的时候,oracle数据库才会给我们统计基于表的统计信息--这是对于表中字段的统计信息select table_name,column_name,num_distinct,low_value,high_value,density from user_tab_columns where table_name in ('T1','T2','T3','T4');
TABLE_NAME COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
T1 OBJECT_NAME 
T1 SUBOBJECT_NAME 
T1 OBJECT_ID 
T1 DATA_OBJECT_ID 
T1 OBJECT_TYPE 
T1 CREATED 
T1 LAST_DDL_TIME 
T1 TIMESTAMP 
T1 STATUS 
T1 TEMPORARY 
T1 GENERATED 
T1 SECONDARY 
T2 OBJECT_NAME 382341423030 D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5 .000270447891062615T2 SUBOBJECT_NAME 7750303152455354.012987012987013T2 OBJECT_ID 3930 C304062D C30F4619 .000254452926208651T2 DATA_OBJECT_ID 3662 C304062D C30F4619 .000273074822501365T2 OBJECT_TYPE 154441544142415345204C494E4B 56494557.000127194098193844T2 CREATED 36847867081E111F33 7868071211152F .000547559423988464T2 LAST_DDL_TIME 35747867081E11251B 7868071211152F .000565522924083892T2 TIMESTAMP 3649323030332D30382D33303A31363A33303A3530 323030342D30372D31383A31363A32303A3436 .000559822349362313T2 STATUS 2494E56414C4944 56414C4944 .000127194098193844T2 TEMPORARY 24E 59.000127194098193844T2 GENERATED 24E 59.000127194098193844T2 SECONDARY 24E 59.000127194098193844T3 OBJECT_NAME 
T3 SUBOBJECT_NAME 
T3 OBJECT_ID 3931 C304062D C30F461A .000254388196387688T3 DATA_OBJECT_ID 
T3 OBJECT_TYPE 
T3 CREATED 
T3 LAST_DDL_TIME 
T3 TIMESTAMP 
T3 STATUS 
T3 TEMPORARY 
T3 GENERATED 
T3 SECONDARY 
T4 OBJECT_NAME 382541423030 D3F1BBB736D4C2B7DDCFFABBA7C7E5B5A5 .000261437908496732T4 SUBOBJECT_NAME 7750303152455354.012987012987013T4 OBJECT_ID 3932 C304062D C30F461B .000254323499491353T4 DATA_OBJECT_ID 3664 C304062D C30F461B .00027292576419214T4 OBJECT_TYPE 154441544142415345204C494E4B 56494557.0666666666666667T4 CREATED 36857867081E111F33 78680712111530.000271370420624152T4 LAST_DDL_TIME 35757867081E11251B 78680712111530.00027972027972028T4 TIMESTAMP 3650323030332D30382D33303A31363A33303A3530 323030342D30372D31383A31363A32303A3437 .000273972602739726T4 STATUS 2494E56414C4944 56414C4944 .5T4 TEMPORARY 24E 59.5T4 GENERATED 24E 59.5T4 SECONDARY 24E 59.5/*
在这个结果中我们可以看到,oracle数据库给t2,t4的所有字段都做了统计信息.
对表t3的object_id(索引字段)做了统计信息.
由此得出结论,
在指定for all columns 和不指定任何参数的时候oracle会给所有字段做统计信息,在指定for indexed columns时,oracle只给[b]有索引的字段进行字段信息统计[/b],如果我们别有必要给所有字段统计信息时,这个属性就很有用了.
*/--这里是对于索引的统计信息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');
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1 PK_T1_IDX 
T2 PK_T2_IDX 
T3 PK_T3_IDX 
T4 PK_T4_IDX 1939321121433932--从这里我们可以看出,只有表t4有索引统计信息.--再综合前面的我们就会发现,如果在运行analyze table是我们不指定参数,oracle将收集对于特定表的所有统计信息(表,索引,表字段的统计信息)补充,truncate命令不修改以上统计信息
复制内容到剪贴板 
代码:
truncate table t1;
truncate table t2;
truncate table t3;
truncate table t4;--我们在查看表和索引的统计信息select table_name,num_rows,blocks,empty_blocks from user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS
T1 3930551T2 
T3 
T4 3933551--索引的统计信息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');
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1 PK_T1_IDX 
T2 PK_T2_IDX 
T3 PK_T3_IDX 
T4 PK_T4_IDX 1939321121433932--我们再对以上各表做一次分析analyze table t1 compute statistics fortable;
analyze table t2 compute statistics for all columns;
analyze table t3 compute statistics for all indexed columns;
analyze table t4 compute statistics;--现在再来查看表和索引的统计信息select table_name,num_rows,blocks,empty_blocks,initial_extent,'8192' block_size from user_tables where table_name in ('T1','T2','T3','T4');
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS INITIAL_EXTENT BLOCK_SIZE
T1 008655368192T2 655368192T3 655368192T4 008655368192--索引的统计信息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');
TABLE_NAME INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS AVG_DATA_BLOCKS CLUSTERING_FACTOR NUM_ROWS
T1 PK_T1_IDX 
T2 PK_T2_IDX 
T3 PK_T3_IDX 
T4 PK_T4_IDX 0000000--由此得出结论,truncate命令不会修改数据的统计信息,--也就是如果我们想让CBO利用合理利用数据的统计信息的时候,需要我们及时的使用analyze命令或者dbms_stats重新统计数据的统计信息分类: OralceRac



Copyright © 2011 Dowemo All rights reserved.    Creative Commons   AboutUs