Oracle 12c多线程模式介绍
👉 本文共约517个字，系统预计阅读时间或需2分钟。
简介
在Unix/Linux等环境下面。数据库是以多进程的方式运行的，当一个会话连接进来，就会通过监听，然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c之后开始，Oracle在Unix/Linux平台上做出了一些改变，引入了多线程的方式。
通过参数threaded_execution，我们可以控制数据库是以多进程方式运行还是以多线程方式运行，默认该参数是false数据库以多进程方式运行。
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 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | [oracle@lhrora19c ~]$ sas SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 10 17:38:46 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SYS@ORCLCDB> startup ORACLE instance started. Total System Global Area 1073739168 bytes Fixed Size 9143712 bytes Variable Size 381681664 bytes Database Buffers 675282944 bytes Redo Buffers 7630848 bytes Database mounted. Database opened. SYS@ORCLCDB> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO SYS@ORCLCDB> ! ps -ef|grep ora root 185 160 0 17:38 pts/1 00:00:00 su - oracle oracle 186 185 0 17:38 pts/1 00:00:00 -bash oracle 733 1 0 17:44 ? 00:00:00 ora_pmon_ORCLCDB oracle 735 1 0 17:44 ? 00:00:00 ora_clmn_ORCLCDB oracle 737 1 0 17:44 ? 00:00:00 ora_psp0_ORCLCDB oracle 739 1 0 17:44 ? 00:00:00 ora_vktm_ORCLCDB oracle 743 1 0 17:44 ? 00:00:00 ora_gen0_ORCLCDB oracle 745 1 0 17:44 ? 00:00:00 ora_mman_ORCLCDB oracle 749 1 0 17:44 ? 00:00:00 ora_gen1_ORCLCDB oracle 752 1 0 17:44 ? 00:00:00 ora_diag_ORCLCDB oracle 754 1 0 17:44 ? 00:00:00 ora_ofsd_ORCLCDB oracle 757 1 0 17:44 ? 00:00:00 ora_dbrm_ORCLCDB oracle 759 1 0 17:44 ? 00:00:00 ora_vkrm_ORCLCDB oracle 761 1 0 17:44 ? 00:00:00 ora_svcb_ORCLCDB oracle 763 1 0 17:44 ? 00:00:00 ora_pman_ORCLCDB oracle 765 1 0 17:44 ? 00:00:00 ora_dia0_ORCLCDB oracle 767 1 0 17:44 ? 00:00:00 ora_dbw0_ORCLCDB oracle 769 1 0 17:44 ? 00:00:00 ora_lgwr_ORCLCDB oracle 771 1 0 17:44 ? 00:00:00 ora_ckpt_ORCLCDB oracle 773 1 0 17:44 ? 00:00:00 ora_lg00_ORCLCDB oracle 775 1 0 17:44 ? 00:00:00 ora_smon_ORCLCDB oracle 777 1 0 17:44 ? 00:00:00 ora_lg01_ORCLCDB oracle 779 1 0 17:44 ? 00:00:00 ora_smco_ORCLCDB oracle 781 1 0 17:44 ? 00:00:00 ora_reco_ORCLCDB oracle 783 1 0 17:44 ? 00:00:00 ora_w000_ORCLCDB oracle 785 1 0 17:44 ? 00:00:00 ora_lreg_ORCLCDB oracle 787 1 0 17:44 ? 00:00:00 ora_w001_ORCLCDB oracle 789 1 0 17:44 ? 00:00:00 ora_pxmn_ORCLCDB oracle 793 1 1 17:44 ? 00:00:01 ora_mmon_ORCLCDB oracle 795 1 0 17:44 ? 00:00:00 ora_mmnl_ORCLCDB oracle 797 1 0 17:44 ? 00:00:00 ora_d000_ORCLCDB oracle 799 1 0 17:44 ? 00:00:00 ora_s000_ORCLCDB oracle 801 1 0 17:44 ? 00:00:00 ora_tmon_ORCLCDB oracle 804 1 0 17:44 ? 00:00:00 ora_m000_ORCLCDB oracle 806 1 0 17:44 ? 00:00:00 ora_m001_ORCLCDB oracle 811 1 0 17:44 ? 00:00:00 ora_p000_ORCLCDB oracle 813 1 0 17:44 ? 00:00:00 ora_p001_ORCLCDB oracle 815 1 0 17:44 ? 00:00:00 ora_p002_ORCLCDB oracle 817 1 0 17:44 ? 00:00:00 ora_p003_ORCLCDB oracle 819 1 0 17:44 ? 00:00:00 ora_p004_ORCLCDB oracle 821 1 0 17:44 ? 00:00:00 ora_p005_ORCLCDB oracle 823 1 0 17:44 ? 00:00:00 ora_p006_ORCLCDB oracle 825 1 0 17:44 ? 00:00:00 ora_tt00_ORCLCDB oracle 827 1 0 17:44 ? 00:00:00 ora_arc0_ORCLCDB oracle 829 1 0 17:44 ? 00:00:00 ora_tt01_ORCLCDB oracle 831 1 0 17:44 ? 00:00:00 ora_arc1_ORCLCDB oracle 833 1 0 17:44 ? 00:00:00 ora_arc2_ORCLCDB oracle 835 1 0 17:44 ? 00:00:00 ora_arc3_ORCLCDB oracle 837 1 0 17:44 ? 00:00:00 ora_tt02_ORCLCDB oracle 839 1 0 17:44 ? 00:00:00 ora_aqpc_ORCLCDB oracle 843 1 0 17:44 ? 00:00:00 ora_w002_ORCLCDB oracle 845 1 0 17:44 ? 00:00:00 ora_p007_ORCLCDB oracle 847 1 0 17:44 ? 00:00:00 ora_p008_ORCLCDB oracle 849 1 0 17:44 ? 00:00:00 ora_p009_ORCLCDB oracle 851 1 0 17:44 ? 00:00:00 ora_p00a_ORCLCDB oracle 853 1 0 17:44 ? 00:00:00 ora_p00b_ORCLCDB oracle 855 1 0 17:44 ? 00:00:00 ora_p00c_ORCLCDB oracle 857 1 0 17:44 ? 00:00:00 ora_p00d_ORCLCDB oracle 859 1 0 17:44 ? 00:00:00 ora_p00e_ORCLCDB oracle 861 1 0 17:44 ? 00:00:00 ora_p00f_ORCLCDB oracle 1035 1 0 17:44 ? 00:00:00 ora_cjq0_ORCLCDB oracle 1052 1 1 17:44 ? 00:00:01 ora_m002_ORCLCDB oracle 1054 1 0 17:44 ? 00:00:00 ora_m003_ORCLCDB oracle 1078 1 0 17:44 ? 00:00:00 ora_w003_ORCLCDB oracle 1082 1 0 17:44 ? 00:00:00 ora_w004_ORCLCDB oracle 1084 1 0 17:44 ? 00:00:00 ora_qm02_ORCLCDB oracle 1088 1 0 17:44 ? 00:00:00 ora_q002_ORCLCDB oracle 1090 1 0 17:44 ? 00:00:00 ora_q003_ORCLCDB oracle 1092 1 0 17:44 ? 00:00:00 ora_q004_ORCLCDB oracle 1094 1 0 17:44 ? 00:00:00 ora_q005_ORCLCDB oracle 1097 186 0 17:45 pts/1 00:00:00 ps -ef oracle 1098 186 0 17:45 pts/1 00:00:00 grep --color=auto ora SYS@ORCLCDB> show parameter threaded_execution NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ threaded_execution boolean FALSE SYS@ORCLCDB> alter system set threaded_execution=true scope=spfile; System altered. SYS@ORCLCDB> startup force ERROR: ORA-01017: invalid username/password; logon denied ORA-01017: invalid username/password; logon denied @ORCLCDB> exit [oracle@lhrora19c ~]$ ps -ef|grep pmon oracle 622 1 0 17:40 ? 00:00:00 ora_pmon_ORCLCDB oracle 715 186 0 17:42 pts/1 00:00:00 grep --color=auto pmon [oracle@lhrora19c ~]$ ps -ef|grep smon oracle 717 186 0 17:42 pts/1 00:00:00 grep --color=auto smon [oracle@lhrora19c ~]$ ps -ef|grep ora root 185 160 0 17:38 pts/1 00:00:00 su - oracle oracle 186 185 0 17:38 pts/1 00:00:00 -bash oracle 622 1 0 17:40 ? 00:00:00 ora_pmon_ORCLCDB oracle 624 1 0 17:40 ? 00:00:00 ora_u002_ORCLCDB oracle 627 1 0 17:40 ? 00:00:00 ora_psp0_ORCLCDB oracle 629 1 0 17:40 ? 00:00:00 ora_vktm_ORCLCDB oracle 636 1 0 17:40 ? 00:00:00 ora_gen1_ORCLCDB oracle 639 1 0 17:40 ? 00:00:00 ora_u006_ORCLCDB oracle 642 1 0 17:40 ? 00:00:00 ora_ofsd_ORCLCDB oracle 650 1 0 17:40 ? 00:00:00 ora_dbw0_ORCLCDB oracle 652 1 0 17:40 ? 00:00:00 ora_lgwr_ORCLCDB oracle 670 1 0 17:40 ? 00:00:00 ora_u00a_ORCLCDB oracle 718 186 0 17:42 pts/1 00:00:00 ps -ef oracle 719 186 0 17:42 pts/1 00:00:00 grep --color=auto ora [oracle@lhrora19c ~]$ sqlplus /nolog SQL*Plus: Release 19.0.0.0.0 - Production on Mon Oct 10 17:43:25 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. @> conn sys as sysdba Enter password: Connected. SYS@ORCLCDB> show pdbs SYS@ORCLCDB> show pdbs SYS@ORCLCDB> alter system set threaded_execution=false scope=spfile; System altered. SYS@ORCLCDB> startup force ORACLE instance started. Total System Global Area 1073739168 bytes Fixed Size 9143712 bytes Variable Size 381681664 bytes Database Buffers 675282944 bytes Redo Buffers 7630848 bytes Database mounted. Database opened. SYS@ORCLCDB> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB1 READ WRITE NO SYS@ORCLCDB> |
修改为线程模式后，后台进程的数量减少了。一些后台进程（pmon，dbw，lgwr，psp，vktm）的行为与以前一样。奇怪的是居然smon也没了。其他backgtound进程属于名为orauxxx 的多线程进程。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
如果要通过监听的方式连接变成线程模式，需要修改监听的参数DEDICATED_THROUGH_BROKER_listener-name，并重启监听才行。
值得注意的是，当你需要kill某个阻塞进程时，这时kill的将会变为线程。切记不能乱kill。
总结
1、不要随意去修改参数“threaded_execution”哟！！！
