oracle大表执行慢的优化方法,Oracle性能突然变慢的解决思路

场景：某个ETL任务正常情况下每天11:30~12:00之间结束，突然某天后延到13：50，次日15点，然后逐步每天稳定在17:00，同服务器下其他任务也相对变慢。

一、IO层面问题排查

1.查看操作系统资源管理器磁盘活动，确定排名靠前的磁盘IO，定位具体进程，确认是否有异常或不必要的IO进程，此处定位并暂停OGG进程(谨慎停止)，观察一整天数据库性能

2.排除系统自动备份任务造成的IO性能资源堵塞，此处排除

经过一天观察，性能虽有些微的提升，但不能排除观察时点任务稀疏的特殊性造成的假象。再次经过任务高峰期比对历史性能差异，排除该进程抢占磁盘资源造成的性能问题，进而忽略IO层面问题

二、数据库层面问题排查

1.确认等待时间和阻塞原因

查看数据库中过去一天内排在前10位的等待事件及其总等待时间

SELECT *

FROM (SELECT EVENT,

TOTAL_WAIT_TM,

ROUND(TOTAL_WAIT_TM / SUM(TOTAL_WAIT_TM) OVER(ORDER BY 1), 4) * 100 || ‘%’ ZB

FROM (SELECT NVL(EVENT, ‘ON CPU’) EVENT, COUNT(*) TOTAL_WAIT_TM

FROM V$ACTIVE_SESSION_HISTORY

WHERE SAMPLE_TIME > TRUNC(SYSDATE) — 15 / (24 * 60)

GROUP BY EVENT

ORDER BY 2 DESC))

WHERE ROWNUM <= 10;

image.png

发现有大量的log file switch (checkpoint incomplete)等待事件

网上百度的解释：

服务器进程正要写入重做记录的时刻，若重做日志文件已满不能继续写入操作，则进程想LGWR请求执行对日志文件的切换。服务器进程由于LGWR，直到日志文件切换结束为止，需等待log file switch completion事件。但日志文件的切换结束时，如果将要投入使用的重做日志文件，还有没有完成的工作，就需要另外等待如下事件。

(1)如果对欲重新使用的重做日志文件尚未结束检查点，进程就应该等待由DBWR来结束检查点。这时，进程将等待log file switch (checkpoint incomplete)事件。

(2)如果对欲重新使用的重做日志文件尚未完成对党工作，进程就应该等待ARCH进程来结束归档工作。这时，进程将等待log file switch (archiving needed)事件。这个事件只在归档模式数据库上发生。

(3)如果对于欲重新使用的重做日志文件尚未完成对private strand的flush工作，就应该等待这个工作结束。这时，进程将等待log file switch (private strand flush incomplete)事件。

以上的三种等待现象在重做日志文件被循环使用的情况下，将生成许多重做数据，所以在尚未完成工作就重新使用时发生。因此这些等待现象一向是与log file switch completion等待现象一起出现的。准确的说，服务器进程首先等待log file switch completion事件，特殊情况下还会等待log file switch (checkpoint incomplete)、log file switch (archiving needed)和log file switch (private strand flush incomplete)事件。

因为名字相似，所以给管理人员带来了相当混乱的几个等待现象，发生原因和解决方式相同。发生原因是比起事务所创建的重做数据，重做日志文件过小。所以解决方法是将重做日志文件的大小调整为足够大。而且，使用Direct load operation或nologging选项对减少重做数据的量也是有帮助的。

解决办法(3选1)：

1.添加日志组

2.按照实际情况增大日志组成员的大小。

3.或者同时做1,2

此处选择方法2：

–1.查看当前日志组成员

SELECT MEMBER FROM V$LOGFILE;

–2.查看当前日志组状态

SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG;

–3.增加日志组

ALTER DATABASE ADD LOGFILE GROUP 4 (‘D:/APP/CLPC_EDW/CLPC_EDW/REDO04.LOG’) SIZE 200M;

ALTER DATABASE ADD LOGFILE GROUP 5 (‘D:/APP/CLPC_EDW/CLPC_EDW/REDO05.LOG’) SIZE 200M;

ALTER DATABASE ADD LOGFILE GROUP 6 (‘D:/APP/CLPC_EDW/CLPC_EDW/REDO06.LOG’) SIZE 200M;

–4.切换到新增的日志组上

ALTER SYSTEM SWITCH LOGFILE;–(可多次执行,直到CURRENT指向新建的日志组)

–5.查看当前日志组状态

SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG;

SELECT * FROM V$LOG;

各种状态含义:

A.CURRENT指当前的日志文件,在进行实例恢复时是必须的；

B.ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。ACTIVE状态意味着,CHECKPOINT尚未完成,因此该日志文件不能被覆盖。这时也不能DROP掉,应该执行ALTER SYSTEM CHECKPOINT; –强制执行检查点;然后在操作。

C.INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。

D.UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重置。

–6.删除旧的日志组

ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 2;

ALTER DATABASE DROP LOGFILE GROUP 3;

–6.1如果删不掉

ALTER SYSTEM SWITCH LOGFILE;

SELECT GROUP#,MEMBERS,BYTES/1024/1024,STATUS FROM V$LOG;

ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1;

–7.在操作系统下删除掉REDOLOG 日志文件

备注:可以先新建日志组作为过渡日志组,然后删除原有日志组并修改大小后进行重建。

再次查看数据库中过去15分钟内排在前5位的等待事件及其总等待时间，恢复正常：

image.png

到此，数据库性能突然变慢的症结已解决，后面的均为数据库业务功能层面的优化，时间紧迫可以忽略

2.查看过去15分钟内使用CPU及资源最多的前5位会话

SELECT *

FROM (SELECT S.USERNAME, S.MODULE, S.SID, S.SERIAL#, S.EVENT, COUNT(*)

FROM V$ACTIVE_SESSION_HISTORY H, V$SESSION S

WHERE H.SESSION_ID = S.SID

AND H.SESSION_SERIAL# = S.SERIAL#

AND SESSION_STATE = ‘ON CPU’

AND SAMPLE_TIME > SYSDATE – INTERVAL ’15’ MINUTE

GROUP BY S.USERNAME, S.MODULE, S.SID, S.SERIAL#, S.EVENT

ORDER BY COUNT(*) DESC)

WHERE ROWNUM <= 5;

SELECT DISTINCT EVENT FROM V$SESSION

SESSION_STATE列有两个有效的值：ON CPU和WAITING，分别表示会话是活动的还是在等待资源，同样将 SESSION_STATE指定为WAITING值，可以查看在等待资源的前5位会话信息。

3、查看那些业务用户占用最多的资源

SELECT *

FROM (SELECT U.USERNAME,

H.MODULE,

H.SESSION_ID SID,

H.SESSION_SERIAL# SERIAL#,

COUNT(*)

FROM DBA_HIST_ACTIVE_SESS_HISTORY H, DBA_USERS U

WHERE H.USER_ID = U.USER_ID

AND SESSION_STATE = ‘ON CPU’

AND (SAMPLE_TIME BETWEEN

TO_DATE(‘2018-07-22:00:00:00’, ‘yyyy-mm-dd:hh24:mi:ss’) AND

TO_DATE(‘2021-07-22:23:59:59’, ‘yyyy-mm-dd:hh24:mi:ss’))

AND U.USERNAME != ‘SYS’

GROUP BY U.USERNAME, H.MODULE, H.SESSION_ID, H.SESSION_SERIAL#

ORDER BY COUNT(*) DESC)

WHERE ROWNUM <= 5;

113 19422 561

141 54513 529

110 20841 515

38 11920 511

12 45449 490

4、定位造成大量资源消耗的对象信息

SELECT *

FROM (SELECT O.OBJECT_NAME,

O.OBJECT_TYPE,

S.EVENT,

S.TIME_WAITED,

S.CURRENT_OBJ#

FROM DBA_HIST_ACTIVE_SESS_HISTORY S, DBA_OBJECTS O

WHERE S.SAMPLE_TIME BETWEEN

TO_DATE(‘2020-12-26:00:00:00’, ‘yyyy-mm-dd:hh24:mi:ss’) AND

TO_DATE(‘2021-07-22:23:59:59’, ‘yyyy-mm-dd:hh24:mi:ss’)

AND S.CURRENT_OBJ# = O.OBJECT_ID

ORDER BY S.TIME_WAITED DESC);

select * from DBA_HIST_ACTIVE_SESS_HISTORY s where S.CURRENT_OBJ#=’1042886′

5、查看过去15分钟内造成最多数据库等待的sql语句

SELECT ASH.USER_ID,

U.USERNAME,

S.SQL_TEXT,

SUM(ASH.WAIT_TIME + ASH.TIME_WAITED) TTL_WAIT_TIME

FROM V$ACTIVE_SESSION_HISTORY ASH, V$SQLAREA S, DBA_USERS U

WHERE ASH.SAMPLE_TIME BETWEEN SYSDATE – 15 / (24 * 60) AND SYSDATE

AND ASH.SQL_ID = S.SQL_ID

AND ASH.USER_ID = U.USER_ID

GROUP BY ASH.USER_ID, S.SQL_TEXT, U.USERNAME

ORDER BY TTL_WAIT_TIME

6、找出谁持有阻塞锁

SELECT L1.BLOCK,

S1.USERNAME || ‘@’ || S1.MACHINE || ‘ ( SID=’ || S1.SID ||

‘ ) is blocking ‘ || S2.USERNAME || ‘@’ || S2.MACHINE || ‘ ( SID=’ ||

S2.SID || ‘ ) ‘ AS BLOCKING_STATUS

FROM V$LOCK L1, V$SESSION S1, V$LOCK L2, V$SESSION S2

WHERE S1.SID = L1.SID

AND S2.SID = L2.SID

AND L1.BLOCK = 1

AND L2.REQUEST > 0

AND L1.ID1 = L2.ID1

AND L2.ID2 = L2.ID2;

7、定位某些有问题的表

1.首先对某些你觉得有问题的表进行分析:

ANALYZE TABLE T_RPT_TRUST_GATHER_WEEKLY COMPUTE STATISTICS;

image.png

SELECT table_name,blocks, empty_blocks, num_rows

FROM user_tables order by empty_blocks desc

说明：

BLOCKS 列代表该表中曾经使用过得数据库块的数目，即水线。

EMPTY_BLOCKS 代表分配给该表，但是在水线以上的数据库块，即从来没有使用的数据块。

三、问题总结

1.log file switch (checkpoint incomplete)等待事件：数据库性能突然变慢的症结为REDO日志文件空间不足导致等待

2.数据库残留问题包含：数据读直接跳过SGA，从硬盘读数据快到PGA，由于服务器处理PGA中数据块的速度远大于从磁盘读数据块到PGA的速度，通常解决这个问题的方法都是增大PGA的大小。由于系统资源有限，将pga_aggregate_target增大到1G，direct path read temp等待时间还是很明显

3.direct path read等待事件：很多表处于高水位，导致表的数据量很大，远大于_small_table_threshold(0.02*2G),所以就走了directpath read