一 数据库版本
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 11.2.0.2.0 RAC YES EMSTA Host Name emsta1 433507400 emsta1 Platform Solaris[tm] OE (64-bit) Snap Id 6023 6026 Snap Time 07-Sep-12 14:00:09 07-Sep-12 17:00:06 179.94 (mins) 79.25 (mins) CPUs 64 1 14-Aug-12 22:08 Cores 32 Sessions 1788 1793 Sockets 8 Memory (GB) 128.00 Begin Snap: End Snap: Elapsed: DB Time: Cursors/Session 2.8 2.9 数据库名：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 EMSTA Host Name emsta2 DB Id Instance Inst num Startup Time 2 14-Aug-12 22:08 CPUs 64 Cores 32 Sessions 1363 Sockets 8 Release 11.2.0.2.0 RAC YES 433507400 emsta2 Platform Solaris[tm] OE (64-bit) Snap Id 6023 Snap Time 07-Sep-12 14:00:09 Memory (GB) 128.00 Begin Snap: Cursors/Session 3.0 End Snap: Elapsed: DB Time: 6026 07-Sep-12 17:00:06 179.94 (mins) 136.61 (mins) 1378 3.0 实例2中各个部分的含义值和实例1相同，这里不再另外说明 2.cache size Buffer Cache: Shared Pool Size: Begin 15,360M 6,272M End 8K 111,456K 15,360M Std Block Size: 6,272M Log Buffer: Instance1：数据库缓冲区15360M 共享池6272M
redo log 缓冲区111.456M
数据块大小8K Buffer Cache: Shared Pool Size: 13,696M 6,144M 13,696M Std Block Size: 6,144M Log Buffer: 8K 111,456K Instance2：数据库缓冲区13696M 共享池6144M
redo log 缓冲区111.456M
数据块大小8K
2个实例的SGA有一点点的大小差异，但是差距不大。 3.Load profile
数据库负载属性信息 美秒 每个事物 每次执行 每次调用
DB Time(s): DB CPU(s): Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: W/A MB processed: Logons: Executes: Rollbacks: Transactions: Per Second 0.4 0.4 15,275.9 13,716.1 79.2 365.3 4.5 232.7 11.4 0.3 2.7 0.0 54.3 0.0 1.7 Per Transaction 0.3 0.2 8,983.0 8,065.8 46.6 214.8 2.7 136.8 6.7 0.2 1.6 0.0 32.0 0.0 Per Exec 0.01 0.01 Per Call 0.00 0.00 Instance1：逻辑读和物理读较多，是以读为主 Instance2：物理写较多，是以写为主
如果我们有一个基线值，就好比较性能优略了 Per Second Per Transaction Per Exec Per Call DB Time(s): DB CPU(s): Redo size: Logical reads: Block changes: Physical reads: Physical writes: User calls: Parses: Hard parses: W/A MB processed: Logons: Executes: Rollbacks: Transactions: 0.8 0.4 102,788.5 4,287.6 436.4 100.5 40.6 261.7 108.9 0.1 0.9 3.1 263.1 0.0 8.9 0.1 0.1 11,594.5 483.6 49.2 11.3 4.6 29.5 12.3 0.0 0.1 0.4 29.7 0.0 0.00 0.00 0.00 0.00 业务类型不同关注数据指标也不同 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 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 Leonarding 2013.3.10 天津&spring 分享技术~成就梦想 Blog：www.leonarding.com 因篇幅问题不能全部显示，请点此查看更多更全内容