统计级别
show parameter statistics_level
basic :自动优化功能会禁用
typical:推荐默认值
all:用于人工SQL诊断的其他统计信息
统计内容：
表统计： 行数，块数，行平均长度；
dba_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
列统计： 列中唯一值的数量（NDV，NULL值的数量，数据分布；
DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
索引统计：叶块数量，等级，聚簇因子；
DBA_INDEXES：LEAF_BLOCKS，BLEVEL,CLUSTERING_FACTOR;
系统统计：I/O性能与使用率；
CPU性能与使用率；
select NUM_ROWS，BLOCKS，AVG_ROW_LEN,last_analyzed from dba_tables
11G默认自动收集统计时间信息
select window_name,duration,next_start_date from dba_scheduler_windows;
1：查看自动收集统计信息的任务及状态
select CLIENT_NAME,STATUS from dba_autotask_client ;
select * from dba_scheduler_jobs;
select * from dba_scheduler_programs;
select * from dba_scheduler_windows;
select * from dba_scheduler_job_run_details;
select a.window_name,a.REPEAT_INTERVAL,a.DURATION from dba_scheduler_windows a, dba_scheduler_wingroup_members b
where a.window_name=b.window_name and b.window_group_name='MAINTENANCE_WINDOW_GROUP';
2：禁止自动收集统计信息
exec dbms_auto_task_admin.disable( client_name => 'auto optimizer stats collection',operation => NULL,WINDOW_NAME => NULL);
select CLIENT_NAME,STATUS from dba_autotask_client ;
3: 启动自动统计信任务
exec dbms_auto_task_admin.enable( client_name => 'auto optimizer stats collection',operation => NULL,WINDOW_NAME => NULL);
select CLIENT_NAME,STATUS from dba_autotask_client ;
4：查看自动收集任务历史执行状态
SELECT client_name, window_name, jobs_created, jobs_started, jobs_completed FROM dba_autotask_client_history WHERE client_name like '%stats%';
5：查看自动收集任务执行时间窗口
select WINDOW_NAME, WINDOW_NEXT_TIME , WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME ;
6：查询自动收集任务正在执行的JOB
select client_name, JOB_SCHEDULER_STATUS from DBA_AUTOTASK_CLIENT_JOB where client_name='auto optimizer stats collection';
7：与时间窗口相关的视图
7.1 查询自动收集任务所属时间窗口组
select client_name,window_group from dba_autotask_client where client_name='auto optimizer stats collection';
7.2 查询自动收集任务所属时间窗口组详细信息
select * from dba_scheduler_window_groups where window_group_name='ORA$AT_WGRP_OS';
7.3 查看自动收集任务所属时间窗口组包含的子时间窗口
select * from dba_scheduler_wingroup_members where window_group_name='ORA$AT_WGRP_OS';
8：ORACLE 11G默认自动收集统计信息时间点
select window_name,duration,next_start_date from dba_scheduler_windows;
9：修改自动收集统计信息时间
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 08:00:00');
end;
/
begin
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=2;byminute=0; bysecond=0');
sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 08:00:00');
end;
/
select window_name,duration,next_start_date from dba_scheduler_windows;
二：手动收集统计信息
–收集数据库信息
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
select table_name,NUM_ROWS，BLOCKS，AVG_ROW_LEN,last_analyzed from dba_tables
–收集schema信息
EXEC DBMS_STATS.gather_schema_stats('WEISI');
EXEC DBMS_STATS.gather_schema_stats('WEISI', estimate_percent => 15);
select table_name,NUM_ROWS，BLOCKS，AVG_ROW_LEN,last_analyzed from dba_tables t where t.owner='WEISI'
–收集表信息
EXEC DBMS_STATS.gather_table_stats('WEISI', 'NORMAL_T');
EXEC DBMS_STATS.gather_table_stats('WEISI', 'NORMAL_T', estimate_percent => 15);
select table_name,NUM_ROWS，BLOCKS，AVG_ROW_LEN,last_analyzed from dba_tables t where t.owner='WEISI'
–收集index信息
EXEC DBMS_STATS.gather_index_stats('WEISI', 'NORMAL_T_INDEX');
EXEC DBMS_STATS.gather_index_stats('WEISI', 'NORMAL_T_INDEX', estimate_percent => 15);
select LEAF_BLOCKS，BLEVEL,CLUSTERING_FACTOR from dba_indexes t where t.owner='WEISI'
–删除收集信息
EXEC DBMS_STATS.delete_database_stats;
EXEC DBMS_STATS.delete_schema_stats('WEISI');
EXEC DBMS_STATS.delete_table_stats('WEISI', 'NORMAL_T');
EXEC DBMS_STATS.delete_index_stats('WEISI', 'NORMAL_T_INDEX');
select table_name,NUM_ROWS，BLOCKS，AVG_ROW_LEN,last_analyzed from dba_tables t where t.owner='WEISI'
select index_name,LEAF_BLOCKS，BLEVEL,CLUSTERING_FACTOR from dba_indexes t where t.owner='WEISI'
–创建备份收集信息表
数据库的统计信息备份恢复（只能是sys用户下）
exec dbms_stats.create_stat_table('sys','stat_wzx');
exec dbms_stats.export_database_stats('stat_wzx');
exec dbms_stats.import_database_stats('stat_wzx');
方案的统计信息备份恢复（只能在方案拥有者的用户下，weisi为用户名）
exec dbms_stats.create_stat_table('weisi','stat_wzx');
exec dbms_stats.export_schema_stats('weisi','stat_wzx');
exec dbms_stats.import_schema_stats('weisi','yyy');
表的统计信息备份恢复（只能在表拥有者的用户下）
exec dbms_stats.create_stat_table('weisi','stat_wzx');
exec dbms_stats.export_table_stats('weisi','NORMAL_T',null,'stat_wzx');
exec dbms_stats.import_table_stats('weisi','NORMAL_T',null,'stat_wzx');
sqlplus weisi/wesi@weisi
create table stat_t as select * from normal_t;
select table_name,last_analyzed from user_tables where table_name='STAT_T';
exec dbms_stats.gather_table_stats('weisi','STAT_T');
select table_name,last_analyzed from user_tables where table_name='STAT_T';
exec dbms_stats.create_stat_table('weisi','stat_wzx');
select count(1) from stat_wzx;
exec dbms_stats.export_table_stats('weisi','STAT_T',null,'stat_wzx');
--exec dbms_stats.export_table_stats('weisi','NORMAL_T',null,'stat_wzx');
select count(1) from stat_wzx;
exec dbms_stats.delete_table_stats('weisi','STAT_T');
select table_name,last_analyzed from user_tables where table_name='STAT_T';
exec dbms_stats.import_table_stats('weisi','STAT_T',null,'stat_wzx');
select table_name,last_analyzed from user_tables where table_name='STAT_T';
1：查询表最后一次统计时间
-- tables statistics
select owner,table_name,num_rows,last_analyzed from dba_tables where table_name in
('NORMAL_T')
order by table_name;
2：查询分区表统计时间
-- partition tables staticstics
select TABLE_OWNER,table_name,partition_name,num_rows,last_analyzed from dba_tab_partitions
where table_name in
('NORMAL_T')
order by TABLE_OWNER,table_name, partition_name;
3：查询索引统计时间
-- indexes staticstics
select OWNER,index_name,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_indexes
where table_name in
('NORMAL_T')
order by OWNER,table_name,index_name;
4：查询分区本地索引统计时间
-- local parition indexes staticstics
select dip.INDEX_OWNER,di.table_name,dip.INDEX_NAME,dip.PARTITION_NAME,dip.partition_name,dip.num_rows,dip.last_analyzed
from dba_ind_partitions dip, dba_indexes di where di.table_name in
('NORMAL_T')
and dip.index_name=di.index_name
order by dip.INDEX_OWNER,di.table_name,dip.index_name,dip.partition_name;
5：查看自动收集统计信息的任务及状态
select CLIENT_NAME,STATUS from dba_autotask_client ;
DBMS_STATS.GATHER_TABLE_STATS的语法如下:
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade BOOLEAN, stattab VARCHAR2, statid VARCHAR2, statown VARCHAR2, no_invalidate BOOLEAN, force BOOLEAN);
参数说明:
ownname:要分析表的拥者
tabname:要分析的表名.
partname:分区的名字,只对分区表或分区索引用.
estimate_percent:采样行的百分比,取值范围[0.000001,100],null为全部分析,不采样. 常量:DBMS_STATS.AUTO_SAMPLE_SIZE是默认值,由oracle决定最佳取采样值.
block_sapmple:是否用块采样代替行采样.
method_opt:决定histograms信息是怎样被统计的.method_opt的取值如下(默认值为FOR ALL COLUMNS SIZE AUTO):
for all columns:统计所列的histograms.
for all indexed columns:统计所indexed列的histograms.
for all hidden columns:统计你看不到列的histograms
for columns <list> SIZE <N> | REPEAT | AUTO | SKEWONLY:统计指定列的histograms.N的取值范围[1,254]; REPEAT上次统计过的histograms;AUTO由oracle决定N的大小;SKEWONLY multiple end-points with the same value which is what we define by "there is skew in thedata
degree:决定并行度.默认值为null.
granularity:Granularity of statistics to collect ,only pertinent if the table is partitioned.
cascade:是收集索引的信息.默认为FALSE.
stattab:指定要存储统计信息的表,statid如果多个表的统计信息存储在同一个stattab中用于进行区分.statown存储统计信息表的拥者.以上个参数若不指定,统计信息会直接更新到数据字典.
no_invalidate: Does not invalidate the dependent cursors if set to TRUE. The procedure invalidates the dependent cursors immediately if set to FALSE.
force:即使表锁住了也收集统计信息.
