QUESTION 158-Oracle Database12c新特性:多线程模式- Multi-threaded Model

Which two are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled? (Choose two.)
A. reduced CPU utilization
B. reduced physical I/O
C. improved Serial Execution performance
D. reduced logical I/O
E. reduced virtual memory utilization
Correct Answer: AE

 

QUESTION 9

https://blog.csdn.net/jisen_huang/article/details/113529713?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522162357915716780262595504%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=162357915716780262595504&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_v2~rank_v29-1-113529713.nonecase&utm_term=+multiprocess&spm=1018.2226.3001.4450

 

*多进程和多线程Oracle数据库系统多进程Oracle数据库（也称为多用户Oracle数据库）使用多个进程来运行

Oracle数据库代码的不同部分以及用户的其他Oracle进程—每个连接的用户一个进程或多个用户共享的一个或多个进程。大多数数据库都是多用户的，因为数据库的一个主要优点是同时管理多个用户所需的数据。

数据库实例中的每个进程执行一个特定的作业。通过将数据库和应用程序的工作划分为多个进程，多个用户和应用程序可以同时连接到一个实例，同时系统性能良好。

*在以前的版本中，Oracle进程没有在UNIX和Linux系统上作为线程运行。从Oracle Database 12c开始，多线程Oracle数据库模型允许Oracle进程作为操作系统线程在不同的地址空间中执行。

在Oracle Database 12c中，Oracle引入了多线程模式，允许在Windows平台之外的Unix、Linux系统使用多线程模式，结合多进程与多线程模式，Oracle可以改进进程管理与性能。

先来看一下多进程与多线程模式下，Oracle后台进程的对比。下图是多进程模式：

下图是多线程模式下，Oracle数据库启动的后台进程：

Multi-process ,Multi-threaded Model 现在作为两种运行模式被支持。如下在Linux系统，缺省的数据库会启动一系列的后台进程：

oracle    1727     1  0 Jul11 ?        00:00:57 ora_pmon_core
oracle    1729     1  0 Jul11 ?        00:01:21 ora_psp0_core
oracle    1731     1  3 Jul11 ?        02:30:26 ora_vktm_core
oracle    1735     1  0 Jul11 ?        00:00:24 ora_gen0_core
oracle    1737     1  0 Jul11 ?        00:00:11 ora_mman_core
oracle    1741     1  0 Jul11 ?        00:00:20 ora_diag_core
oracle    1743     1  0 Jul11 ?        00:00:18 ora_dbrm_core
oracle    1745     1  0 Jul11 ?        00:05:02 ora_dia0_core
oracle    1747     1  0 Jul11 ?        00:00:28 ora_dbw0_core
oracle    1749     1  0 Jul11 ?        00:00:14 ora_lgwr_core
oracle    1751     1  0 Jul11 ?        00:01:54 ora_ckpt_core
oracle    1753     1  0 Jul11 ?        00:00:07 ora_lg00_core
oracle    1755     1  0 Jul11 ?        00:00:04 ora_lg01_core
oracle    1757     1  0 Jul11 ?        00:00:15 ora_smon_core
oracle    1759     1  0 Jul11 ?        00:00:03 ora_reco_core
oracle    1761     1  0 Jul11 ?        00:00:12 ora_lreg_core
oracle    1763     1  0 Jul11 ?        00:04:43 ora_mmon_core
oracle    1765     1  0 Jul11 ?        00:03:31 ora_mmnl_core
oracle    1767     1  0 Jul11 ?        00:00:05 ora_d000_core
oracle    1769     1  0 Jul11 ?        00:00:05 ora_s000_core
oracle    1781     1  0 Jul11 ?        00:00:08 ora_p000_core
oracle    1783     1  0 Jul11 ?        00:00:08 ora_p001_core
oracle    1785     1  0 Jul11 ?        00:00:04 ora_tmon_core
oracle    1787     1  0 Jul11 ?        00:00:15 ora_tt00_core
oracle    1789     1  0 Jul11 ?        00:00:12 ora_smco_core
oracle    1791     1  0 Jul11 ?        00:00:05 ora_fbda_core
oracle    1793     1  0 Jul11 ?        00:00:07 ora_w000_core
oracle    1795     1  0 Jul11 ?        00:00:03 ora_aqpc_core
oracle    1799     1  0 Jul11 ?        00:00:08 ora_p002_core
oracle    1801     1  0 Jul11 ?        00:00:08 ora_p003_core
oracle    1803     1  0 Jul11 ?        00:00:08 ora_p004_core
oracle    1805     1  0 Jul11 ?        00:00:08 ora_p005_core
oracle    1807     1  0 Jul11 ?        00:00:08 ora_p006_core
oracle    1809     1  0 Jul11 ?        00:00:08 ora_p007_core
oracle    1811     1  0 Jul11 ?        00:01:49 ora_cjq0_core
oracle    1813     1  0 Jul11 ?        00:00:04 ora_qm02_core
oracle    1817     1  0 Jul11 ?        00:00:04 ora_q002_core
oracle    1819     1  0 Jul11 ?        00:00:03 ora_q003_core
oracle    1933     1  0 Jul11 ?        00:00:07 ora_w001_core
oracle    1971     1  0 Jul11 ?        00:00:07 ora_w002_core
oracle    3175     1  0 Jul11 ?        00:00:06 ora_w003_core
oracle    4941     1  0 Jul12 ?        00:00:06 ora_w004_core
oracle    9621     1  0 Jul12 ?        00:00:04 ora_w005_core
oracle   23548     1  0 06:00 ?        00:02:17 ora_vkrm_core

从数据库内部也可以看到详细的进程信息：

SQL> select addr,pname,program from v$process
  2  /

ADDR         PNAME PROGRAM
---------------- ----- ------------------------------------------------
00000000C2965BE0       PSEUDO
00000000C2966D40 PMON  oracle@orcl (PMON)
00000000C2967EA0 PSP0  oracle@orcl (PSP0)
00000000C2969000 VKTM  oracle@orcl (VKTM)
00000000C296A160 GEN0  oracle@orcl (GEN0)
00000000C296B2C0 MMAN  oracle@orcl (MMAN)
00000000C296C420 W001  oracle@orcl (W001)
00000000C296D580 DIAG  oracle@orcl (DIAG)
00000000C296E6E0 DBRM  oracle@orcl (DBRM)
00000000C296F840 DIA0  oracle@orcl (DIA0)
00000000C29709A0 DBW0  oracle@orcl (DBW0)

ADDR         PNAME PROGRAM
---------------- ----- ------------------------------------------------
00000000C2971B00 LGWR  oracle@orcl (LGWR)
00000000C2972C60 CKPT  oracle@orcl (CKPT)
00000000C2973DC0 LG00  oracle@orcl (LG00)
00000000C2974F20 LG01  oracle@orcl (LG01)
00000000C2976080 SMON  oracle@orcl (SMON)
00000000C29771E0 RECO  oracle@orcl (RECO)
00000000C2978340 LREG  oracle@orcl (LREG)
00000000C29794A0 MMON  oracle@orcl (MMON)
00000000C297A600 MMNL  oracle@orcl (MMNL)
00000000C297B760 D000  oracle@orcl (D000)
00000000C297C8C0 S000  oracle@orcl (S000)

ADDR         PNAME PROGRAM
---------------- ----- ------------------------------------------------
00000000C297DA20 P000  oracle@orcl (P000)
00000000C297EB80 P001  oracle@orcl (P001)
00000000C297FCE0 TMON  oracle@orcl (TMON)
00000000C2980E40 TT00  oracle@orcl (TT00)
00000000C2981FA0 SMCO  oracle@orcl (SMCO)
00000000C2983100 FBDA  oracle@orcl (FBDA)
00000000C2984260 W000  oracle@orcl (W000)
00000000C29853C0 AQPC  oracle@orcl (AQPC)
00000000C2986520 CJQ0  oracle@orcl (CJQ0)
00000000C2987680 P002  oracle@orcl (P002)
00000000C29887E0 P003  oracle@orcl (P003)

ADDR         PNAME PROGRAM
---------------- ----- ------------------------------------------------
00000000C2989940 P004  oracle@orcl (P004)
00000000C298AAA0 P005  oracle@orcl (P005)
00000000C298BC00 P006  oracle@orcl (P006)
00000000C298CD60 P007  oracle@orcl (P007)
00000000C298DEC0 QM02  oracle@orcl (QM02)
00000000C298F020 W002  oracle@orcl (W002)
00000000C2990180 Q002  oracle@orcl (Q002)
00000000C29912E0 Q003  oracle@orcl (Q003)
00000000C2992440 VKRM  oracle@orcl (VKRM)
00000000C29935A0       oracle@orcl (TNS V1-V3)
00000000C2994700 W003  oracle@orcl (W003)
00000000C2995860 W004  oracle@orcl (W004)
00000000C29969C0 W005  oracle@orcl (W005)

通过设置初始化参数 threaded_execution ，可以启用或关闭多线程模式，该参数缺省值为False，设置为TRUE启用12c的这个新特性：

SQL> show parameter threaded_exec

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
threaded_execution             boolean     FALSE
SQL> alter system set threaded_execution=true scope=spfile;

System altered.

该参数重新启动数据库后生效，但是注意，多线程模式，不支持操作系统认证，不能直接启动数据库，需要提供SYS的密码认证后方能启动数据库：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01017: invalid username/password; logon denied

以下通过用户名和密码登录数据库：

[oracle@orcl dbs]$ sqlplus sys/oracle as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 14 17:05:54 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col program for a40
SQL> select addr,pname,program from v$process;

ADDR         PNAME        PROGRAM
---------------- ---------- ----------------------------------------
00000000C2965BE0        PSEUDO
00000000C2966D40 PMON        oracle@orcl (PMON)
00000000C2967EA0 PSP0        oracle@orcl (PSP0)
00000000C2969000 VKTM        oracle@orcl (VKTM)
00000000C296A160 GEN0        oracle@orcl (GEN0)

现在在操作系统上启动的进程只有5个（PMON / PSP0 / VKTM / DBW0 / U004 / U005）：

[oracle@orcl dbs]$ ps -ef|grep ora_
oracle   27404     1  0 17:00 ?        00:00:00 ora_pmon_core
oracle   27406     1  0 17:00 ?        00:00:00 ora_psp0_core
oracle   27408     1  3 17:00 ?        00:00:05 ora_vktm_core
oracle   27412     1  0 17:00 ?        00:00:00 ora_u004_core
oracle   27418     1  0 17:00 ?        00:00:00 ora_u005_core
oracle   27424     1  0 17:00 ?        00:00:00 ora_dbw0_core

其中U<NNN>进程是共享线程的"容器进程"，每个进程可以容纳100个线程：

SQL> select spid,count(*) from v$process group by spid;

SPID               COUNT(*)
------------------------ ----------
                  1
28278                  1
28284                 10
28586                100
28806                 58
28290                100
28296                  1
28280                  1
28276                  1

启用该参数之后，需要设置监听器参数 DEDICATED_THROUGH_BROKER_LISTENER=ON ,如果不设置该参数，网络进程仍然会以进程模式连接。