在《Database Concepts》的Process Architecture部分,讲到12c引入的一个新的特性,叫做Multithreaded Oracle Database Model。
之前在Unix/Linux等环境下,数据库是以多进程的方式运行的,当一个会话连接进来,就会通过监听,然后在服务器上创建一个前台进程,数据库后台进程也是以多进程方式运行的。而在Windows上面以多线程的方式来运行,windows下的oracle db的一个进程有很多个线程,而到了12c这个版本,Oracle在Unix/Linux平台上做出了一些改变,引入了多线程的模式。
这个特性由threaded_execution这个参数来控制,通过这个参数,可以控制数据库以多进程方式运行或者以多线程方式运行,该参数默认是false。数据库以多进程方式运行。
在默认情况下,查看linux下oracle的进程如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
[grid@dm01db02 ~]$ ps -ef | grep ora_ ora19x 5946 1 0 10:45 ? 00:00:00 ora_pmon_ORA19x ora19x 5948 1 0 10:45 ? 00:00:00 ora_clmn_ORA19x ora19x 5950 1 0 10:45 ? 00:00:00 ora_psp0_ORA19x ora19x 5953 1 1 10:45 ? 00:00:31 ora_vktm_ORA19x ora19x 5957 1 0 10:45 ? 00:00:00 ora_gen0_ORA19x ora19x 5959 1 0 10:45 ? 00:00:00 ora_mman_ORA19x ora19x 5963 1 0 10:45 ? 00:00:00 ora_gen1_ORA19x ora19x 5966 1 0 10:45 ? 00:00:00 ora_diag_ORA19x ora19x 5968 1 0 10:45 ? 00:00:00 ora_ofsd_ORA19x ora19x 5971 1 0 10:45 ? 00:00:01 ora_dbrm_ORA19x ora19x 5973 1 0 10:45 ? 00:00:06 ora_vkrm_ORA19x ora19x 5975 1 0 10:45 ? 00:00:00 ora_svcb_ORA19x ora19x 5977 1 0 10:45 ? 00:00:00 ora_pman_ORA19x ora19x 5979 1 0 10:45 ? 00:00:02 ora_dia0_ORA19x ora19x 5981 1 0 10:45 ? 00:00:00 ora_dbw0_ORA19x ora19x 5983 1 0 10:45 ? 00:00:00 ora_lgwr_ORA19x ora19x 5985 1 0 10:45 ? 00:00:00 ora_ckpt_ORA19x ora19x 5987 1 0 10:45 ? 00:00:00 ora_lg00_ORA19x ora19x 5989 1 0 10:45 ? 00:00:00 ora_smon_ORA19x ora19x 5991 1 0 10:45 ? 00:00:00 ora_lg01_ORA19x ora19x 5993 1 0 10:45 ? 00:00:00 ora_smco_ORA19x ora19x 5995 1 0 10:45 ? 00:00:00 ora_reco_ORA19x ora19x 5997 1 0 10:45 ? 00:00:00 ora_w000_ORA19x ora19x 5999 1 0 10:45 ? 00:00:00 ora_lreg_ORA19x ora19x 6005 1 0 10:45 ? 00:00:00 ora_w001_ORA19x ora19x 6015 1 0 10:45 ? 00:00:00 ora_pxmn_ORA19x ora19x 6028 1 0 10:45 ? 00:00:04 ora_mmon_ORA19x ora19x 6030 1 0 10:45 ? 00:00:01 ora_mmnl_ORA19x ora19x 6032 1 0 10:45 ? 00:00:00 ora_d000_ORA19x ora19x 6034 1 0 10:45 ? 00:00:00 ora_s000_ORA19x ora19x 6036 1 0 10:45 ? 00:00:00 ora_tmon_ORA19x ora19x 6044 1 0 10:45 ? 00:00:05 ora_m000_ORA19x ora19x 6051 1 0 10:45 ? 00:00:00 ora_tt00_ORA19x ora19x 6053 1 0 10:45 ? 00:00:00 ora_arc0_ORA19x ora19x 6055 1 0 10:45 ? 00:00:00 ora_tt01_ORA19x ora19x 6057 1 0 10:45 ? 00:00:00 ora_arc1_ORA19x ora19x 6059 1 0 10:45 ? 00:00:00 ora_arc2_ORA19x ora19x 6061 1 0 10:45 ? 00:00:00 ora_arc3_ORA19x ora19x 6063 1 0 10:45 ? 00:00:00 ora_tt02_ORA19x ora19x 6066 1 0 10:45 ? 00:00:00 ora_aqpc_ORA19x ora19x 6073 1 0 10:45 ? 00:00:03 ora_p000_ORA19x ora19x 6075 1 0 10:45 ? 00:00:02 ora_p001_ORA19x ora19x 6077 1 0 10:45 ? 00:00:00 ora_p002_ORA19x ora19x 6079 1 0 10:45 ? 00:00:00 ora_p003_ORA19x ora19x 6083 1 0 10:45 ? 00:00:00 ora_w002_ORA19x ora19x 6234 1 0 10:45 ? 00:00:05 ora_cjq0_ORA19x ora19x 6244 1 0 10:45 ? 00:00:00 ora_w003_ORA19x ora19x 6259 1 0 10:45 ? 00:00:04 ora_m001_ORA19x ora19x 6261 1 0 10:45 ? 00:00:00 ora_w004_ORA19x ora19x 6263 1 0 10:45 ? 00:00:08 ora_m002_ORA19x ora19x 6265 1 0 10:45 ? 00:00:02 ora_m003_ORA19x ora19x 6287 1 0 10:45 ? 00:00:00 ora_qm02_ORA19x ora19x 6293 1 0 10:45 ? 00:00:00 ora_q003_ORA19x ora19x 6297 1 0 10:45 ? 00:00:01 ora_q005_ORA19x ora19x 6304 1 0 10:45 ? 00:00:00 ora_q006_ORA19x ora19x 6961 1 0 10:55 ? 00:00:00 ora_w005_ORA19x ora19x 6964 1 0 10:55 ? 00:00:01 ora_m004_ORA19x ora19x 6968 1 0 10:55 ? 00:00:00 ora_w006_ORA19x ora19x 6972 1 0 10:55 ? 00:00:00 ora_w007_ORA19x ora19x 8880 1 2 11:11 ? 00:00:00 ora_j000_ORA19x |
在此不一一介绍这些进程了。之后修改参数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:15:43 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> alter system set threaded_execution=true scope=spfile; System altered. SQL> |
重启数据库会报错:
1 2 3 4 5 6 7 8 |
SQL> startup force ERROR: ORA-01017: invalid username/password; logon denied ORA-01017: invalid username/password; logon denied SQL> |
这是正常的。因为改为多线程模式之后,本地OS验证会失效,需使用密码+TNS方式登录。这里无法启动的解决办法有三个,如果是RAC的话,可以通过srvctl start database方式启动,单实例的话,一是可以用/nolog+conn sys as sysdba输入密码登录来继续数据库mount和open,二是可以设置export ORA_SERVER_THREAD_ENABLED=false这个环境变量来实现sqlplus / as sysdba登录启动数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[ora19x@dm01db02 ~]$ sqlplus /nolog SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:19:25 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. SQL> SQL> conn sys as sysdba Enter password: Connected. SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
ORA-01017: invalid username/password; logon denied SQL> SQL> [ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ export ORA_SERVER_THREAD_ENABLED=false [ora19x@dm01db02 ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:21:44 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> alter database mount 2 ; Database altered. SQL> alter database open; Database altered. SQL> |
这个时候,数据库的进程就非常少了:
1 2 3 4 5 6 7 8 9 10 11 12 |
[ora19x@dm01db02 ~]$ ps -ef | grep ora_ ora19x 11494 1 0 11:21 ? 00:00:00 ora_pmon_ORA19x ora19x 11496 1 1 11:21 ? 00:00:01 ora_u002_ORA19x ora19x 11499 1 0 11:21 ? 00:00:00 ora_psp0_ORA19x ora19x 11505 1 2 11:21 ? 00:00:01 ora_vktm_ORA19x ora19x 11512 1 0 11:21 ? 00:00:00 ora_gen1_ORA19x ora19x 11515 1 17 11:21 ? 00:00:15 ora_u006_ORA19x ora19x 11518 1 0 11:21 ? 00:00:00 ora_ofsd_ORA19x ora19x 11526 1 0 11:21 ? 00:00:00 ora_dbw0_ORA19x ora19x 11528 1 0 11:21 ? 00:00:00 ora_lgwr_ORA19x ora19x 11546 1 0 11:21 ? 00:00:00 ora_u00a_ORA19x |
可以看到,消失了大量的进程,包括之前我们说的非常核心的后台进程,如ckpt,smon等等等等。
官方的解释如下:
In a database running in threaded mode, PMON and DBW might run as operating system processes, whereas LGWR and CMON might run as threads within a single process. Two foreground processes and a parallel execution (PX) server process might run as threads in a second operating system process. A third operating system process might contain multiple foreground threads. Thus, “Oracle process” does not always mean “operating system process.”
PMON,DBW必须继续用操作系统进程方式运行,LGWR和CMON在同一个进程里。两个前台进程和并行PX进程运行在一个OS进程里。第三个OS进程包含了其他所有的前台进程。
此时,如果从客户端连接数据库,每一个连接依然会独立创建一个操作系统进程。我从客户端连接连个会话,会看到两个LOCAL=NO的进程:
1 2 3 4 |
[ora19x@dm01db02 ~]$ ps -ef | grep LOCAL=NO ora19x 15660 1 0 11:37 ? 00:00:00 oracleORA19x (LOCAL=NO) ora19x 15951 1 0 11:38 ? 00:00:00 oracleORA19x (LOCAL=NO) |
官方文档中描述:
Also, when this initialization parameter is set to
需要修改监听参数TRUE
, theDEDICATED_THROUGH_BROKER_listener-name=ON
parameter should be added to the listener.ora file, wherelistener-name
is the name of the Oracle Net listener. This enables the server to spawn threads when connections to the database are requested through the listener.DEDICATED_THROUGH_BROKER_listener-name=ON
修改如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ cat /u01/app/ora19c/product/19.x.0.0/dbhome_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/ora19c/product/19.x.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db02)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) DEDICATED_THROUGH_BROKER_LISTENER=ON [ora19x@dm01db02 ~]$ |
之后通过远程客户端连接,不再生成LOCAL=NO的进程,OS上生成了一个类似ora_u00b_ORA19x的进程,跟文档描述一致。
对v$session和v$process关联查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
PID SOSID SPID STID EXECUTION_ PNAME SID SERIAL# PORT PROGRAM MODULE ---------- ----------- ---------- ----- ---------- ----- ---------- ---------- ---------- --------------------------------- ---------------------------------------- 2 11494 11494 11494 PROCESS PMON 1 46080 0 oracle@dm01db02 (PMON) 4 11496_11496 11496 11496 THREAD SCMN 2 24748 0 oracle@dm01db02 (SCMN) 3 11496_11497 11496 11497 THREAD CLMN 1138 22991 0 oracle@dm01db02 (CLMN) 7 11496_11508 11496 11508 THREAD GEN0 1140 6522 0 oracle@dm01db02 (GEN0) 8 11496_11509 11496 11509 THREAD MMAN 4 19231 0 oracle@dm01db02 (MMAN) 16 11496_11520 11496 11520 THREAD DBRM 8 54999 0 oracle@dm01db02 (DBRM) 19 11496_11523 11496 11523 THREAD PMAN 1146 21011 0 oracle@dm01db02 (PMAN) 24 11496_11530 11496 11530 THREAD CKPT 12 43448 0 oracle@dm01db02 (CKPT) 26 11496_11532 11496 11532 THREAD SMON 13 52779 0 oracle@dm01db02 (SMON) 31 11496_11537 11496 11537 THREAD LREG 1152 20485 0 oracle@dm01db02 (LREG) 5 11499 11499 11499 PROCESS PSP0 1139 12425 0 oracle@dm01db02 (PSP0) 6 11505 11505 11505 PROCESS VKTM 3 11332 0 oracle@dm01db02 (VKTM) 11 11512_11512 11512 11512 THREAD SCMN 1142 6137 0 oracle@dm01db02 (SCMN) 10 11512_11513 11512 11513 THREAD GEN1 5 9749 0 oracle@dm01db02 (GEN1) 13 11515_11515 11515 11515 THREAD SCMN 1143 12217 0 oracle@dm01db02 (SCMN) 12 11515_11516 11515 11516 THREAD DIAG 6 43993 0 oracle@dm01db02 (DIAG) 17 11515_11521 11515 11521 THREAD VKRM 1145 37270 0 oracle@dm01db02 (VKRM) 18 11515_11522 11515 11522 THREAD SVCB 9 61035 0 oracle@dm01db02 (SVCB) 20 11515_11524 11515 11524 THREAD DIA0 10 13232 0 oracle@dm01db02 (DIA0) 28 11515_11534 11515 11534 THREAD SMCO 14 1843 0 oracle@dm01db02 (SMCO) KTSJ 29 11515_11535 11515 11535 THREAD RECO 1151 62446 0 oracle@dm01db02 (RECO) 30 11515_11536 11515 11536 THREAD W000 15 17976 0 oracle@dm01db02 (W000) KTSJ 32 11515_11538 11515 11538 THREAD W001 17 13279 0 oracle@dm01db02 (W001) KTSJ 33 11515_11539 11515 11539 THREAD PXMN 1153 32910 0 oracle@dm01db02 (PXMN) 35 11515_11541 11515 11541 THREAD MMON 1154 31561 0 oracle@dm01db02 (MMON) 36 11515_11542 11515 11542 THREAD MMNL 19 26195 0 oracle@dm01db02 (MMNL) 61 11515_11582 11515 11582 THREAD M000 1141 17827 0 oracle@dm01db02 (M000) MMON_SLAVE 75 11515_11696 11515 11696 THREAD W002 1162 46842 0 oracle@dm01db02 (W002) KTSJ 76 11515_11698 11515 11698 THREAD AQPC 25 61505 0 oracle@dm01db02 (AQPC) 80 11515_11867 11515 11867 THREAD CJQ0 28 12999 0 oracle@dm01db02 (CJQ0) 86 11515_11868 11515 11868 THREAD W003 29 4314 0 oracle@dm01db02 (W003) KTSJ 90 11515_11894 11515 11894 THREAD M001 34 57541 0 oracle@dm01db02 (M001) MMON_SLAVE 91 11515_11895 11515 11895 THREAD M002 1159 20340 0 oracle@dm01db02 (M002) MMON_SLAVE 88 11515_11896 11515 11896 THREAD W004 32 29029 0 oracle@dm01db02 (W004) KTSJ 89 11515_11897 11515 11897 THREAD M003 1168 31130 0 oracle@dm01db02 (M003) MMON_SLAVE 99 11515_11917 11515 11917 THREAD M004 1173 59248 0 oracle@dm01db02 (M004) MMON_SLAVE 93 11515_11931 11515 11931 THREAD QM02 1171 28359 0 oracle@dm01db02 (QM02) Streams 96 11515_11934 11515 11934 THREAD Q003 39 47823 0 oracle@dm01db02 (Q003) Streams 97 11515_11935 11515 11935 THREAD Q004 1169 41115 0 oracle@dm01db02 (Q004) Streams 106 11515_11939 11515 11939 THREAD Q008 47 15535 0 oracle@dm01db02 (Q008) Streams 102 11515_13278 11515 13278 THREAD W005 49 47999 0 oracle@dm01db02 (W005) KTSJ 87 11515_13497 11515 13497 THREAD W006 1182 40722 0 oracle@dm01db02 (W006) KTSJ 92 11515_14281 11515 14281 THREAD W007 43 28719 0 oracle@dm01db02 (W007) KTSJ 98 11515_29192 11515 29192 THREAD J000 58 37565 0 oracle@dm01db02 (J000) 100 11515_29193 11515 29193 THREAD J001 41 28217 0 oracle@dm01db02 (J001) 101 11515_29194 11515 29194 THREAD J002 1184 45685 0 oracle@dm01db02 (J002) 15 11518_11518 11518 11518 THREAD SCMN 1144 48107 0 oracle@dm01db02 (SCMN) 14 11518_11519 11518 11519 THREAD OFSD 7 5788 0 oracle@dm01db02 (OFSD) 21 11526 11526 11526 PROCESS DBW0 1147 35795 0 oracle@dm01db02 (DBW0) 23 11528_11528 11528 11528 THREAD SCMN 1148 37139 0 oracle@dm01db02 (SCMN) 22 11528_11529 11528 11529 THREAD LGWR 11 36006 0 oracle@dm01db02 (LGWR) 25 11528_11531 11528 11531 THREAD LG00 1149 51808 0 oracle@dm01db02 (LG00) 27 11528_11533 11528 11533 THREAD LG01 1150 24360 0 oracle@dm01db02 (LG01) 39 11546_11546 11546 11546 THREAD SCMN 1155 56108 0 oracle@dm01db02 (SCMN) 38 11546_11547 11546 11547 THREAD TMON 20 50916 0 oracle@dm01db02 (TMON) 72 11546_11693 11546 11693 THREAD TT00 21 46809 0 oracle@dm01db02 (TT00) 73 11546_11694 11546 11694 THREAD ARC0 1160 19708 0 oracle@dm01db02 (ARC0) 74 11546_11695 11546 11695 THREAD TT01 22 31187 0 oracle@dm01db02 (TT01) 77 11546_11702 11546 11702 THREAD ARC1 1164 15299 0 oracle@dm01db02 (ARC1) 78 11546_11703 11546 11703 THREAD ARC2 27 46856 0 oracle@dm01db02 (ARC2) 79 11546_11704 11546 11704 THREAD ARC3 1165 17667 0 oracle@dm01db02 (ARC3) 81 11546_11706 11546 11706 THREAD TT02 1166 46447 0 oracle@dm01db02 (TT02) 9 11633 11633 11633 PROCESS 1158 37058 0 sqlplus@dm01db02 (TNS V1-V3) sqlplus@dm01db02 (TNS V1-V3) 56 19368_19368 19368 19368 THREAD SCMN 44 37396 0 oracle@dm01db02 (SCMN) 55 19368_19369 19368 19369 THREAD 1176 46683 58152 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 58 19368_19819 19368 19819 THREAD 38 514 58228 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 62 19368_20035 19368 20035 THREAD 37 42284 58238 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 94 19368_20051 19368 20051 THREAD 51 41584 58239 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 95 19368_20455 19368 20455 THREAD 1178 8597 58272 SQL Developer SQL Developer 69 rows selected. |
这个查询结果很明显的可以看出来,PMON依然是进程,SCMN,CLMN,GEN0,MMAN,DBRM,PMAN,CKPT,SMON,LREG是线程模式,被封装在11496这个进程里,这个进程是ora_u002_ORA19x。PSP0和VKTM依然是进程模式。第二个SCMN和GEN1被分装在11512进程里,这个进程是ora_gen1_ORA19x。SCMN,DIAG,VKRM,SVCB,DIA0,SMCO,RECO,W000,W001,PXMN,MMON,MMNL,M000,W002,AQPC,CJQ0,W003,M001,M002,W004,M003,M004,QM02,Q003,Q004,Q008,W005,W006,W007被封装在11515进程里,这个进程是ora_u006_ORA19x。第三个SCMN和OFSD封装在11518(ora_ofsd_ORA19x)里。DBW0依然是进程模式。SCMN,LGWR,LG00,LG01被封装在11528(ora_lgwr_ORA19x)里。SCMN,TMON,TT00,ARC0,TT01,ARC1,ARC2,ARC3,TT02被封装在11546(ora_u00a_ORA19x)里。4个客户端连接被封装在单独的一个进程19368(ora_u00b_ORA19x)里。
可以看到每个启用了多线程的进程都有个SCMN线程,这个线程作为本进程内监听其他线程的线程。这个SCMN在MOS上检索会发现一个12.1.0.2上的BUG。
1 2 |
Bug 22226365 : THREADED_EXECUTION=TRUE - SCMN PROCESS RES MEMORY INCREASE |
下面尝试在OS层面来杀这些进程,看看数据库表现。
首先PMON就不测试了。。。
kill u002进程,数据库会挂掉:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 3, ospid: 3374_3376, CLMN)' |
CLMN是一个很重要的进程:
Cleanup Main Process (CLMN)
PMON将清理工作委托给清理主进程(CLMN)。检测异常终止的任务仍然是PMON。
PMON delegates cleanup work to the cleanup main process (CLMN). The task of detecting abnormal termination remains with PMON.
CLMN periodically performs cleanup of terminated processes, terminated sessions, transactions, network connections, idle sessions, detached transactions, and detached network connections that have exceeded their idle timeout.
Cleanup Helper Processes (CLnn)
CLMN delegates cleanup work to the CLnn helper processes.
The CLnn processes assist in the cleanup of terminated processes and sessions. The number of helper processes is proportional to the amount of cleanup work to be done and the current efficiency of cleanup.
A cleanup process can become blocked, which prevents it from proceeding to clean up other processes. Also, if multiple processes require cleanup, then cleanup time can be significant. For these reasons, Oracle Database can use multiple helper processes in parallel to perform cleanup, thus alleviating slow performance.
TheV$CLEANUP_PROCESS
andV$DEAD_CLEANUP
views contain metadata about CLMN cleanup. TheV$CLEANUP_PROCESS
view contains one row for every cleanup process. For example, ifV$CLEANUP_PROCESS.STATE
isBUSY
, then the process is currently engaged in cleanup.
CLMN定期执行已终止进程,已终止会话,事务,网络连接,空闲会话,分离事务以及已超出其空闲超时的已分离网络连接的清除。
清理助手进程(CLnn)
CLMN将清理工作委托给CLnn帮助程序进程。
CLnn进程有助于清除已终止的进程和会话。辅助进程的数量与要完成的清理工作量和当前清理效率成正比。
清理过程可能会被阻止,从而阻止其继续清理其他进程。此外,如果多个进程需要清理,那么清理时间可能很长。由于这些原因,Oracle数据库可以并行使用多个辅助进程来执行清理,从而缓解性能下降。
可以看到Pmon将进程清理的工作交给了CLMN,我们遇到过多次PMON清理进程时候,拿不到LATCH,导致PMON挂掉等等故障。不知道Oracle把这个功能独立出来是不是为了解决这个问题?
杀PSP0进程也会导致数据库crash:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 5, ospid: 4843, PSP0)' |
PSP是Process SPawner的意思,它负责创建job进程及管理其他进程的小工进程等等,显然是个很重要的进程。
杀VKTM进程也会导致数据库crash:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 6, ospid: 7688, VKTM)' |
VKTM作为两组时间的发布者,必要在数据库中扮演重要的角色。
杀GEN1进程,GEN1会自动重启:
1 2 3 4 5 6 7 |
2019-07-19T13:21:17.295672+08:00 Restarting dead background process GEN1 Starting background process GEN1 2019-07-19T13:21:17.317775+08:00 GEN1 started with pid=10, OS id=9481_9482 |
GEN1跟GEN0一样,都是所谓常规任务的执行进程,执行客户端的DML和DQL类的SQL任务。看来并不是核心进程。
杀u006进程,上文提到这个进程包含了除了几个核心进程之外的其他进程(线程),杀了这个进程之后,这个进程会重启,相关的线程也会跟着重启。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
2019-07-19T13:25:22.180350+08:00 found dead shared server 'S000', pid = (38, 1) found dead dispatcher 'D000', pid = (37, 1) Restarting dead background process DIAG Starting background process DIAG Restarting dead background process VKRM Starting background process VKRM 2019-07-19T13:25:22.227169+08:00 DIAG started with pid=20, OS id=10444_10448 Restarting dead background process SVCB Starting background process SVCB 2019-07-19T13:25:22.235221+08:00 VKRM started with pid=28, OS id=10444_10449 2019-07-19T13:25:22.246453+08:00 SVCB started with pid=29, OS id=10444_10450 Restarting dead background process DIA0 Starting background process DIA0 Restarting dead background process SMCO Starting background process SMCO 2019-07-19T13:25:22.256194+08:00 DIA0 started with pid=30, OS id=10444_10451 2019-07-19T13:25:22.263575+08:00 SMCO started with pid=32, OS id=10444_10452 Restarting dead background process RECO Starting background process RECO Restarting dead background process CJQ0 2019-07-19T13:25:22.273211+08:00 RECO started with pid=33, OS id=10444_10453 Starting background process CJQ0 2019-07-19T13:25:22.285421+08:00 CJQ0 started with pid=36, OS id=10444_10455 Restarting dead background process PXMN Starting background process PXMN 2019-07-19T13:25:22.296149+08:00 PXMN started with pid=38, OS id=10444_10457 Restarting dead background process AQPC Starting background process AQPC 2019-07-19T13:25:22.302241+08:00 AQPC started with pid=40, OS id=10444_10458 Restarting dead background process MMON Starting background process MMON Restarting dead background process MMNL Starting background process MMNL 2019-07-19T13:25:22.307693+08:00 MMON started with pid=41, OS id=10444_10459 2019-07-19T13:25:22.312651+08:00 MMNL started with pid=42, OS id=10444_10460 |
这些线程相关的小工线程(000,001之类)也会被重启。
杀OFSD进程,进程也会自动重启。OFS是Oracle Filesystem Server,一种类似DBFS的FS,也是基于表空间的文件系统。
1 2 3 4 5 6 7 8 9 |
2019-07-19T13:32:32.943072+08:00 Restarting dead background process OFSD Starting background process OFSD 2019-07-19T13:32:32.963556+08:00 OFSD started with pid=14, OS id=12221_12222 2019-07-19T13:32:32.964643+08:00 Oracle running with ofslib:'Oracle File Server Library' version=2 inc=2 |
杀LGWR进程,注意这个进程包含了几个log write相关的线程。可以预见数据库会crash的。
1 2 3 |
Cause - 'Instance is being terminated due to fatal process death (pid: 22, ospid: 8714_8715, LGWR)' |
杀DBW0进程,数据库也会crash,这跟db block write及部分检查点功能相关。
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 21, ospid: 12717, DBW0)' |
杀u00a进程,这个进程包含跟归档ARC相关和redo日志传输到备库相关的TT进程。理论上杀这个进程,也不会影响数据库,数据库的PMON会重启TMON,TMON会重启这些归档和redo传输进程,日志也是如此:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
2019-07-19T13:39:27.935309+08:00 Restarting dead background process TMON Starting background process TMON 2019-07-19T13:39:27.960609+08:00 TMON started with pid=34, OS id=14438_14439 2019-07-19T13:39:31.001540+08:00 TT00 (PID:14438_14452): Gap Manager starting 2019-07-19T13:39:31.017498+08:00 TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): STARTING ARCH PROCESSES Starting background process ARC0 2019-07-19T13:39:31.021988+08:00 ARC0 started with pid=79, OS id=14438_14455 Starting background process ARC1 Starting background process ARC2 2019-07-19T13:39:31.028257+08:00 ARC1 started with pid=80, OS id=14438_14456 2019-07-19T13:39:31.034028+08:00 ARC2 started with pid=81, OS id=14438_14457 Starting background process ARC3 TMON (PID:14438_14439): ARC0: Archival started TMON (PID:14438_14439): ARC1: Archival started TMON (PID:14438_14439): ARC2: Archival started 2019-07-19T13:39:31.039157+08:00 ARC1 (PID:14438_14456): Becoming a 'no FAL' ARCH ARC1 (PID:14438_14456): Becoming the 'no SRL' ARCH 2019-07-19T13:39:31.039644+08:00 ARC3 started with pid=82, OS id=14438_14458 2019-07-19T13:39:31.051755+08:00 TMON (PID:14438_14439): ARC3: Archival started TMON (PID:14438_14439): STARTING ARCH PROCESSES COMPLETE |
最后是u00b进程,这个进程全部都是客户端的连接进程。这个杀了就杀了吧。
在《Database Concepts》的Process Architecture部分,讲到12c引入的一个新的特性,叫做Multithreaded Oracle Database Model。
之前在Unix/Linux等环境下,数据库是以多进程的方式运行的,当一个会话连接进来,就会通过监听,然后在服务器上创建一个前台进程,数据库后台进程也是以多进程方式运行的。而在Windows上面以多线程的方式来运行,windows下的oracle db的一个进程有很多个线程,而到了12c这个版本,Oracle在Unix/Linux平台上做出了一些改变,引入了多线程的模式。
这个特性由threaded_execution这个参数来控制,通过这个参数,可以控制数据库以多进程方式运行或者以多线程方式运行,该参数默认是false。数据库以多进程方式运行。
在默认情况下,查看linux下oracle的进程如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
[grid@dm01db02 ~]$ ps -ef | grep ora_ ora19x 5946 1 0 10:45 ? 00:00:00 ora_pmon_ORA19x ora19x 5948 1 0 10:45 ? 00:00:00 ora_clmn_ORA19x ora19x 5950 1 0 10:45 ? 00:00:00 ora_psp0_ORA19x ora19x 5953 1 1 10:45 ? 00:00:31 ora_vktm_ORA19x ora19x 5957 1 0 10:45 ? 00:00:00 ora_gen0_ORA19x ora19x 5959 1 0 10:45 ? 00:00:00 ora_mman_ORA19x ora19x 5963 1 0 10:45 ? 00:00:00 ora_gen1_ORA19x ora19x 5966 1 0 10:45 ? 00:00:00 ora_diag_ORA19x ora19x 5968 1 0 10:45 ? 00:00:00 ora_ofsd_ORA19x ora19x 5971 1 0 10:45 ? 00:00:01 ora_dbrm_ORA19x ora19x 5973 1 0 10:45 ? 00:00:06 ora_vkrm_ORA19x ora19x 5975 1 0 10:45 ? 00:00:00 ora_svcb_ORA19x ora19x 5977 1 0 10:45 ? 00:00:00 ora_pman_ORA19x ora19x 5979 1 0 10:45 ? 00:00:02 ora_dia0_ORA19x ora19x 5981 1 0 10:45 ? 00:00:00 ora_dbw0_ORA19x ora19x 5983 1 0 10:45 ? 00:00:00 ora_lgwr_ORA19x ora19x 5985 1 0 10:45 ? 00:00:00 ora_ckpt_ORA19x ora19x 5987 1 0 10:45 ? 00:00:00 ora_lg00_ORA19x ora19x 5989 1 0 10:45 ? 00:00:00 ora_smon_ORA19x ora19x 5991 1 0 10:45 ? 00:00:00 ora_lg01_ORA19x ora19x 5993 1 0 10:45 ? 00:00:00 ora_smco_ORA19x ora19x 5995 1 0 10:45 ? 00:00:00 ora_reco_ORA19x ora19x 5997 1 0 10:45 ? 00:00:00 ora_w000_ORA19x ora19x 5999 1 0 10:45 ? 00:00:00 ora_lreg_ORA19x ora19x 6005 1 0 10:45 ? 00:00:00 ora_w001_ORA19x ora19x 6015 1 0 10:45 ? 00:00:00 ora_pxmn_ORA19x ora19x 6028 1 0 10:45 ? 00:00:04 ora_mmon_ORA19x ora19x 6030 1 0 10:45 ? 00:00:01 ora_mmnl_ORA19x ora19x 6032 1 0 10:45 ? 00:00:00 ora_d000_ORA19x ora19x 6034 1 0 10:45 ? 00:00:00 ora_s000_ORA19x ora19x 6036 1 0 10:45 ? 00:00:00 ora_tmon_ORA19x ora19x 6044 1 0 10:45 ? 00:00:05 ora_m000_ORA19x ora19x 6051 1 0 10:45 ? 00:00:00 ora_tt00_ORA19x ora19x 6053 1 0 10:45 ? 00:00:00 ora_arc0_ORA19x ora19x 6055 1 0 10:45 ? 00:00:00 ora_tt01_ORA19x ora19x 6057 1 0 10:45 ? 00:00:00 ora_arc1_ORA19x ora19x 6059 1 0 10:45 ? 00:00:00 ora_arc2_ORA19x ora19x 6061 1 0 10:45 ? 00:00:00 ora_arc3_ORA19x ora19x 6063 1 0 10:45 ? 00:00:00 ora_tt02_ORA19x ora19x 6066 1 0 10:45 ? 00:00:00 ora_aqpc_ORA19x ora19x 6073 1 0 10:45 ? 00:00:03 ora_p000_ORA19x ora19x 6075 1 0 10:45 ? 00:00:02 ora_p001_ORA19x ora19x 6077 1 0 10:45 ? 00:00:00 ora_p002_ORA19x ora19x 6079 1 0 10:45 ? 00:00:00 ora_p003_ORA19x ora19x 6083 1 0 10:45 ? 00:00:00 ora_w002_ORA19x ora19x 6234 1 0 10:45 ? 00:00:05 ora_cjq0_ORA19x ora19x 6244 1 0 10:45 ? 00:00:00 ora_w003_ORA19x ora19x 6259 1 0 10:45 ? 00:00:04 ora_m001_ORA19x ora19x 6261 1 0 10:45 ? 00:00:00 ora_w004_ORA19x ora19x 6263 1 0 10:45 ? 00:00:08 ora_m002_ORA19x ora19x 6265 1 0 10:45 ? 00:00:02 ora_m003_ORA19x ora19x 6287 1 0 10:45 ? 00:00:00 ora_qm02_ORA19x ora19x 6293 1 0 10:45 ? 00:00:00 ora_q003_ORA19x ora19x 6297 1 0 10:45 ? 00:00:01 ora_q005_ORA19x ora19x 6304 1 0 10:45 ? 00:00:00 ora_q006_ORA19x ora19x 6961 1 0 10:55 ? 00:00:00 ora_w005_ORA19x ora19x 6964 1 0 10:55 ? 00:00:01 ora_m004_ORA19x ora19x 6968 1 0 10:55 ? 00:00:00 ora_w006_ORA19x ora19x 6972 1 0 10:55 ? 00:00:00 ora_w007_ORA19x ora19x 8880 1 2 11:11 ? 00:00:00 ora_j000_ORA19x |
在此不一一介绍这些进程了。之后修改参数。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:15:43 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> alter system set threaded_execution=true scope=spfile; System altered. SQL> |
重启数据库会报错:
1 2 3 4 5 6 7 8 |
SQL> startup force ERROR: ORA-01017: invalid username/password; logon denied ORA-01017: invalid username/password; logon denied SQL> |
这是正常的。因为改为多线程模式之后,本地OS验证会失效,需使用密码+TNS方式登录。这里无法启动的解决办法有三个,如果是RAC的话,可以通过srvctl start database方式启动,单实例的话,一是可以用/nolog+conn sys as sysdba输入密码登录来继续数据库mount和open,二是可以设置export ORA_SERVER_THREAD_ENABLED=false这个环境变量来实现sqlplus / as sysdba登录启动数据库。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
[ora19x@dm01db02 ~]$ sqlplus /nolog SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:19:25 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. SQL> SQL> conn sys as sysdba Enter password: Connected. SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
ORA-01017: invalid username/password; logon denied SQL> SQL> [ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ export ORA_SERVER_THREAD_ENABLED=false [ora19x@dm01db02 ~]$ !sq sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jul 19 11:21:44 2019 Version 19.4.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.4.0.0.0 SQL> alter database mount 2 ; Database altered. SQL> alter database open; Database altered. SQL> |
这个时候,数据库的进程就非常少了:
1 2 3 4 5 6 7 8 9 10 11 12 |
[ora19x@dm01db02 ~]$ ps -ef | grep ora_ ora19x 11494 1 0 11:21 ? 00:00:00 ora_pmon_ORA19x ora19x 11496 1 1 11:21 ? 00:00:01 ora_u002_ORA19x ora19x 11499 1 0 11:21 ? 00:00:00 ora_psp0_ORA19x ora19x 11505 1 2 11:21 ? 00:00:01 ora_vktm_ORA19x ora19x 11512 1 0 11:21 ? 00:00:00 ora_gen1_ORA19x ora19x 11515 1 17 11:21 ? 00:00:15 ora_u006_ORA19x ora19x 11518 1 0 11:21 ? 00:00:00 ora_ofsd_ORA19x ora19x 11526 1 0 11:21 ? 00:00:00 ora_dbw0_ORA19x ora19x 11528 1 0 11:21 ? 00:00:00 ora_lgwr_ORA19x ora19x 11546 1 0 11:21 ? 00:00:00 ora_u00a_ORA19x |
可以看到,消失了大量的进程,包括之前我们说的非常核心的后台进程,如ckpt,smon等等等等。
官方的解释如下:
In a database running in threaded mode, PMON and DBW might run as operating system processes, whereas LGWR and CMON might run as threads within a single process. Two foreground processes and a parallel execution (PX) server process might run as threads in a second operating system process. A third operating system process might contain multiple foreground threads. Thus, “Oracle process” does not always mean “operating system process.”
PMON,DBW必须继续用操作系统进程方式运行,LGWR和CMON在同一个进程里。两个前台进程和并行PX进程运行在一个OS进程里。第三个OS进程包含了其他所有的前台进程。
此时,如果从客户端连接数据库,每一个连接依然会独立创建一个操作系统进程。我从客户端连接连个会话,会看到两个LOCAL=NO的进程:
1 2 3 4 |
[ora19x@dm01db02 ~]$ ps -ef | grep LOCAL=NO ora19x 15660 1 0 11:37 ? 00:00:00 oracleORA19x (LOCAL=NO) ora19x 15951 1 0 11:38 ? 00:00:00 oracleORA19x (LOCAL=NO) |
官方文档中描述:
Also, when this initialization parameter is set to
需要修改监听参数TRUE
, theDEDICATED_THROUGH_BROKER_listener-name=ON
parameter should be added to the listener.ora file, wherelistener-name
is the name of the Oracle Net listener. This enables the server to spawn threads when connections to the database are requested through the listener.DEDICATED_THROUGH_BROKER_listener-name=ON
修改如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[ora19x@dm01db02 ~]$ [ora19x@dm01db02 ~]$ cat /u01/app/ora19c/product/19.x.0.0/dbhome_1/network/admin/listener.ora # listener.ora Network Configuration File: /u01/app/ora19c/product/19.x.0.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dm01db02)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) DEDICATED_THROUGH_BROKER_LISTENER=ON [ora19x@dm01db02 ~]$ |
之后通过远程客户端连接,不再生成LOCAL=NO的进程,OS上生成了一个类似ora_u00b_ORA19x的进程,跟文档描述一致。
对v$session和v$process关联查询:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
PID SOSID SPID STID EXECUTION_ PNAME SID SERIAL# PORT PROGRAM MODULE ---------- ----------- ---------- ----- ---------- ----- ---------- ---------- ---------- --------------------------------- ---------------------------------------- 2 11494 11494 11494 PROCESS PMON 1 46080 0 oracle@dm01db02 (PMON) 4 11496_11496 11496 11496 THREAD SCMN 2 24748 0 oracle@dm01db02 (SCMN) 3 11496_11497 11496 11497 THREAD CLMN 1138 22991 0 oracle@dm01db02 (CLMN) 7 11496_11508 11496 11508 THREAD GEN0 1140 6522 0 oracle@dm01db02 (GEN0) 8 11496_11509 11496 11509 THREAD MMAN 4 19231 0 oracle@dm01db02 (MMAN) 16 11496_11520 11496 11520 THREAD DBRM 8 54999 0 oracle@dm01db02 (DBRM) 19 11496_11523 11496 11523 THREAD PMAN 1146 21011 0 oracle@dm01db02 (PMAN) 24 11496_11530 11496 11530 THREAD CKPT 12 43448 0 oracle@dm01db02 (CKPT) 26 11496_11532 11496 11532 THREAD SMON 13 52779 0 oracle@dm01db02 (SMON) 31 11496_11537 11496 11537 THREAD LREG 1152 20485 0 oracle@dm01db02 (LREG) 5 11499 11499 11499 PROCESS PSP0 1139 12425 0 oracle@dm01db02 (PSP0) 6 11505 11505 11505 PROCESS VKTM 3 11332 0 oracle@dm01db02 (VKTM) 11 11512_11512 11512 11512 THREAD SCMN 1142 6137 0 oracle@dm01db02 (SCMN) 10 11512_11513 11512 11513 THREAD GEN1 5 9749 0 oracle@dm01db02 (GEN1) 13 11515_11515 11515 11515 THREAD SCMN 1143 12217 0 oracle@dm01db02 (SCMN) 12 11515_11516 11515 11516 THREAD DIAG 6 43993 0 oracle@dm01db02 (DIAG) 17 11515_11521 11515 11521 THREAD VKRM 1145 37270 0 oracle@dm01db02 (VKRM) 18 11515_11522 11515 11522 THREAD SVCB 9 61035 0 oracle@dm01db02 (SVCB) 20 11515_11524 11515 11524 THREAD DIA0 10 13232 0 oracle@dm01db02 (DIA0) 28 11515_11534 11515 11534 THREAD SMCO 14 1843 0 oracle@dm01db02 (SMCO) KTSJ 29 11515_11535 11515 11535 THREAD RECO 1151 62446 0 oracle@dm01db02 (RECO) 30 11515_11536 11515 11536 THREAD W000 15 17976 0 oracle@dm01db02 (W000) KTSJ 32 11515_11538 11515 11538 THREAD W001 17 13279 0 oracle@dm01db02 (W001) KTSJ 33 11515_11539 11515 11539 THREAD PXMN 1153 32910 0 oracle@dm01db02 (PXMN) 35 11515_11541 11515 11541 THREAD MMON 1154 31561 0 oracle@dm01db02 (MMON) 36 11515_11542 11515 11542 THREAD MMNL 19 26195 0 oracle@dm01db02 (MMNL) 61 11515_11582 11515 11582 THREAD M000 1141 17827 0 oracle@dm01db02 (M000) MMON_SLAVE 75 11515_11696 11515 11696 THREAD W002 1162 46842 0 oracle@dm01db02 (W002) KTSJ 76 11515_11698 11515 11698 THREAD AQPC 25 61505 0 oracle@dm01db02 (AQPC) 80 11515_11867 11515 11867 THREAD CJQ0 28 12999 0 oracle@dm01db02 (CJQ0) 86 11515_11868 11515 11868 THREAD W003 29 4314 0 oracle@dm01db02 (W003) KTSJ 90 11515_11894 11515 11894 THREAD M001 34 57541 0 oracle@dm01db02 (M001) MMON_SLAVE 91 11515_11895 11515 11895 THREAD M002 1159 20340 0 oracle@dm01db02 (M002) MMON_SLAVE 88 11515_11896 11515 11896 THREAD W004 32 29029 0 oracle@dm01db02 (W004) KTSJ 89 11515_11897 11515 11897 THREAD M003 1168 31130 0 oracle@dm01db02 (M003) MMON_SLAVE 99 11515_11917 11515 11917 THREAD M004 1173 59248 0 oracle@dm01db02 (M004) MMON_SLAVE 93 11515_11931 11515 11931 THREAD QM02 1171 28359 0 oracle@dm01db02 (QM02) Streams 96 11515_11934 11515 11934 THREAD Q003 39 47823 0 oracle@dm01db02 (Q003) Streams 97 11515_11935 11515 11935 THREAD Q004 1169 41115 0 oracle@dm01db02 (Q004) Streams 106 11515_11939 11515 11939 THREAD Q008 47 15535 0 oracle@dm01db02 (Q008) Streams 102 11515_13278 11515 13278 THREAD W005 49 47999 0 oracle@dm01db02 (W005) KTSJ 87 11515_13497 11515 13497 THREAD W006 1182 40722 0 oracle@dm01db02 (W006) KTSJ 92 11515_14281 11515 14281 THREAD W007 43 28719 0 oracle@dm01db02 (W007) KTSJ 98 11515_29192 11515 29192 THREAD J000 58 37565 0 oracle@dm01db02 (J000) 100 11515_29193 11515 29193 THREAD J001 41 28217 0 oracle@dm01db02 (J001) 101 11515_29194 11515 29194 THREAD J002 1184 45685 0 oracle@dm01db02 (J002) 15 11518_11518 11518 11518 THREAD SCMN 1144 48107 0 oracle@dm01db02 (SCMN) 14 11518_11519 11518 11519 THREAD OFSD 7 5788 0 oracle@dm01db02 (OFSD) 21 11526 11526 11526 PROCESS DBW0 1147 35795 0 oracle@dm01db02 (DBW0) 23 11528_11528 11528 11528 THREAD SCMN 1148 37139 0 oracle@dm01db02 (SCMN) 22 11528_11529 11528 11529 THREAD LGWR 11 36006 0 oracle@dm01db02 (LGWR) 25 11528_11531 11528 11531 THREAD LG00 1149 51808 0 oracle@dm01db02 (LG00) 27 11528_11533 11528 11533 THREAD LG01 1150 24360 0 oracle@dm01db02 (LG01) 39 11546_11546 11546 11546 THREAD SCMN 1155 56108 0 oracle@dm01db02 (SCMN) 38 11546_11547 11546 11547 THREAD TMON 20 50916 0 oracle@dm01db02 (TMON) 72 11546_11693 11546 11693 THREAD TT00 21 46809 0 oracle@dm01db02 (TT00) 73 11546_11694 11546 11694 THREAD ARC0 1160 19708 0 oracle@dm01db02 (ARC0) 74 11546_11695 11546 11695 THREAD TT01 22 31187 0 oracle@dm01db02 (TT01) 77 11546_11702 11546 11702 THREAD ARC1 1164 15299 0 oracle@dm01db02 (ARC1) 78 11546_11703 11546 11703 THREAD ARC2 27 46856 0 oracle@dm01db02 (ARC2) 79 11546_11704 11546 11704 THREAD ARC3 1165 17667 0 oracle@dm01db02 (ARC3) 81 11546_11706 11546 11706 THREAD TT02 1166 46447 0 oracle@dm01db02 (TT02) 9 11633 11633 11633 PROCESS 1158 37058 0 sqlplus@dm01db02 (TNS V1-V3) sqlplus@dm01db02 (TNS V1-V3) 56 19368_19368 19368 19368 THREAD SCMN 44 37396 0 oracle@dm01db02 (SCMN) 55 19368_19369 19368 19369 THREAD 1176 46683 58152 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 58 19368_19819 19368 19819 THREAD 38 514 58228 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 62 19368_20035 19368 20035 THREAD 37 42284 58238 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 94 19368_20051 19368 20051 THREAD 51 41584 58239 sqlplus@m01dm01db03 (TNS V1-V3) sqlplus@m01dm01db03 (TNS V1-V3) 95 19368_20455 19368 20455 THREAD 1178 8597 58272 SQL Developer SQL Developer 69 rows selected. |
这个查询结果很明显的可以看出来,PMON依然是进程,SCMN,CLMN,GEN0,MMAN,DBRM,PMAN,CKPT,SMON,LREG是线程模式,被封装在11496这个进程里,这个进程是ora_u002_ORA19x。PSP0和VKTM依然是进程模式。第二个SCMN和GEN1被分装在11512进程里,这个进程是ora_gen1_ORA19x。SCMN,DIAG,VKRM,SVCB,DIA0,SMCO,RECO,W000,W001,PXMN,MMON,MMNL,M000,W002,AQPC,CJQ0,W003,M001,M002,W004,M003,M004,QM02,Q003,Q004,Q008,W005,W006,W007被封装在11515进程里,这个进程是ora_u006_ORA19x。第三个SCMN和OFSD封装在11518(ora_ofsd_ORA19x)里。DBW0依然是进程模式。SCMN,LGWR,LG00,LG01被封装在11528(ora_lgwr_ORA19x)里。SCMN,TMON,TT00,ARC0,TT01,ARC1,ARC2,ARC3,TT02被封装在11546(ora_u00a_ORA19x)里。4个客户端连接被封装在单独的一个进程19368(ora_u00b_ORA19x)里。
可以看到每个启用了多线程的进程都有个SCMN线程,这个线程作为本进程内监听其他线程的线程。这个SCMN在MOS上检索会发现一个12.1.0.2上的BUG。
1 2 |
Bug 22226365 : THREADED_EXECUTION=TRUE - SCMN PROCESS RES MEMORY INCREASE |
下面尝试在OS层面来杀这些进程,看看数据库表现。
首先PMON就不测试了。。。
kill u002进程,数据库会挂掉:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 3, ospid: 3374_3376, CLMN)' |
CLMN是一个很重要的进程:
Cleanup Main Process (CLMN)
PMON将清理工作委托给清理主进程(CLMN)。检测异常终止的任务仍然是PMON。
PMON delegates cleanup work to the cleanup main process (CLMN). The task of detecting abnormal termination remains with PMON.
CLMN periodically performs cleanup of terminated processes, terminated sessions, transactions, network connections, idle sessions, detached transactions, and detached network connections that have exceeded their idle timeout.
Cleanup Helper Processes (CLnn)
CLMN delegates cleanup work to the CLnn helper processes.
The CLnn processes assist in the cleanup of terminated processes and sessions. The number of helper processes is proportional to the amount of cleanup work to be done and the current efficiency of cleanup.
A cleanup process can become blocked, which prevents it from proceeding to clean up other processes. Also, if multiple processes require cleanup, then cleanup time can be significant. For these reasons, Oracle Database can use multiple helper processes in parallel to perform cleanup, thus alleviating slow performance.
TheV$CLEANUP_PROCESS
andV$DEAD_CLEANUP
views contain metadata about CLMN cleanup. TheV$CLEANUP_PROCESS
view contains one row for every cleanup process. For example, ifV$CLEANUP_PROCESS.STATE
isBUSY
, then the process is currently engaged in cleanup.
CLMN定期执行已终止进程,已终止会话,事务,网络连接,空闲会话,分离事务以及已超出其空闲超时的已分离网络连接的清除。
清理助手进程(CLnn)
CLMN将清理工作委托给CLnn帮助程序进程。
CLnn进程有助于清除已终止的进程和会话。辅助进程的数量与要完成的清理工作量和当前清理效率成正比。
清理过程可能会被阻止,从而阻止其继续清理其他进程。此外,如果多个进程需要清理,那么清理时间可能很长。由于这些原因,Oracle数据库可以并行使用多个辅助进程来执行清理,从而缓解性能下降。
可以看到Pmon将进程清理的工作交给了CLMN,我们遇到过多次PMON清理进程时候,拿不到LATCH,导致PMON挂掉等等故障。不知道Oracle把这个功能独立出来是不是为了解决这个问题?
杀PSP0进程也会导致数据库crash:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 5, ospid: 4843, PSP0)' |
PSP是Process SPawner的意思,它负责创建job进程及管理其他进程的小工进程等等,显然是个很重要的进程。
杀VKTM进程也会导致数据库crash:
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 6, ospid: 7688, VKTM)' |
VKTM作为两组时间的发布者,必要在数据库中扮演重要的角色。
杀GEN1进程,GEN1会自动重启:
1 2 3 4 5 6 7 |
2019-07-19T13:21:17.295672+08:00 Restarting dead background process GEN1 Starting background process GEN1 2019-07-19T13:21:17.317775+08:00 GEN1 started with pid=10, OS id=9481_9482 |
GEN1跟GEN0一样,都是所谓常规任务的执行进程,执行客户端的DML和DQL类的SQL任务。看来并不是核心进程。
杀u006进程,上文提到这个进程包含了除了几个核心进程之外的其他进程(线程),杀了这个进程之后,这个进程会重启,相关的线程也会跟着重启。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
2019-07-19T13:25:22.180350+08:00 found dead shared server 'S000', pid = (38, 1) found dead dispatcher 'D000', pid = (37, 1) Restarting dead background process DIAG Starting background process DIAG Restarting dead background process VKRM Starting background process VKRM 2019-07-19T13:25:22.227169+08:00 DIAG started with pid=20, OS id=10444_10448 Restarting dead background process SVCB Starting background process SVCB 2019-07-19T13:25:22.235221+08:00 VKRM started with pid=28, OS id=10444_10449 2019-07-19T13:25:22.246453+08:00 SVCB started with pid=29, OS id=10444_10450 Restarting dead background process DIA0 Starting background process DIA0 Restarting dead background process SMCO Starting background process SMCO 2019-07-19T13:25:22.256194+08:00 DIA0 started with pid=30, OS id=10444_10451 2019-07-19T13:25:22.263575+08:00 SMCO started with pid=32, OS id=10444_10452 Restarting dead background process RECO Starting background process RECO Restarting dead background process CJQ0 2019-07-19T13:25:22.273211+08:00 RECO started with pid=33, OS id=10444_10453 Starting background process CJQ0 2019-07-19T13:25:22.285421+08:00 CJQ0 started with pid=36, OS id=10444_10455 Restarting dead background process PXMN Starting background process PXMN 2019-07-19T13:25:22.296149+08:00 PXMN started with pid=38, OS id=10444_10457 Restarting dead background process AQPC Starting background process AQPC 2019-07-19T13:25:22.302241+08:00 AQPC started with pid=40, OS id=10444_10458 Restarting dead background process MMON Starting background process MMON Restarting dead background process MMNL Starting background process MMNL 2019-07-19T13:25:22.307693+08:00 MMON started with pid=41, OS id=10444_10459 2019-07-19T13:25:22.312651+08:00 MMNL started with pid=42, OS id=10444_10460 |
这些线程相关的小工线程(000,001之类)也会被重启。
杀OFSD进程,进程也会自动重启。OFS是Oracle Filesystem Server,一种类似DBFS的FS,也是基于表空间的文件系统。
1 2 3 4 5 6 7 8 9 |
2019-07-19T13:32:32.943072+08:00 Restarting dead background process OFSD Starting background process OFSD 2019-07-19T13:32:32.963556+08:00 OFSD started with pid=14, OS id=12221_12222 2019-07-19T13:32:32.964643+08:00 Oracle running with ofslib:'Oracle File Server Library' version=2 inc=2 |
杀LGWR进程,注意这个进程包含了几个log write相关的线程。可以预见数据库会crash的。
1 2 3 |
Cause - 'Instance is being terminated due to fatal process death (pid: 22, ospid: 8714_8715, LGWR)' |
杀DBW0进程,数据库也会crash,这跟db block write及部分检查点功能相关。
1 2 |
Cause - 'Instance is being terminated due to fatal process death (pid: 21, ospid: 12717, DBW0)' |
杀u00a进程,这个进程包含跟归档ARC相关和redo日志传输到备库相关的TT进程。理论上杀这个进程,也不会影响数据库,数据库的PMON会重启TMON,TMON会重启这些归档和redo传输进程,日志也是如此:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
2019-07-19T13:39:27.935309+08:00 Restarting dead background process TMON Starting background process TMON 2019-07-19T13:39:27.960609+08:00 TMON started with pid=34, OS id=14438_14439 2019-07-19T13:39:31.001540+08:00 TT00 (PID:14438_14452): Gap Manager starting 2019-07-19T13:39:31.017498+08:00 TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): Detected ARCH process failure TMON (PID:14438_14439): STARTING ARCH PROCESSES Starting background process ARC0 2019-07-19T13:39:31.021988+08:00 ARC0 started with pid=79, OS id=14438_14455 Starting background process ARC1 Starting background process ARC2 2019-07-19T13:39:31.028257+08:00 ARC1 started with pid=80, OS id=14438_14456 2019-07-19T13:39:31.034028+08:00 ARC2 started with pid=81, OS id=14438_14457 Starting background process ARC3 TMON (PID:14438_14439): ARC0: Archival started TMON (PID:14438_14439): ARC1: Archival started TMON (PID:14438_14439): ARC2: Archival started 2019-07-19T13:39:31.039157+08:00 ARC1 (PID:14438_14456): Becoming a 'no FAL' ARCH ARC1 (PID:14438_14456): Becoming the 'no SRL' ARCH 2019-07-19T13:39:31.039644+08:00 ARC3 started with pid=82, OS id=14438_14458 2019-07-19T13:39:31.051755+08:00 TMON (PID:14438_14439): ARC3: Archival started TMON (PID:14438_14439): STARTING ARCH PROCESSES COMPLETE |
最后是u00b进程,这个进程全部都是客户端的连接进程。这个杀了就杀了吧。
基本到这儿就看的基本明了了。但是这个特性是为了解决什么问题的 ?是为了避免操作系统进程过多,消耗资源?归根到底,在UNIX/LINUX下的oracle到底用哪种模式好? 提升性能吗?让维护变的更复杂吗?

祭出神器…swingbench…
200个会话,多线程模式测试:
首先,虽然多线程模式,但是数据库依然认为连接数受process参数来限制,超出的依然会报错:
1 2 3 4 5 6 |
2019-07-19T15:27:01.275914+08:00 ORA-00020: maximum number of processes (200) 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. |
跑出来的结果大概是:

观察OS的TOP,负载较高,主要消耗资源的是这几个u00x进程的scmn监听线程,每个里面大概四五十个线程,都是来自客户端的会话。

200个会话,多进程模式测试:

单从两种模式的测试结果来看,多线程模式的测试性能最多要比多进程模式好13%左右。
多次测试的结果多线程模式都好于多进程模式,提升幅度有大有小。看起来多线程模式确实是有利于性能提升。但是CPU的使用率要比多进程模式高10%到15%。这个应该是CPU上下文切换导致的。
总结:
不太理解这个特性有啥用。。。
–END–