Oracle 12c 新特性之多线程数据库操作

原作者: [db:作者] 来自: [db:来源] 收藏邀请

之前我们学习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 [email protected] (PMON)          PROCESS
25242          25242          PSP0 [email protected] (PSP0)          PROCESS
25244          25244          VKTM [email protected] (VKTM)          PROCESS
25268          25268          DBW0 [email protected] (DBW0)          PROCESS
25238          25238          SCMN [email protected] (SCMN)          THREAD
25238          25240          CLMN [email protected] (CLMN)          THREAD
25238          25247          GEN0 [email protected] (GEN0)          THREAD
25238          25248          MMAN [email protected] (MMAN)          THREAD
25238          25262          DBRM [email protected] (DBRM)          THREAD
25238          25265          PMAN [email protected] (PMAN)          THREAD
25238          25273          CKPT [email protected] (CKPT)          THREAD
25238          25274          SMON [email protected] (SMON)          THREAD
25238          25278          LREG [email protected] (LREG)          THREAD
25251          25251          SCMN [email protected] (SCMN)          THREAD
25251          25253          GEN1 [email protected] (GEN1)          THREAD
25255          25255          SCMN [email protected] (SCMN)          THREAD
25255          25257          DIAG [email protected] (DIAG)          THREAD
25255          25263          VKRM [email protected] (VKRM)          THREAD
25255          25264          SVCB [email protected] (SVCB)          THREAD
25255          25266          DIA0 [email protected] (DIA0)          THREAD
25255          25275          SMCO [email protected] (SMCO)          THREAD
25255          25276          RECO [email protected] (RECO)          THREAD
25255          25277          W000 [email protected] (W000)          THREAD
25255          25279          W001 [email protected] (W001)          THREAD
25255          25280          PXMN [email protected] (PXMN)          THREAD
25255          25282          MMON [email protected] (MMON)          THREAD
25255          25283          MMNL [email protected] (MMNL)          THREAD
25255          25284          D000 [email protected] (D000)          THREAD
25255          25285          S000 [email protected] (S000)          THREAD
25255          25286          TMON [email protected] (TMON)          THREAD
25255          25287          N000 [email protected] (N000)          THREAD
25255          25296             [email protected]              THREAD
25255          25297             [email protected]              THREAD
25255          25298             [email protected]              THREAD
25255          25299             [email protected]              THREAD
25255          25300             [email protected]              THREAD
25255          25301             [email protected]              THREAD
25255          25302             [email protected]              THREAD
25255          25303             [email protected]              THREAD
25255          25304             [email protected]              THREAD
25255          25305             [email protected]              THREAD
25255          25306             [email protected]              THREAD
25255          25307             [email protected]              THREAD
25255          25308             [email protected]              THREAD
25255          25309             [email protected]              THREAD
25255          25310             [email protected]              THREAD
25255          25311             [email protected]              THREAD
25255          25312             [email protected]              THREAD
25255          25313             [email protected]              THREAD
25255          25314             [email protected]              THREAD
25255          25315             [email protected]              THREAD
25255          25319          TT00 [email protected] (TT00)          THREAD
25255          25320          TT01 [email protected] (TT01)          THREAD
25255          25321          TT02 [email protected] (TT02)          THREAD
25255          25330             [email protected]              THREAD
25255          25331             [email protected]              THREAD
25255          25332             [email protected]              THREAD
25255          25333             [email protected]              THREAD
25255          25334             [email protected]              THREAD
25255          25335             [email protected]              THREAD
25255          25336             [email protected]              THREAD
25255          25337             [email protected]              THREAD
25255          25338             [email protected]              THREAD
25255          25339             [email protected]              THREAD
25255          25340          AQPC [email protected] (AQPC)          THREAD
25255          25342          P000 [email protected] (P000)          THREAD
25255          25343          P001 [email protected] (P001)          THREAD
25255          25344          P002 [email protected] (P002)          THREAD
25255          25345          P003 [email protected] (P003)          THREAD
25255          25491          CJQ0 [email protected] (CJQ0)          THREAD
25255          25528          QM02 [email protected] (QM02)          THREAD
25255          25530          Q002 [email protected] (Q002)          THREAD
25255          25531          Q003 [email protected] (Q003)          THREAD
25255          25532          Q004 [email protected] (Q004)          THREAD
25255          25533          Q005 [email protected] (Q005)          THREAD
25255          25534          Q006 [email protected] (Q006)          THREAD
25255          25535          Q007 [email protected] (Q007)          THREAD
25255          25536          Q008 [email protected] (Q008)          THREAD
25255          25633          W002 [email protected] (W002)          THREAD
25259          25259          SCMN [email protected] (SCMN)          THREAD
25259          25261          OFSD [email protected] (OFSD)          THREAD
25270          25270          SCMN [email protected] (SCMN)          THREAD
25270          25272          LGWR [email protected] (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 [email protected] (PMON)          PROCESS
25242          25242          PSP0 [email protected] (PSP0)          PROCESS
25244          25244          VKTM [email protected] (VKTM)          PROCESS
25268          25268          DBW0 [email protected] (DBW0)          PROCESS
27220          27220             [email protected]              PROCESS
25238          25238          SCMN [email protected] (SCMN)          THREAD
25238          25240          CLMN [email protected] (CLMN)          THREAD
25238          25247          GEN0 [email protected] (GEN0)          THREAD
25238          25248          MMAN [email protected] (MMAN)          THREAD
25238          25262          DBRM [email protected] (DBRM)          THREAD
25238          25265          PMAN [email protected] (PMAN)          THREAD
25238          25273          CKPT [email protected] (CKPT)          THREAD
25238          25274          SMON [email protected] (SMON)          THREAD
25238          25278          LREG [email protected] (LREG)          THREAD
25251          25251          SCMN [email protected] (SCMN)          THREAD
25251          25253          GEN1 [email protected] (GEN1)          THREAD
25255          25255          SCMN [email protected] (SCMN)          THREAD
25255          25257          DIAG [email protected] (DIAG)          THREAD
25255          25263          VKRM [email protected] (VKRM)          THREAD
25255          25264          SVCB [email protected] (SVCB)          THREAD
25255          25266          DIA0 [email protected] (DIA0)          THREAD
25255          25275          SMCO [email protected] (SMCO)          THREAD
25255          25276          RECO [email protected] (RECO)          THREAD
25255          25280          PXMN [email protected] (PXMN)          THREAD
25255          25282          MMON [email protected] (MMON)          THREAD
25255          25283          MMNL [email protected] (MMNL)          THREAD
25255          25284          D000 [email protected] (D000)          THREAD
25255          25285          S000 [email protected] (S000)          THREAD
25255          25286          TMON [email protected] (TMON)          THREAD
25255          25287          N000 [email protected] (N000)          THREAD
25255          25296             [email protected]              THREAD
25255          25297             [email protected]              THREAD
25255          25300             [email protected]              THREAD
25255          25301             [email protected]              THREAD
25255          25302             [email protected]              THREAD
25255          25304             [email protected]              THREAD
25255          25306             [email protected]              THREAD
25255          25307             [email protected]              THREAD
25255          25308             [email protected]              THREAD
25255          25309             [email protected]              THREAD
25255          25310             [email protected]              THREAD
25255          25311             [email protected]              THREAD
25255          25314             [email protected]              THREAD
25255          25315             [email protected]              THREAD
25255          25319          TT00 [email protected] (TT00)          THREAD
25255          25320          TT01 [email protected] (TT01)          THREAD
25255          25321          TT02 [email protected] (TT02)          THREAD
25255          25330             [email protected]              THREAD
25255          25331             [email protected]              THREAD
25255          25332             [email protected]              THREAD
25255          25333             [email protected]              THREAD
25255          25334             [email protected]              THREAD
25255          25336             [email protected]              THREAD
25255          25337             [email protected]              THREAD
25255          25338             [email protected]              THREAD
25255          25339             [email protected]              THREAD
25255          25340          AQPC [email protected] (AQPC)          THREAD
25255          25342          P000 [email protected] (P000)          THREAD
25255          25343          P001 [email protected] (P001)          THREAD
25255          25344          P002 [email protected] (P002)          THREAD
25255          25345          P003 [email protected] (P003)          THREAD
25255          25491          CJQ0 [email protected] (CJQ0)          THREAD
25255          25528          QM02 [email protected] (QM02)          THREAD
25255          25530          Q002 [email protected] (Q002)          THREAD
25255          25533          Q005 [email protected] (Q005)          THREAD
25255          25535          Q007 [email protected] (Q007)          THREAD
25255          26267          W003 [email protected] (W003)          THREAD
25255          26842          W004 [email protected] (W004)          THREAD
25255          27011          W005 [email protected] (W005)          THREAD
25255          27239          W006 [email protected] (W006)          THREAD
25259          25259          SCMN [email protected] (SCMN)          THREAD
25259          25261          OFSD [email protected] (OFSD)          THREAD
25270          25270          SCMN [email protected] (SCMN)          THREAD
25270          25272          LGWR [email protected] (LGWR)          THREAD