oracle自动or手动收集信息

create table TestUserTbale(
 id         INTEGER,
 hash_value VARCHAR2(100)
);
declare
i int;
begin
i := 0;
for i in 1..10000 loop
insert into TESTUSERTBALE
select i,ora_hash(i) as hash_value from dual;
end loop;
end;

select t.LAST_ANALYZED,t.TABLE_NAME,t.BLOCKS 
from user_tables t 
where t.TABLE_NAME='TESTUSERTBALE';

select max(t.LAST_ANALYZED) as LAST_ANALYZED 
from user_tables t

begin
dbms_stats.gather_table_stats(ownname => 'XIR_TRD',tabname => 'TESTUSERTBALE');
end;

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED 
from user_tables t where table_name in ('TESTUSERTBALE');

select table_name,index_name,t.blevel,t.num_rows,t.leaf_blocks,t.last_analyzed 
from user_indexes t
where table_name in ('TESTUSERTBALE');

select t1.window_name,t1.repeat_interval,t1.duration 
from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name 
and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

begin
dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TEST',estimate_percent => 10,method_opt=> 'for all indexed columns');
end;
begin
dbms_stats.gather_table_stats(ownname => 'USER',tabname => 'TAB_NAME',CASCADE=>TURE);
end;

begin
dbms_stats.gather_table_stats(ownname => 'USER',
                              tabname => 'RANGE_PART_TAB',
                              partname => 'p_201312',
                              estimate_percent => 10,
                              method_opt=> 'for all indexed columns',
                              cascade=>TRUE);
end;

begin
dbms_stats.gather_index_stats(ownname => 'USER',
                              indname => 'IDX_OBJECT_ID',
                              estimate_percent => '10',
                              degree => '4');
end;

begin
dbms_stats.gather_table_stats(ownname => 'USER',
                              tabname => 'TEST',
                              estimate_percent => 10,
                              method_opt=> 'for all indexed columns',
                              cascade=>TRUE);
end;

begin
dbms_stats.gather_schema_stats(ownname=>'CS',
                               estimate_percent=>10,
                               degree=>8,
                               cascade=>true,
                               granularity=>'ALL');
end;

begin
dbms_stats.gather_database_stats(estimate_percent=>10,
                                degree=>8,
                                 cascade=>true,
                                 granularity=>'ALL');

end;

ownname： USER_NAME
tabname： TABLE_NAME
partname: 分区表的某个分区名
estimate_percent: 采样百分比，有效范围为[0.000001,100]
block_sample：使用随机块采样代替随机行采样
method_opt：
cascade:是否收集此表索引的统计信息
degree:并行处理的cpu数量
granularity： 统计数据的收集，'ALL' - 收集所有（子分区，分区和全局）统计信息