search 2013 adfgs
作者:Sky.Jian | 可以任意转载, 但转载时务必以超链接形式标明文章原始出处 和 作者信息 及 版权声明
链接:http://isky000.com/database/oracle-12c-multi-thread | del.icio.us | Twitter it

在Oracle Database 12c中,Oracle引入了多线程模式,允许在Windows平台之外的Unix、Linux系统使用多线程模式,结合多进程与多线程模式,Oracle可以改进进程管理与性能。 先来看一下多进程与多线程模式下,Oracle后台进程的对比。下图是多进程模式: MultiProcessOracle.png 下图是多线程模式下,Oracle数据库启动的后台进程: MultiThreadOracle.png 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@enmocoredb (PMON) 00000000C2967EA0 PSP0  oracle@enmocoredb (PSP0) 00000000C2969000 VKTM  oracle@enmocoredb (VKTM) 00000000C296A160 GEN0  oracle@enmocoredb (GEN0) 00000000C296B2C0 MMAN  oracle@enmocoredb (MMAN) 00000000C296C420 W001  oracle@enmocoredb (W001) 00000000C296D580 DIAG  oracle@enmocoredb (DIAG) 00000000C296E6E0 DBRM  oracle@enmocoredb (DBRM) 00000000C296F840 DIA0  oracle@enmocoredb (DIA0) 00000000C29709A0 DBW0  oracle@enmocoredb (DBW0) ADDR         PNAME PROGRAM —————- —– ———————————————— 00000000C2971B00 LGWR  oracle@enmocoredb (LGWR) 00000000C2972C60 CKPT  oracle@enmocoredb (CKPT) 00000000C2973DC0 LG00  oracle@enmocoredb (LG00) 00000000C2974F20 LG01  oracle@enmocoredb (LG01) 00000000C2976080 SMON  oracle@enmocoredb (SMON) 00000000C29771E0 RECO  oracle@enmocoredb (RECO) 00000000C2978340 LREG  oracle@enmocoredb (LREG) 00000000C29794A0 MMON  oracle@enmocoredb (MMON) 00000000C297A600 MMNL  oracle@enmocoredb (MMNL) 00000000C297B760 D000  oracle@enmocoredb (D000) 00000000C297C8C0 S000  oracle@enmocoredb (S000) ADDR         PNAME PROGRAM —————- —– ———————————————— 00000000C297DA20 P000  oracle@enmocoredb (P000) 00000000C297EB80 P001  oracle@enmocoredb (P001) 00000000C297FCE0 TMON  oracle@enmocoredb (TMON) 00000000C2980E40 TT00  oracle@enmocoredb (TT00) 00000000C2981FA0 SMCO  oracle@enmocoredb (SMCO) 00000000C2983100 FBDA  oracle@enmocoredb (FBDA) 00000000C2984260 W000  oracle@enmocoredb (W000) 00000000C29853C0 AQPC  oracle@enmocoredb (AQPC) 00000000C2986520 CJQ0  oracle@enmocoredb (CJQ0) 00000000C2987680 P002  oracle@enmocoredb (P002) 00000000C29887E0 P003  oracle@enmocoredb (P003) ADDR         PNAME PROGRAM —————- —– ———————————————— 00000000C2989940 P004  oracle@enmocoredb (P004) 00000000C298AAA0 P005  oracle@enmocoredb (P005) 00000000C298BC00 P006  oracle@enmocoredb (P006) 00000000C298CD60 P007  oracle@enmocoredb (P007) 00000000C298DEC0 QM02  oracle@enmocoredb (QM02) 00000000C298F020 W002  oracle@enmocoredb (W002) 00000000C2990180 Q002  oracle@enmocoredb (Q002) 00000000C29912E0 Q003  oracle@enmocoredb (Q003) 00000000C2992440 VKRM  oracle@enmocoredb (VKRM) 00000000C29935A0       oracle@enmocoredb (TNS V1-V3) 00000000C2994700 W003  oracle@enmocoredb (W003) 00000000C2995860 W004  oracle@enmocoredb (W004) 00000000C29969C0 W005  oracle@enmocoredb (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@enmocoredb 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@enmocoredb (PMON) 00000000C2967EA0 PSP0        oracle@enmocoredb (PSP0) 00000000C2969000 VKTM        oracle@enmocoredb (VKTM) 00000000C296A160 GEN0        oracle@enmocoredb (GEN0)

现在在操作系统上启动的进程只有5个(PMON / PSP0 / VKTM / DBW0 / U004 / U005):

[oracle@enmocoredb 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 ,如果不设置该参数,网络进程仍然会以进程模式连接。

看完了要说点啥么?