Oracle教程（4）快照与AWR报告

一、Oracle 快照

Oracle中的快照（Snapshot）是指数据库在某个时间点对性能相关的数据做的一次全量采集。包括：系统资源使用情况、Top SQL、IO 性能指标、SGA、PGA 使用情况。快照是生成AWR报告的前提，如果没有快照也就无法生成AWR报告。快照功能是 Oracle 默认开启的，按照每 60 分钟自动打一个快照并且保留 8 天的策略进行运作。

1、查看快照

通过下面的SQL查看当前数据库中是否有快照

SELECT SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME FROM   DBA_HIST_SNAPSHOT ORDER BY SNAP_ID DESC;

2、查看快照策略

SELECT * FROM dba_hist_wr_control;

snapshot_interval字段：代表快照采集频率，默认1个小时
retention字段：代表快照保留天数，默认为8天
topnsql字段：代表需要捕获的SQL类型，默认为全部类型

3、手动配置快照

· 手动产生快照

该操作通常是需要立即进行性能问题分析时进行，精准锁定问题时间点，避免被整点快照的均值掩盖问题

#。该语句在图形化下无法直接执行
SQL > EXEC dbms_workload_repository.create_snapshot;


#图形化下执行方式
BEGIN
  dbms_workload_repository.create_snapshot;
END;

· 调整采集间隔

如调整为每30分钟采集一次

EXEC dbms_workload_repository.modify_snapshot_settings(
    interval => 30, 
    retention => 8*24*60
);

· 调整快照保留天数

如调整为保留最长30天的历史快照

EXEC dbms_workload_repository.modify_snapshot_settings(
    retention => 30*24*60
);

二、Oracle AWR 报告

AWR 报告是基于两个快照之间数据库行为的对比分析，是在进行Oracle性能分析时最核心的诊断报告。

1、如何生成AWR报告

Oracle内置了SQL脚本用于创建AWR报告，脚本都放置于$ORACLE_HOME/rdbms/admin/目录中，主要使用到以下几个脚本

awrrpt.sql： 单实例 AWR 报告

awrgrpt.sql：RAC 全局报告

awrgrpti.sql：RAC 全局报告（指定实例号）

awrsqrpt.sql：单条 SQL 的 AWR 报告

awrsqrpti.sql：RAC 单条 SQL 报告

要调用脚本的话只需要按照下面的格式指定SQL脚本即可，@代表调用脚本，？代表的是$ORACLE_HOME

@?/rdbms/admin/awrsqrpt.sql

执行脚本后会提示生成报告的类型，比如html或者text

SQL> @?/rdbms/admin/awrrpt.sql

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats.	Please enter the
name of the format at the prompt.  Default value is 'html'.

'html'		HTML format (default)
'text'		Text format
'active-html'	Includes Performance Hub active report

Enter value for report_type: html

确认格式后会输出数据库实例相关信息，在这一步需要输入需要列出的快照天数，直接回车默认就是所有快照

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  DB Id      Inst Num	DB Name      Instance	  Host
------------ ---------- ---------    ----------   ------
* 1743450041	 1	ORCL	     orcl	  centos1

Using 1743450041 for database Id
Using	       1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days:

输入快照ID选择快照区间

输入报告名字，通常回车默认即可，然后会在当前目录创建一个报告文件

[oracle@centos1 admin]$ ll *html
-rw-r--r-- 1 oracle oinstall 656756 Nov 10 13:52 awrrpt_1_21_22.html

2、如何分析AWR报告

打开HTML格式的报告后，可以看到报告分成了很多部分

· 实例摘要信息

主要显示了实例整体负载情况，关注

DB Time：是否明显高于报告区间的实际时长，例如报告区间只有 10 分钟，但 DB Time = 1300 分钟，说明系统有大量等待

Logical Reads（逻辑读）：是否突然暴涨，通常反映 top SQL 扫描过大表、索引失效

Hard Parse（硬解析）： 是否异常突出，通常大于30%都可以判定存在绑定变量无效、执行计划频繁重算

Executions / s：每秒执行的SQL数，如果过高说明应用存在循环调用问题

READ IO（MB）：读写IO是否过高

· Instance Efficiency Percentages

实例命中率信息，主要关注：

Library Hit%：SQL 在 Shared Pool 里的命中率，正常期望 ≥ 95%。偏低意味SQL 没有复用，硬解析多或者shared pool 太小

Soft Parse%：软解析比例，通常和 Library hit% 相关。正常期望 ≥ 95% 。偏低说明应用端没有绑定变量、频繁 hard parse

Execute to Parse %：该指标越高表示解析比越低。较低的话说明 SQL 解析量大，属于性能隐患

· Top Foreground Events

重要的诊断入口，Oracle 会把所有前台会话的等待事件按总等待时间和占比排序，第一名通常就是整个系统的主要性能瓶颈来源。细分为以下指标：

DB CPU：数据库使用 CPU 的时间，其中 Total Wait Time 就是采样期间CPU工作总时长，%DB time代表CPU运算比例。如果二者都很高，代表系统主要瓶颈在 SQL的计算上，可能存在全表扫描、复杂函数计算、大量排序等情况

db file sequential read：数据库单块读等待时长，顺序读主要是索引扫描、RowID 回表产生。一般 <1ms 是正常，>3 ms属于磁盘性能偏慢或 IO 负载重 

TX - row lock contention：还未提交事务的行锁竞争，如图出现了57 次wait，但耗时 955 秒，平均等待 16.75s，属于非常糟糕的情况，代表可能有会话长时间占着一行没提交、UPDATE/DELETE 的 where 条件太宽等情况

direct path read：绕过 buffer cache 直接读磁盘的等待时间，通常是全表扫描、大型排序产生

db file scattered read：数据库多块读的等待时长，主要是全表扫描、Index Fast Full Scan产生。通常情况下多块读应该比单块读更快。如果相反的话，说明磁盘吞吐不足、SQL 的全表扫描过多

log file sync：事务提交时等待写redo的时长，正常应该 <1ms，如果过高代表 LGWR 写慢、redo log 太小导致频繁切换、IO 写延迟偏高

log file switch (checkpoint incomplete)：日志切换时 checkpoint 没来得及完成，常见原因有redo log 太小、I/O 慢、归档慢（尤其备库延迟、网络不良），如果数据库有 Data Guard，这个等待值得关注

db file parallel read：多个文件同时读取，一般是 rollback segment 或物化视图刷新，延迟 6.42ms 偏高一点但不关键。

latch: shared pool：shared pool latch 竞争，通常是频繁硬解析、SQL 未绑定变量、shared pool 太小，这里只占 0.1%，可以忽略

PGA memory operation：PGA 分配/释放的等待时长，一般不是问题

· Wait Classes by Total Wait Time

等待大类总览，将数据库等待时间按种类分布统计。主要包含以下内容：

DB CPU：数据库执行 SQL 的 CPU 时间，占 54.6% DB time，说明系统 CPU 成本较高

Other：杂项，包含PGA Memory，Streams，Scheduler 等。占比 28.4% 非常高，多半是SQL 在做大量排序、HASH JOIN 、大量 direct path read/write ，也能对应上面的等待事件

User I/O：说明大量等待来自db file sequential read（索引回表）、scattered read（全表扫描）、direct path read（大表扫描），大于 20%说明 SQL的 I/O 成本很高。需要关注后面的 SQL Ordered by Reads 部分

Application：呼应前面的TX - row lock contention，锁等待次数不多，但耗时极高，达到 956 秒，需要追查锁来源（SQL Ordered by Elapsed 或 by Gets）

· Host CPU、Instance CPU、IO、MEMORY

这几项分别是操作系统层 CPU、数据库计算层CPU、磁盘IO、内存资源消耗等信息

在报告中，idle 82.5%说明操作系统CPU非常空闲。而IO Profile中的信息比较关键，可以判断数据库是否将磁盘IO打满，重点关注项：

Total Requests per second：一般 OLTP 小于 200 次/s 都不算高

Database (MB/s)：IO 带宽压力，通常 100MB/s 以上才算明显压力

Direct IO：关键指标，如果数值较高表示有大量 SQL 直接读取了磁盘，而没有通过 buffer cache。比如大表全表扫描、并行事务、大量排序等

Host Mem (MB)、SGA use (MB):、PGA use (MB)：服务器内存情况、SGA和PGA分配情况

· SQL Statistics

SQL统计是AWR Main Report（主报告）中的灵魂部分，可以分析到底是什么SQL占用了CPU资源、IO资源等情况，典型分析路径是先看 Elapsed Time TOP1  → 再看它的 Gets / Reads / Plan → 再判断是不是锁、IO、执行计划问题

SQL Ordered by Elapsed Time

耗时最长的SQL，通常是第一嫌疑。以下面报告为例进行分析：

TQMTC00 表相关的 DELETE FROM TQMTC00 WHERE ... + SELECT * FROM TQMTC00 WHERE... 属于热点操作，并且来自同一个模块eb_qmtc10_zbs_batch@mesrunap01，判断是一个MES系统。在快照窗口期执行次数达到了3400多次，意味着应用每秒都在访问该表，并且SELECT 操作占用 CPU=99.53%表示大概率是在全表扫描。另外 DELETE + SELECT 同时发生，很容易导致行锁竞争（对应了前面 AWR 的 TX 锁等待）

SQL Ordered by Gets

按逻辑读排序，逻辑读最多的SQL往往是性能杀手。逻辑读是 SQL 执行过程中从数据库Buffer Cache中读取的数据块次数。一般 正常的 OLTP SQL 每次 Gets 不应超过几万，如果高 Buffer Gets + 高 Elapsed Time表示 SQL 每次执行都访问大量数据块。可能有全表扫描、索引缺失或无效、多表连接等情况。在下面的报告中可以看到cy0wt2fhxw7xg所对应的select distinct destination from …就是高频SQL。Gets per Exec: 25,406、Executions: 7,523、%CPU: 98.8%表示执行 7,523 次，每次逻辑读 2.5 万块，几乎无 I/O 等待，属于CPU 密集型

SQL Ordered by Physical Reads

这部分展示了哪些SQL真正产生了物理磁盘读（即没有命中Buffer Cache的读操作），是定位I/O瓶颈的关键依据。在下面的报告中可以看到ggqkdzqca37wv – SELECT t.REC_CREATOR ... 单次执行触发 105287 次物理读，表示SQL可能访问了一个从未使用过的大表或分区。若是业务SQL，建议检查执行计划是否使用了索引，若是批量报表，应考虑离线生成或分区并行。

SQL Ordered by Executions

高频 SQL，关注这里的SQL是否效率低

SQL Ordered by CPU Time

最占用CPU资源的SQL

· IO Stats

统计了实例在 AWR 报告时间段内，不同功能模块产生的 物理 I/O 活动量（包括 RMAN、Buffer Cache、Direct I/O、LGWR、DBWR 等）。可以看出是谁在占用磁盘资源、读多还是写多、平均速度等信息。在下面的报告中可以看到AWR报告期间，RMAN 操作的 I/O读有185.7G、写 31.6G，平均每次等待仅 12.7 µs，表示RMAN正在运行，I/O量巨大但响应极快。而Direct Reads 是绕过Buffer Cache直接读取硬盘，达到了173.8G ，通常出现在并行查询、大表扫描、排序操作。Buffer Cache Reads通过缓冲区缓存的读操作读 24.5G，等待 5.9 ms典型的业务读路径，平均等待 5.9 毫秒说明磁盘或cache命中率一般，略有I/O延迟。

数据库oracle