阿里云
发表主题 回复主题
  • 402阅读
  • 9回复

数据库优化之统计分析实战篇

级别: 小白
发帖
0
云币
-35



我们发现有很多dba对oracle的统计分析都存在一些误解,认为这个是oracle后台自动运行的,我们不用去关心,我觉得统计分析有很深的学问。

我想问大家的是:

1、你们开启了的每天收集统计信息,每天窗口运行正常吗?

2、所有的表都使用oracle的自动窗口收集?大表是不是要考虑定制收集?分区表是不是要增量收集?

3、在不影响业务的情况下,什么时间段收集统计信息?

4、如何判断统计信息是否过期,如何判断哪些字段需要收集直方图,收集的规则是什么?

下面我来一一解答:

1、

查询自动任务是否开启

SELECT OPERATION_NAME,STATUS FROM DBA_AUTOTASK_OPERATION WHERE CLIENT_NAME LIKE '%stats%';

STATUS为ENABLED表示任务开启

如果为DISABLE exec dbms_auto_task_admin.ENABLE(client_name => 'auto optimizer stats collection',operation =>'auto optimizer stats job',window_name=> null);

查看后台的窗口信息:

select * from dba_scheduler_windows

查看每天的统计JOB是否成功数据库优化之统计分析实战篇-oracle数据库统计分析racle11g调用窗口会自动生成以ORA$AT_OS_OPT开头的JOB

select * from dba_scheduler_job_run_details where job_name LIKE '%ORA$AT_OS_OPT%' ORDER BY LOG_DATE DESC

如果stoped要查看具体的内容:我这边有两次发生stop.

(1) 执行总共收集的时间,超出了收集窗口的时间(默认是周一到周五 22:00~02:00 4个小时 周六和周日06:00~02:00 20个小时)

    解决办法:可以开启并行收集(默认是串行)

    EXEC DBMS_STATS.SET_PARAM('DEGREE',4);

    或者将窗口时间调长

   begin

   dbms_scheduler.set_attribute(name => 'TUESDAY_WINDOW', attribute => 'duration', value =>numtdosinterval(480,'minute'));

   end;

   /

     

(2) job执行到一半的时候,由于其它窗口启动导致收集终止。

    解决办法:将收集的窗口优先级调高

    BEGIN

  dbms_scheduler.set_attribute(

    name      => 'TUESDAY_WINDOW',

    attribute => 'window_priority',

    value     => 'HIGH');

   END;

   /

---备注:调整窗口的时间点收集:

BEGIN

  dbms_scheduler.disable(

    name  => 'TUESDAY_WINDOW');

  dbms_scheduler.set_attribute(

    name      => 'TUESDAY_WINDOW',

    attribute => 'repeat_interval',

    value     => 'freq=daily;byday=TUE;byhour=03;byminute=0;bysecond=0');

  dbms_scheduler.enable(

    name => 'TUESDAY_WINDOW');

END;

/

2、表是否都是oracle自动收集

(1) 获取对象级别的统计信息设置选型

SELECT * FROM DBA_TAB_STAT_PREFS

(2)大表我们要定制化收集,调整收集的采样率(例如我们有张表是800G,如果你使用oracle的默认收集,收集时间保守估计在800分钟)

  

  可以定制收集

  exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',100);

  

  BEGIN

   DBMS_STATS.GATHER_TABLE_STATS(ownname => 'XX',

   tabname => 'XX',

    estimate_percent => 30,

    degree => 4,

    no_invalidate    => FALSE,

    method_opt => 'for all columns size auto',

    cascade=>TRUE

   );

    END;  

具体什么样的表需要定制,这个根据你们的数据库实际情况,找出具体的表手工收集,下面有个脚本参考一下:

declare

  cursor stale_table is

  select owner, segment_name,

         case  when size_gb<0.5 then 30

         when size_gb>=0.5 and size_gb<1 then 20

         when size_gb >=1 and size_gb<5 then 10

         when size_gb>=5 and size_gb<10 then

         when size_gb>=10 then 1

         end as percent,

         8 as degree

          from (select owner,segment_name,sum(bytes/1024/1024/1024)size_gb

          from dba_segments where owner='' AND SEGMENT_NAME IN

          (SELECT /*+ UNNEST */ DISTINCT TABLE_NAME FROM DBA_TAB_STATISTICS WHERE

          (LAST_ANALYZED IS NULL OR STALE_STATS='YES') AND OWNER='')

          GROUP BY OWNER,SEGMENT_NAME);

          BEGIN

FOR STALE IN STALE_TABLE LOOP

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>STALE.OWNER,

  TABNAME=>STALE.SEGMENT_NAME,

  ESTIMATE_PERCENT =>STALE.PERCENT,

  METHOD_OPT =>'for all colums size skewonly',---默认oracle 是for all colums size auto

  degree =>8,

  granularity =>'ALL',

  cascade =>true);

  end 1oop;

  end;

(3) 分区表的增量收集:

exec dbms_stats.set_table_prefs('username','TABLENAME','INCREMENTAL','TRUE');

为什么要增量收集,我们这边有一个log表,记录用户下的所有源表的增删改查,这个表用于用户程序的增量同步的判断。

每天的数据量很大,按周的rang分区,查询的时间都是在最近时间。

----我们暂且不用考虑分区表的设计问题和增量的断点问题。

3、第三点上面已经解答。

4、

(1)判断是否过期

(stale_stats='YES' or last_analyzed is  null)可以说明统计信息过期:

  select owner,table_name ,object_type,stale_stats,last_analyzed from dba_tab_statistics where owner ='XX'

and  table_name in('XX')  

(2)oracle默认表的变化量在10%收集统计信息。

有时我们发现有些表数据量变化不大,很久没有收集统计信息,导致执行计划问题(oracle 估算基数处在一个临界点)我们可以根据表的变化率进行微调,从而制定合理的阀值。

查看表的变化率:

select *

  from (select t2.table_name,

               t2.partition_name,

               t2.subpartition_name,

               t2.inserts,

               t2.updates,

               t2.deletes,

               t1.NUM_ROWS,

               case

                 when (t1.NUM_ROWS = 0 or t1.NUM_ROWS is null) then

                  100

                 else

                  trunc((t2.inserts + t2.updates + t2.deletes) / t1.NUM_ROWS * 100,

                        2)

               end "mod_pct(%)",

               case

                 when t1.LAST_ANALYZED is null then

                  '未分析'

                 else

                  to_char(t1.LAST_ANALYZED)

               end LAST_ANALYZED,

                t2.timestamp,

               'begin dbms_stats.gather_table_stats('||'ownname=>'''|| t2.table_owner ||

               ''','||'tabname=>''' || t2.table_name ||''','||'estimate_percent=>100'||','||'degree=>4'||',cascade => TRUE); end;' EXEC_STATS

          from dba_tab_statistics t1, dba_tab_modifications t2

         where t1.OWNER = t2.table_owner

           and t1.TABLE_NAME = t2.table_name

           and DECODE(t1.PARTITION_NAME, t2.partition_name, 0, 1) = 0

           and DECODE(t1.SUBPARTITION_NAME, t2.subpartition_name, 0, 1) = 0

           and t2.table_owner = '' AND T2.table_name='')

where "mod_pct(%)" >= 0

order by "mod_pct(%)" desc;

调整阀值:

   exec dbms_stats.set_table_prefs('用户名','表名','STALE_PERCENT',1); 这样表变化率在1%会自动收集

(3) 直方图什么情况下收集:

oracle默认是for all colums size auto

  oracle文档中说(文档 ID 338926.1)中有描述

  Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.

  其实这句话有两层含义

  一是必须这个字段是被条件筛选到就是where a=xx

  select object_id from dba_objects where owner='XX' and object_name='XX';

  select name,intcol# from sys.col$ where obj#=XX and name='XX';

  select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=XX;

  这个字段equality_preds要有值

  二是 字段的数据有倾斜

  select count(distinct a) from xx 和select count(*) from xx  数据量不能相等并且数据量大于254,会以HEIGHT BALANCED收集。

  小于254会以FREQUENCY方式收集,当然我们也可以指定桶的大小。

  

5 具体什么情况下用 auto 、repeat  和 skewonly 方式收集统计信息,这个要理解这三个参数的含义。我个人的见解

  auto 使用于系统上线的初期,系统上线稳定了使用repeat方式,如果要自己定制收集使用skewonly方式。


如果发现不足之处,望指出。







级别: *
发帖
*
云币
*


请问,oracle不是提供动态采样的功能么?是否还需要定制采样Job?
级别: *
发帖
*
云币
*


谢谢楼主的共享,写的很详细。

下面我也补充几点。

1、

各个版本查询自动收集任务的方法少有差异,楼主说的是11g。

(1)

楼主说到开启为收集任务开启并行,这没问题,但得注意期间的IO资源情况。

(2)

一般来说,一个任务会配置一个或多个时间窗,多个时间窗之间通常不会有冲突,如果有,那就会发生楼主说的情况了。

2、

(1)

dba_tab_stat_prefs这个视图空的,除非用户定制了表的收集特性。

(2)

收集粒度,这个一般为系统默认,不同大小的表会有不同的粒度,一般设定为自动,特殊再定制。

(3)

增量收集,记得在默认情况下是开启的,虽然这个参数默认是false,可以看看官方的文档。

4、

直方图,一般默认即可,特殊情况再定制收集。

仅供讨论和参考。
级别: *
发帖
*
云币
*




如果存储不是问题,确实应该采用AUTO,可惜大部分跑ORACLE的环境,存储都是问题数据库优化之统计分析实战篇-疾病分析统计数据库数据库优化之统计分析实战篇-数据库优化数据库优化之统计分析实战篇-数据库优化方案数据库优化之统计分析实战篇-mysql数据库优化数据库优化之统计分析实战篇-oracle数据库统计分析数据库优化之统计分析实战篇-疾病分析统计数据库数据库优化之统计分析实战篇-数据库优化数据库优化之统计分析实战篇-数据库优化方案数据库优化之统计分析实战篇-mysql数据库优化数据库优化之统计分析实战篇-oracle数据库统计分析:sweat2::sweat2:
级别: *
发帖
*
云币
*


支持一下,慢慢看
级别: *
发帖
*
云币
*




调整采样比也是过气的方式
级别: *
发帖
*
云币
*


采用系统任务自动收集是局限于OLTP数据库才应该采用的方式
级别: *
发帖
*
云币
*




你可以把动态采样看成是统计分析的一个补充

oracle 9R2版本以上 默认是开启动态采样功能(optimizer_dynamic_sampling 大于等于1),引入动态采样是为了准确估算出SQL语句中where

条件中出现有关联关系的列,可以相对准确的估算出基数返回的结果。

我们这边是没有定制的,但是你可以修改采样数据块的数量 调整level的级别
级别: *
发帖
*
云币
*





那说说你的方法  auto_sample_size
级别: *
发帖
*
云币
*




OLAP数据仓库系统 你可以对大表进行定制化收集,调整采样的比率
发表主题 回复主题
« 返回列表
«12345678910»
共10页
上一主题下一主题

限100 字节
如果您在写长篇帖子又不马上发表,建议存为草稿
 
验证问题: 阿里云官网域名是什么? 正确答案:www.aliyun.com
上一个 下一个
      ×
      全新阿里云开发者社区, 去探索开发者的新世界吧!
      一站式的体验,更多的精彩!
      通过下面领域大门,一起探索新的技术世界吧~ (点击图标进入)

      版权声明

      开发者论坛为你提供“数据库优化之统计分析实战篇”的内容,论坛中还有更多关于 “数据库优化之统计分析实战篇” 的内容供你使用,该内容是网友上传,与开发者论坛无关,如果需要删除请联系zixun-group@service.aliyun.com,工作人员会在5个工作日内回复您。