之前我們學習Oracle基礎的時候，有一個概念，叫多進程和多線程。在Unix/Linux等環境下面。數據庫是以多進程的方式運行的，當一個會話連接進來，就會通過監聽，然后在服務器上創建一個進程。而在Windows上面它是以多線程的方式來運行的。一個進程有很多個thread線程。而在12c這個版本上面，Oracle在Unix/Linux平臺上做出了一些改變，引入了多線程的方式。通過參數threaded_execution，我們可以控制數據庫是以多進程方式運行還是以多線程方式運行，默認該參數是false數據庫以多進程方式運行。
1.查看參數的默認值，和數據庫進程狀態.
[oracle@ol6 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:57:59 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show parameter threaded
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
threaded_execution boolean FALSE
[root@ol6 ~]# ps -ef | grep cdb1
oracle 1773 1 0 Aug02 ? 00:00:04 ora_pmon_cdb1
oracle 1775 1 0 Aug02 ? 00:00:01 ora_clmn_cdb1
oracle 1777 1 0 Aug02 ? 00:00:13 ora_psp0_cdb1
oracle 1786 1 0 Aug02 ? 00:17:01 ora_vktm_cdb1
oracle 1790 1 0 Aug02 ? 00:00:08 ora_gen0_cdb1
oracle 1792 1 0 Aug02 ? 00:00:01 ora_mman_cdb1
oracle 1796 1 0 Aug02 ? 00:00:19 ora_gen1_cdb1
oracle 1800 1 0 Aug02 ? 00:00:03 ora_diag_cdb1
oracle 1802 1 0 Aug02 ? 00:00:01 ora_ofsd_cdb1
oracle 1806 1 0 Aug02 ? 00:00:29 ora_dbrm_cdb1
oracle 1808 1 0 Aug02 ? 00:01:14 ora_vkrm_cdb1
oracle 1810 1 0 Aug02 ? 00:00:03 ora_svcb_cdb1
oracle 1812 1 0 Aug02 ? 00:00:10 ora_pman_cdb1
oracle 1814 1 0 Aug02 ? 00:00:48 ora_dia0_cdb1
oracle 1816 1 0 Aug02 ? 00:00:08 ora_dbw0_cdb1
oracle 1818 1 0 Aug02 ? 00:00:10 ora_lgwr_cdb1
oracle 1820 1 0 Aug02 ? 00:00:18 ora_ckpt_cdb1
oracle 1822 1 0 Aug02 ? 00:00:01 ora_smon_cdb1
oracle 1824 1 0 Aug02 ? 00:00:04 ora_smco_cdb1
oracle 1826 1 0 Aug02 ? 00:00:00 ora_reco_cdb1
oracle 1830 1 0 Aug02 ? 00:00:03 ora_lreg_cdb1
oracle 1834 1 0 Aug02 ? 00:00:01 ora_pxmn_cdb1
oracle 1838 1 0 Aug02 ? 00:00:20 ora_mmon_cdb1
oracle 1840 1 0 Aug02 ? 00:00:26 ora_mmnl_cdb1
oracle 1842 1 0 Aug02 ? 00:00:00 ora_d000_cdb1
oracle 1844 1 0 Aug02 ? 00:00:00 ora_s000_cdb1
oracle 1846 1 0 Aug02 ? 00:00:00 ora_tmon_cdb1
oracle 1869 1 0 Aug02 ? 00:00:00 ora_tt00_cdb1
oracle 1871 1 0 Aug02 ? 00:00:00 ora_tt01_cdb1
oracle 1873 1 0 Aug02 ? 00:00:02 ora_tt02_cdb1
oracle 1875 1 0 Aug02 ? 00:00:00 ora_aqpc_cdb1
oracle 1879 1 0 Aug02 ? 00:00:02 ora_p000_cdb1
oracle 1881 1 0 Aug02 ? 00:00:02 ora_p001_cdb1
oracle 1883 1 0 Aug02 ? 00:00:02 ora_p002_cdb1
oracle 1885 1 0 Aug02 ? 00:00:02 ora_p003_cdb1
oracle 2039 1 0 Aug02 ? 00:02:36 ora_cjq0_cdb1
oracle 2109 1 0 Aug02 ? 00:00:01 ora_qm02_cdb1
oracle 2113 1 0 Aug02 ? 00:00:00 ora_q002_cdb1
oracle 2120 1 0 Aug02 ? 00:00:02 ora_q005_cdb1
oracle 24076 1 0 15:56 ? 00:00:00 ora_w000_cdb1
oracle 24149 1 0 15:56 ? 00:00:00 ora_q003_cdb1
oracle 24154 1 0 15:56 ? 00:00:00 ora_q004_cdb1
oracle 24161 1 0 15:56 ? 00:00:00 ora_q006_cdb1
oracle 24165 1 0 15:56 ? 00:00:00 ora_w001_cdb1
oracle 24317 1 0 15:57 ? 00:00:00 ora_w002_cdb1
oracle 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 cdb1
oracle 28778 1 0 Aug02 ? 00:00:01 ora_q001_cdb1
oracle 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 sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:59:47 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ERROR:
ORA-01017: invalid username/password; logon denied
當重啟數據庫的時候會遇到一些障礙，這里居然報無效的用戶名和密碼。這是因為我們修改了線程模式導致的，在這個時候我們需要通過先sqlplus /nolog，然后在conn的方式進行連接。
[oracle@ol6 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:02:15 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 629145600 bytes
Fixed Size 8795760 bytes
Variable Size 322963856 bytes
Database Buffers 293601280 bytes
Redo Buffers 3784704 bytes
Database mounted.
Database opened.
SQL> show parameter threaded
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
threaded_execution boolean TRUE
3.修改完參數之后查看進程狀態。
后臺進程的數量減少了。一些后臺進程（pmon，dbw，lgwr，psp，vktm）的行為與以前一樣。奇怪的是居然smon也沒了。其他backgtound進程屬于名為ora_uxxx_ 的多線程進程。
[root@ol6 ~]# ps -ef | grep cdb1
oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1
oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1
oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1
oracle 25244 1 0 16:03 ? 00:00:00 ora_vktm_cdb1
oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1
oracle 25255 1 21 16:03 ? 00:00:14 ora_u006_cdb1
oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1
oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1
oracle 25270 1 0 16:03 ? 00:00:00 ora_lgwr_cdb1
root 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 NONE
25236 25236 PMON oracle@ol6.localdomain (PMON) PROCESS
25242 25242 PSP0 oracle@ol6.localdomain (PSP0) PROCESS
25244 25244 VKTM oracle@ol6.localdomain (VKTM) PROCESS
25268 25268 DBW0 oracle@ol6.localdomain (DBW0) PROCESS
25238 25238 SCMN oracle@ol6.localdomain (SCMN) THREAD
25238 25240 CLMN oracle@ol6.localdomain (CLMN) THREAD
25238 25247 GEN0 oracle@ol6.localdomain (GEN0) THREAD
25238 25248 MMAN oracle@ol6.localdomain (MMAN) THREAD
25238 25262 DBRM oracle@ol6.localdomain (DBRM) THREAD
25238 25265 PMAN oracle@ol6.localdomain (PMAN) THREAD
25238 25273 CKPT oracle@ol6.localdomain (CKPT) THREAD
25238 25274 SMON oracle@ol6.localdomain (SMON) THREAD
25238 25278 LREG oracle@ol6.localdomain (LREG) THREAD
25251 25251 SCMN oracle@ol6.localdomain (SCMN) THREAD
25251 25253 GEN1 oracle@ol6.localdomain (GEN1) THREAD
25255 25255 SCMN oracle@ol6.localdomain (SCMN) THREAD
25255 25257 DIAG oracle@ol6.localdomain (DIAG) THREAD
25255 25263 VKRM oracle@ol6.localdomain (VKRM) THREAD
25255 25264 SVCB oracle@ol6.localdomain (SVCB) THREAD
25255 25266 DIA0 oracle@ol6.localdomain (DIA0) THREAD
25255 25275 SMCO oracle@ol6.localdomain (SMCO) THREAD
25255 25276 RECO oracle@ol6.localdomain (RECO) THREAD
25255 25277 W000 oracle@ol6.localdomain (W000) THREAD
25255 25279 W001 oracle@ol6.localdomain (W001) THREAD
25255 25280 PXMN oracle@ol6.localdomain (PXMN) THREAD
25255 25282 MMON oracle@ol6.localdomain (MMON) THREAD
25255 25283 MMNL oracle@ol6.localdomain (MMNL) THREAD
25255 25284 D000 oracle@ol6.localdomain (D000) THREAD
25255 25285 S000 oracle@ol6.localdomain (S000) THREAD
25255 25286 TMON oracle@ol6.localdomain (TMON) THREAD
25255 25287 N000 oracle@ol6.localdomain (N000) THREAD
25255 25296 oracle@ol6.localdomain THREAD
25255 25297 oracle@ol6.localdomain THREAD
25255 25298 oracle@ol6.localdomain THREAD
25255 25299 oracle@ol6.localdomain THREAD
25255 25300 oracle@ol6.localdomain THREAD
25255 25301 oracle@ol6.localdomain THREAD
25255 25302 oracle@ol6.localdomain THREAD
25255 25303 oracle@ol6.localdomain THREAD
25255 25304 oracle@ol6.localdomain THREAD
25255 25305 oracle@ol6.localdomain THREAD
25255 25306 oracle@ol6.localdomain THREAD
25255 25307 oracle@ol6.localdomain THREAD
25255 25308 oracle@ol6.localdomain THREAD
25255 25309 oracle@ol6.localdomain THREAD
25255 25310 oracle@ol6.localdomain THREAD
25255 25311 oracle@ol6.localdomain THREAD
25255 25312 oracle@ol6.localdomain THREAD
25255 25313 oracle@ol6.localdomain THREAD
25255 25314 oracle@ol6.localdomain THREAD
25255 25315 oracle@ol6.localdomain THREAD
25255 25319 TT00 oracle@ol6.localdomain (TT00) THREAD
25255 25320 TT01 oracle@ol6.localdomain (TT01) THREAD
25255 25321 TT02 oracle@ol6.localdomain (TT02) THREAD
25255 25330 oracle@ol6.localdomain THREAD
25255 25331 oracle@ol6.localdomain THREAD
25255 25332 oracle@ol6.localdomain THREAD
25255 25333 oracle@ol6.localdomain THREAD
25255 25334 oracle@ol6.localdomain THREAD
25255 25335 oracle@ol6.localdomain THREAD
25255 25336 oracle@ol6.localdomain THREAD
25255 25337 oracle@ol6.localdomain THREAD
25255 25338 oracle@ol6.localdomain THREAD
25255 25339 oracle@ol6.localdomain THREAD
25255 25340 AQPC oracle@ol6.localdomain (AQPC) THREAD
25255 25342 P000 oracle@ol6.localdomain (P000) THREAD
25255 25343 P001 oracle@ol6.localdomain (P001) THREAD
25255 25344 P002 oracle@ol6.localdomain (P002) THREAD
25255 25345 P003 oracle@ol6.localdomain (P003) THREAD
25255 25491 CJQ0 oracle@ol6.localdomain (CJQ0) THREAD
25255 25528 QM02 oracle@ol6.localdomain (QM02) THREAD
25255 25530 Q002 oracle@ol6.localdomain (Q002) THREAD
25255 25531 Q003 oracle@ol6.localdomain (Q003) THREAD
25255 25532 Q004 oracle@ol6.localdomain (Q004) THREAD
25255 25533 Q005 oracle@ol6.localdomain (Q005) THREAD
25255 25534 Q006 oracle@ol6.localdomain (Q006) THREAD
25255 25535 Q007 oracle@ol6.localdomain (Q007) THREAD
25255 25536 Q008 oracle@ol6.localdomain (Q008) THREAD
25255 25633 W002 oracle@ol6.localdomain (W002) THREAD
25259 25259 SCMN oracle@ol6.localdomain (SCMN) THREAD
25259 25261 OFSD oracle@ol6.localdomain (OFSD) THREAD
25270 25270 SCMN oracle@ol6.localdomain (SCMN) THREAD
25270 25272 LGWR oracle@ol6.localdomain (LGWR) THREAD
5.通過監聽連接，可以看到仍然是進程模式。
[oracle@ol6 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:14:48 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> connect sys/oracle as sysdba
Connected.
SQL>
SQL> connect sys/oracle@pdb as sysdba
Connected.
[root@ol6 ~]# ps -ef | grep cdb1
oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1
oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1
oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1
oracle 25244 1 0 16:03 ? 00:00:06 ora_vktm_cdb1
oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1
oracle 25255 1 2 16:03 ? 00:00:17 ora_u006_cdb1
oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1
oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1
oracle 25270 1 0 16:03 ? 00:00:00 ora_lgwr_cdb1
oracle 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
------------------------
25255
SQL> /
SPID STID PNAME PROGRAM EXECUTION_
------------------------ ------------------------ ----- ------------------------------------------------ ----------
PSEUDO NONE
25236 25236 PMON oracle@ol6.localdomain (PMON) PROCESS
25242 25242 PSP0 oracle@ol6.localdomain (PSP0) PROCESS
25244 25244 VKTM oracle@ol6.localdomain (VKTM) PROCESS
25268 25268 DBW0 oracle@ol6.localdomain (DBW0) PROCESS
27220 27220 oracle@ol6.localdomain PROCESS
25238 25238 SCMN oracle@ol6.localdomain (SCMN) THREAD
25238 25240 CLMN oracle@ol6.localdomain (CLMN) THREAD
25238 25247 GEN0 oracle@ol6.localdomain (GEN0) THREAD
25238 25248 MMAN oracle@ol6.localdomain (MMAN) THREAD
25238 25262 DBRM oracle@ol6.localdomain (DBRM) THREAD
25238 25265 PMAN oracle@ol6.localdomain (PMAN) THREAD
25238 25273 CKPT oracle@ol6.localdomain (CKPT) THREAD
25238 25274 SMON oracle@ol6.localdomain (SMON) THREAD
25238 25278 LREG oracle@ol6.localdomain (LREG) THREAD
25251 25251 SCMN oracle@ol6.localdomain (SCMN) THREAD
25251 25253 GEN1 oracle@ol6.localdomain (GEN1) THREAD
25255 25255 SCMN oracle@ol6.localdomain (SCMN) THREAD
25255 25257 DIAG oracle@ol6.localdomain (DIAG) THREAD
25255 25263 VKRM oracle@ol6.localdomain (VKRM) THREAD
25255 25264 SVCB oracle@ol6.localdomain (SVCB) THREAD
25255 25266 DIA0 oracle@ol6.localdomain (DIA0) THREAD
25255 25275 SMCO oracle@ol6.localdomain (SMCO) THREAD
25255 25276 RECO oracle@ol6.localdomain (RECO) THREAD
25255 25280 PXMN oracle@ol6.localdomain (PXMN) THREAD
25255 25282 MMON oracle@ol6.localdomain (MMON) THREAD
25255 25283 MMNL oracle@ol6.localdomain (MMNL) THREAD
25255 25284 D000 oracle@ol6.localdomain (D000) THREAD
25255 25285 S000 oracle@ol6.localdomain (S000) THREAD
25255 25286 TMON oracle@ol6.localdomain (TMON) THREAD
25255 25287 N000 oracle@ol6.localdomain (N000) THREAD
25255 25296 oracle@ol6.localdomain THREAD
25255 25297 oracle@ol6.localdomain THREAD
25255 25300 oracle@ol6.localdomain THREAD
25255 25301 oracle@ol6.localdomain THREAD
25255 25302 oracle@ol6.localdomain THREAD
25255 25304 oracle@ol6.localdomain THREAD
25255 25306 oracle@ol6.localdomain THREAD
25255 25307 oracle@ol6.localdomain THREAD
25255 25308 oracle@ol6.localdomain THREAD
25255 25309 oracle@ol6.localdomain THREAD
25255 25310 oracle@ol6.localdomain THREAD
25255 25311 oracle@ol6.localdomain THREAD
25255 25314 oracle@ol6.localdomain THREAD
25255 25315 oracle@ol6.localdomain THREAD
25255 25319 TT00 oracle@ol6.localdomain (TT00) THREAD
25255 25320 TT01 oracle@ol6.localdomain (TT01) THREAD
25255 25321 TT02 oracle@ol6.localdomain (TT02) THREAD
25255 25330 oracle@ol6.localdomain THREAD
25255 25331 oracle@ol6.localdomain THREAD
25255 25332 oracle@ol6.localdomain THREAD
25255 25333 oracle@ol6.localdomain THREAD
25255 25334 oracle@ol6.localdomain THREAD
25255 25336 oracle@ol6.localdomain THREAD
25255 25337 oracle@ol6.localdomain THREAD
25255 25338 oracle@ol6.localdomain THREAD
25255 25339 oracle@ol6.localdomain THREAD
25255 25340 AQPC oracle@ol6.localdomain (AQPC) THREAD
25255 25342 P000 oracle@ol6.localdomain (P000) THREAD
25255 25343 P001 oracle@ol6.localdomain (P001) THREAD
25255 25344 P002 oracle@ol6.localdomain (P002) THREAD
25255 25345 P003 oracle@ol6.localdomain (P003) THREAD
25255 25491 CJQ0 oracle@ol6.localdomain (CJQ0) THREAD
25255 25528 QM02 oracle@ol6.localdomain (QM02) THREAD
25255 25530 Q002 oracle@ol6.localdomain (Q002) THREAD
25255 25533 Q005 oracle@ol6.localdomain (Q005) THREAD
25255 25535 Q007 oracle@ol6.localdomain (Q007) THREAD
25255 26267 W003 oracle@ol6.localdomain (W003) THREAD
25255 26842 W004 oracle@ol6.localdomain (W004) THREAD
25255 27011 W005 oracle@ol6.localdomain (W005) THREAD
25255 27239 W006 oracle@ol6.localdomain (W006) THREAD
25259 25259 SCMN oracle@ol6.localdomain (SCMN) THREAD
25259 25261 OFSD oracle@ol6.localdomain (OFSD) THREAD
25270 25270 SCMN oracle@ol6.localdomain (SCMN) THREAD
25270 25272 LGWR oracle@ol6.localdomain (LGWR) THREAD
當然需要注意的一點是，如果在AIX上使用線程模式，需要安裝補丁BUG 22226365 – THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASES。
總結
以上所述是小編給大家介紹的Oracle 12c 新特性之多線程數據庫操作，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！
您可能感興趣的文章:- Oracle 12c新特性之如何檢測有用的多列統計信息詳解
- Windows Sever 2012下Oracle 12c安裝配置方法圖文教程
- oracle 12c創建可插拔數據庫（PDB）與用戶詳解
- Linux下安裝Oracle（CentOS-Oracle 12c）的方法
- ORACLE 12C PDB 維護基礎知識介紹
- win x64下安裝oracle 12c出現INS-30131報錯的解決方法
- Oracle12c的數據庫向11g導進的方法
- oracle12c安裝報錯：PRVF-0002的解決方法
