Oracle 12c 新特性之多線程數(shù)據(jù)庫操作

更新時間：2018年08月10日 09:40:45 投稿：mrr

這篇文章主要介紹了Oracle 12c 新特性之多線程數(shù)據(jù)庫操作，非常不錯，具有一定的參考借鑒價值,需要的朋友可以參考下

之前我們學習Oracle基礎(chǔ)的時候，有一個概念，叫多進程和多線程。在Unix/Linux等環(huán)境下面。數(shù)據(jù)庫是以多進程的方式運行的，當一個會話連接進來，就會通過監(jiān)聽，然后在服務(wù)器上創(chuàng)建一個進程。而在Windows上面它是以多線程的方式來運行的。一個進程有很多個thread線程。而在12c這個版本上面，Oracle在Unix/Linux平臺上做出了一些改變，引入了多線程的方式。通過參數(shù)threaded_execution，我們可以控制數(shù)據(jù)庫是以多進程方式運行還是以多線程方式運行，默認該參數(shù)是false數(shù)據(jù)庫以多進程方式運行。

1.查看參數(shù)的默認值，和數(shù)據(jù)庫進程狀態(tài).

[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.修改參數(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 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

當重啟數(shù)據(jù)庫的時候會遇到一些障礙，這里居然報無效的用戶名和密碼。這是因為我們修改了線程模式導致的，在這個時候我們需要通過先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.修改完參數(shù)之后查看進程狀態(tài)。

后臺進程的數(shù)量減少了。一些后臺進程（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.通過系統(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                      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.通過監(jiān)聽連接，可以看到仍然是進程模式。

[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.如果要通過監(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
------------------------
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。

總結(jié)

以上所述是小編給大家介紹的Oracle 12c 新特性之多線程數(shù)據(jù)庫操作，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持！

您可能感興趣的文章: