如何优化一个看似正常的数据库

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

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

1.wait event alert

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

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

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

从OEM上看系统的整体负载还是可以的，并无明显的负载波动

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

这个时候就需要拉一下系统的awr报表来看看了

AWR 需要怎么来看？

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

从负载看数据库的负载不高

最重要就是foreground wait event，如果一个数据库有问题，基本都可以在这里找到端倪

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

1.log file sync

2.direct path read temp

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

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

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

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

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

3.direct path read

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

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

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

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

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

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

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

并行查询从机

全表扫描

预读(一个进程可能会对它预计在不久的将来需要的块发出IO请求)

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

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

查询最近一天的top event和对应的sqlid

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的等待事件

具体的优化过程

SQL1

根据sql id f9pp4j38yxfwk查一下sqlmonitor

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

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

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

可以看到执行时间在8-9秒左右，执行计划位全表扫描，sql没有绑定变量

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

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

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

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

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

$D:\wxdata\WeChat Files\xiaofan23z\FileStorage\Temp\1708493547417.png$

CREATE INDEX EAP.EAP_RT_TRACK_OUT_INX2

ON EAP.EAP_RT_TRACK_OUT(IS_PROCESSED,TYPE) ONLINE PARALLEL 8 NOLOGGING;

参数解释

noline ：减少lock的概率

parallel ：设置并发，加快索引的创建速度

nologging：减少 log的量（并不是完全没有log）

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

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

SQL2

很明显这个是OEM执行的数据库audit相关

这个处理比较简单直接关闭数据库的audit

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>

找合适的窗口重启数据库

SQL3

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

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

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

select *from (SELECT round(t2.MAIN_QTY - t1.MAIN_QTY, 2) MAIN_QTYFROM (SELECT ROUND(nvl(sum(MAIN_QTY), 0) / 1000, 2) MAIN_QTYFROM (SELECT sum(t.MAIN_QTY) MAIN_QTY, t.TRANS_TIME CREATEDFROM LJ_MAINTENANCE_PLAN tWHERE 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_QTYFROM (SELECT t2.TRANS_TIME CREATED,sum(decode(attributeValue.Attribute_Name,'HX_DislocationFreeLength',attributeValue.Attribute_Value)) /t3.SPEC3 MAIN_QTYFROM 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) rnFROM WIP_LOT_HIS t, WIP_WO wwWHERE t.TRANS_TYPE = 'TRACKIN'AND t.STEP_NAME = 'STEP_HX'AND ww.WORKSHIP = '1') t2,MM_MATERIAL t3WHERE attributeValue.TARGET_OBJECT_RRN = t2.LOT_RRNAND t2.PART_NAME = t3.nameAND t2.rn = 1AND 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看一下执行情况和执行计划