oracle统计信息

统计级别

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:即使表锁住了也收集统计信息.

©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容