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

QUESTION 9
Which three are direct benefits of the multiprocess, multithreaded architecture of Oracle Database 12c when it is enabled?
A. Reduced logical I/O
B. Reduced virtual memory utilization
C. Improved parallel Execution performance
D. Improved Serial Execution performance
E. Reduced physical I/O
F. Reduced CPU utilization

 

A、 减少逻辑I/O

B、 虚拟内存利用率降低

C、 改进的并行执行性能

D、 改进的串行执行性能

E、 减少物理I/O

F、 CPU利用率降低

Correct Answer: BCF
Section: (none)
Explanation
Explanation/Reference:
Explanation:
* Multiprocess and Multithreaded Oracle Database Systems Multiprocess Oracle Database (also called multiuser Oracle Database) uses several processes to run
different parts of the Oracle Database code and additional Oracle processes for the users—either one process for each connected user or one or more processes shared by multiple users. Most databases are multiuser because a primary advantage of a database is managing data needed by multiple users simultaneously.
Each process in a database instance performs a specific job. By dividing the work of the database and applications into several processes, multiple users and applications can connect to an instance simultaneously while the system gives good performance.
* In previous releases, Oracle processes did not run as threads on UNIX and Linux systems. Starting in Oracle Database 12c, the multithreaded Oracle Database model enables Oracle processes to execute as operating system threads in separate address spaces.

 

 

 

*多进程和多线程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 ,如果不设置该参数，网络进程仍然会以进程模式连接。