Oracle统计信息管理之基础知识

原创 于 2018-08-27 11:32:27 发布 · 461 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。

本文围绕数据库统计信息展开，介绍了优化器相关统计信息，包括表、列、索引和系统统计；列举了统计信息相关的数据字典；说明了系统统计信息的收集方法；提及数据库自动统计信息及查看自动任务运行窗口；还阐述了analyze和dbms_stats两种统计信息收集方式及权限设置。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

1、优化器相关统计信息

优化器统计范围：
表统计： 行数，块数，行平均长度；all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；
列统计： 列中唯一值的数量（NDV），NULL值的数量，数据分布；
             --DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；
索引统计：叶块数量，等级，聚簇因子；
             --DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；
系统统计：I/O性能与使用率；
         CPU性能与使用率；
             --存储在aux_stats$中，需要使用dbms_stats收集，I/O统计在X$KCFIO中；

2、统计信息相关数据字典

o   DBA_TABLES
o   DBA_OBJECT_TABLES
o   DBA_TAB_STATISTICS
o   DBA_TAB_COL_STATISTICS
o   DBA_TAB_HISTOGRAMS
o   DBA_INDEXES
o   DBA_IND_STATISTICS
o   DBA_CLUSTERS
o   DBA_TAB_PARTITIONS
o   DBA_TAB_SUBPARTITIONS
o   DBA_IND_PARTITIONS
o   DBA_IND_SUBPARTITIONS
o   DBA_PART_COL_STATISTICS
o   DBA_PART_HISTOGRAMS
o   DBA_SUBPART_COL_STATISTICS
o   DBA_SUBPART_HISTOGRAMS
o   INDEX_STATS              存储ANALYZE ..VALIDATE STRUCTURE统计信息
o   AUX_STATS$               存储CPU统计信息
o   X$KCFIO                  存储I/O统计信息

3、系统统计信息

使用DBMS_STATS.GATHER_SYSTEM_STATS收集系统统计，Oracle推荐收集系统统计。收集系统统计需要DBA权限。
收集的优化器系统统计包括：
cpuspeedNW：代表无负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位Millions/sec。
ioseektim：I/O查找时间=查找时间+延迟时间+OS负载时间；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为ms。
Iotfrspeed：I/O传输速度；通过设置gathering_mode = NOWORKLOAD或手工设置统计；单位为Bytes/ms.
Cpuspeed：代表有负载CPU速度，CPU速度为每秒钟CPU周期数；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Millions/sec。
Maxthr：最大I/O吞吐量；通过设置gathering_mode =NOWORKLOAD,INTERVAL, START|STOP或手工设置统计；单位Bytes/sec.
Slavethr：服务I/O吞吐量是平均并行服务I/O吞吐量；通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；Bytes/sec.
Sreadtim：随机读取单块的平均时间；通过设置gathering_mode =INTERVAL，START|STOP或手工设置统计；单位为ms。
Mreadtim：顺序读取多块的平均时间，通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为ms。
Mbrc: 多块读平均每次读取的块数量；通过设置通过设置gathering_mode = INTERVAL，START|STOP或手工设置统计；单位为blocks。

4、数据库自动统计信息

SQL> SQL> col CLIENT_NAME for a35
SQL> col WINDOW_GROUP for a20
SQL> col status for a20
SQL> select client_name,WINDOW_GROUP,status from Dba_Autotask_Client;

CLIENT_NAME                         WINDOW_GROUP         STATUS
----------------------------------- -------------------- --------------------
sql tuning advisor                  ORA$AT_WGRP_SQ       ENABLED
auto optimizer stats collection     ORA$AT_WGRP_OS       ENABLED
auto space advisor                  ORA$AT_WGRP_SA       ENABLED

sql tuning advisor  自动SQL调优分析
auto optimizer stats collection  自动统计信息收集
auto space advisor   自动段分析

----------------关闭自动任务
BEGIN
       dbms_auto_task_admin.disable(
       client_name => 'auto space advisor',
       operation => NULL,
       window_name => NULL);
     END;  
/
--------------开启自动任务
BEGIN
       dbms_auto_task_admin.enable(
       client_name => 'sql tuning advisor',
       operation => NULL,
       window_name => NULL);
     END;
/

查看自动任务运行窗口

set linesize 1000 pagesize 500
col WINDOW_NAME for a30
col NEXT_TIME for a30
select WINDOW_NAME,
       to_char(WINDOW_NEXT_TIME, 'yyyy-mm-dd hh24:mi:ss') NEXT_TIME,
       WINDOW_ACTIVE,
       OPTIMIZER_STATS,
       SEGMENT_ADVISOR,
       SQL_TUNE_ADVISOR
  from DBA_AUTOTASK_WINDOW_CLIENTS
order by WINDOW_NEXT_TIME;
WINDOW_NAME                    NEXT_TIME                      WINDOW_ACTIVE   OPTIMIZER_STATS          SEGMENT_ADVISOR          SQL_TUNE_ADVISOR
------------------------------ ------------------------------ --------------- ------------------------ ------------------------ ------------------------
MONDAY_WINDOW                  2018-08-27 22:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
TUESDAY_WINDOW                 2018-08-28 22:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
WEDNESDAY_WINDOW               2018-08-29 22:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
THURSDAY_WINDOW                2018-08-30 22:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
FRIDAY_WINDOW                  2018-08-31 22:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
SATURDAY_WINDOW                2018-09-01 06:00:00            FALSE           ENABLED                  ENABLED                  ENABLED
SUNDAY_WINDOW                  2018-09-02 06:00:00            FALSE           ENABLED                  ENABLED                  ENABLED

SQL> set linesize 1000 pagesize 5000
SQL> col REPEAT_INTERVAL for a70
SQL> select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
  2      where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

WINDOW_NAME                    REPEAT_INTERVAL                                                        DURATION
------------------------------ ---------------------------------------------------------------------- ---------------
MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0                  +000 04:00:00
SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0                   +000 20:00:00
SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0                   +000 20:00:00

-----禁用Windows
BEGIN
    dbms_scheduler.disable(name  => 'SUNDAY_WINDOW');
END;
/
------启用windows
BEGIN dbms_scheduler.enable(
    name => 'SUNDAY_WINDOW');
END;
/
----修改windows属性
BEGIN
    dbms_scheduler.set_attribute(
        name      => 'SATURDAY_WINDOW',
        attribute => 'DURATION',
        value     => numtodsinterval(2, 'hour'));
END;
/    
------删除windows
BEGIN
DBMS_SCHEDULER.REMOVE_WINDOW_GROUP_MEMBER(
    group_name  => 'MAINTENANCE_WINDOW_GROUP',
    window_list => 'MORNING_WINDOW');
END;
/

5、统计信息收集

analyze统计：
需要使用ANALYZE统计的统计：使用LIST CHAINED ROWS和VALIDATE子句收集空闲列表块的统计；
ANALYZE table tablename compute statistics;
ANALYZE index| cluster indexname estimate statistics;
ANALYZE TABLE tablename COMPUTE STATISTICS  FOR TABLE/ FOR ALL [ LOCAL] INDEXES/FOR ALL [INDEXED ] COLUMNS ;
ANALYZE TABLE tablename DELETE STATISTICS
ANALYZE TABLE tablename VALIDATE REF UPDATE
ANALYZE TABLE tablename VALIDATE STRUCTURE [ CASCADE] |[ INTO TableName]
ANALYZE TABLE tablename LIST CHAINED ROWS [ INTO TableName]
analyze table irm.E_DEVICE_PTN validate structure cascade online
注意：ANALYZE 不适合做分区表的分析

dbms_stats：
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。这个包的下面四个存储过程分别收集index、 table、 schema、database的统计信息 :
dbms_stats.gather_table_stats           收集表、列和索引的统计信息；
dbms_stats.gather_schema_stats         收集SCHEMA下所有对象的统计信息；
dbms_stats.gather_index_stats           收集索引的统计信息；
dbms_stats.gather_system_stats          收集系统统计信息
dbms_stats.GATHER_DICTIONARY_STATS：  所有字典对象的统计；
DBMS_STATS.GATHER_DICTIONARY_STATS   其收集所有系统模式的统计
dbms_stats.delete_table_stats            删除表的统计信息
dbms_stats.delete_index_stats            删除索引的统计信息
dbms_stats.export_table_stats            输出表的统计信息
dbms_stats.create_state_table
dbms_stats.set_table_stats               设置表的统计
dbms_stats.auto_sample_size
dbms_stats.GATHER_FIXED_OBJECTS_STATS;

查看自动统计信息是否开启，
oracle 10 g ：SELECT OWNER, JOB_NAME, ENABLED FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'GATHER_STATS_JOB';
oracle 11 g ：select t1.owner, t1.job_name, t1.enabled  from dba_scheduler_jobs t1  where t1.job_name = 'BSLN_MAINTAIN_STATS_JOB' ;

---10g关闭自动统计信息命令
exec DBMS_SCHEDULER.DISABLE ('GATHER_STATS_JOB' );
BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB' );
END;
/
---10g启用自动统计信息命令
exec DBMS_SCHEDULER.ENABLE ('GATHER_STATS_JOB' );


-------11g 打开自动收集统计信息
BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE(
     client_name => 'auto optimizer stats collection'
,    operation   => NULL
,    window_name => NULL
);
END;
/
-------11g 关闭自动收集统计信息
BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
     client_name => 'auto optimizer stats collection'
,    operation   => NULL
,    window_name => NULL
);
END;
/

统计收集的权限
必须授予普通用户权限
sys@ORADB> grant execute_catalog_role to hr ;
sys@ORADB> grant connect, resource, analyze any to hr ;

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值