Oracle 12c 新特性-多线程数据库

之前我们学习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  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.通过监听连接,可以看到仍然是进程模式。

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

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *