AWR报告分析之TOP SQL(转载)

本文章向大家介绍AWR报告分析之TOP SQL，主要内容包括SQL ordered by Elapsed Time、SQL ordered by Physical Reads (UnOptimized)、SQL ordered by Version Count、基本概念、基础应用、原理机制和需要注意的事项等，并结合实例形式分析了其使用技巧，希望通过本文能帮助到大家理解应用这部分内容。

编者按：

本文作者系Walt，关注SQL开发，Oracle、MySQL、PostgreSQL、TiDB等数据库，AWS、Azure、OCI等公有云计算架构和技术。

个人主页: https://blog.csdn.net/lukeUnique。

SQL专栏课程：https://www.modb.pro/course/125

编辑|SQL和数据库技术(ID:SQLplusDB)

在AWR报告中，有一个称为【SQL Statistics】的部分，会输出在AWS报告期间内根据各种条件进行排序的TOP SQL，各TOP SQL详细如下。 （相关内容会保存在AWR的DBA_HIST_SQLSTAT表中。）

SQL ordered by Elapsed Time

根据SQL执行时间（Elapsed Time）排序的TOP SQL。

Elapsed Time(S): SQL语句执行的总时间（单位秒）。
                 注意：Elapsed Time = CPU Time + Wait Time
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
Elap per Exec(s): SQL一次执行的平均时间（单位秒）。=Elapsed Time/Executions
% Total        : SQL的Elapsed Time时间占数据库总时间的百分比。=  Elapsed time / DB Time * 100(%)
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by CPU Time

根据SQL消耗CPU时间排序的TOP SQL。

CPU Time (s)：SQL语句执行消耗的CPU总时间（单位秒）。
                    当CPU Time和Elapsed Time相近时，表示时间主要消耗在CPU上。
                    当CPU Time和Elapsed Time差值较大时，表示时间大量消耗在等待上，这是我们要关注等待事件。
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
CPU per Exec (s)：SQL一次执行的平均CPU时间（单位秒）。=CPU Time /Executions
% Total        : SQL的CPU Time 时间占数据库总CPU 时间的百分比。
Elapsed Time(S): SQL语句执行的总时间（单位秒）。
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by User I/O Wait Time

根据SQL消耗I/O等待时间排序的TOP SQL。

User I/O Time (s)：SQL语句执行消耗的I/O总时间（单位秒）。
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
UIO per Exec (s)：SQL一次执行的平均I/O时间（单位秒）。=User I/O Time /Executions
% Total        : SQL的User I/O Time时间占数据库总User I/O 时间的百分比。
Elapsed Time(S): SQL语句执行的总时间（单位秒）。
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Gets

根据SQL缓存读块数排序的TOP SQL

Buffer Gets   ：SQL访问的缓存数据块数（逻辑IO）
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
Gets per Exec：SQL一次执行的平均访问的缓存数据块数。=Buffer Gets /Executions
% Total        : SQL的Buffer Gets  占总Buffer Gets 的百分比。
Elapsed Time(S): SQL语句执行的总时间（单位秒）。
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Reads

根据SQL物理读块数排序的TOP SQL。

Physical Reads  ：SQL物理读的数据块数（物理IO）
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
Reads per Exec：SQL一次执行的物理读数据块数。=Physical Reads/Executions
% Total        : SQL的Physical Reads占总Physical Reads的百分比。
Elapsed Time(S): SQL语句执行的总时间（单位秒）。
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Physical Reads (UnOptimized)

根据SQL非优化的物理读次数排序的TOP SQL。

我们可以看到AWR报告中第一行的内容UnOptimized Read Reqs = Physical Read Reqs – (Optimized Read Reqs – Cell Flash Cache Read Hits for Controlfile)。 所谓的非优化的物理读（Optimized Read Reqs）是通过Database Smart Flash Cache 或者Exadata Smart Flash Cache功能进行的物理读。

智能闪存（ Smart Flash Cache）功能主要是将SSD硬盘当做内存（Buffer cache）使用的功能，用于减少I/O。通过这个当内存不足的时候，设定的Smart Flash Cache可以当做二级缓存用于缓存数据块。

要使用这个智能闪存功能需要设置如下参数：

db_flash_cache_file ：智能闪存的位置
db_flash_cache_size ：智能闪存的大小

另外，当未启用智能闪存（Database Smart Flash Cache）时，我们可以认为所有的物理读都是非优化的。

UnOptimized Read Reqs ：SQL非优化的物理读的次数
Physical Read Reqs  ：SQL物理读的次数
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
UnOptimized Reqs per Exec：SQL一次执行的非优化的物理读次数。=UnOptimized Read Reqs/Executions
%Opt        : 优化的物理读次数和物理读总次数的百分比。
% Total        : 非优化的物理读的次数占总P非优化的物理读的次数的百分比。
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Executions

根据SQL执行次数排序的TOP SQL。

Executions      : SQL执行总次数。
Rows Processed：SQL处理的总行数。
Rows per Exec ：SQL一次执行的平均处理行数。=Rows Processed /Executions
Elapsed Time(S): SQL语句执行的总时间（单位秒）。
%CPU          : CPU时间和Elapsed Time时间的百分比。= CPU Time /Elapsed Time * 100(%)
%IO            : IO 时间和Elapsed Time时间的百分比。= User IO Time /Elapsed Time * 100(%)
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Parse Calls

根据SQL解析次数排序的TOP SQL。

Parse Calls     ：SQL的解析次数。如果Parse Calls接近 Executions 表示SQL每次执行都需要解析，考虑游标共享。
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
% Total Parses：SQL的解析次数占总解析次数的百分比。
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

SQL ordered by Sharable Memory

根据SQL使用的共享内存排序的TOP SQL。

Sharable Mem (b) ：SQL占用的内存大小（单位byte）。
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
% Total       ：SQL的占用的内存占总内存的百分比。
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

当发生大量内存使用导致内存不足、ORA-4031等错误时，可以关注这部分的SQL。

SQL ordered by Version Count

根据SQL版本（version count）排序的TOP SQL。

version count ：同一个父游标下子游标的数量。
Executions      : SQL执行总次数，如果Executions =0表示在awr报告时间内未执行完。
SQL ID         : SQL语句的唯一标识。
SQL Module : 调用SQL的程序模块。
SQL Text     ：SQL 文本内容的一部分，完整内容需要查看【Complete List of SQL Text】。

Version Count主要用于描述同一个父游标下子游标的数量。

对于一条SQL语句而言，会同时存在一个父游标和至少一个子游标。父游标主要依赖于SQL文本内容（必须完全一致，即空格、大小写、注释等不同都会被认为是不同的SQL），不同用户会话的执行SQL语句只要文本内容相同的，就会共享同一个父游标。对于子游标而言，即使执行相同的SQL语句，也可能因为执行用户的不同，或者访问的对象有所不同而不同。另外，由于优化器设置、绑定变量值等情况，也可能导致同一个SQL存在多个子游标。