Oracle AWR报告的分析

原创 eygle 2019-12-12

2902

AWR报告和Statspack的报告极为类似，不过Oracle 10g增加了很多新的内容，我们对新的内容进行一点简单介绍。

首先报告在Top 5 Timed Events后面增加了时间模型部分，根据对Statspack的分析可以知道，实际上等待时间也是依据时间模型来建立的，在Oracle 10g中，时间模型被独立出来并进一步细化：

Time Model Statistics             DB/Inst: SMSBOSS/smsboss  Snaps: 10735-10756
-> Total time in database user-calls (DB Time): 24514.6s
-> Statistics including the word "background" measure background process
   time, and so do not contribute to the DB time statistic
-> Ordered by % or DB time desc, Statistic name

Statistic Name                                       Time (s) % of DB Time
------------------------------------------ ------------------ ------------
sql execute elapsed time                             24,813.6        101.2
DB CPU                                               12,885.3         52.6
PL/SQL execution elapsed time                           362.3          1.5
parse time elapsed                                       61.0           .2
connection management call elapsed time                  38.9           .2
hard parse elapsed time                                  19.9           .1
hard parse (sharing criteria) elapsed time                4.3           .0
sequence load elapsed time                                2.0           .0
PL/SQL compilation elapsed time                           1.2           .0
failed parse elapsed time                                 0.2           .0
repeated bind elapsed time                                0.1           .0
hard parse (bind mismatch) elapsed time                   0.1           .0
DB time                                              24,514.6          N/A
background elapsed time                               3,050.2          N/A
background cpu time                                     664.4          N/A
          -------------------------------------------------------------
复制

等待事件通过分类之后，可以被快速汇总，从而显示数据库瓶颈消耗在哪一类资源上：

Wait Class                        DB/Inst: SMSBOSS/smsboss  Snaps: 10735-10756
-> s  - second
-> cs - centisecond -     100th of a second
-> ms - millisecond -    1000th of a second
-> us - microsecond - 1000000th of a second
-> ordered by wait time desc, waits desc

                                                                  Avg
                                       %Time       Total Wait    wait     Waits
Wait Class                      Waits  -outs         Time (s)    (ms)      /txn
-------------------- ---------------- ------ ---------------- ------- ---------
User I/O                    2,435,546     .0            6,996       3       1.4
System I/O                    786,339     .0            2,398       3       0.4
Configuration                   1,800   37.3              217     121       0.0
Commit                         13,264     .0              124       9       0.0
Network                       828,998     .0               80       0       0.5
Concurrency                     1,092     .0               74      68       0.0
Other                          85,958     .0               10       0       0.0
Application                     8,484     .0                9       1       0.0
          -------------------------------------------------------------
复制

对于这个数据库，显然主要的等待都消耗在IO上，那么调整SQL、优化IO实际上应该是优化这个系统的主要目标。

更进一步地，Oracle将操作系统的统计信息也收集计算进来，这部分信息非常重要（以前的Statspack中是不包含这部分信息的），包括了CPU的繁忙程度、IO等待情况、内存总量、CPU数量等信息，这些信息对于评价数据库的并发性能、事务处理能力等都非常重要：

Operating System Statistics       DB/Inst: SMSBOSS/smsboss  Snaps: 10735-10756

Statistic                                       Total
-------------------------------- --------------------
AVG_BUSY_TIME                                 627,108
AVG_IDLE_TIME                               6,927,000
AVG_IOWAIT_TIME                               291,066
AVG_SYS_TIME                                  155,566
AVG_USER_TIME                                 470,269
BUSY_TIME                                   1,256,735
IDLE_TIME                                  13,856,519
IOWAIT_TIME                                   584,638
SYS_TIME                                      313,671
USER_TIME                                     943,064
LOAD                                                0
OS_CPU_WAIT_TIME                              106,200
RSRC_MGR_CPU_WAIT_TIME                              0
VM_IN_BYTES                                    57,344
VM_OUT_BYTES                                        0
PHYSICAL_MEMORY_BYTES                   4,165,320,704
NUM_CPUS                                            2
          -------------------------------------------------------------
复制

在AWR报告的SQL部分，则增加了SQL ordered by Elapsed Time和SQL ordered by CPU Time部分，通过这两部分内容，Oracle将最耗时的SQL抓取了出来，那么现在AWR对SQL的采样已经包括了以下内容。

SQL ordered by CPU Time
SQL ordered by Elapsed Time
SQL ordered by Executions
SQL ordered by Gets
SQL ordered by Parse Calls
SQL ordered by Reads
SQL ordered by Sharable Memory
SQL ordered by Version Count

可以说在Oracle 10g中，Oracle对SQL的采样已经相当完备了。从Oracle 11gR2开始，在SQL采样部分还包括了SQL ordered by User I/O Wait Time和SQL ordered by Physical Reads (UnOptimized)部分，对I/O资源的使用做出了进一步的评估，从SQL ordered by User I/O Wait Time部分我们可以清晰的看到哪些SQL消耗了更高的I/O等待，对于协助诊断分析数据库具有极大的参考意义。

下图是这一新增量度的范例：