答案：通过AWR报告分析Top 5 Timed Events定位性能瓶颈，结合SQL Statistics识别高负载SQL，利用执行计划、索引优化与SQL重写提升效率，并根据资源使用趋势进行容量规划。
SQL性能优化，在Oracle里绝对是个绕不开的话题。用AWR报告，算是相对靠谱也比较常用的方法之一。它能帮你定位问题，但具体怎么用，可不是简单几步就能搞定的。
AWR报告的使用，目标是找出性能瓶颈，然后对症下药。
解决方案
-
生成AWR报告：
这个是基础，但也是关键。连接到你的Oracle数据库，用
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
脚本生成报告。它会问你开始和结束的snapshot ID，根据你的需求选择时间范围。记住，时间范围太短可能抓不到问题，太长信息又太杂。SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
报告会生成HTML或TEXT格式，看你喜欢哪个。HTML方便交互，TEXT方便搜索。
-
Top 5 Timed Events：
打开报告，直接找"Top 5 Timed Events"这个部分。这里列出了数据库花费时间最多的几个事件。例如，
CPU time
说明CPU是瓶颈，db file sequential read
说明IO有问题，log file sync
说明日志写入慢。- CPU time高： 检查SQL语句，看看有没有全表扫描，有没有可以优化的算法。
- db file sequential read高： 检查索引，看看是不是缺少索引，或者索引失效了。
- log file sync高： 检查日志文件的大小，看看是不是太小导致频繁切换。
-
SQL Statistics：
接下来，看"SQL Statistics"部分。这里列出了执行次数多、消耗资源多的SQL语句。重点关注"Elapsed Time"和"CPU Time"高的SQL。
- SQL ID： 找到有问题的SQL ID。
- Plan Hash Value： 记录下SQL的执行计划哈希值，后面会用到。
- Executions： 执行次数，如果执行次数很多，但是Elapsed Time也很高，说明这条SQL效率很低。
-
执行计划分析：
拿到SQL ID和Plan Hash Value后，用
DBMS_XPLAN.DISPLAY_AWR
查看SQL的执行计划。SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('YOUR_SQL_ID', 'YOUR_PLAN_HASH_VALUE'));执行计划会告诉你SQL是怎么执行的，有没有全表扫描，有没有用到索引，有没有表连接的问题。
-
索引优化：
根据执行计划，如果发现缺少索引，或者索引没有被用到，就要考虑创建或重建索引。
CREATE INDEX YOUR_INDEX ON YOUR_TABLE (YOUR_COLUMN);
记住，创建索引要谨慎，过多的索引会影响写入性能。
-
SQL重写：
有时候，SQL语句本身写得不好，导致性能很差。这时候就需要重写SQL。例如，避免使用
SELECT *
，尽量只选择需要的列；避免在WHERE子句中使用函数，尽量使用索引。-- 优化前 SELECT * FROM orders WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2023-10-26'; -- 优化后 SELECT * FROM orders WHERE order_date >= DATE '2023-10-26' AND order_date < DATE '2023-10-27';
-
SGA和PGA调整：
如果Top 5 Timed Events里面有和内存相关的事件，例如
direct path read
，说明SGA或PGA可能需要调整。SGA是系统全局区，PGA是程序全局区。-- 查看SGA大小 SHOW SGA; -- 查看PGA大小 SHOW PARAMETER pga_aggregate_target;
根据实际情况，调整
sga_target
和pga_aggregate_target
参数。
如何解读AWR报告中的等待事件？
AWR报告中最重要的部分之一就是等待事件（Wait Events）。它揭示了数据库在执行过程中，因为等待某些资源而消耗的时间。正确解读这些等待事件，是性能优化的关键。
-
理解等待事件的分类：
等待事件可以分为几大类，例如IO相关的、CPU相关的、锁相关的、网络相关的等等。每种类型的等待事件都指向不同的瓶颈。
-
IO相关的等待事件： 例如
db file sequential read
、db file scattered read
、direct path read
、direct path write
。这些等待事件通常表示IO子系统存在瓶颈，可能是磁盘速度慢，或者IO请求过多。 -
CPU相关的等待事件： 例如
CPU time
。虽然它不是严格意义上的等待事件，但它表示数据库消耗在CPU上的时间。如果CPU time很高，说明SQL语句的执行效率不高，需要优化SQL。 -
锁相关的等待事件： 例如
enq: TX - row lock contention
、enq: TM - contention
。这些等待事件表示存在锁冲突，多个会话在争夺同一资源。 -
网络相关的等待事件： 例如
SQL*Net more data to client
、SQL*Net message from client
。这些等待事件表示网络传输存在瓶颈，可能是网络带宽不足，或者网络延迟高。
-
IO相关的等待事件： 例如
-
关注Top N等待事件：
AWR报告会列出Top N等待事件，通常是Top 5或者Top 10。重点关注这些等待事件，因为它们消耗了数据库大部分的时间。
-
深入分析等待事件：
对于每个等待事件，需要深入分析其原因。
-
db file sequential read
： 通常表示需要读取单个数据块。可能是缺少索引，或者索引失效。检查SQL语句的执行计划，看看是否进行了全表扫描。 -
db file scattered read
： 通常表示需要读取多个数据块。可能是进行了全表扫描，或者进行了大量的表连接。检查SQL语句的执行计划，看看是否可以优化表连接的顺序，或者创建合适的索引。 -
direct path read
： 通常表示需要直接读取数据文件，绕过了SGA。可能是进行了大量的排序操作，或者使用了并行查询。检查SQL语句的执行计划，看看是否可以减少排序操作，或者调整并行查询的参数。 -
enq: TX - row lock contention
： 通常表示存在行锁冲突。检查SQL语句的事务隔离级别，看看是否可以降低隔离级别。检查SQL语句的更新逻辑，看看是否可以减少锁的持有时间。
-
-
结合其他信息：
解读等待事件，需要结合AWR报告中的其他信息，例如SQL Statistics、Instance Activity Statistics等等。通过综合分析，才能找到真正的瓶颈。
如何利用AWR报告识别高负载SQL语句？
AWR报告里找高负载SQL，其实是个体力活，但也需要一些技巧。
-
SQL Statistics部分：
这是最直接的地方。关注"Elapsed Time"、"CPU Time"、"Executions"、"Buffer Gets"这些指标。
- Elapsed Time： 总的执行时间，越高说明SQL越慢。
- CPU Time： 消耗的CPU时间，越高说明SQL的计算量越大。
- Executions： 执行次数，越高说明SQL被频繁执行。
- Buffer Gets： 从缓冲区读取的数据块数，越高说明SQL需要访问大量的数据。
把这些指标排序，例如按照Elapsed Time排序，就能找到最慢的SQL。按照Executions排序，就能找到执行次数最多的SQL。
-
SQL Ordered by Elapsed Time：
AWR报告通常会有一个"SQL Ordered by Elapsed Time"部分，直接列出了按照Elapsed Time排序的SQL语句。
-
SQL Ordered by CPU Time：
AWR报告通常会有一个"SQL Ordered by CPU Time"部分，直接列出了按照CPU Time排序的SQL语句。
-
SQL Ordered by Gets：
AWR报告通常会有一个"SQL Ordered by Gets"部分，直接列出了按照Buffer Gets排序的SQL语句。
-
SQL Plan Directives：
这个部分列出了SQL执行计划的指导信息。如果Oracle优化器认为SQL的执行计划有问题，会在这里给出提示。例如，可能会建议创建索引，或者修改SQL语句。
-
SQL Monitoring：
如果开启了SQL Monitoring功能，AWR报告会包含SQL Monitoring的信息。SQL Monitoring可以实时监控SQL语句的执行情况，包括执行时间、CPU时间、IO时间等等。
-
结合等待事件：
把高负载SQL和等待事件结合起来分析。例如，如果一条SQL的Elapsed Time很高，同时
db file sequential read
等待事件也很高，说明这条SQL可能存在IO瓶颈。 -
历史趋势分析：
不要只看一个时间段的AWR报告。对比不同时间段的AWR报告，看看高负载SQL是否一直存在，或者只是在某个时间段出现。
找到高负载SQL后，就要分析其执行计划，看看有没有可以优化的地方。可以尝试创建索引、重写SQL语句、调整参数等等。
如何使用AWR报告进行数据库容量规划？
AWR报告不仅仅用于性能诊断，还可以用于数据库容量规划，帮助你预测未来的资源需求，避免资源瓶颈。
-
监控资源使用率：
AWR报告会记录数据库的各种资源使用率，例如CPU使用率、IO使用率、内存使用率等等。
- CPU使用率： 关注"Host CPU Utilization"部分。如果CPU使用率长期处于高位，说明CPU可能存在瓶颈。
- IO使用率： 关注"Tablespace IO Stats"和"File IO Stats"部分。如果IO使用率长期处于高位，说明IO子系统可能存在瓶颈。
- 内存使用率： 关注"SGA"和"PGA"部分。如果SGA或PGA的使用率长期处于高位，说明内存可能存在瓶颈。
-
预测资源增长趋势：
收集一段时间的AWR报告，分析资源使用率的增长趋势。例如，如果CPU使用率每年增长10%，那么可以预测未来几年CPU的需求。
-
分析高负载SQL：
分析AWR报告中的高负载SQL，看看这些SQL消耗了多少资源。如果这些SQL的执行频率或数据量不断增长，那么可以预测未来这些SQL对资源的需求。
-
监控数据库增长：
监控数据库的大小，包括数据文件、日志文件等等。如果数据库的大小不断增长，那么可以预测未来磁盘空间的需求。
-
评估硬件性能：
评估当前硬件的性能，例如CPU的处理能力、磁盘的IOPS、内存的大小等等。如果硬件性能不足，那么需要升级硬件。
-
考虑业务增长：
考虑业务的增长情况。如果业务量不断增长，那么数据库的负载也会不断增加。需要根据业务增长情况，预测未来的资源需求。
-
制定容量规划方案：
根据以上分析，制定数据库容量规划方案。方案应该包括：
- 硬件升级计划： 例如，升级CPU、增加内存、更换磁盘等等。
- 数据库参数调整计划： 例如，调整SGA大小、PGA大小等等。
- SQL优化计划： 例如，创建索引、重写SQL语句等等。
- 数据库迁移计划： 如果当前硬件无法满足需求，可能需要将数据库迁移到更强大的硬件上。
记住，容量规划是一个持续的过程，需要定期评估和调整。
