循序渐进Oracle - 全面认识Oracle ASH

从Oracle 10g开始,Oracle引入了ASH新特性,也就是活动Session历史信息记录(Active Session History,ASH)。如果说v$session_wait_history是一小步,那么ASH则是Oracle迈出根本变革的一大步。

本文节选自<循序渐进Oracle:数据库管理、优化与备份恢复>

一、ASH概述

ASH以v$session为基础,每秒钟采样一次,记录活动会话等待的事件。因为记录所有会话的活动是非常昂贵的,所以不活动的会话不会被采样,这一点从ASH的“A”上就可以看出。采样工作由Oracle 10g新引入的一个后台进程MMNL来完成。

是否启用ASH功能,受一个内部隐含参数控制:

而采样时间同样由另一个内部隐含参数决定:

1000毫秒正好是一秒的时间。此处引用的脚本GetHparDes.sql内容如下:

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.inst_id = USERENV ('Instance') AND y.inst_id = USERENV ('Instance') AND x.indx = y.indx AND x.ksppinm LIKE '%&par%' /

注意:隐含参数通常具有特殊的作用,一般不建议用户查询或者修改,本文大量引用隐含参数的目的只有一个,那就是希望大家知道,所有在文档中见到的限制、约束、阈值、比率都是有来源的,只要足够细心,就能找出数据库的真相,不再靠记忆来学习。

很多人可能更关心性能,如此频繁的采样是否会极大地影响数据库的性能呢?

采样的性能影响无疑是存在的,但是因为Oracle的采样工具可以直接访问Oracle10g内部结构,所以是极其高效的,对于性能的影响也非常小,这也正是Oracle提供优化或诊断工具的优势所在。

ASH信息被设计为在内存中滚动的,在需要时早期的信息是会被覆盖的。ASH记录的信息可以通过v$active_session_history视图来访问,对于每个活动session,每次采样会在这个视图中记录一行信息。

这部分内存在SGA中分配:

SQL> select * from v$sgastat where name like '%ASH%'; POOL NAME BYTES ------------ --------------- ---------- shared pool ASH buffers 6291456

注意,ASH buffers的最小值为1MB,最大值不超过30MB,大小按照以下算法分配:

Max ( Min (cpu_count * 2MB, 5% * SHARED_POOL_SIZE, 30MB), 1MB)

在以上公式中,如果SHARED_POOL_SIZE未显示设置,则限制为2%*SGA_TARGET。这一算法在Oracle 10g的不同版本中,可能不同。

根据这个算法,这里采样系统分配的ASH Buffers为6MB:

另外一个生产系统中,这一内存分配为8MB:

记录在SGA中的ASH信息,可以通过v$session_wait_history进行查询:

二、ASH报告

我们可以通过Oracle提供的工具生成ASH的报告,报告可以以几分钟为跨度对数据库进行精确分析;也可以以数小时或数天为时间跨度,为数据库提供概要分析。生成ASH报告主要可以通过两种方式。

脚本方式

调用$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本,回答一系列问题之后,就可以生成一个ASH的报告,报告包括Top等待事件、Top SQL、Top SQL命令类型、Top sessions等内容,摘录部分报告内容如下。

首先使用$ORACLE_HOME/rdbms/admin/ashrpt.sql脚本:

数据库可用的采样数据:

完成一系列定义之后,ASH会将用户定义概要显示出来:

然后生成ASH报告,接下来对这个报告进行一点详细说明:

顶级用户事件(TopUser Events)部分列出了采样时段内数据库发生的显著用户等待,可以看到行级锁竞争(enq: TX - row lock contention)占用了87.82%的活动时间。

顶级后台进程事件(TopBackground Events)部分列出后台进程等待事件,对于本例没有输出:

对于顶级等待事件,接下来部分列出了相关等待时间及其参数值。根据这些参数,可以知道等待发生在哪些对象或资源上,对于不同的等待事件,P1/P2/P3列信息被完整地记录在案,在Oracle 10g之前,我们是无法在事后获得这部分信息的:

顶级服务和组件(TopService/Module),这一部分列出活动进程的用户及应用信息,通过这部分信息可以看到,活动时间最久的应用是SQL*Plus,那么也就意味着,相关SQL是通过SQL*Plus直接执行的:

顶级SQL命令类型(Top SQL Command Types)部分列出了ASH采样阶段数据库接受的命令类型,通过以下的数据注意到,采样阶段占用87.82%时间的SQL是一条UPDATE命令:

再转到Top SQL部分,引发数据库等待的SQL在此被列出,在这里看到那条引发等待的UPDATE语句排在了第一位,第二位的SQL则引发了全表扫描:

通过以上介绍我们已经能够感受到ASH的强大之处。

session及session的等待信息、进一步的SQL信息都被记录在案,并且能够通过ASH报告清晰展现,这对于追踪数据库的性能问题,发现数据库性能瓶颈提供了极大的帮助。

这在Oracle 10g之前都需要DBA去跟踪、捕获,现在Oracle帮助我们完成了这一切。

接下来列出了数据库未使用绑定变量的SQL示例(Top SQLUsing Literals),看来Oracle将绑定变量的重要性进一步提高了:

顶级会话(TopSessions)部分列举了处于等待的会话,信息包括详细的等待事件、等待用户等:

阻塞会话列表(TopBlocking Sessions)部分列举了被阻塞的会话信息,这个信息和测试追踪的信息一致,其来源同样一致:

此外还有顶级并行查询信息,在这次采样中,未有这类信息记录:

顶级数据库对象(Top DBObjects)部分则列举了相关等待具体等待的对象,对于我们测试的EYGLE表此处已经列出,数据库业务应用全表扫描的数据表也已经被列出:

顶级数据文件列表(Top DB Files)部分列出了全表扫描访问的数据文件信息:

顶级栓锁(Top Latches)部分列出Latch竞争,对于本例没有这类竞争:

活动结束时间(Activity Over Time)部分列出了各类等待的细粒度显示:

通过ASH报告可以注意到,以前需要通过用户追踪或者不易跟踪的信息,通过现在的ASH报告,可以很清晰地从始至终获得全面的信息,与以前版本比较起来,性能诊断和分析可以用“易如反掌”来形容。

EM图形方式

通过Oracle 10g Web方式的EM,可以在性能页,单击“运行ASH报告”按钮生成ASH报告,如图所示。

只要试用一下就可以感受到ASH的强大功能,OEM生成的ASH报告非常清晰和直观。ASH的概况信息如下图所示。

等待事件信息如下。

等待参数信息如下。

Top SQL等信息如下。

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

原文发表时间:2017-04-10

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

超实用运维经验:TEMP表空间不足、热块竞争经典案例

作者介绍 ? 邓秋爽 云和恩墨技术专家,擅长于SQL tuning、troubleshooting 系统运行过程中可能遇见各种各样的性能问题,如果仅仅是当前系统...

2805
来自专栏码神联盟

碎片化 | 第四阶段-48-hibernate概述和配置-视频

本套视频从Java基础到架构模式以及AI算法,整体视频以“碎片化”学习的模式,提供给大家 ,并配备实际项目为案例,让大家在坐车、吃饭、午休、蹲坑的时候,都可以学...

3216
来自专栏程序员的SOD蜜

还在写SQL的同志,去喝杯咖啡吧!

--标题可能比较“雷人”,但这是我今天早上的第一个感受。我们有一个同事昨天加班写了一大堆有关某些大表(字段很多的表)的增、删、查的SQL语句,看着哪些SQL语句...

2275
来自专栏数据和云

Oracle数据库12c release 2优化器详解

序言:优化器是Oracle数据库最引人入胜的部件之一,因为它对每一个SQL语句的处理都必不可少。优化器为每个SQL语句确定最有效的执行计划,这是基于给定的查询的...

3396
来自专栏数据和云

Oracle数据库中最让人匪夷所思的十大问题盘点

数据的世界无奇不有,常常会遇到一些超出常识之外的故障的发生。这就要求广大的DBA要深入了解数据库的内部机制,面对一些奇葩的故障或者问题能够拨开迷雾找到真相。今...

2835
来自专栏me的随笔

SQL SERVER FOR LINUX初体验

今天得空,就在Ubuntu17.04上安装了SQL SERVER 2017体验下,总体来说还是不错的。

1483
来自专栏人工智能LeadAI

ElasticSearch优化系列三:索引过程

大家可能会遇到索引数据比较慢的过程。其实明白索引的原理就可以有针对性的进行优化。ES索引的过程到相对Lucene的索引过程多了分布式数据的扩展,而这ES主要是用...

3729
来自专栏PPV课数据科学社区

【数据科学】R语言连接数据库

数据是关系数据库系统中存储的统一化格式。 因此,实施我们需要非常先进和复杂的SQL查询统计计算。但是R能够轻松地连接到诸如MySql, Oracle, Sql ...

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

物化视图自动刷新的碰壁(r7笔记第61天)

今天和开发的同事讨论一个问题,他们说source 1的环境中存在一个表,现在希望目标环境target 1和target 2中都需要用到这部分的数据。 ? 对...

3514
来自专栏数据和云

SQL之美 - Oracle 子查询优化系列精讲

题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。 本系列...

3063

扫码关注云+社区