如何优化一个看似正常的数据库（优化数据库的主要步骤）

通常DBA是不会太了解业务逻辑的，遇到系统中劣质的sql 一般也是以通过添加索引的方式来优化，但并不是所有的sql都能通过添加索引来优化

这就需要从sql的本身来做分析，另外还要了解什么样的语句会不走索引！本文通过几个简单的例子来介绍 一个看似正常的系统如何做优化!

• 1.wait event alert

近期一套MES系统 时不时的会报一个异常等待的报警

异常的等待事件是log file sync 这个等待事件虽然归类为commit 但是其实IO强相关的

• 2.通过OEM看数据库整体负载状态

从OEM上看系统的整体负载还是可以的，并无明显的负载波动，用户端也没有任何异常的反馈，那这样一个看似健康的库该如何下手优化？

• 3.通过AWR具体细致的分析异常

AWR报表是我们系统调优的必备工具，这个时候就需要拉一个业务高峰时段的AWR报表来仔细的排查了。

3.1 load profile

先看load profile 这里显示了系统的基本的负载信息

从负载看数据库的负载不高，着重关注以下几项

redo size :单位时间内的参数的日志的量

logical read/physical read：单位时间内逻辑读和物理读的量

hard parses：硬解析的次数（体现绑定变量的程度）

executes：单位时间执行sql的次数 直接反应数据库的负载

transactions ：单位时间内的事务数

3.2 foreground wait event

前台等待事件是awr的最关键信息，如果一个数据库有问题，基本都可以在这里找到端倪

这里可看到top wait 中有明显的几个异常等待

A.log file sync

The Oracle “log file sync” wait event is triggered when a user session issues a commit (or a rollback). The user session will signal or post the LGWR to write the log buffer to the redo log file. When the LGWR has finished writing, it will post the user session. The wait is entirely dependent on LGWR to write out the necessary redo blocks and send confirmation of its completion back to the user session. The wait time includes the writing of the log buffer and the post, and is sometimes called “commit latency”.

虽然log file sync等待归类为commit 但是其实是和IO 强相关

B.direct path read temp

当Oracle从TEMPORARY数据库文件读取到PGA内存(而不是缓冲区缓存)时，直接从“temp”读取路径。如果支持异步IO(并且正在使用)，那么Oracle可以提交IO请求并继续处理。然后，它可以稍后拾取IO请求的结果，并将等待“直接路径读取temp”，直到所需的IO完成。

如果异步IO没有被使用，那么IO请求会阻塞，直到完成，但这些在IO发出时不会显示为等待。会话稍后返回以获取已完成的IO数据，但随后会在“direct path read temp”上显示等待，尽管此等待将立即返回。

因此，这个等待事件非常具有误导性，因为:

等待的总数可能不能反映IO请求的数量

在“直接路径读取temp”中花费的总时间可能并不总是反映真实的等待时间。

C.direct path read

当直接读到PGA内存(而不是读到缓冲缓存)时，Oracle通常使用直接路径读取。如果支持异步IO(并且正在使用)，那么Oracle可以提交IO请求并继续处理。然后，它可以稍后获取IO请求的结果，并等待“直接路径读取”，直到所需的IO完成。

如果异步IO没有被使用，那么IO请求会阻塞，直到完成，但这些在IO发出时不会显示为等待。会话稍后返回以获取已完成的IO数据，但随后会显示“直接路径读取”的等待，尽管此等待将立即返回。

因此，这个等待事件非常具有误导性，因为:

等待的总数并不反映IO请求的数量

在“直接路径读取”中花费的总时间并不总是反映真实的等待时间。

这种类型的读请求通常用于:

1.排序IO(当排序不适合内存时)

2.并行查询

3.全表扫描

4.大数据量插入 比如使用INSERT INTO ... SELECT ... 或者 CREATE TABLE AS SELECT ...

5.使用expdp做数据导入时

6.归档日志传输（Archived Redo Log Transfer）：在Oracle数据库的备份和恢复过程中，归档日志的传输通常涉及大量数据的读取和写入操作。

这三个都和IO 相关，在OLTP系统中出现这2,3等待基本可以判定是因为sql中有大量的全表扫描引起的

另外从 top 10的 wait event来看，如果一个OLTP系统的db cpu 不是排在第一位，而且占比不能达到80%以上， 就可以判定这个系统是不健康的，需要做系统的调优！

再查询对应时段的top event和对应的sql_id

select *
from (select count(*), event, sql_id, program, inst_id
from gv$active_session_history
where
–to_char(sample_time,‘hh24’) BETWEEN ‘11’ AND ‘14’
–AND to_char(sample_time,‘yyyymmdd’) = ‘20240218’ --也可以按具体时间查询
sample_time > sysdate -1
group by event, sql_id, program, inst_id
order by 1 desc)
where rownum < 21;

也能发现有不少 direct path read的等待事件

具体的优化过程，针对明显有问题的sql逐一优化

SQL1

根据sql id f9pp4j38yxfwk查一下sqlmonitor

Sql_monitor是oracle11g后引入了一个调优工具包,满足如下任一个条件才会被监控到

1. 如果sql执行消耗超过5秒 cpu or IO time则会被记录到v$sql_monitor
2. 加了/*+ monitor*/ hint

SELECT DBMS_SQLTUNE.report_sql_monitor(sql_id => ‘&sql_id’, type => ‘TEXT’) AS report FROM dual;

可以看到执行时间在8-9秒左右

问题1：执行计划为全表扫描

问题2：sql没有绑定变量

可以看到 table access full 的等待事件为 direct path read

这种sql相对比较容易优化， 首先看一下检索项的数据分布，从数据的分布来看如果is_processed =0,则筛选度还是不错的，再看一下数据库类似的查询

参数基本都是is_processed =0 and type = x ，

如果查询大部分为is_processed =1 则我认为没有必要加联合索引，因为筛选度不高，

一般认为如果一个查询的结果集达到表的全部数据的5-10% 则优化器会优先选择使用全表扫描，无论是否有索引

CREATE INDEX EAP.EAP_RT_TRACK_OUT_INX2
ON EAP.EAP_RT_TRACK_OUT(IS_PROCESSED,TYPE) ONLINE PARALLEL 8 NOLOGGING;
参数解释
 online ：减少lock的概率
 parallel ：设置并发，加快索引的创建速度
 nologging：减少log的量（并不是完全没有log）

再次看执行计划 已经走了新建的索引

执行时间也由原来的8-9秒缩短为0.2秒左右，如果绑定了变量执行时间会更快

SQL2

很明显这个是OEM执行的数据库audit相关的sql，aud$表是没有索引的

这个处理就比较简单直接关闭数据库的audit，并清空aud$表

SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/btmesdb/
adump
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
/*audit_trail 参数介绍
设置为`none`以禁用审计功能。
设置为`os`以启用审计功能并将其记录写入操作系统文件。
设置为`db`或`true`以启用审计功能，并将审计记录写入数据库的`SYS.AUD
表。
设置为`db, extended`以启用审计功能，并在`SYS.AUD
表的基础上填充额外的列如`SQLBIND`和`SQLTEXT`。
设置为`xml`以启用审计功能，并将所有记录写入XML格式的文件。
设置为`xml, extended`以启用审计功能，并输出审计记录的所有列，包括`SQLBIND`和`SQLTEXT`的值。
*/
SQL> alter system set audit_trail = ‘none’ scope=spfile;
System altered.
SQL> alter system set audit_trail = ‘none’ scope=spfile sid=’*’;
System altered.
SQL>truncate table sys.aud$;

然后找合适的窗口重启数据库，使修改的参数生效

SQL3

看到top 10有几个sql都是类似的只有个别参数不同（没有做绑定变量）

选择一个sql单独拉出来看看

具体sql如下，下面看看在不了解业务情况下，仅仅从sql的角度如何来优化

select *
  from (SELECT round(t2.MAIN_QTY - t1.MAIN_QTY, 2) MAIN_QTY
          FROM (SELECT ROUND(nvl(sum(MAIN_QTY), 0) / 1000, 2) MAIN_QTY
                  FROM (SELECT sum(t.MAIN_QTY) MAIN_QTY, t.TRANS_TIME CREATED
                          FROM LJ_MAINTENANCE_PLAN t
                         WHERE t.STEP_NAME = 'LJ'
                           AND t.OUTPUT_SORT_NAME = 'JHCCYB'
                           and WORK_SHIP = '1'
                         GROUP BY t.TRANS_TIME)
                 WHERE to_char(CREATED, 'yyyy-mm-dd') = '2024-02-14') t1,
               (SELECT round(MAIN_QTY / 1000, 2) MAIN_QTY
                  FROM (SELECT t2.TRANS_TIME CREATED,
                               sum(decode(attributeValue.Attribute_Name,
                                          'HX_DislocationFreeLength',
                                          attributeValue.Attribute_Value)) /
                               t3.SPEC3 MAIN_QTY
                          FROM AD_ATTRIBUTE_VALUE attributeValue,
                               (SELECT TRANS_TIME,
                                       t.LOT_RRN,
                                       t.PART_NAME,
                                       row_number() over(partition by t.LOT_RRN order by TRANS_TIME DESC) rn
                                  FROM WIP_LOT_HIS t, WIP_WO ww
                                 WHERE t.TRANS_TYPE = 'TRACKIN'
                                   AND t.STEP_NAME = 'STEP_HX'
                                   AND ww.WORKSHIP = '1') t2,
                               MM_MATERIAL t3
                         WHERE attributeValue.TARGET_OBJECT_RRN = t2.LOT_RRN
                           AND t2.PART_NAME = t3.name
                           AND t2.rn = 1
                           AND STATUS = 'Active'
                         GROUP BY t2.TRANS_TIME, t3.SPEC3)
                 WHERE to_char(CREATED, 'yyyy-mm-dd') = '2024-02-14') t2)
 where rownum <= :1

老样子 想拉sql monitor看一下执行情况和执行计划

一步步来解决 尽可能把full table scan的优化掉

LJ_MAINTENANCE_PLAN 看一下检索的三个条件和对应的数据分布

查询对应的数据分布

虽然数据量不大，但是三个条件在一起筛选度 还是可以的 ， 很少量的数据落在三个参数都非空的位置上

而参数的数据都集中在这里，加上联合索引后看执行计划 ，有了明显改善

在仔细观察这个sql 可以看出 筛选度最高的条件应该是最外围的

WHERE to_char(CREATED, 'yyyy-mm-dd') = '2024-02-14')

表连接的几个查询只有几个简单的筛选条件，导致查询的为几个大表的 hash join，cost非常大 如下图的执行计划

查看created 是来之t2.trans_time,如果将查询拿到子查询内 试试效果

对比执行计划 cost有100倍的减少

对比了原sql和 将条件拿到内部后 结果集一致的，执行时间由130秒缩短为3秒 ，但是因为有个row_number函数，条件入内后 查询并不完全等价，如果查询的时间范围变大，结果集会有差异。

但是优化的原则是没有变的，筛选度高的条件尽可能放在内层，使用最少的结果集和其他表做连接，可以大大减少sql的io量和执行时间，优化后执行计划还是全表扫描，感觉是少了关键筛选条件！因为涉及到需要修改代码，这部分只能邮件通知开发，来进行修改，并督促开发尽可能绑定变量，以减少硬解析，提高效率！

后记：数据库的优化 我个人总结的路径如下

以监控为引子（异常等待alert，alertlog等），通过OEM 监控平台总览数据库的状态，这时如果有明显的异常OEM 可以轻松看出，再通过AWR,ADDM,ASH等报表做更细致的分析，sql优化不改动代码优化最佳（加索引），明显的sql代码问题，提出修改意见给开发部门。

-------------------------------------------------

墨天伦，CSDN，头条，公众号：潇湘秦 转载请注明出处

-------------------------------------------------