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
