Oracle并发(CONCURREMT)收集统计信息

编辑手记:从11.2开始,可以通过CONCURRENT参数,启用表或分区的并行扫描,加快统计信息的收集速度。

作者简介:何剑敏 Oracle ACS华南区售后团队,首席技术工程师。多年从事第一线的数据库运维工作,有丰富项目经验、维护经验和调优经验,专注于数据库的整体运维。

对于大表的统计信息收集,我们可以加degree参数,使得扫描大表的时候,进行并行扫描,加快扫描速度。

但是这在收集的时候,还是进行一个表一个表的扫描。并没有并发的扫描各个表。在oracle 11.2.0.2之后,就有了一个参数,可以并发扫描表(或者分区),这就是CONCURRENT参数。

可以通过以下语句看到你的数据库是否启用了CONCURRENT收集统计信息。

SELECT DBMS_STATS.get_prefs('CONCURRENT') FROM dual;

开启方式为:

SQL> begin 2 dbms_stats.set_global_prefs('CONCURRENT','TRUE'); 3 end; 4 /

开启concurrent之后,收集统计信息就会以并发的形式进行,会并发出多个job进程。

其收集方式如下图:

从测试结果看,启用concurrent的收集统计信息速度对比,schema级别的收集,XXX_SCHEMA下有400个多segment,大约20多GB:

默认:

exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA'); --263秒

开启8个并发:

exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA',degree => 8); --95秒。

开启concurrent+8个并发:

begin dbms_stats.set_global_prefs('CONCURRENT','TRUE'); end; exec dbms_stats.gather_schema_stats(ownname => 'XXX_SCHEMA',degree => 8); --61秒

database级别的收集:(600多G数据,9万多个segment)

默认:

exec sys.dbms_stats.gather_database_stats; --9小时

开启concurrent+8个并发:

begin dbms_stats.set_global_prefs('CONCURRENT','TRUE'); end; exec dbms_stats.sys.dbms_stats.gather_database_stats(degree => 8); --4小时

需要注意的是:

1. 用concurrent收集统计信息,需要收集统计信息用户具有以下权限:

  • CREATE JOB
  • MANAGE SCHEDULER
  • MANAGE ANY QUEUE

即使是该用户具有了dba角色,也还是需要显式授权上述权限。不然执行job的时候,可能会报错如下: ORA-27486 insufficient privileges和ORA-20000: Statistics collection failed for 32235 objects in the database

2. concurrent不能控制多少的并发度,所以如果数据库的初始化参数job_queue_processes设置的太高,(注意,在11.2.0.3之后,这个值的默认值是1000,所以就可能并发出1000个job。)

如在测试时,某测试库设置了60个job_queue_processes的时候,数据库中就会并发出60个job来收集统计信息。此时的top情况,可以看到CPU的user部分基本已经在90%以上了。

所以开启concurrent的另外一个建议,就是使用resource manager。

3. 观察concurrent收集的进度:

select job_name, state, comments from dba_scheduler_jobs where job_class like 'CONC%'; select state,count(*) from dba_scheduler_jobs where job_class like 'CONC%'; group by state;

4. 当启用concurrent的时候,同时再使用并行,建议将PARALLEL_ADAPTIVE_MULTI_USER设置成false,关闭并发度的自适应调整。 默认值是true,当使用默认值时,使自适应算法,在查询开始时基于系统负载来自动减少被要求的并行度。实际的并行度基于默认、来自表或hints的并行度,然后除以一个缩减因数。该算法假设系统已经在单用户环境下进行了最优调整。表和hints用默认的并行度

5. EBS系统应用是采用自己的并发管理器(FND_STATS)来收集统计信息,而收集统计信息用户往往是没有显式授权CREATE JOB、MANAGE SCHEDULER、MANAGE ANY QUEUE的。且EBS中用户众多,不可能为这些应用用户都显式授权。

所以在EBS中不能开启concurrent参数。EBS的安装文档中(Doc ID 396009.1),也是说将数据上收集统计信息的功能关闭的(_optimizer_autostats_job=false)

参考: https://blogs.oracle.com/optimizer/entry/gathering_optimizer_statistics_is_one http://blog.csdn.net/lukeUnique/article/details/51705922 Doc ID 1555451.1 – FAQ: Gathering Concurrent Statistics Using DBMS_STATS Frequently Asked Questions Doc ID 396009.1 – Database Initialization Parameters for Oracle E-Business Suite Release 12

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-02-23

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

辞旧迎新:2018年的分区你们建了吗?

各位同学,大家好! 转眼2017年就要结束了,在这过去的一年,我们有遇到困难解决不了的苦恼,亦有处理了棘手问题的快乐,还有在云和恩墨大讲堂里得到云和恩墨舵主以及...

3419
来自专栏散尽浮华

分布式监控系统Zabbix3.4-钉钉告警配置记录

群机器人是钉钉群的高级扩展功能,群机器人可以将第三方服务的信息聚合到群聊中,实现自动化的信息同步。例如:通过聚合GitHub,GitLab等源码管理服务,实现源...

2065
来自专栏FreeBuf

低成本玩转硬件安全(一) | BadUSB on Arduino

引言 鉴于硬件安全对于大多数新人是较少接触的,而这方面又非常吸引我,但是部分专业安全研究设备较高的价格使人望而却步。在该系列中,笔者希望对此感兴趣的读者在花费较...

2848
来自专栏CodingToDie

微信机器人

使用它可以方便的完成 回复消息、搜索好友、被添加自动回复、获取好友信息等功能,当然功能不止于这些,这里我们用到了回复信息功能

1361
来自专栏杨建荣的学习笔记

Oracle数据误操作全面恢复实战(r11笔记第78天)

对于DBA来说,面对误操作带来的数据恢复难度,其实很大。主要有以下几个方面: 误操作的影响范围极大,很可能不是删点,改点数据的操作,有时候可能是让人望而兴叹...

3375
来自专栏杨建荣的学习笔记

Oracle和MySQL的高可用方案对比(二)

昨天聊了一篇关于高可用方案中Oracle的RAC和MySQL的MHA的对比。 今天来说下Oracle的DG和MySQL的方案对比,相比来说,可能这方面MySQ...

2955
来自专栏杨建荣的学习笔记

通过外部表改进一个繁琐的大查询 (r8笔记第32天)

今天处理了一个比较有意思的案例,说是有意思,因为涉及多个部门,但是哪个部门似乎都不愿意接。最后还是用了一些巧力,化干戈为玉帛。 问题的背景是这样的,业务部门需要...

3339
来自专栏杨建荣的学习笔记

对IMP-00013问题的思考(r3笔记第71天)

对于IMP的问题或者是各类ORA问题,如果碰到的时候使用oerr是一个很不错的参考方案。比如对于ora错误12041的解释如下。 > oerr ora 1204...

2634
来自专栏杨建荣的学习笔记

一则备库CPU报警的思考(r7笔记第69天)

今天收到一封报警邮件,这引起了我的注意。当然过了一会,有收到了CPU使用率恢复的邮件。 报警邮件内容如下: ZABBIX-监控系统: ---------...

3093
来自专栏大数据和云计算技术

新数仓系列:Hbase周边生态梳理(1)

本文简单梳理下其中一个应用比较广的HBASE的生态,可能不全,有更多的请大家留言。具体HBASE的基本原理扫描大家可以自行百度下,另外,要系统掌握HBASE,推...

3997

扫码关注云+社区