数据库系统查询优化，数据库优化步骤

摘要：近期，我们生产系统的功能管理模块遇到了一个问题，输入查询条件到显示查询结果页面需要20分钟的时间，对现场工作效率造成了较大影响。不过之前并没有什么异常，最近也没有做过什么大的手术。以下是故障排除步骤：

1. 查看当前等待事件的故障排除结果。等待事件没有明显异常，等待时间也不长。

SELECT inst_id,wait_class,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) \’Prev\’, SUM(DECODE(WAIT_TIME, 0, 1, 0)) \’Curr\’, COUNT(*) \’Tot\’ , 状态， sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT FROM GV$SESSION_WAITWHERE 事件NOTIN (\’smon 计时器\’,\’pmon 计时器\’,\’rdbms ipc 消息\’,\’来自客户端的SQL*Net 消息\’,\’gcs 远程消息\’) AND 事件不喜欢\’ % idle%\’ AND event NOT LIKE \’%Idle%\’ AND event NOT LIKE \’%Streams AQ%\’ GROUP BY inst_id,wait_class,EVENT,stateORDER BY 1,6 desc 注意： 1) 如果状态值为Waiting，则Second_in_wait 值为实际等待时间（以秒为单位）。如果状态值为等待已知时间，则wait_time 值为实际等待时间。 Prev 代表最后一次等待计数，Curr 代表当前等待计数。

2. 查看对长时间运行的SQL 进行故障排除的结果。这些都是正常运行的SQL和任务。

— 实时监控执行时间超过10秒的SQL语句SELECT \’kill -9 \’ || /*p.spid,p.pid,*/s.sid,s.sql_id,s .username、s.machine、s.sql_hash_value、s.last_call_et \’第二\’、s.last_call_et/60 \’running_time\’、s.client_info、p.program \’OSProgram\’、\’更改系统终止会话\’\’\’ || | \’, \’ || \’\’;\’ FROM v$session s, v$process p WHERE (s.username 不为NULL) (NVL(s.osuser , \’x\’) \’SYSTEM\’) AND(s.TYPE \’背景\’ )) AND (p.addr(+)=s.paddr) –and s.username in (\’CRMDB\’) and s.last_call_et 10/*and s.sql_hash_value=880766746*/ORDER BY s.last_call_et/60 desc , \’USERNAME\’ ASC, Ownerid, \’USERNAME\’ ASC;– 根据sid select username, sql_text 显示具体的SQL 语句。计算机，来自gv$ 会话a、gv$sqltext_with_newlines b 的osuser，其中DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value 和a.sid=sid 按部分排序。

3.查看阻塞锁排查结果：无

SELECT mm.inst_id, mm.sid, mm.TYPE, mm.id1, mm.id2, LPAD(TRUNC(mm.ctime/60/60), 3) || TO_CHAR(TRUNC) (mm .ctime/60 ) – TRUNC(mm.ctime/60/60) * 60, \’fm09\’), 2) || \’分钟\’ || ) * 60, \’fm09\’), 2) || \’ 秒\’ ctime, CASE WHEN mm .block=1 AND mm.lmode !=0 THEN \’holder\’ WHEN mm.block=0 AND mm.request !=0 THEN \’waiter\’ ELSE NULL END 角色，如果EE.blocking_session 不为NULL | ee.blocking_session ELSE NULL END block_session, dd.sql_text sql_text, cc.event wait_event FROM gv$lock mm, gv$session ee, gv$sqlarea dd, gv$session_wait cc WHERE mm.sid IN (SELECT nn.sidFROM ( SELECT tt.*, COUNT(1) OVER (按tt.TYPE, tt.id1, tt.id2 分区) cnt , MAX(tt .lmode) OVER (按tt.TYPE, tt.id1, tt 分区。 id2) lmod_flag, MAX(tt.request) OVER (PARTITION BY tt.TYPE, tt.id1, tt.id2) ) request_flag FROM gv$lock tt) nn WHERE nn.cnt 1 AND nn.lmod_flag !=0 AND nn.request_flag !=0) AND mm.sid=ee.sid (+) AND ee.sql_id=dd.sql_id (+) AND mm.sid=sid (+) AND ((mm.block=1 AND mm) .lmode !=0) OR (mm.block=0 AND mm.request !=0)) 按mm.TYPE、mm.id1、mm.id2、mm.lmode DESC、mm.ctime DESC 排序

4. 检查定时任务是否正在运行。 故障排除结果：无。

–查询正在运行的scheduler_jobselectowner,job_name,sid,b.SERIAL#,b.username,spid from ALL_SCHEDULER_RUNNING_JOBS,v$session b,v$process (session_id=sid and paddr=addr)。

5. 分析AWR报告。前4条SQL是凭经验来快速定位问题，只有故障期的AWR才能进行整体分析。当您可以更好地控制时间点时，请使用ASH 外观。

1.AWR概述

实例效率百分比（100% 目标） 所有指标的目标都是100%。换句话说，越大越好，但对于某些错误，它可能会超过100%，甚至为负值。

标准是：

80% 或以上% 非分析CPU 90% 或以上缓冲区命中%、内存排序%、软分析% 95% 或以上库命中%、Redo Nowait%、缓冲区无等待% 98% 或以上锁存器命中%缓冲区命中%: 是标准的，请参见下文。

内存利用率%：对于已经运行一段时间的数据库，共享池内存利用率应该稳定在75%到90%之间。

SQL1 with Execution：执行时间大于1 的SQL 的百分比。如果这个值太小，则意味着您的应用程序应该使用更多的绑定变量，以避免过多的SQL 解析。

Memory for SQL w/exec1：执行时间大于1 的SQL 消耗的内存百分比。这是衡量经常使用的SQL 语句与不经常使用的SQL 语句相比消耗多少内存的指标。

2.时间模型分析

在awr中，时间模型统计数据用于回答诸如“前台进程花费的时间”和“解析语句花费的时间”等问题。

在这里您可以看到最上面的值是经过的SQL 执行时间。这表示执行SQL 语句所花费的总时间。请注意，这还包括获取查询结果的时间。

可以看到，用于SQL执行的时间（SQL执行经过时间）占95.02%。对于一个相对健康的系统来说，这个比例应该不低于90%或者更高。

PS:

结合解析所用时间和硬解析所用时间来查看解析是否是主要差异，重点是软解析还是硬解析。

序列加载耗时序列冲突是问题的焦点

PL/SQL 编译运行时间PL/SQL 对象编译时间

请注意，PL/SQL 执行运行时间纯粹是在PL/SQL 解释器中花费的时间。它不包括执行和解析SQL（包括SQL）所花费的时间。

连接管理调用经过时间建立数据库会话连接和断开连接所花费的时间。

分析失败经过时间由于ORA-4031 等原因分析失败。

硬分析（共享标准） 已用时间无法共享游标的硬分析

硬解析（绑定不匹配） 已用时间由于绑定类型或绑定大小不匹配而进行硬解析

3.等待事件分析

等待事件表明它主要是I/0 瓶颈。提交的平均延迟为16毫秒，后台日志文件并行写入的平均延迟为8毫秒，这在实践中被认为太慢了。表明系统明显卡住。

ps:常见等待事件及解决方案

4. TOP SQL分析

分析上面的结果，主要问题是I/0，从整体角度优化时，还是需要从SQL按照用户I/O等待时间（I/0等待时间）的顺序进行优化。 Find SQL从四个方面进行优化：获取有序SQL（逻辑读）、读取有序SQL（物理读）。

然而，下面涉及的所有SQL都是日常使用，系统突然卡住了，尽管没有明显的异常。

为了显示：

CPU TIME : 快照时间内执行该SQL 所消耗的Cumulative CPU time slices (in sExecutions) : 快照时间内该SQL 的累计执行次数CPU per Exec (s): By a singleexecution Average CPU Time Consumed Running this SQL，或（SQL CPU TIME/SQL 运行）%Total : 此SQL 累计消耗的CPU 时间是该时间段内总DB CPU 的百分比，或（SQL CPU TIME/总DB CPU）。 % CPU 此SQL消耗的CPU时间占该SQL消耗时间的百分比，即（SQL CPU时间/SQL运行时间）。该指示器显示该语句是否依赖于CPU %IO。 SQL 消耗的时间百分比，或（SQL I/O 时间/SQL 经过时间），指示该语句是否依赖于I/O。

为了显示：

Buffer Gets : 快照期间SQL 消耗的累积缓冲区（包括一致读取和当前readExecutions） : 快照期间SQL 执行的累计执行次数Gets per Exec : 事务SQL 的平均单个缓冲区获取对于事务操作，单个缓冲区获取为通常小于SQL 累积执行消耗的总缓冲区数与数据库缓冲区获取总数的2000%（SQL 缓冲区获取数/数据库检索的缓冲区总数）。

Physical Reads : 此SQL 执行累计消耗的物理读取次数Execution : 快照时间内此SQL 累计执行次数Reads Per Execution : 此SQL 每次执行累计消耗的物理读取次数（SQL 物理读取/执行），对于OLTP事务这类操作，单次操作一般不会超过100%，总计：该SQL消耗的累计物理读计算出该时间段内的物理读总量。即（SQL 物理读取数/DB 物理读取总数）

5.表段分析

从上面关于IO问题的整体提示来看，我们看一下Segments per Physical Write和Segments per Physical Write Request，看看哪些表段占用了更多的IO。我们可以看到AUDIT_LOGIN_DB表占物理写入的20%。物理请求也占20%和8.2%，估计数据太多了。

该表不属于业务表，因此异常情况由该表决定。

六、故障表分析1、检查数据量和表内容

该表记录了用户登录时的IP等信息。每次用户登录时，触发器都会写入此表。目前，该表的数据量已达到800万条级别，从登录时可以看到以下内容：插入的频率非常高。

2.减少数据量

这个表不是很有用。这里，我打算保留它一段时间，然后切换表，然后截断它。由于空间有限，到目前为止我已经解释了N 次如何做到这一点。我不会在这里介绍它，但是.

7、测试经过一段时间的测试，业务反馈应该正常，问题解决，可以重新开始工作。

如果您觉得有用，请分享。稍后我会详细讨论DevOps 和DBA，感兴趣的请关注。