之前我們學習Oracle基礎的時候，有一個概念，叫多進程和多線程。在Unix/Linux等環境下面。數據庫是以多進程的方式運行的，當一個會話連接進來，就會通過監聽，然后在服務器上創建一個進程。而在Windows上面它是以多線程的方式來運行的。一個進程有很多個thread線程。而在12c這個版本上面，Oracle在Unix/Linux平臺上做出了一些改變，引入了多線程的方式。通過參數threaded_execution，我們可以控制數據庫是以多進程方式運行還是以多線程方式運行，默認該參數是false數據庫以多進程方式運行。
1.查看參數的默認值，和數據庫進程狀態.
|
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
|
[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.修改參數重啟數據庫
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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的方式進行連接。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[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_ 的多線程進程。
|
1
2
3
4
5
6
7
8
9
10
11
|
[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狀態。
|
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
|
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 [email protected] (PMON) PROCESS25242 25242 PSP0 [email protected] (PSP0) PROCESS25244 25244 VKTM [email protected] (VKTM) PROCESS25268 25268 DBW0 [email protected] (DBW0) PROCESS25238 25238 SCMN [email protected] (SCMN) THREAD25238 25240 CLMN [email protected] (CLMN) THREAD25238 25247 GEN0 [email protected] (GEN0) THREAD25238 25248 MMAN [email protected] (MMAN) THREAD25238 25262 DBRM [email protected] (DBRM) THREAD25238 25265 PMAN [email protected] (PMAN) THREAD25238 25273 CKPT [email protected] (CKPT) THREAD25238 25274 SMON [email protected] (SMON) THREAD25238 25278 LREG [email protected] (LREG) THREAD25251 25251 SCMN [email protected] (SCMN) THREAD25251 25253 GEN1 [email protected] (GEN1) THREAD25255 25255 SCMN [email protected] (SCMN) THREAD25255 25257 DIAG [email protected] (DIAG) THREAD25255 25263 VKRM [email protected] (VKRM) THREAD25255 25264 SVCB [email protected] (SVCB) THREAD25255 25266 DIA0 [email protected] (DIA0) THREAD25255 25275 SMCO [email protected] (SMCO) THREAD25255 25276 RECO [email protected] (RECO) THREAD25255 25277 W000 [email protected] (W000) THREAD25255 25279 W001 [email protected] (W001) THREAD25255 25280 PXMN [email protected] (PXMN) THREAD25255 25282 MMON [email protected] (MMON) THREAD25255 25283 MMNL [email protected] (MMNL) THREAD25255 25284 D000 [email protected] (D000) THREAD25255 25285 S000 [email protected] (S000) THREAD25255 25286 TMON [email protected] (TMON) THREAD25255 25287 N000 [email protected] (N000) THREAD25255 25296 [email protected] THREAD25255 25297 [email protected] THREAD25255 25298 [email protected] THREAD25255 25299 [email protected] THREAD25255 25300 [email protected] THREAD25255 25301 [email protected] THREAD25255 25302 [email protected] THREAD25255 25303 [email protected] THREAD25255 25304 [email protected] THREAD25255 25305 [email protected] THREAD25255 25306 [email protected] THREAD25255 25307 [email protected] THREAD25255 25308 [email protected] THREAD25255 25309 [email protected] THREAD25255 25310 [email protected] THREAD25255 25311 [email protected] THREAD25255 25312 [email protected] THREAD25255 25313 [email protected] THREAD25255 25314 [email protected] THREAD25255 25315 [email protected] THREAD25255 25319 TT00 [email protected] (TT00) THREAD25255 25320 TT01 [email protected] (TT01) THREAD25255 25321 TT02 [email protected] (TT02) THREAD25255 25330 [email protected] THREAD25255 25331 [email protected] THREAD25255 25332 [email protected] THREAD25255 25333 [email protected] THREAD25255 25334 [email protected] THREAD25255 25335 [email protected] THREAD25255 25336 [email protected] THREAD25255 25337 [email protected] THREAD25255 25338 [email protected] THREAD25255 25339 [email protected] THREAD25255 25340 AQPC [email protected] (AQPC) THREAD25255 25342 P000 [email protected] (P000) THREAD25255 25343 P001 [email protected] (P001) THREAD25255 25344 P002 [email protected] (P002) THREAD25255 25345 P003 [email protected] (P003) THREAD25255 25491 CJQ0 [email protected] (CJQ0) THREAD25255 25528 QM02 [email protected] (QM02) THREAD25255 25530 Q002 [email protected] (Q002) THREAD25255 25531 Q003 [email protected] (Q003) THREAD25255 25532 Q004 [email protected] (Q004) THREAD25255 25533 Q005 [email protected] (Q005) THREAD25255 25534 Q006 [email protected] (Q006) THREAD25255 25535 Q007 [email protected] (Q007) THREAD25255 25536 Q008 [email protected] (Q008) THREAD25255 25633 W002 [email protected] (W002) THREAD25259 25259 SCMN [email protected] (SCMN) THREAD25259 25261 OFSD [email protected] (OFSD) THREAD25270 25270 SCMN [email protected] (SCMN) THREAD25270 25272 LGWR [email protected] (LGWR) THREAD |
5.通過監聽連接，可以看到仍然是進程模式。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
[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，并重啟監聽才行。
|
1
2
3
4
5
6
7
8
9
10
11
|
[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.再次通過監聽連接到數據庫,發現新連接上的會話已經變成了線程模式。
|
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
|
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 [email protected] (PMON) PROCESS25242 25242 PSP0 [email protected] (PSP0) PROCESS25244 25244 VKTM [email protected] (VKTM) PROCESS25268 25268 DBW0 [email protected] (DBW0) PROCESS27220 27220 [email protected] PROCESS25238 25238 SCMN [email protected] (SCMN) THREAD25238 25240 CLMN [email protected] (CLMN) THREAD25238 25247 GEN0 [email protected] (GEN0) THREAD25238 25248 MMAN [email protected] (MMAN) THREAD25238 25262 DBRM [email protected] (DBRM) THREAD25238 25265 PMAN [email protected] (PMAN) THREAD25238 25273 CKPT [email protected] (CKPT) THREAD25238 25274 SMON [email protected] (SMON) THREAD25238 25278 LREG [email protected] (LREG) THREAD25251 25251 SCMN [email protected] (SCMN) THREAD25251 25253 GEN1 [email protected] (GEN1) THREAD25255 25255 SCMN [email protected] (SCMN) THREAD25255 25257 DIAG [email protected] (DIAG) THREAD25255 25263 VKRM [email protected] (VKRM) THREAD25255 25264 SVCB [email protected] (SVCB) THREAD25255 25266 DIA0 [email protected] (DIA0) THREAD25255 25275 SMCO [email protected] (SMCO) THREAD25255 25276 RECO [email protected] (RECO) THREAD25255 25280 PXMN [email protected] (PXMN) THREAD25255 25282 MMON [email protected] (MMON) THREAD25255 25283 MMNL [email protected] (MMNL) THREAD25255 25284 D000 [email protected] (D000) THREAD25255 25285 S000 [email protected] (S000) THREAD25255 25286 TMON [email protected] (TMON) THREAD25255 25287 N000 [email protected] (N000) THREAD25255 25296 [email protected] THREAD25255 25297 [email protected] THREAD25255 25300 [email protected] THREAD25255 25301 [email protected] THREAD25255 25302 [email protected] THREAD25255 25304 [email protected] THREAD25255 25306 [email protected] THREAD25255 25307 [email protected] THREAD25255 25308 [email protected] THREAD25255 25309 [email protected] THREAD25255 25310 [email protected] THREAD25255 25311 [email protected] THREAD25255 25314 [email protected] THREAD25255 25315 [email protected] THREAD25255 25319 TT00 [email protected] (TT00) THREAD25255 25320 TT01 [email protected] (TT01) THREAD25255 25321 TT02 [email protected] (TT02) THREAD25255 25330 [email protected] THREAD25255 25331 [email protected] THREAD25255 25332 [email protected] THREAD25255 25333 [email protected] THREAD25255 25334 [email protected] THREAD25255 25336 [email protected] THREAD25255 25337 [email protected] THREAD25255 25338 [email protected] THREAD25255 25339 [email protected] THREAD25255 25340 AQPC [email protected] (AQPC) THREAD25255 25342 P000 [email protected] (P000) THREAD25255 25343 P001 [email protected] (P001) THREAD25255 25344 P002 [email protected] (P002) THREAD25255 25345 P003 [email protected] (P003) THREAD25255 25491 CJQ0 [email protected] (CJQ0) THREAD25255 25528 QM02 [email protected] (QM02) THREAD25255 25530 Q002 [email protected] (Q002) THREAD25255 25533 Q005 [email protected] (Q005) THREAD25255 25535 Q007 [email protected] (Q007) THREAD25255 26267 W003 [email protected] (W003) THREAD25255 26842 W004 [email protected] (W004) THREAD25255 27011 W005 [email protected] (W005) THREAD25255 27239 W006 [email protected] (W006) THREAD25259 25259 SCMN [email protected] (SCMN) THREAD25259 25261 OFSD [email protected] (OFSD) THREAD25270 25270 SCMN [email protected] (SCMN) THREAD25270 25272 LGWR [email protected] (LGWR) THREAD |
當然需要注意的一點是，如果在AIX上使用線程模式，需要安裝補丁BUG 22226365 – THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASES。
總結
以上所述是小編給大家介紹的Oracle 12c 新特性之多線程數據庫操作，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對服務器之家網站的支持！
