之前我们学习Oracle基础的时候，有一个概念，叫多进程和多线程。在Unix/Linux等环境下面。数据库是以多进程的方式运行的，当一个会话连接进来，就会通过监听，然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c这个版本上面，Oracle在Unix/Linux平台上做出了一些改变，引入了多线程的方式。通过参数threaded_execution，我们可以控制数据库是以多进程方式运行还是以多线程方式运行，默认该参数是false数据库以多进程方式运行。
1.查看参数的默认值，和数据库进程状态.
[oracle@ol6 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:57:59 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> show parameter threaded NAME TYPE VALUE------------------------------------ ----------- ------------------------------threaded_execution boolean FALSE[root@ol6 ~]# ps -ef | grep cdb1oracle 1773 1 0 Aug02 ? 00:00:04 ora_pmon_cdb1oracle 1775 1 0 Aug02 ? 00:00:01 ora_clmn_cdb1oracle 1777 1 0 Aug02 ? 00:00:13 ora_psp0_cdb1oracle 1786 1 0 Aug02 ? 00:17:01 ora_vktm_cdb1oracle 1790 1 0 Aug02 ? 00:00:08 ora_gen0_cdb1oracle 1792 1 0 Aug02 ? 00:00:01 ora_mman_cdb1oracle 1796 1 0 Aug02 ? 00:00:19 ora_gen1_cdb1oracle 1800 1 0 Aug02 ? 00:00:03 ora_diag_cdb1oracle 1802 1 0 Aug02 ? 00:00:01 ora_ofsd_cdb1oracle 1806 1 0 Aug02 ? 00:00:29 ora_dbrm_cdb1oracle 1808 1 0 Aug02 ? 00:01:14 ora_vkrm_cdb1oracle 1810 1 0 Aug02 ? 00:00:03 ora_svcb_cdb1oracle 1812 1 0 Aug02 ? 00:00:10 ora_pman_cdb1oracle 1814 1 0 Aug02 ? 00:00:48 ora_dia0_cdb1oracle 1816 1 0 Aug02 ? 00:00:08 ora_dbw0_cdb1oracle 1818 1 0 Aug02 ? 00:00:10 ora_lgwr_cdb1oracle 1820 1 0 Aug02 ? 00:00:18 ora_ckpt_cdb1oracle 1822 1 0 Aug02 ? 00:00:01 ora_smon_cdb1oracle 1824 1 0 Aug02 ? 00:00:04 ora_smco_cdb1oracle 1826 1 0 Aug02 ? 00:00:00 ora_reco_cdb1oracle 1830 1 0 Aug02 ? 00:00:03 ora_lreg_cdb1oracle 1834 1 0 Aug02 ? 00:00:01 ora_pxmn_cdb1oracle 1838 1 0 Aug02 ? 00:00:20 ora_mmon_cdb1oracle 1840 1 0 Aug02 ? 00:00:26 ora_mmnl_cdb1oracle 1842 1 0 Aug02 ? 00:00:00 ora_d000_cdb1oracle 1844 1 0 Aug02 ? 00:00:00 ora_s000_cdb1oracle 1846 1 0 Aug02 ? 00:00:00 ora_tmon_cdb1oracle 1869 1 0 Aug02 ? 00:00:00 ora_tt00_cdb1oracle 1871 1 0 Aug02 ? 00:00:00 ora_tt01_cdb1oracle 1873 1 0 Aug02 ? 00:00:02 ora_tt02_cdb1oracle 1875 1 0 Aug02 ? 00:00:00 ora_aqpc_cdb1oracle 1879 1 0 Aug02 ? 00:00:02 ora_p000_cdb1oracle 1881 1 0 Aug02 ? 00:00:02 ora_p001_cdb1oracle 1883 1 0 Aug02 ? 00:00:02 ora_p002_cdb1oracle 1885 1 0 Aug02 ? 00:00:02 ora_p003_cdb1oracle 2039 1 0 Aug02 ? 00:02:36 ora_cjq0_cdb1oracle 2109 1 0 Aug02 ? 00:00:01 ora_qm02_cdb1oracle 2113 1 0 Aug02 ? 00:00:00 ora_q002_cdb1oracle 2120 1 0 Aug02 ? 00:00:02 ora_q005_cdb1oracle 24076 1 0 15:56 ? 00:00:00 ora_w000_cdb1oracle 24149 1 0 15:56 ? 00:00:00 ora_q003_cdb1oracle 24154 1 0 15:56 ? 00:00:00 ora_q004_cdb1oracle 24161 1 0 15:56 ? 00:00:00 ora_q006_cdb1oracle 24165 1 0 15:56 ? 00:00:00 ora_w001_cdb1oracle 24317 1 0 15:57 ? 00:00:00 ora_w002_cdb1oracle 24422 24421 0 15:57 ? 00:00:00 oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))root 24504 24458 0 15:58 pts/1 00:00:00 grep cdb1oracle 28778 1 0 Aug02 ? 00:00:01 ora_q001_cdb1oracle 29034 29033 0 Aug02 ? 00:00:00 oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
2.修改参数重启数据库
SQL> ALTER SYSTEM SET threaded_execution = true SCOPE = SPFILE; System altered.SQL> shutdown immediate; Database closed.Database dismounted.ORACLE instance shut down.[oracle@ol6 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:59:47 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance.SQL> startupERROR:ORA-01017: invalid username/password; logon denied
当重启数据库的时候会遇到一些障碍，这里居然报无效的用户名和密码。这是因为我们修改了线程模式导致的，在这个时候我们需要通过先sqlplus /nolog，然后在conn的方式进行连接。
[oracle@ol6 ~]$ sqlplus /nologSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:02:15 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.SQL> conn sys as sysdbaEnter password: Connected.SQL> shutdown immediate; ORA-01507: database not mountedORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 629145600 bytesFixed Size 8795760 bytesVariable Size 322963856 bytesDatabase Buffers 293601280 bytesRedo Buffers 3784704 bytesDatabase mounted.Database opened.SQL> show parameter threadedNAME TYPE VALUE------------------------------------ ----------- ------------------------------threaded_execution boolean TRUE
3.修改完参数之后查看进程状态。
后台进程的数量减少了。一些后台进程（pmon，dbw，lgwr，psp，vktm）的行为与以前一样。奇怪的是居然smon也没了。其他backgtound进程属于名为ora_uxxx_ 的多线程进程。
[root@ol6 ~]# ps -ef | grep cdb1oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1oracle 25244 1 0 16:03 ? 00:00:00 ora_vktm_cdb1oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1oracle 25255 1 21 16:03 ? 00:00:14 ora_u006_cdb1oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1oracle 25270 1 0 16:03 ? 00:00:00 ora_lgwr_cdb1root 25629 24458 0 16:04 pts/1 00:00:00 grep cdb1
4.通过系统视图查看进程状态。
这里我们可以发现execution_type,一部分已经变成了THREAD,我们的SMON也变成了THREAD状态。
SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid;SPID STID PNAME PROGRAM EXECUTION_------------------------ ------------------------ ----- ------------------------------------------------ ---------- PSEUDO NONE25236 25236 PMON oracle@ol6.localdomain (PMON) PROCESS25242 25242 PSP0 oracle@ol6.localdomain (PSP0) PROCESS25244 25244 VKTM oracle@ol6.localdomain (VKTM) PROCESS25268 25268 DBW0 oracle@ol6.localdomain (DBW0) PROCESS25238 25238 SCMN oracle@ol6.localdomain (SCMN) THREAD25238 25240 CLMN oracle@ol6.localdomain (CLMN) THREAD25238 25247 GEN0 oracle@ol6.localdomain (GEN0) THREAD25238 25248 MMAN oracle@ol6.localdomain (MMAN) THREAD25238 25262 DBRM oracle@ol6.localdomain (DBRM) THREAD25238 25265 PMAN oracle@ol6.localdomain (PMAN) THREAD25238 25273 CKPT oracle@ol6.localdomain (CKPT) THREAD25238 25274 SMON oracle@ol6.localdomain (SMON) THREAD25238 25278 LREG oracle@ol6.localdomain (LREG) THREAD25251 25251 SCMN oracle@ol6.localdomain (SCMN) THREAD25251 25253 GEN1 oracle@ol6.localdomain (GEN1) THREAD25255 25255 SCMN oracle@ol6.localdomain (SCMN) THREAD25255 25257 DIAG oracle@ol6.localdomain (DIAG) THREAD25255 25263 VKRM oracle@ol6.localdomain (VKRM) THREAD25255 25264 SVCB oracle@ol6.localdomain (SVCB) THREAD25255 25266 DIA0 oracle@ol6.localdomain (DIA0) THREAD25255 25275 SMCO oracle@ol6.localdomain (SMCO) THREAD25255 25276 RECO oracle@ol6.localdomain (RECO) THREAD25255 25277 W000 oracle@ol6.localdomain (W000) THREAD25255 25279 W001 oracle@ol6.localdomain (W001) THREAD25255 25280 PXMN oracle@ol6.localdomain (PXMN) THREAD25255 25282 MMON oracle@ol6.localdomain (MMON) THREAD25255 25283 MMNL oracle@ol6.localdomain (MMNL) THREAD25255 25284 D000 oracle@ol6.localdomain (D000) THREAD25255 25285 S000 oracle@ol6.localdomain (S000) THREAD25255 25286 TMON oracle@ol6.localdomain (TMON) THREAD25255 25287 N000 oracle@ol6.localdomain (N000) THREAD25255 25296 oracle@ol6.localdomain THREAD25255 25297 oracle@ol6.localdomain THREAD25255 25298 oracle@ol6.localdomain THREAD25255 25299 oracle@ol6.localdomain THREAD25255 25300 oracle@ol6.localdomain THREAD25255 25301 oracle@ol6.localdomain THREAD25255 25302 oracle@ol6.localdomain THREAD25255 25303 oracle@ol6.localdomain THREAD25255 25304 oracle@ol6.localdomain THREAD25255 25305 oracle@ol6.localdomain THREAD25255 25306 oracle@ol6.localdomain THREAD25255 25307 oracle@ol6.localdomain THREAD25255 25308 oracle@ol6.localdomain THREAD25255 25309 oracle@ol6.localdomain THREAD25255 25310 oracle@ol6.localdomain THREAD25255 25311 oracle@ol6.localdomain THREAD25255 25312 oracle@ol6.localdomain THREAD25255 25313 oracle@ol6.localdomain THREAD25255 25314 oracle@ol6.localdomain THREAD25255 25315 oracle@ol6.localdomain THREAD25255 25319 TT00 oracle@ol6.localdomain (TT00) THREAD25255 25320 TT01 oracle@ol6.localdomain (TT01) THREAD25255 25321 TT02 oracle@ol6.localdomain (TT02) THREAD25255 25330 oracle@ol6.localdomain THREAD25255 25331 oracle@ol6.localdomain THREAD25255 25332 oracle@ol6.localdomain THREAD25255 25333 oracle@ol6.localdomain THREAD25255 25334 oracle@ol6.localdomain THREAD25255 25335 oracle@ol6.localdomain THREAD25255 25336 oracle@ol6.localdomain THREAD25255 25337 oracle@ol6.localdomain THREAD25255 25338 oracle@ol6.localdomain THREAD25255 25339 oracle@ol6.localdomain THREAD25255 25340 AQPC oracle@ol6.localdomain (AQPC) THREAD25255 25342 P000 oracle@ol6.localdomain (P000) THREAD25255 25343 P001 oracle@ol6.localdomain (P001) THREAD25255 25344 P002 oracle@ol6.localdomain (P002) THREAD25255 25345 P003 oracle@ol6.localdomain (P003) THREAD25255 25491 CJQ0 oracle@ol6.localdomain (CJQ0) THREAD25255 25528 QM02 oracle@ol6.localdomain (QM02) THREAD25255 25530 Q002 oracle@ol6.localdomain (Q002) THREAD25255 25531 Q003 oracle@ol6.localdomain (Q003) THREAD25255 25532 Q004 oracle@ol6.localdomain (Q004) THREAD25255 25533 Q005 oracle@ol6.localdomain (Q005) THREAD25255 25534 Q006 oracle@ol6.localdomain (Q006) THREAD25255 25535 Q007 oracle@ol6.localdomain (Q007) THREAD25255 25536 Q008 oracle@ol6.localdomain (Q008) THREAD25255 25633 W002 oracle@ol6.localdomain (W002) THREAD25259 25259 SCMN oracle@ol6.localdomain (SCMN) THREAD25259 25261 OFSD oracle@ol6.localdomain (OFSD) THREAD25270 25270 SCMN oracle@ol6.localdomain (SCMN) THREAD25270 25272 LGWR oracle@ol6.localdomain (LGWR) THREAD
5.通过监听连接，可以看到仍然是进程模式。
[oracle@ol6 ~]$ sqlplus /nologSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:14:48 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.SQL> connect sys/oracle as sysdbaConnected.SQL> SQL> connect sys/oracle@pdb as sysdbaConnected.[root@ol6 ~]# ps -ef | grep cdb1oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1oracle 25244 1 0 16:03 ? 00:00:06 ora_vktm_cdb1oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1oracle 25255 1 2 16:03 ? 00:00:17 ora_u006_cdb1oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1oracle 25270 1 0 16:03 ? 00:00:00 ora_lgwr_cdb1oracle 27220 1 0 16:15 ? 00:00:00 oraclecdb1 (LOCAL=NO)root 27270 24458 0 16:15 pts/1 00:00:00 grep cdb1
6.如果要通过监听的方式连接变成线程模式，需要修改监听的参数DEDICATED_THROUGH_BROKER_listener-name，并重启监听才行。
[oracle@ol6 admin]$ vi listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol6.localdomain)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )DEDICATED_THROUGH_BROKER_listener-name=ON
7.再次通过监听连接到数据库,发现新连接上的会话已经变成了线程模式。
SQL> select spid from v$process where addr in (select paddr from v$session where sid=28);SPID------------------------25255SQL> /SPID STID PNAME PROGRAM EXECUTION_------------------------ ------------------------ ----- ------------------------------------------------ ---------- PSEUDO NONE25236 25236 PMON oracle@ol6.localdomain (PMON) PROCESS25242 25242 PSP0 oracle@ol6.localdomain (PSP0) PROCESS25244 25244 VKTM oracle@ol6.localdomain (VKTM) PROCESS25268 25268 DBW0 oracle@ol6.localdomain (DBW0) PROCESS27220 27220 oracle@ol6.localdomain PROCESS25238 25238 SCMN oracle@ol6.localdomain (SCMN) THREAD25238 25240 CLMN oracle@ol6.localdomain (CLMN) THREAD25238 25247 GEN0 oracle@ol6.localdomain (GEN0) THREAD25238 25248 MMAN oracle@ol6.localdomain (MMAN) THREAD25238 25262 DBRM oracle@ol6.localdomain (DBRM) THREAD25238 25265 PMAN oracle@ol6.localdomain (PMAN) THREAD25238 25273 CKPT oracle@ol6.localdomain (CKPT) THREAD25238 25274 SMON oracle@ol6.localdomain (SMON) THREAD25238 25278 LREG oracle@ol6.localdomain (LREG) THREAD25251 25251 SCMN oracle@ol6.localdomain (SCMN) THREAD25251 25253 GEN1 oracle@ol6.localdomain (GEN1) THREAD25255 25255 SCMN oracle@ol6.localdomain (SCMN) THREAD25255 25257 DIAG oracle@ol6.localdomain (DIAG) THREAD25255 25263 VKRM oracle@ol6.localdomain (VKRM) THREAD25255 25264 SVCB oracle@ol6.localdomain (SVCB) THREAD25255 25266 DIA0 oracle@ol6.localdomain (DIA0) THREAD25255 25275 SMCO oracle@ol6.localdomain (SMCO) THREAD25255 25276 RECO oracle@ol6.localdomain (RECO) THREAD25255 25280 PXMN oracle@ol6.localdomain (PXMN) THREAD25255 25282 MMON oracle@ol6.localdomain (MMON) THREAD25255 25283 MMNL oracle@ol6.localdomain (MMNL) THREAD25255 25284 D000 oracle@ol6.localdomain (D000) THREAD25255 25285 S000 oracle@ol6.localdomain (S000) THREAD25255 25286 TMON oracle@ol6.localdomain (TMON) THREAD25255 25287 N000 oracle@ol6.localdomain (N000) THREAD25255 25296 oracle@ol6.localdomain THREAD25255 25297 oracle@ol6.localdomain THREAD25255 25300 oracle@ol6.localdomain THREAD25255 25301 oracle@ol6.localdomain THREAD25255 25302 oracle@ol6.localdomain THREAD25255 25304 oracle@ol6.localdomain THREAD25255 25306 oracle@ol6.localdomain THREAD25255 25307 oracle@ol6.localdomain THREAD25255 25308 oracle@ol6.localdomain THREAD25255 25309 oracle@ol6.localdomain THREAD25255 25310 oracle@ol6.localdomain THREAD25255 25311 oracle@ol6.localdomain THREAD25255 25314 oracle@ol6.localdomain THREAD25255 25315 oracle@ol6.localdomain THREAD25255 25319 TT00 oracle@ol6.localdomain (TT00) THREAD25255 25320 TT01 oracle@ol6.localdomain (TT01) THREAD25255 25321 TT02 oracle@ol6.localdomain (TT02) THREAD25255 25330 oracle@ol6.localdomain THREAD25255 25331 oracle@ol6.localdomain THREAD25255 25332 oracle@ol6.localdomain THREAD25255 25333 oracle@ol6.localdomain THREAD25255 25334 oracle@ol6.localdomain THREAD25255 25336 oracle@ol6.localdomain THREAD25255 25337 oracle@ol6.localdomain THREAD25255 25338 oracle@ol6.localdomain THREAD25255 25339 oracle@ol6.localdomain THREAD25255 25340 AQPC oracle@ol6.localdomain (AQPC) THREAD25255 25342 P000 oracle@ol6.localdomain (P000) THREAD25255 25343 P001 oracle@ol6.localdomain (P001) THREAD25255 25344 P002 oracle@ol6.localdomain (P002) THREAD25255 25345 P003 oracle@ol6.localdomain (P003) THREAD25255 25491 CJQ0 oracle@ol6.localdomain (CJQ0) THREAD25255 25528 QM02 oracle@ol6.localdomain (QM02) THREAD25255 25530 Q002 oracle@ol6.localdomain (Q002) THREAD25255 25533 Q005 oracle@ol6.localdomain (Q005) THREAD25255 25535 Q007 oracle@ol6.localdomain (Q007) THREAD25255 26267 W003 oracle@ol6.localdomain (W003) THREAD25255 26842 W004 oracle@ol6.localdomain (W004) THREAD25255 27011 W005 oracle@ol6.localdomain (W005) THREAD25255 27239 W006 oracle@ol6.localdomain (W006) THREAD25259 25259 SCMN oracle@ol6.localdomain (SCMN) THREAD25259 25261 OFSD oracle@ol6.localdomain (OFSD) THREAD25270 25270 SCMN oracle@ol6.localdomain (SCMN) THREAD25270 25272 LGWR oracle@ol6.localdomain (LGWR) THREAD
当然需要注意的一点是，如果在AIX上使用线程模式，需要安装补丁BUG 22226365 – THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASES。
以上所述是小编给大家介绍的Oracle 12c 新特性之多线程数据库操作，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！
