之前我們學(xué)習(xí)Oracle基礎(chǔ)的時候，有一個概念，叫多進程和多線程。在Unix/Linux等環(huán)境下面。數(shù)據(jù)庫是以多進程的方式運行的，當(dāng)一個會話連接進來，就會通過監(jiān)聽，然后在服務(wù)器上創(chuàng)建一個進程。而在Windows上面它是以多線程的方式來運行的。一個進程有很多個thread線程。而在12c這個版本上面，Oracle在Unix/Linux平臺上做出了一些改變，引入了多線程的方式。通過參數(shù)threaded_execution，我們可以控制數(shù)據(jù)庫是以多進程方式運行還是以多線程方式運行，默認(rèn)該參數(shù)是false數(shù)據(jù)庫以多進程方式運行。
1.查看參數(shù)的默認(rèn)值，和數(shù)據(jù)庫進程狀態(tài).
[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.修改參數(shù)重啟數(shù)據(jù)庫
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
當(dāng)重啟數(shù)據(jù)庫的時候會遇到一些障礙，這里居然報無效的用戶名和密碼。這是因為我們修改了線程模式導(dǎo)致的，在這個時候我們需要通過先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.修改完參數(shù)之后查看進程狀態(tài)。
后臺進程的數(shù)量減少了。一些后臺進程（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.通過系統(tǒng)視圖查看進程狀態(tài)。
這里我們可以發(fā)現(xiàn)execution_type,一部分已經(jīng)變成了THREAD,我們的SMON也變成了THREAD狀態(tài)。
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.通過監(jiān)聽連接，可以看到仍然是進程模式。
[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.如果要通過監(jiān)聽的方式連接變成線程模式，需要修改監(jiān)聽的參數(shù)DEDICATED_THROUGH_BROKER_listener-name，并重啟監(jiān)聽才行。
[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.再次通過監(jiān)聽連接到數(shù)據(jù)庫,發(fā)現(xiàn)新連接上的會話已經(jīng)變成了線程模式。
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
當(dāng)然需要注意的一點是，如果在AIX上使用線程模式，需要安裝補丁BUG 22226365 – THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASES。
總結(jié)
以上所述是小編給大家介紹的Oracle 12c 新特性之多線程數(shù)據(jù)庫操作，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對VeVb武林網(wǎng)網(wǎng)站的支持！
新聞熱點
疑難解答
圖片精選
