- 数据库alert日志出现报错
Thu Mar 04 11:01:08 2021
ORA-00020: maximum number of processes (500) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
Process m000 submission failed with error = 20
Process m000 submission failed with error = 20
alert日志报错很明显，process使用已经达到上限500。
- 数据库中查询历史process使用情况
SQL> set linesize 2000
SQL> select * from gv$resource_limit where resource_name='processes';
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------
1 processes 188 500 500 500
2 processes 191 500 500 500
查询得知，数据库的进程数确实使用达到过500最大上限。
3. 查询当前process使用情况
SQL> select inst_id,count(*) from gv$process group by inst_id;
INST_ID COUNT(*)
---------- ----------
1 186
2 189
查询得知，当前进程数正常，未达到上限。
-
根据时间点抓取ASH报告（10:50:00 ~ 11:10:00）
1节点：
2节点
通过ASH报告看出，在10:50 ~ 11:10之间，数据库存在较多的TX锁类等待
造成TX锁类等待的语句有2个
SQL_ID:dwa1k3kf5zny6
SQL_ID:0z8ky1ntts250 -
根据时间点查询阻塞情况
SQL> col sample_time for a35;
SQL> col event for a55;
SQL> set linesize 1000;
select t.dbid,t.sample_id,t.sample_time,t.instance_number,t.event,t.session_state,t.c session_count
from (select t.*,rank() over(partition by dbid, instance_number, sample_time order by c desc) r
from (select /*+ parallel 2 */ t.*,count(*) over(partition by dbid, instance_number, sample_time, event) c,row_number() over(partition by dbid, instance_number, sample_time, event order by 1) r1
from dba_hist_active_sess_history t
where sample_time >to_timestamp('2021-03-04 10:55:00','yyyy-mm-dd hh24:mi:ss')
and sample_time <to_timestamp('2021-03-04 11:05:00','yyyy-mm-dd hh24:mi:ss')) t
where r1 = 1) t where r < 3 order by 3;
SQL> 2 3 4 5 6 7
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 148650271 04-MAR-21 10.55.03.408 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650271 04-MAR-21 10.55.03.408 AM 2 enq: TX - allocate ITL entry WAITING 21
116220666 155663650 04-MAR-21 10.55.06.759 AM 1 enq: TX - row lock contention WAITING 88
116220666 155663650 04-MAR-21 10.55.06.759 AM 1 enq: TX - allocate ITL entry WAITING 19
116220666 148650281 04-MAR-21 10.55.13.438 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650281 04-MAR-21 10.55.13.438 AM 2 enq: TX - allocate ITL entry WAITING 23
116220666 155663660 04-MAR-21 10.55.16.769 AM 1 enq: TX - row lock contention WAITING 88
116220666 155663660 04-MAR-21 10.55.16.769 AM 1 enq: TX - allocate ITL entry WAITING 23
116220666 148650291 04-MAR-21 10.55.23.448 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650291 04-MAR-21 10.55.23.448 AM 2 enq: TX - allocate ITL entry WAITING 32
116220666 155663670 04-MAR-21 10.55.26.779 AM 1 enq: TX - row lock contention WAITING 88
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663670 04-MAR-21 10.55.26.779 AM 1 enq: TX - allocate ITL entry WAITING 25
116220666 148650301 04-MAR-21 10.55.33.458 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650301 04-MAR-21 10.55.33.458 AM 2 enq: TX - allocate ITL entry WAITING 34
116220666 155663680 04-MAR-21 10.55.36.819 AM 1 enq: TX - row lock contention WAITING 88
116220666 155663680 04-MAR-21 10.55.36.819 AM 1 enq: TX - allocate ITL entry WAITING 28
116220666 148650311 04-MAR-21 10.55.43.468 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650311 04-MAR-21 10.55.43.468 AM 2 enq: TX - allocate ITL entry WAITING 38
116220666 155663690 04-MAR-21 10.55.46.847 AM 1 enq: TX - row lock contention WAITING 89
116220666 155663690 04-MAR-21 10.55.46.847 AM 1 enq: TX - allocate ITL entry WAITING 33
116220666 148650321 04-MAR-21 10.55.53.488 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650321 04-MAR-21 10.55.53.488 AM 2 enq: TX - allocate ITL entry WAITING 43
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663700 04-MAR-21 10.55.56.857 AM 1 enq: TX - row lock contention WAITING 90
116220666 155663700 04-MAR-21 10.55.56.857 AM 1 enq: TX - allocate ITL entry WAITING 42
116220666 148650331 04-MAR-21 10.56.03.498 AM 2 enq: TX - row lock contention WAITING 84
116220666 148650331 04-MAR-21 10.56.03.498 AM 2 enq: TX - allocate ITL entry WAITING 48
116220666 155663710 04-MAR-21 10.56.06.877 AM 1 enq: TX - row lock contention WAITING 91
116220666 155663710 04-MAR-21 10.56.06.877 AM 1 enq: TX - allocate ITL entry WAITING 43
116220666 148650341 04-MAR-21 10.56.13.540 AM 2 enq: TX - row lock contention WAITING 85
116220666 148650341 04-MAR-21 10.56.13.540 AM 2 enq: TX - allocate ITL entry WAITING 52
116220666 155663720 04-MAR-21 10.56.16.887 AM 1 enq: TX - row lock contention WAITING 92
116220666 155663720 04-MAR-21 10.56.16.887 AM 1 enq: TX - allocate ITL entry WAITING 46
116220666 148650351 04-MAR-21 10.56.23.560 AM 2 enq: TX - row lock contention WAITING 85
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 148650351 04-MAR-21 10.56.23.560 AM 2 enq: TX - allocate ITL entry WAITING 55
116220666 155663730 04-MAR-21 10.56.26.907 AM 1 enq: TX - row lock contention WAITING 94
116220666 155663730 04-MAR-21 10.56.26.907 AM 1 enq: TX - allocate ITL entry WAITING 51
116220666 148650361 04-MAR-21 10.56.33.570 AM 2 enq: TX - row lock contention WAITING 86
116220666 148650361 04-MAR-21 10.56.33.570 AM 2 enq: TX - allocate ITL entry WAITING 57
116220666 155663740 04-MAR-21 10.56.36.937 AM 1 enq: TX - row lock contention WAITING 97
116220666 155663740 04-MAR-21 10.56.36.937 AM 1 enq: TX - allocate ITL entry WAITING 52
116220666 148650371 04-MAR-21 10.56.43.590 AM 2 enq: TX - row lock contention WAITING 88
116220666 148650371 04-MAR-21 10.56.43.590 AM 2 enq: TX - allocate ITL entry WAITING 60
116220666 155663750 04-MAR-21 10.56.46.947 AM 1 enq: TX - row lock contention WAITING 98
116220666 155663750 04-MAR-21 10.56.46.947 AM 1 enq: TX - allocate ITL entry WAITING 60
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 148650381 04-MAR-21 10.56.53.600 AM 2 enq: TX - row lock contention WAITING 90
116220666 148650381 04-MAR-21 10.56.53.600 AM 2 enq: TX - allocate ITL entry WAITING 65
116220666 155663760 04-MAR-21 10.56.56.967 AM 1 enq: TX - row lock contention WAITING 101
116220666 155663760 04-MAR-21 10.56.56.967 AM 1 enq: TX - allocate ITL entry WAITING 63
116220666 148650391 04-MAR-21 10.57.03.610 AM 2 enq: TX - row lock contention WAITING 92
116220666 148650391 04-MAR-21 10.57.03.610 AM 2 enq: TX - allocate ITL entry WAITING 69
116220666 155663770 04-MAR-21 10.57.06.977 AM 1 enq: TX - row lock contention WAITING 102
116220666 155663770 04-MAR-21 10.57.06.977 AM 1 enq: TX - allocate ITL entry WAITING 67
116220666 148650401 04-MAR-21 10.57.13.640 AM 2 enq: TX - row lock contention WAITING 95
116220666 148650401 04-MAR-21 10.57.13.640 AM 2 enq: TX - allocate ITL entry WAITING 74
116220666 155663780 04-MAR-21 10.57.16.987 AM 1 enq: TX - row lock contention WAITING 104
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663780 04-MAR-21 10.57.16.987 AM 1 enq: TX - allocate ITL entry WAITING 70
116220666 148650411 04-MAR-21 10.57.23.660 AM 2 enq: TX - row lock contention WAITING 101
116220666 148650411 04-MAR-21 10.57.23.660 AM 2 enq: TX - allocate ITL entry WAITING 75
116220666 155663790 04-MAR-21 10.57.26.997 AM 1 enq: TX - row lock contention WAITING 106
116220666 155663790 04-MAR-21 10.57.26.997 AM 1 enq: TX - allocate ITL entry WAITING 74
116220666 148650421 04-MAR-21 10.57.33.670 AM 2 enq: TX - row lock contention WAITING 104
116220666 148650421 04-MAR-21 10.57.33.670 AM 2 enq: TX - allocate ITL entry WAITING 78
116220666 155663800 04-MAR-21 10.57.37.047 AM 1 enq: TX - row lock contention WAITING 107
116220666 155663800 04-MAR-21 10.57.37.047 AM 1 enq: TX - allocate ITL entry WAITING 79
116220666 148650431 04-MAR-21 10.57.43.680 AM 2 enq: TX - row lock contention WAITING 107
116220666 148650431 04-MAR-21 10.57.43.680 AM 2 enq: TX - allocate ITL entry WAITING 82
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663810 04-MAR-21 10.57.47.074 AM 1 enq: TX - row lock contention WAITING 109
116220666 155663810 04-MAR-21 10.57.47.074 AM 1 enq: TX - allocate ITL entry WAITING 84
116220666 148650441 04-MAR-21 10.57.53.700 AM 2 enq: TX - row lock contention WAITING 108
116220666 148650441 04-MAR-21 10.57.53.700 AM 2 enq: TX - allocate ITL entry WAITING 84
116220666 155663820 04-MAR-21 10.57.57.084 AM 1 enq: TX - row lock contention WAITING 112
116220666 155663820 04-MAR-21 10.57.57.084 AM 1 enq: TX - allocate ITL entry WAITING 87
116220666 148650451 04-MAR-21 10.58.03.710 AM 2 enq: TX - row lock contention WAITING 108
116220666 148650451 04-MAR-21 10.58.03.710 AM 2 enq: TX - allocate ITL entry WAITING 89
116220666 155663830 04-MAR-21 10.58.07.104 AM 1 enq: TX - row lock contention WAITING 114
116220666 155663830 04-MAR-21 10.58.07.104 AM 1 enq: TX - allocate ITL entry WAITING 90
116220666 148650461 04-MAR-21 10.58.13.776 AM 2 enq: TX - row lock contention WAITING 111
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 148650461 04-MAR-21 10.58.13.776 AM 2 enq: TX - allocate ITL entry WAITING 94
116220666 155663840 04-MAR-21 10.58.17.114 AM 1 enq: TX - row lock contention WAITING 116
116220666 155663840 04-MAR-21 10.58.17.114 AM 1 enq: TX - allocate ITL entry WAITING 96
116220666 148650471 04-MAR-21 10.58.23.796 AM 2 enq: TX - row lock contention WAITING 114
116220666 148650471 04-MAR-21 10.58.23.796 AM 2 enq: TX - allocate ITL entry WAITING 97
116220666 155663850 04-MAR-21 10.58.27.124 AM 1 enq: TX - row lock contention WAITING 118
116220666 155663850 04-MAR-21 10.58.27.124 AM 1 enq: TX - allocate ITL entry WAITING 96
116220666 148650481 04-MAR-21 10.58.33.806 AM 2 enq: TX - row lock contention WAITING 116
116220666 148650481 04-MAR-21 10.58.33.806 AM 2 enq: TX - allocate ITL entry WAITING 101
116220666 155663860 04-MAR-21 10.58.37.174 AM 1 enq: TX - row lock contention WAITING 119
116220666 155663860 04-MAR-21 10.58.37.174 AM 1 enq: TX - allocate ITL entry WAITING 98
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 148650491 04-MAR-21 10.58.43.826 AM 2 enq: TX - row lock contention WAITING 116
116220666 148650491 04-MAR-21 10.58.43.826 AM 2 enq: TX - allocate ITL entry WAITING 104
116220666 155663870 04-MAR-21 10.58.47.194 AM 1 enq: TX - row lock contention WAITING 123
116220666 155663870 04-MAR-21 10.58.47.194 AM 1 enq: TX - allocate ITL entry WAITING 102
116220666 148650501 04-MAR-21 10.58.53.836 AM 2 enq: TX - row lock contention WAITING 117
116220666 148650501 04-MAR-21 10.58.53.836 AM 2 enq: TX - allocate ITL entry WAITING 107
116220666 155663880 04-MAR-21 10.58.57.214 AM 1 enq: TX - row lock contention WAITING 123
116220666 155663880 04-MAR-21 10.58.57.214 AM 1 enq: TX - allocate ITL entry WAITING 102
116220666 148650511 04-MAR-21 10.59.03.856 AM 2 enq: TX - row lock contention WAITING 120
116220666 148650511 04-MAR-21 10.59.03.856 AM 2 enq: TX - allocate ITL entry WAITING 108
116220666 155663890 04-MAR-21 10.59.07.234 AM 1 enq: TX - row lock contention WAITING 125
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663890 04-MAR-21 10.59.07.234 AM 1 enq: TX - allocate ITL entry WAITING 109
116220666 148650521 04-MAR-21 10.59.13.916 AM 2 enq: TX - row lock contention WAITING 125
116220666 148650521 04-MAR-21 10.59.13.916 AM 2 enq: TX - allocate ITL entry WAITING 114
116220666 155663900 04-MAR-21 10.59.17.254 AM 1 enq: TX - row lock contention WAITING 125
116220666 155663900 04-MAR-21 10.59.17.254 AM 1 enq: TX - allocate ITL entry WAITING 113
116220666 148650531 04-MAR-21 10.59.23.936 AM 2 enq: TX - row lock contention WAITING 125
116220666 148650531 04-MAR-21 10.59.23.936 AM 2 enq: TX - allocate ITL entry WAITING 114
116220666 155663910 04-MAR-21 10.59.27.284 AM 1 enq: TX - row lock contention WAITING 126
116220666 155663910 04-MAR-21 10.59.27.284 AM 1 enq: TX - allocate ITL entry WAITING 114
116220666 148650541 04-MAR-21 10.59.33.956 AM 2 enq: TX - row lock contention WAITING 127
116220666 148650541 04-MAR-21 10.59.33.956 AM 2 enq: TX - allocate ITL entry WAITING 117
DBID SAMPLE_ID SAMPLE_TIME INSTANCE_NUMBER EVENT SESSION SESSION_COUNT
---------- ---------- ----------------------------------- --------------- ------------------------------------------------------- ------- -------------
116220666 155663920 04-MAR-21 10.59.37.334 AM 1 enq: TX - row lock contention WAITING 126
116220666 155663920 04-MAR-21 10.59.37.334 AM 1 enq: TX - allocate ITL entry WAITING 121
116220666 148650551 04-MAR-21 10.59.43.986 AM 2 enq: TX - row lock contention WAITING 129
116220666 148650551 04-MAR-21 10.59.43.986 AM 2 enq: TX - allocate ITL entry WAITING 120
116220666 148650561 04-MAR-21 10.59.54.006 AM 2 enq: TX - row lock contention WAITING 130
116220666 148650561 04-MAR-21 10.59.54.006 AM 2 enq: TX - allocate ITL entry WAITING 122
116220666 148650571 04-MAR-21 11.00.04.026 AM 2 enq: TX - row lock contention WAITING 131
116220666 148650571 04-MAR-21 11.00.04.026 AM 2 enq: TX - allocate ITL entry WAITING 125
116220666 148650581 04-MAR-21 11.00.14.096 AM 2 enq: TX - row lock contention WAITING 134
116220666 148650581 04-MAR-21 11.00.14.096 AM 2 enq: TX - allocate ITL entry WAITING 128
120 rows selected.
查询数据库中10:55 ~ 11:05的阻塞会话数情况，可以看到，在10:55 ~ 11:05之间，被阻塞的会话数一直在增长。
- 原因分析总结
根据客户反馈，该数据库对应的业务系统，有多个模块是短连接形式，如果业务执行失败，会不停的尝试重新连接和重新执行，这与上述分析得出的情况想吻合，sql语句造成了阻塞，被阻塞的会话/连接不停的重新连接和重新执行，慢慢的造成了积压，从而达到process上限，后将SQL ID和SQL语句提交给客户研发和业务部门进行业务逻辑分析。
4795
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
