Oracle性能调整的三把利剑--ASH,AWR,ADDM

0    487    2

Tags:

👉 本文共约16378个字,系统预计阅读时间或需62分钟。

简介

ASH(Active Session History,活动会话历史信息)、AWR(Automatic Workload Repository,自动负载信息库)、ADDM(Automatic Database Diagnostic Monitor,数据库自动诊断监视工具)是Oracle性能调整的三把利剑,需要深入地了解,但是面试一般都问得比较简单,主要问到的是AWR。

Oracle性能调整最重要的就是对最影响性能的SQL的调整。在一个应用中,能够影响到数据库的只有SQL,也只能是SQL。系统不能一味地依靠增强硬件、修改系统、数据库参数来提高数据库的性能,更多的应该关注那些最影响性能的SQL语句。ASH报告、AWR报告和ADDM报告都是能够找出影响性能SQL的工具。在分析ASH报告、AWR报告和ADDM报告的时候,最重要的工作就是找出对性能影响最大的SQL语句,并对其进行优化。

ASH介绍

简介

ASH(Active Session History,活动会话历史信息)每秒从V$SESSION中取ACTIVE状态会话的信息,存储在V$ACTIVE_SESSION_HISTORY中,并收集所有活动会话的等待信息,不活动的会话不会采样。这里的活动会话包含2类情况,一类是非空闲等待事件(WAIT_CLASS <> 'Idle'),一类是“ON CPU”状态的会话。采样工作由新引入的后台进程MMNL(Manageability Monitor Lite Process)来完成。若ASH数据被刷新到磁盘,则需要从DBA_HIST_ACTIVE_SESS_HISTORY视图中查询相关信息。

是否启用ASH功能,受一个隐含参数“_ASH_ENABLE”的控制,默认为TRUE。而采样时间同样由另一个隐含参数“_ASH_SAMPLING_INTERVAL”决定,默认为1000毫秒,即1秒。

1000毫秒,正好是1秒的时间。

众所周知,动态性能视图实质是Oracle自行构造的一堆存在于SGA内存区的虚表,也就是说,ASH的数据是保存在内存里的,实际上,Oracle分配给ASH的空间并不是无限大,ASH buffers的最小值为1MB,最大值不超过30MB。ASH占用的SGA内存大小可以通过如下SQL查询:

可以看到这里分配了大约16M大小的内存。

直白地讲,V$ACTIVE_SESSION_HISTORY中能够记录多少会话信息,一方面取决于该数据库的SGA分配给ASH buffers的大小,另一方面取决于数据库的启动和关闭(重启数据库时将重构SGA内存区)。这两方面的因素制约了V$ACTIVE_SESSION_HISTORY中能够保存的会话信息的能力。作为一名DBA,肯定是希望ASH尽可能多地保留关于会话的信息,但目前来看,单纯依靠V$ACTIVE_SESSION_HISTORY肯定无法实现这个目标,有什么更好的方法呢?Oracle又提供了AWR特性,ASH收集到的会话信息,是作为AWR中快照信息的一部分,被保存到了硬盘上。

ASH内存记录数据始终是有限的,为了保存历史数据,引入了自动负载信息库(Automatic Workload Repository ,AWR) 由后台进程MMON完成。ASH信息同样被采集写出到AWR负载库中。由于内存不是足够的,所以MMNL进程在ASH写满后会将信息写出到AWR负载库中。ASH全部写出是不可接受的,所以一般只写入收集的10%的数据量,而且使用direct-path insert完成,尽量减少日志的生成,从而最小化数据库性能影响。

写出到AWR负载库的ASH信息记录在AWR的基础表wrh$active_session_hist中,wrh$active_session_hist是一个分区表,Oracle会自动进行数据清理。

如何获取ASH报告?

获取ASH报告可以有3种方式:(1)脚本生成,(2)OEM生成,(3)存储过程生成。

(1)ASH报告生成脚本如下所示,根据提示输入相应的值即可获得ASH报告。

(2)使用OEM,可以在性能页,单击“运行ASH报告”按钮生成ASH报告,由于OEM生产用的相对比较少,这里就不讨论了。

(3)可以利用存储过程DBMS_WORKLOAD_REPOSITORY.ASH_REPORT_HTML()来获取ASH报告的文本内容,然后将文本内容拷贝到文本文件中,最后修改文本文件的后缀名为html即可打开html格式的ASH报告。例如取3116-3117之间的快照:

可以利用如下的脚本来批量生成要运行的存储过程:

--->>根据时段区间生成ASH报告:
img

AWR介绍

简介

AWR(Automatic Workload Repository)自动工作负载信息库AWR(Automatic Workload Repository,自动负载信息库)是Oracle 10g引入的一个重要组件。在AWR里面存储着近一段时间内(Oracle 10g默认是7天,Oracle 11g及其之后的版本默认是8天)数据库活动状态的详细信息。

AWR报告是对AWR视图进行查询而得到的一份自动生成的报告,它用于显示两个快照或者两个时间点之间捕捉到的数据。AWR报告其实就是一张数据库健康体检表,它显示了数据库健康的各项指标。通过AWR报告,DBA可以容易地获知数据库最近的活动状态,数据库的各种性能指标的变化趋势曲线,数据库最近可能存在的异常,分析数据库可能存在的性能瓶颈,从而对数据库进行优化。AWR使用几个表来存储采集的统计数据,所有的表都存储在新的名称为SYSAUX的特定表空间中的SYS模式下,并且以WRM$_*WRH$_*的格式命名。前一种类型存储元数据信息(如检查的数据库和采集的快照),后一种类型保存实际采集的统计数据。H代表“历史数据(Historical)”,M代表“元数据(Metadata)”。在这些表上构建了几种带前缀DBA_HIST_*的视图,这些视图可以用来编写自己的性能诊断工具。视图的名称直接与表相关;例如,视图DBA_HIST_SYSMETRIC_SUMMARY是在WRH$_SYSMETRIC_SUMMARY表上构建的。AWR报告所有的数据来源于AWR视图,即以DBA_HIST_开头的所有系统表。

AWR的前身是Statspack,Statspack在Oracle 10g和Oracle 11g中也有提供,同时和AWR一起做了同步更新,而且Statspack是公开源代码的,因此,关于Statspack的资料和源代码,都是理解AWR的一个有用的辅助工具。

AWR主要是由MMON(Manageability Monitor Process,可管理性监视器进程)和它的slave进程(Mnnn)来维护的。MMON执行很多关于AWR的任务,和各种与可管理性相关的后台任务,具体包括以下功能:

(1)启动slave进程Mnnn去做AWR快照。

(2)当某个测量值(metrics)超过了其度量阀值(threshold value)时发出alert告警。

(3)为最近改变过的SQL对象捕获指标信息。

若系统不能自动生成AWR快照,则可以从以下几个方面去检查:

① 参数STATISTICS_LEVEL的值必须设置为TYPICAL或者ALL。

② 在“SELECT SNAP_INTERVAL FROM DBA_HIST_WR_CONTROL;”的查询结果中,SNAP_INTERVAL的值不能无穷大,一般为1小时,可以通过执行如下的SQL语句来修改自动生成AWR快照的时间间隔:EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 60);

③ 在SQL语句“SELECT D.INSTANCE_NUMBER, (SYSDATE - D.END_INTERVAL_TIME) INTERVAL FROM DBA_HIST_SNAPSHOT D WHERE D.SNAP_ID = (SELECT MAX(SNAP_ID) FROM DBA_HIST_SNAPSHOT);”的查询结果中,INTERVAL列的值需大于0,若小于0,则可以手动生成几次快照来解决这个问题,SQL语句为:EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();。需要注意的是,在RAC环境下该命令会对数据库的所有节点都生成一次快照。

④ 检查MMON进程是否HANG住,若MMON进程HANG住,则AWR不可用。

真题1、You have set Tablespace Full Metrics Threshold values for the USERS tablespace as follows:

Warning (%):90

Critical (%):95

Which background process is responsible for issuing alerts when the threshold is crossed?

A、System monitor (SMON)

B、Process monitor (PMON)

C、Memory manager process (MMAN)

D、Manageability Monitor process (MMON)

答案:D。

题目说为USERS表空间设置了阀值,问的是当到达阀值之后哪个后台进程会发出alert告警。

本题中,对于选项A,SMON的作用是检查数据库的一致性如有必要还会在数据库打开时启动数据库的恢复。所以,选项A错误。

对于选项B,PMON的作用是负责在一个Oracle进程失败时清理资源。所以,选项B错误。

对于选项C,MMAN的作用是协调内存各组成部分的大小。所以,选项C错误。

对于选项D,MMON的作用有:(1)启动slave进程Mnnn去做AWR快照;(2)当某个测量值(metrics)超过了其度量阀值(threshold value)时发出alert告警;(3)为最近改变过的SQL对象捕获指标信息。所以,选项D正确。

所以,本题的答案为D。

如何获取AWR报告?

Oracle可以生成两种类型的AWR报告:文本格式和html格式。其中,html格式的报告界面更加友好。有针对整个数据库的AWR报告,有针对某个实例的AWR报告(在集群环境),也有针对单条SQL语句的AWR报告。可以通过运行Oracle自带的SQL脚本产生AWR报告,只是产生不同的AWR报告,需要运行不同的脚本。

另外,在这里介绍一种作者常用的生成AWR报告的方法:

首先查询数据库所有的快照:

例如,如果要生成203和204之间的快照,那么可以执行SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(3196811885,1,203,204));img

将结果拷贝到文本文件里边,然后另存为html文件,这样就可以生成html报告了。

---->>根据snapshot敬意生成AWR报告:
img

定时生成AWR报告

有的系统需要定时生成html格式的AWR报告,这个需求可以使用SHELL脚本来完成。

下面给出相应的SHELL脚本:

ADDM介绍

介绍

ADDM (Automatic Database Diagnostic Monitor AWR)ADDM(Automatic Database Diagnostic Monitor,数据库自动诊断监视工具)是植入Oracle数据库的一个自我诊断引擎。ADDM通过检查和分析AWR获取的数据来判断Oracle数据库中可能的问题。例如,系统资源紧张,出现了明显的性能问题,根据以往的办法,收集一份AWR报告,发现“db file scattered read”事件在top 5 events里面。根据经验,这个事件一般可能是因为缺少索引、统计分析信息不够新、热表都放在一个数据文件上导致I/O争用等引起的。根据这些经验,需要逐个定位排除,例如查看语句的执行计划、查看USER_TABLES的LAST_ANALYSED字段、检查热块等等步骤来最后定位出原因,并给出优化建议,但是现在可以根据ADDM采集到的数据直接给出优化建议,甚至给出优化后的语句。

与其它指导不同,ADDM在记录每个AWR快照之后会自动运行。每次记录快照后,ADDM会分析与后两个快照对应的时段。ADDM会预先监视实例,以便在大多数瓶颈问题成为严重问题之前检测到这些问题。多数情况下,ADDM会为检测到的问题提供建议解决方案,甚至可以量化这些建议的优势。

ADDM能发现定位的问题包括以下内容:

  • 操作系统内存诊断

  • 由于Oracle负载和非Oracle负载导致的CPU瓶颈问题

  • 导致不同资源负载的Top SQL语句和对象——CPU消耗、I/O带宽占用、潜在I/O问题、RAC内部通讯繁忙

  • 按照PL/SQL和Java执行时间找出Top SQL语句

  • 过多的连接(LOGIN/LOGOFF)

  • 过多硬解析问题——由于Shared Pool过小、书写问题、绑定大小不适应、解析失败原因引起的

  • 过多软解析问题

  • 索引查询过多导致资源争用

  • 由于用户锁导致的过多的等待时间(通过包DBMS_LOCK加的锁)

  • 由于DML锁导致的过多的等待时间

  • 由于管道输出导致的过多等待时间(例如通过包DBMS_PIPE.PUT进行管道输出)

  • 由于并发更新同一个记录导致的过多等待时间(行级锁等待)

  • 由于ITL不够导致的过多等待时间(大量的事务操作同一个数据块)

  • 系统中过多的COMMIT和ROLLBACK(logfile sync事件)

  • 由于磁盘带宽太小和其它潜在问题(例如由于LOGFILE太小导致过多的CHECKPOINT,MTTR设置问题,过多的Undo操作等等)导致的I/O性能问题

  • 对于DBWn进程写数据块,磁盘I/O吞吐量不足

  • 由于归档进程无法跟上Redo日志产生的速度,导致系统变慢

  • Redo数据文件太小导致的问题

  • 由于扩展磁盘分配导致的争用

  • 由于移动一个对象的高水位导致的争用问题

  • 内存太小问题——SGA_TARGET、PGA、Buffer Cache、Shared Pool

  • 在一个实例或者一个集群环境中存在频繁读写争用的热块或热对象

  • RAC环境中内部通讯问题

  • LMS进程无法跟上导致锁请求阻塞

  • 在RAC环境中由于阻塞和争用导致的实例倾斜

  • RMAN导致的I/O和CPU问题

  • STREAMS和AQ问题

  • 资源管理等待事件

总之,ADDM提供了一个整体的优化方案,基于一段时间内的AWR快照(默认一小时一次)可以执行ADDM分析,它可以帮助诊断在这段期间内数据库可能存在的瓶颈。

如何获取ADDM报告?

有两种办法可以获取ADDM报告:

(1) 采用addmrpt.sql脚本。运行脚本:@?/rdbms/admin/addmrpt.sql

(2) 采用DBMS_ADVISOR包:

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复