Oracle 12c 新特性之多線程數據庫操作

2024-08-29 14:01:10

字體：大中小

來源：轉載

供稿：網友

之前我們學習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 [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.通過監聽連接，可以看到仍然是進程模式。

[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 [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