ORA-00020: maximum number of processes (500) exceeded原因分析
本文共 23745 字，大约阅读时间需要 79 分钟。
- 数据库alert日志出现报错
Thu Mar 04 11:01:08 2021ORA-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 = 20Process m000 submission failed with error = 20
alert日志报错很明显，process使用已经达到上限500。
- 数据库中查询历史process使用情况
SQL> set linesize 2000SQL> 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 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 128120 rows selected. 查询数据库中10:55 ~ 11:05的阻塞会话数情况，可以看到，在10:55 ~ 11:05之间，被阻塞的会话数一直在增长。
- 原因分析总结 根据客户反馈，该数据库对应的业务系统，有多个模块是短连接形式，如果业务执行失败，会不停的尝试重新连接和重新执行，这与上述分析得出的情况想吻合，sql语句造成了阻塞，被阻塞的会话/连接不停的重新连接和重新执行，慢慢的造成了积压，从而达到process上限，后将SQL ID和SQL语句提交给客户研发和业务部门进行业务逻辑分析。
转载地址：http://qplm.baihongyu.com/
你可能感兴趣的文章
MySQL中使用IN（）查询到底走不走索引？
查看>>
Mysql中使用存储过程插入decimal和时间数据递增的模拟数据
查看>>
MySql中关于geometry类型的数据_空的时候如何插入处理_需用null_空字符串插入会报错_Cannot get geometry object from dat---MySql工作笔记003
查看>>
mysql中出现Incorrect DECIMAL value: '0' for column '' at row -1错误解决方案
查看>>
mysql中出现Unit mysql.service could not be found 的解决方法
查看>>
mysql中出现update-alternatives: 错误: 候选项路径 /etc/mysql/mysql.cnf 不存在 dpkg: 处理软件包 mysql-server-8.0的解决方法（全）
查看>>
Mysql中各类锁的机制图文详细解析（全）
查看>>