《Oracle AWR与ASH性能报告深入解析》

一数据库版本

LEO1@LEO1> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

二 AWR性能诊断报告

AWR：Automatic Workload Repository 自动工作负载信息库

通常在诊断数据库性能的时候分三个阶段

第一阶段：SQL语句级性能优化

第二阶段：session级性能优化，这时我们可以用ASH来做分析

第三阶段：DB级性能优化，AWR就是数据库层性能诊断报告，当我们无法判断数据库哪里性能出现问题时我们可以做一个全身体检报告来找到我们瓶颈所在。

AWR机制：通过对系统整体动态采样收集快照信息，存储在SYSAUX表空间，每小时采样一次，可以保存7天，MMON进程实施，快照分析后写入DBA_HIST_%开头的数据字典。

AWR信息来源：DBA_HIST_%开头的数据字典，请见下图

LEO1@LEO1> select table_name from dictionary where table_name like 'DBA_HIST_%';

TABLE_NAME

------------------------------------------------

DBA_HIST_ACTIVE_SESS_HISTORY

DBA_HIST_ASH_SNAPSHOT

DBA_HIST_BASELINE

DBA_HIST_BASELINE_DETAILS

DBA_HIST_BASELINE_METADATA

DBA_HIST_BASELINE_TEMPLATE

DBA_HIST_BG_EVENT_SUMMARY

DBA_HIST_BUFFERED_QUEUES

DBA_HIST_BUFFERED_SUBSCRIBERS

DBA_HIST_BUFFER_POOL_STAT

DBA_HIST_CLUSTER_INTERCON

DBA_HIST_COLORED_SQL

DBA_HIST_COMP_IOSTAT

DBA_HIST_CR_BLOCK_SERVER

DBA_HIST_CURRENT_BLOCK_SERVER

DBA_HIST_DATABASE_INSTANCE

DBA_HIST_DATAFILE

DBA_HIST_DB_CACHE_ADVICE

…………………………………………………

109 rows selected.

AWR信息就是来自上面这些数据字典表，它是把这些表中数据进行汇总统计后生成HTML or TXT格式

LEO1@LEO1> select snap_id,name,value from DBA_HIST_SGA where snap_id>=173 and snap_id<=174;

SNAP_ID NAME VALUE

---------- ----------------------------------------------------------------------------------------------------------------------------------

173 Database Buffers 117440512

173 Fixed Size 2214856

173 Redo Buffers 8052736

173 Variable Size 385877048

174 Database Buffers 117440512

174 Fixed Size 2214856

174 Redo Buffers 8052736

174 Variable Size 385877048

上面这个例子显示了173-174快照中SGA的信息

OEM可以生成图形化性能分析图，UI版AWR

AWR基线：我们可以在数据库平稳正常的状态下创建AWR基线（参照物），在实际生产中可以作为性能指标曲线的一个参照物，有了基线对比，我们就可以很方便的了解到系统的一个真实的性能趋势。

AWR创建：sqlplus / as system @下面的脚本就可以创建AWR报告了

创建脚本目录：/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awrrpt.sql

AWR报告分析说明

1. WORKLOAD REPOSITORY report for    

2. DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
EMSTA	433507400	emsta1	1	14-Aug-12 22:08	11.2.0.2.0	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
emsta1	Solaris[tm] OE (64-bit)	64	32	8	128.00

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	6023	07-Sep-12 14:00:09	1788	2.8
End Snap:	6026	07-Sep-12 17:00:06	1793	2.9
Elapsed:		179.94 (mins)
DB Time:		79.25 (mins)

数据库名：EMSTA DB ID：433507400 实例名：emsta1 第一个实例 启动时间 版本 是RAC

主机名：emsta1 操作系统平台：Solaris 64位 64颗CPU 32核 内存：128GB

由上述硬件判断这是2台小机组成的RAC模式数据库，上面的是实例1，下面的是实例2，名称后缀不同。

起始快照id：6023

终止快照id：6026 快照与快照间隔1小时从14：00~17：00一共3小时采样信息

起始快照与终止快照间隔时间：180分钟

所有用户使用数据库时间总和（累加值）：80分钟

起始时间有1788个会话，每个会话使用2.8个游标

结束时间有1793个会话，每个会话使用2.9个游标    

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
EMSTA	433507400	emsta2	2	14-Aug-12 22:08	11.2.0.2.0	YES

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
emsta2	Solaris[tm] OE (64-bit)	64	32	8	128.00

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	6023	07-Sep-12 14:00:09	1363	3.0
End Snap:	6026	07-Sep-12 17:00:06	1378	3.0
Elapsed:		179.94 (mins)
DB Time:		136.61 (mins)

实例2中各个部分的含义值和实例1相同，这里不再另外说明

2.cache size    

	Begin	End
Buffer Cache:	15,360M	15,360M	Std Block Size:	8K
Shared Pool Size:	6,272M	6,272M	Log Buffer:	111,456K

Instance1：数据库缓冲区15360M

共享池6272M

redo log 缓冲区111.456M

数据块大小8K    

Buffer Cache:	13,696M	13,696M	Std Block Size:	8K
Shared Pool Size:	6,144M	6,144M	Log Buffer:	111,456K

Instance2：数据库缓冲区13696M

共享池6144M

redo log 缓冲区111.456M

数据块大小8K

2个实例的SGA有一点点的大小差异，但是差距不大。

3.Load profile

数据库负载属性信息 美秒 每个事物 每次执行 每次调用    

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	0.4	0.3	0.01	0.00
DB CPU(s):	0.4	0.2	0.01	0.00
Redo size:	15,275.9	8,983.0
Logical reads:	13,716.1	8,065.8
Block changes:	79.2	46.6
Physical reads:	365.3	214.8
Physical writes:	4.5	2.7
User calls:	232.7	136.8
Parses:	11.4	6.7
Hard parses:	0.3	0.2
W/A MB processed:	2.7	1.6
Logons:	0.0	0.0
Executes:	54.3	32.0
Rollbacks:	0.0	0.0
Transactions:	1.7

Instance1：逻辑读和物理读较多，是以读为主

Instance2：物理写较多，是以写为主

如果我们有一个基线值，就好比较性能优略了    

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	0.8	0.1	0.00	0.00
DB CPU(s):	0.4	0.1	0.00	0.00
Redo size:	102,788.5	11,594.5
Logical reads:	4,287.6	483.6
Block changes:	436.4	49.2
Physical reads:	100.5	11.3
Physical writes:	40.6	4.6
User calls:	261.7	29.5
Parses:	108.9	12.3
Hard parses:	0.1	0.0
W/A MB processed:	0.9	0.1
Logons:	3.1	0.4
Executes:	263.1	29.7
Rollbacks:	0.0	0.0
Transactions:	8.9

业务类型不同关注数据指标也不同

OLAP：关注IO指标

OLTP：关注内存 CPU指标

4.Top 5 Timed Foreground Events

Instance1：这是排名前五位的前台等待事件（用户SQL的等待事件）

DB CPU：数据库消耗CPU时间（所有用户使用CPU的累加值）

Waits：等待了多少次

Times：等待了多少秒

Avg wait（ms）：平均等待一次多少毫秒

%DB time：占整体数据库时间的百分比，我们看到CPU消耗占了82%，应该解析的SQL语句比较多

Wait Class：等待类型

Instance2：%DB time 54% 也是排名第一，说明解析和执行的SQL语句很多

5.CPU&MEMORY 统计信息

Instance1

CPU %Idle：空闲率98% 看来CPU的使用率不高啊

%Busy CPU：忙时CPU占用53.9%

而且CPU等待时间占整体等待时间比例很小

SGA+PGA使用率占物理内存的19%，内存空闲空间还很高，我们还可以增加SGA+PGA容量缓存更多的SQL

Instance2 与 Instance1还是很相近的

6.RAC性能报告

AWR信息太多，这里简要截图举例说明

实例数从快照开始到快照结束都是2

Instance1 每秒全局缓冲区接收块数6个

每个事物接收块数3个

DBRW Fusion write 0.2写的不是很多，大部分动作都在读

Instance2 每秒全局缓冲区接收块数21个，这个要比Instance1的多

每个事物接收块数2个，比Instance1的少

7.按照消耗时间排名

Instance1：SQL执行处理时间，消耗时间最多

CPU使用时间排第二

从这2点可以推断出，这是一个OLTP系统，主要消耗资源在CPU上而不是IO上

Instance2：CPU使用时间最多

8.Foreground Wait Class 前台进程等待事件（用户触发的）

Instance1

按等待类型分类：还是CPU消耗的时间最多

Instance2：Network 资源等待时间较长，说明数据块在2个实例间交叉复制和传输较多

9.Background Wait Events后台进程等待事件（数据库后台进程触发的）

这里列举了数据库后台进程的等待事件排名，我们可以看这些来判断哪些资源使用的较多

10.Instance Activity Stat 实例活跃度

IO向量统计排名最多

小结：我们从上面的各种指标参数来分析CPU资源消耗的较多，IO资源相对较少，根据不同业务类型关注指标类型不同判断这个RAC系统是一个OLTP系统。

（1）.我们首先要了解系统的业务种类AWR报告才能定位准确。

（2）.OLTP多关注CPU&MEMORY指标（软硬解析 cursor共享 绑定变量 SGA命中率）

OLAP多关注IO指标（物理/逻辑读写 一致性 数据块大小 数据块吞吐量 追踪SQL进行优化）

（3）.面->线->点：AWR -> TOP 5 -> 哪块资源有问题

三产生一个ASH报告，并进行分析，给出最后的结论。

ASH：Active Session History 活动会话历史记录

ASH是一个会话级别的性能诊断报告，可以提供更细粒度的时间区间，可以精确到分钟，ASH可以提供比AWR更详细的关于历史会话的信息，可以作为AWR的补充。

ASH信息来源“v$active_session_history” 保存当前会话的采集信息（一秒钟一次快照），视图容量满后可以被覆盖，可以从下面的数据字典中寻找

“dba_hist_active_sess_history”保持历史会话的采集信息

生成ASH报告

创建脚本目录：/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ashrpt.sql

ASH创建：sqlplus leo1/leo1 @创建脚本

[oracle@leonarding1 ~]$ sqlplus leo1/leo1

@/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ashrpt.sql

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

1678393804 LEO1 1 LEO1

数据库id 数据库名 实例数量 实例名

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text 指定生成ASH报告类型 HTML or TXT

Defaults to 'html' 默认是HTML

Enter value for report_type: 我们直接回车生成HTML类型

Specify the timeframe to generate the ASH report 指定ASH报告采集时间区间

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

-- Valid input formats:

-- To specify absolute begin time:

-- [MM/DD[/YY]] HH24:MI[:SS]

-- Examples: 02/23/03 14:30:15 样例

-- 02/23 14:30:15

-- 14:30:15

-- 14:30

-- To specify relative begin time: (start with '-' sign)

-- -[HH24:]MI

-- Examples: -1:15 (SYSDATE - 1 Hr 15 Mins) 可以当前时间减去指定的分钟数

-- -25 (SYSDATE - 25 Mins)

Defaults to -15 mins 默认减去15分钟

Enter value for begin_time: 03/11/13 00:00:00 我指定开始时间是2013年3月11日零点

Report begin time specified: 03/11/13 00:00:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time 默认是当前时间-03/11/13 00:00:00

Press Enter to analyze till current time

Enter value for duration: 现在是03/11/13 00:35:00，我们按回车

Specify the Report Name 指定ASH报告名称，可以加创建到哪的路径

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is ashrpt_1_0311_0035.html. To use this name, 默认名称

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: /home/oracle/ashrpt_leo1_0000_0035.html

Report written to /home/oracle/ashrpt_leo1_0000_0035.html 报告已经创建到指定目录

LEO1@LEO1>

我们打开ASH报告ashrpt_leo1_0000_0035.html

1.数据库概括信息

数据库名：LEO1

数据库id：1678393804

实例名：LEO1

实例数：1

版本：11.2.0.1.0

RAC：NO

主机名：leonarding1.oracle.com

CPUs：2核

SGA Size：490M 其中data_buffer_cache 112M shared pool 184M ASH buffer size 4M

ASH采样开始时间：2013-03-11 00:00:00 ASH信息来源“v$active_session_history”

ASH采样结束时间：2013-03-11 00:35:00

间隔时间：36分钟

采样数：416

平均活动会话：0.19

每个CPU执行平均会话数：0.10

这么一看跟AWR上来阐述系统概括信息差不多

2.Top User Events 前5名等待事件

事件名 事件类型 占整体百分比 平均会话数

CPU等待时间最长 CPU 37.26 0.07

这是我自己的实验环境，没有很多并发会话，所以活动会话数比较少

这是等待事件的详细信息

3.Top SQL 性能最差SQL排名

按等待事件排名SQL

按数据处理方式排名SQL

4.Top Sessions 按会话信息排名

一个会话由：sid+serial#来唯一定位的

5.Top Objects & Files 按数据库对象和数据文件排名

看第二行就是我们经常使用的LEO5表，object_id=74268

因为我们刚刚做了AWR和ASH报告，用sysaux表空间较多，因此排第一位

小结：ASH主要是针对会话级的一个体检报告，它可以追踪历史会话，从会话的角度分析数据库性能瓶颈，从而找到SQL，优化SQL语句。

四分析说明ASH和AWR报告的使用场景

AWR：如果想全面了解数据库各个方面性能的话（包括 硬件 软件 应用 数据库）可以使用AWR报告，实例级别诊断报告

ASH：如果想了解关于历史会话的信息可以使用ASH报告，会话级别诊断报告

场景功能对比

AWR VS ASH

Instance wide data 实例级广泛数据 Yes Yes

Time based data 基于时间统计数据 Yes Yes

Counts/occurrence data 计数/统计数据 No Yes

Analyze any time period 在任何时间段做分析 Yes No

Detailed session level data 会话级详细数据 Yes No

Individual Wait event data 个别等待事件 Yes No

Sampled data 采集样本数据 Yes No

Time based analysis 基于时间分析 Yes Yes

AWR ASH session OLTP OLAP Repository

PDF51CTO下载中心：《Oracle AWR与ASH性能报告深入解析-核心参数详解-手操-图文-可下载》   请点击下载

2013.3.10     天津&spring     分享技术~成就梦想     Blog：