查看告警日志看到报错:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
第一步:检查Alert Log
告警日志中显示的报错与上述内容一致,报错信息显示异常原因为temp表空间无法扩展,并且在某一特定时间段出现
1.确认temp tablespace空间大小
2.确认该表空间是否开启自动扩展
3.如果确认已开启自动扩展或已知表空间大小,则判断当时数据库的动作有无超过temp最大空间,即可定位至异常的原因
查看temp表空间大小
SQL> select tablespace_name,file_name,bytes/1024/1024/1024 file_size,autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_NAME FILE_SIZE AUT
TEMP +DATA/racdb/tempfile/temp02.dbf 10 NO
TEMP +DATA/racdb/tempfile/temp03.dbf 10 NO
TEMP +DATA/racdb/tempfile/temp01.dbf 10 NO
TEMP +DATA/racdb/tempfile/temp04.dbf 10 NO
可以看出temp表空间下有4个数据文件,每个大小为10G,且未开启自动扩展
目前temp表空间总大小为40G
那么可知,数据库单次获取的数据量应该小于40G,否则就会出现temp表空间不足的情况
查询在异常时间内(2021-06-08 12:00至1:00),占用资源超过40G的SQL
生成指定时间段的AWR报告
告警日志的报错信息是12点至1点左右,所以awr报告生成时间从12点-1点
可以看到发生了等待事件direct path read 和direct path read temp
查看导致direct path write等待事件的sql
SQL> select sql_id, count() from dba_hist_active_sess_history
where event = ‘direct path write’
group by sql_id order by count() desc; 2 3
SQL_ID COUNT(*)
1gz69fj5z6kr6 47
1hvdu8dztxpfx 41
1s5dj6fckb73s 16
bt08vmahnwsm3 8
fp700pqw3yy00 5
b97wpwpmcuuvz 5
4wu7fmh7wcqqx 5
brntjyy188uk7 4
9g2rwr603usa7 4
0jfyv1q7m378w 3
2pxux7fu9tg7r 3
7spktz0yr1gxa 2
gayvdbrghw368 2
6fzzh1nqjc4t4 2
5f4k63fgy7t1r 2
2
8u5159x70k02j 1
dmhqjx16c6w8y 1
956mwc1vc2ky6 1
9ycqu1mdf7svy 1
bqpnkpzk4bt2y 1
4k46mt51n7n8f 1
1arqnr7put41x 1
cmvrcbkpc2px3 1
5bzr32d7rya4f 1
47scxjyac9u03 1
已选择26行。
直接查看导致direct path write temp等待事件的sql
SQL> select sql_id, count() from dba_hist_active_sess_history
where event = ‘direct path write temp’
group by sql_id order by count() desc; 2 3
SQL_ID COUNT(*)
cqg6vfbga9q95 12
0h95w07ddcg2u 5
a9q2hvagvvs81 3
dc9g07nz7a2pp 2
dx059p2t30q04 1
29fy3yj4n7pxb 1
0ffv6pfmr3bx2 1
cu16dzgjyyysj 1
dkfqxss9cwtg8 1
3xsyjsjbgz4yy 1
7jds61daks522 1
2tnt2pa3b9prp 1
66gkwjphn9zvb 1
gv2fufczm34s4 1
dcp9w720y3zfm 1
bjyp0nj6j2nhj 1
0xa2mbckpp4xa 1
0zs65b6zpquxb 1
0dspay35dqdgt 1
dwhb1gruxvx9p 1
27nktfu251hg8 1
5ddsan4jp3zap 1
dnzpwyy0tw969 1
fr26gt7zucnsp 1
03zktf63bd1bs 1
已选择25行。
cqg6vfbga9q95这条sql在物理读也是居高榜首,那么他一定有问题
查看sql_id为cqg6vfbga9q95的SQL
SQL> select * from dba_hist_sqltext where sql_id=‘cqg6vfbga9q95’;
DBID SQL_ID SQL_TEXT COMMAND_TYPE
801114584 cqg6vfbga9q95 select xx from xx; 3
异常处理
优先确认相关的SQL具体的功能,判断是否为系统异常操作
如果是异常操作导致,则忽略不计
如果为系统正常操作导致,则判断是否需要新增TEMP表空间