Oracle12c Database Multithreaded model Option
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. When Oracle Database 12c is installed, the database runs in process mode. You must set a parameter to run the database in threaded mode. In threaded mode, some background processes on UNIX and Linux run as processes (processes containing one thread), whereas the remaining Oracle processes run as threads within processes. Oracle in Windows is always multithreaded ( one process and many threads) Oracle in Unix/Linux by default uses multiprocess model. For example [oracle@ol6-o12c admin]$ ps -ef|grep ASM oracle 3025 1 0 08:24 ? 00:00:00 asm_pmon_+ASM oracle 3027 1 0 08:24 ? 00:00:00 asm_psp0_+ASM oracle 3029 1 6 08:24 ? 00:00:23 asm_vktm_+ASM oracle 3033 1 0 08:24 ? 00:00:00 asm_gen0_+ASM oracle 3035 1 0 08:24 ? 00:00:00 asm_mman_+ASM oracle 3039 1 0 08:24 ? 00:00:00 asm_diag_+ASM oracle 3041 1 0 08:24 ? 00:00:00 asm_dia0_+ASM oracle 3043 1 0 08:24 ? 00:00:00 asm_dbw0_+ASM oracle 3045 1 0 08:24 ? 00:00:00 asm_lgwr_+ASM oracle 3047 1 0 08:24 ? 00:00:00 asm_ckpt_+ASM oracle 3049 1 0 08:24 ? 00:00:00 asm_smon_+ASM oracle 3051 1 0 08:24 ? 00:00:00 asm_lreg_+ASM oracle 3053 1 0 08:24 ? 00:00:00 asm_rbal_+ASM oracle 3055 1 0 08:24 ? 00:00:00 asm_gmon_+ASM oracle 3057 1 0 08:24 ? 00:00:00 asm_mmon_+ASM oracle 3059 1 0 08:24 ? 00:00:00 asm_mmnl_+ASM oracle 3137 1 0 08:24 ? (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
http://www.hendrydatabase.blogspot.com.au 1
00:00:00 oracle+ASM
oracle 3162 1 0 08:25 ? 00:00:00 asm_asmb_+ASM oracle 3164 1 0 08:25 ? 00:00:00 oracle+ASM_asmb_+asm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 3166 1 0 08:25 ? 00:00:00 asm_o000_+ASM oracle 3168 1 0 08:25 ? 00:00:00 oracle+ASM_o000_+asm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4651 1 0 08:28 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4688 1 0 08:28 ? 00:00:00 oracle+ASM_ocf0_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4691 1 0 08:28 ? 00:00:00 oracle+ASM_o000_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4695 1 0 08:28 ? 00:00:00 oracle+ASM_o001_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 5558 1 2 08:30 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 5560 2861 0 08:30 pts/0 00:00:00 grep ASM [oracle@ol6-o12c admin]$ ps -ef|grep ASM |wc -l 27 [oracle@ol6-o12c ~]$ ps -ef|grep cdbo12c oracle 4606 1 0 08:28 ? 00:00:00 ora_pmon_cdbo12c oracle 4608 1 0 08:28 ? 00:00:00 ora_psp0_cdbo12c oracle 4613 1 6 08:28 ? 00:00:10 ora_vktm_cdbo12c oracle 4617 1 0 08:28 ? 00:00:00 ora_gen0_cdbo12c oracle 4619 1 0 08:28 ? 00:00:00 ora_mman_cdbo12c oracle 4623 1 0 08:28 ? 00:00:00 ora_diag_cdbo12c oracle 4628 1 0 08:28 ? 00:00:00 ora_dbrm_cdbo12c oracle 4630 1 0 08:28 ? 00:00:00 ora_dia0_cdbo12c oracle 4632 1 0 08:28 ? 00:00:00 ora_dbw0_cdbo12c oracle 4634 1 0 08:28 ? 00:00:00 ora_lgwr_cdbo12c oracle 4636 1 0 08:28 ? 00:00:00 ora_ckpt_cdbo12c oracle 4638 1 0 08:28 ? 00:00:00 ora_smon_cdbo12c oracle 4640 1 0 08:28 ? 00:00:00 ora_reco_cdbo12c oracle 4642 1 0 08:28 ? 00:00:00 ora_lreg_cdbo12c oracle 4644 1 0 08:28 ? 00:00:00 ora_rbal_cdbo12c oracle 4646 1 0 08:28 ? 00:00:00 ora_asmb_cdbo12c oracle 4648 1 1 08:28 ? 00:00:01 ora_mmon_cdbo12c oracle 4651 1 0 08:28 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4654 1 0 08:28 ? 00:00:00 ora_mmnl_cdbo12c oracle 4657 1 0 08:28 ? 00:00:00 ora_d000_cdbo12c oracle 4659 1 0 08:28 ? 00:00:00 ora_mark_cdbo12c oracle 4661 1 0 08:28 ? 00:00:00 ora_s000_cdbo12c oracle 4667 1 0 08:28 ? 00:00:00 ora_ocf0_cdbo12c oracle 4688 1 0 08:28 ? 00:00:00 oracle+ASM_ocf0_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4689 1 0 08:28 ? 00:00:00 ora_o000_cdbo12c oracle 4691 1 0 08:28 ? 00:00:00 oracle+ASM_o000_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4693 1 0 08:28 ? 00:00:00 ora_o001_cdbo12c
http://www.hendrydatabase.blogspot.com.au 2
oracle 4695 1 0 08:28 ? 00:00:00 oracle+ASM_o001_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 4752 1 0 08:28 ? 00:00:00 ora_tmon_cdbo12c oracle 4757 1 0 08:28 ? 00:00:00 ora_tt00_cdbo12c oracle 4765 1 0 08:28 ? 00:00:00 ora_smco_cdbo12c oracle 4776 1 0 08:28 ? 00:00:00 ora_w000_cdbo12c oracle 4784 1 0 08:28 ? 00:00:00 ora_aqpc_cdbo12c oracle 4800 1 3 08:28 ? 00:00:04 ora_p000_cdbo12c oracle 4802 1 4 08:28 ? 00:00:06 ora_p001_cdbo12c oracle 4804 1 0 08:28 ? 00:00:00 ora_p002_cdbo12c oracle 4806 1 0 08:28 ? 00:00:00 ora_p003_cdbo12c oracle 4869 1 0 08:29 ? 00:00:00 ora_qm02_cdbo12c oracle 4877 1 0 08:29 ? 00:00:00 ora_q002_cdbo12c oracle 4895 1 0 08:29 ? 00:00:00 ora_q003_cdbo12c oracle 5113 1 0 08:29 ? 00:00:00 ora_cjq0_cdbo12c oracle 5161 1 0 08:29 ? 00:00:00 ora_p004_cdbo12c oracle 5163 1 0 08:29 ? 00:00:00 ora_p005_cdbo12c oracle 5165 1 0 08:29 ? 00:00:00 ora_p006_cdbo12c oracle 5167 1 0 08:29 ? 00:00:00 ora_p007_cdbo12c oracle 5635 3287 0 08:31 pts/1 00:00:00 grep cdbo12c [oracle@ol6-o12c ~]$ ps -ef|grep cdbo12c |wc -l 46
There are about 26 proceses in ASM and 45 in Database cdbo12c. Checking the processes inside ASM and Oracle Instance SQL> set pages 1000 lines 150 SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- -----------------------------instance_name string +ASM SQL> SELECT spid, stid, pname, execution_type, program FROM v$process ORDER BY execution_type, stid; 2 3 SPID STID PNAME EXECUTION_ PROGRAM ------------------------ ------------------------ ----- ---------- ----------------------------------------------NONE PSEUDO 3025 3025 PMON PROCESS
[email protected] (PMON) 3027 3027 PSP0 PROCESS
[email protected] (PSP0) 3029 3029 VKTM PROCESS
[email protected] (VKTM) 3033 3033 GEN0 PROCESS
[email protected] (GEN0) 3035 3035 MMAN PROCESS
[email protected] (MMAN) http://www.hendrydatabase.blogspot.com.au 3
3039 o12c.workgroup 3041 o12c.workgroup 3043 o12c.workgroup 3045 o12c.workgroup 3047 o12c.workgroup 3049 o12c.workgroup 3051 o12c.workgroup 3053 o12c.workgroup 3055 o12c.workgroup 3057 o12c.workgroup 3059 o12c.workgroup 3137 o12c.workgroup 3162 o12c.workgroup 3164 o12c.workgroup 4651 o12c.workgroup 6405 o12c.workgroup 7296 o12c.workgroup 7938 o12c.workgroup
(DIAG) (DIA0) (DBW0) (LGWR) (CKPT) (SMON) (LREG) (RBAL) (GMON) (MMON) (MMNL) (TNS V1-V3) (ASMB) (TNS V1-V3) (TNS V1-V3) (TNS V1-V3) (TNS V1-V3) (TNS V1-V3)
3039
DIAG PROCESS
oracle@ol6-
3041
DIA0 PROCESS
oracle@ol6-
3043
DBW0
PROCESS
oracle@ol6-
3045
LGWR
PROCESS
oracle@ol6-
3047
CKPT PROCESS
oracle@ol6-
3049
SMON
oracle@ol6-
3051
LREG PROCESS
oracle@ol6-
3053
RBAL PROCESS
oracle@ol6-
3055
GMON
PROCESS
oracle@ol6-
3057
MMON
PROCESS
oracle@ol6-
3059
MMNL
PROCESS
oracle@ol6-
PROCESS
3137
PROCESS
oracle@ol6-
3162
ASMB PROCESS
oracle@ol6-
3164
PROCESS
oracle@ol6-
4651
PROCESS
oracle@ol6-
6405
PROCESS
oracle@ol6-
7296
PROCESS
oracle@ol6-
7938
PROCESS
oracle@ol6-
24 rows selected.
SQL> set pages 1000 lines 150 SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- -----------------------------instance_name string cdbo12c SQL> SELECT spid, stid, pname, execution_type, program FROM v$process ORDER BY execution_type, stid; 2 3 SPID
STID
http://www.hendrydatabase.blogspot.com.au 4
PNAME EXECUTION_ PROGRAM
------------------------ ------------------------ ----- ---------- ----------------------------------------------NONE PSEUDO 4606 4606 PMON PROCESS
[email protected] (PMON) 4608 4608 PSP0 PROCESS
[email protected] (PSP0) 4613 4613 VKTM PROCESS
[email protected] (VKTM) 4617 4617 GEN0 PROCESS
[email protected] (GEN0) 4619 4619 MMAN PROCESS
[email protected] (MMAN) 4623 4623 DIAG PROCESS
[email protected] (DIAG) 4628 4628 DBRM PROCESS
[email protected] (DBRM) 4630 4630 DIA0 PROCESS
[email protected] (DIA0) 4632 4632 DBW0 PROCESS
[email protected] (DBW0) 4634 4634 LGWR PROCESS
[email protected] (LGWR) 4636 4636 CKPT PROCESS
[email protected] (CKPT) 4638 4638 SMON PROCESS
[email protected] (SMON) 4640 4640 RECO PROCESS
[email protected] (RECO) 4642 4642 LREG PROCESS
[email protected] (LREG) 4644 4644 RBAL PROCESS
[email protected] (RBAL) 4646 4646 ASMB PROCESS
[email protected] (ASMB) 4648 4648 MMON PROCESS
[email protected] (MMON) 4654 4654 MMNL PROCESS
[email protected] (MMNL) 4657 4657 D000 PROCESS
[email protected] (D000) 4659 4659 MARK PROCESS
[email protected] (MARK) 4661 4661 S000 PROCESS
[email protected] (S000) 4752 4752 TMON PROCESS
[email protected] (TMON) 4757 4757 TT00 PROCESS
[email protected] (TT00) 4765 4765 SMCOPROCESS
[email protected] (SMCO)
http://www.hendrydatabase.blogspot.com.au 5
4776 o12c.workgroup 4784 o12c.workgroup 4800 o12c.workgroup 4802 o12c.workgroup 4804 o12c.workgroup 4806 o12c.workgroup 4869 o12c.workgroup 4877 o12c.workgroup 4895 o12c.workgroup 5113 o12c.workgroup 6338 o12c.workgroup 6474 o12c.workgroup 7294 o12c.workgroup 8353 o12c.workgroup
(W000) (AQPC) (P000) (P001) (P002) (P003) (QM02) (Q002) (Q003) (CJQ0) (W001) (TNS V1-V3) (O000) (W002)
4776
W000 PROCESS
oracle@ol6-
4784
AQPC PROCESS
oracle@ol6-
4800
P000 PROCESS
oracle@ol6-
4802
P001 PROCESS
oracle@ol6-
4804
P002 PROCESS
oracle@ol6-
4806
P003 PROCESS
oracle@ol6-
4869
QM02 PROCESS
oracle@ol6-
4877
Q002 PROCESS
oracle@ol6-
4895
Q003 PROCESS
oracle@ol6-
5113
CJQ0 PROCESS
oracle@ol6-
6338
W001 PROCESS
oracle@ol6-
6474
PROCESS
oracle@ol6-
7294
O000 PROCESS
oracle@ol6-
8353
W002 PROCESS
oracle@ol6-
39 rows selected.
In Oracle12c we can change this to Multithreaded using THREADED_EXECUTION init parameter. Change THREADED_EXECUTION = TRUE. ( By default, it’s False)
SQL> show parameter threaded_execution NAME TYPE VALUE ------------------------------------ ----------- -----------------------------threaded_execution boolean FALSE SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- -----------------------------spfile string +DATA/cdbo12c/spfilecdbo12c.or a SQL> alter system set threaded_execution=TRUE scope=spfile; http://www.hendrydatabase.blogspot.com.au 6
System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
[oracle@ol6-o12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 08:48:16 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORA-01017: invalid username/password; logon denied SQL> connect sys as sysdba Enter password: Connected. SQL> startup ORA-01081: cannot start already-running ORACLE - shut it down first SQL> select open_mode from v$database; select open_mode from v$database * ERROR at line 1: ORA-01507: database not mounted SQL> startup force ORACLE instance started. Total System Global Area 952020992 bytes Fixed Size 2295224 bytes Variable Size 348129864 bytes Database Buffers 595591168 bytes Redo Buffers 6004736 bytes Database mounted. Database opened. SQL> show parameter threaded_execution NAME TYPE VALUE ------------------------------------ ----------- -----------------------------threaded_execution boolean TRUE
In addition we need to add the /u01/app/12.1.0.1/grid/network/admin/listener.ora entry
http://www.hendrydatabase.blogspot.com.au 7
DEDICATED_THROUGH_BROKER_
=ON [oracle@ol6-o12c admin]$ cat listener.ora # listener.ora Network /u01/app/12.1.0.1/grid/network/admin/listener.ora # Generated by Oracle configuration tools.
Configuration
File:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-o12c.workgroup)(PORT = 1521)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET added by Agent DEDICATED_THROUGH_BROKER_LISTENER=ON
# line added by #
[oracle@ol6-o12c admin]$ [oracle@ol6-o12c admin]$ [oracle@ol6-o12c admin]$ lsnrctl reload LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 08-AUG-2013 16:21:02 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521))) The command completed successfully
Now check the Processes again
[oracle@ol6-o12c admin]$ ps -ef|grep ASM oracle 3025 1 0 08:24 ? 00:00:00 asm_pmon_+ASM oracle 3027 1 0 08:24 ? 00:00:00 asm_psp0_+ASM oracle 3029 1 6 08:24 ? 00:01:56 asm_vktm_+ASM oracle 3033 1 0 08:24 ? 00:00:00 asm_gen0_+ASM oracle 3035 1 0 08:24 ? 00:00:00 asm_mman_+ASM oracle 3039 1 0 08:24 ? 00:00:00 asm_diag_+ASM oracle 3041 1 0 08:24 ? 00:00:01 asm_dia0_+ASM oracle 3043 1 0 08:24 ? 00:00:00 asm_dbw0_+ASM oracle 3045 1 0 08:24 ? 00:00:00 asm_lgwr_+ASM oracle 3047 1 0 08:24 ? 00:00:00 asm_ckpt_+ASM oracle 3049 1 0 08:24 ? 00:00:00 asm_smon_+ASM oracle 3051 1 0 08:24 ? 00:00:00 asm_lreg_+ASM oracle 3053 1 0 08:24 ? 00:00:00 asm_rbal_+ASM oracle 3055 1 0 08:24 ? 00:00:00 asm_gmon_+ASM oracle 3057 1 0 08:24 ? 00:00:00 asm_mmon_+ASM http://www.hendrydatabase.blogspot.com.au 8
line
oracle 3059 1 0 08:24 ? 00:00:00 asm_mmnl_+ASM oracle 3137 1 0 08:24 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 3162 1 0 08:25 ? 00:00:00 asm_asmb_+ASM oracle 3164 1 0 08:25 ? 00:00:00 oracle+ASM_asmb_+asm (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 9634 1 0 08:49 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11574 2861 0 08:57 pts/0 00:00:00 grep ASM oracle 11575 1 2 08:57 ? 00:00:00 oracle+ASM (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) [oracle@ol6-o12c admin]$ ps -ef|grep ASM |wc -l 22 [oracle@ol6-o12c ~]$ ps -ef|grep cdbo12c oracle 9595 1 0 08:49 ? 00:00:00 ora_pmon_cdbo12c oracle 9597 1 0 08:49 ? 00:00:00 ora_psp0_cdbo12c oracle 9602 1 6 08:49 ? 00:00:33 ora_vktm_cdbo12c oracle 9606 1 0 08:49 ? 00:00:02 ora_u004_cdbo12c oracle 9612 1 8 08:49 ? 00:00:42 ora_u005_cdbo12c oracle 9621 1 0 08:49 ? 00:00:00 ora_dbw0_cdbo12c oracle 9634 1 0 08:49 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11669 3287 0 08:57 pts/1 00:00:00 grep cdbo12c [oracle@ol6-o12c ~]$ ps -ef|grep cdbo12c |wc -l 8 ASM processes has reduced from 27 to 22 ( not much benefits, still using processes, not threads) Database Processes has reduced from 46 to 8 Checking the processes inside ASM and Oracle Instance SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- -----------------------------instance_name string +ASM SQL> SELECT spid, stid, pname, execution_type, program FROM v$process ORDER BY execution_type, stid; 2 3 SPID STID PNAME EXECUTION_ PROGRAM ------------------------ ------------------------ ----- ---------- ----------------------------------------------NONE PSEUDO 12719 12719 PROCESS [email protected] (TNS V1-V3) 13302 13302 PROCESS [email protected] (TNS V1-V3) http://www.hendrydatabase.blogspot.com.au 9
3025 o12c.workgroup 3027 o12c.workgroup 3029 o12c.workgroup 3033 o12c.workgroup 3035 o12c.workgroup 3039 o12c.workgroup 3041 o12c.workgroup 3043 o12c.workgroup 3045 o12c.workgroup 3047 o12c.workgroup 3049 o12c.workgroup 3051 o12c.workgroup 3053 o12c.workgroup 3055 o12c.workgroup 3057 o12c.workgroup 3059 o12c.workgroup 3137 o12c.workgroup 3162 o12c.workgroup 3164 o12c.workgroup 9634 o12c.workgroup
(PMON) (PSP0) (VKTM) (GEN0) (MMAN) (DIAG) (DIA0) (DBW0) (LGWR) (CKPT) (SMON) (LREG) (RBAL) (GMON) (MMON) (MMNL) (TNS V1-V3) (ASMB) (TNS V1-V3) (TNS V1-V3)
3025
PMON
PROCESS
3027
PSP0 PROCESS
oracle@ol6-
3029
VKTM PROCESS
oracle@ol6-
3033
GEN0 PROCESS
oracle@ol6-
3035
MMAN
oracle@ol6-
3039
DIAG PROCESS
oracle@ol6-
3041
DIA0 PROCESS
oracle@ol6-
3043
DBW0
PROCESS
oracle@ol6-
3045
LGWR
PROCESS
oracle@ol6-
3047
CKPT PROCESS
oracle@ol6-
3049
SMON
oracle@ol6-
3051
LREG PROCESS
oracle@ol6-
3053
RBAL PROCESS
oracle@ol6-
3055
GMON
PROCESS
oracle@ol6-
3057
MMON
PROCESS
oracle@ol6-
3059
MMNL
PROCESS
oracle@ol6-
PROCESS
PROCESS
oracle@ol6-
3137
PROCESS
oracle@ol6-
3162
ASMB PROCESS
oracle@ol6-
3164
PROCESS
oracle@ol6-
9634
PROCESS
oracle@ol6-
23 rows selected.
SQL> show parameter instance_name NAME TYPE VALUE ------------------------------------ ----------- -----------------------------instance_name string cdbo12c SQL> SELECT spid, stid, pname, execution_type, program FROM v$process
http://www.hendrydatabase.blogspot.com.au 10
ORDER BY execution_type, stid; 2
3
SPID STID PNAME EXECUTION_ PROGRAM ------------------------ ------------------------ ----- ---------- ----------------------------------------------NONE PSEUDO 9595 9595 PMON PROCESS [email protected] (PMON) 9597 9597 PSP0 PROCESS [email protected] (PSP0) 9602 9602 VKTM PROCESS [email protected] (VKTM) 9621 9621 DBW0 PROCESS [email protected] (DBW0) 9612 10079 CJQ0 THREAD [email protected] (CJQ0) 9612 12080 W001 THREAD [email protected] (W001) 9612 12782 THREAD [email protected] 9612 13265 J000 THREAD [email protected] (J000) 9612 13266 J001 THREAD [email protected] (J001) 9606 9606 SCMN THREAD [email protected] (SCMN) 9606 9608 GEN0 THREAD [email protected] (GEN0) 9606 9609 MMAN THREAD [email protected] (MMAN) 9612 9612 SCMN THREAD [email protected] (SCMN) 9612 9616 DIAG THREAD [email protected] (DIAG) 9606 9618 DBRM THREAD [email protected] (DBRM) 9612 9619 DIA0 THREAD [email protected] (DIA0) 9606 9622 LGWR THREAD [email protected] (LGWR) 9606 9623 CKPT THREAD [email protected] (CKPT) 9606 9624 SMON THREAD [email protected] (SMON) 9612 9625 RECO THREAD [email protected] (RECO) 9606 9626 LREG THREAD [email protected] (LREG) 9606 9627 RBAL THREAD [email protected] (RBAL) 9606 9628 ASMB THREAD [email protected] (ASMB)
http://www.hendrydatabase.blogspot.com.au 11
9612 9629 [email protected] (MMON) 9612 9630 [email protected] (MMNL) 9612 9631 o12c.workgroup (D000) 9612 9632 o12c.workgroup (S000) 9612 9635 o12c.workgroup (N000) 9612 9639 o12c.workgroup (MARK) 9612 9715 [email protected] (TMON) 9612 9719 o12c.workgroup (TT00) 9612 9727 o12c.workgroup (SMCO) 9612 9737 o12c.workgroup (W000) 9612 9747 o12c.workgroup (AQPC) 9612 9761 o12c.workgroup (P000) 9612 9762 o12c.workgroup (P001) 9612 9763 o12c.workgroup (P002) 9612 9764 o12c.workgroup (P003) 9612 9784 o12c.workgroup (QM02) 9612 9786 o12c.workgroup (Q002) 9612 9787 o12c.workgroup (Q003)
MMON
THREAD
MMNL
THREAD
D000 THREAD
oracle@ol6-
S000 THREAD
oracle@ol6-
N000 THREAD
oracle@ol6-
MARK THREAD
oracle@ol6-
TMON
THREAD
TT00 THREAD
oracle@ol6-
SMCOTHREAD
oracle@ol6-
W000 THREAD
oracle@ol6-
AQPC THREAD
oracle@ol6-
P000 THREAD
oracle@ol6-
P001 THREAD
oracle@ol6-
P002 THREAD
oracle@ol6-
P003 THREAD
oracle@ol6-
QM02 THREAD
oracle@ol6-
Q002 THREAD
oracle@ol6-
Q003 THREAD
oracle@ol6-
42 rows selected. In the Database there are only 4 processes SPID STID PNAME EXECUTION_ PROGRAM ------------------------ ------------------------ ----- ---------- ----------------------------------------------NONE PSEUDO 9595 9595 PMON PROCESS [email protected] (PMON) 9597 9597 PSP0 PROCESS [email protected] (PSP0) 9602 9602 VKTM PROCESS [email protected] (VKTM)
http://www.hendrydatabase.blogspot.com.au 12
9621 9621 o12c.workgroup (DBW0)
DBW0
PROCESS
oracle@ol6-
While remaining 37 are all threads (STID) running under SPID 9612, 9606 9612 o12c.workgroup (CJQ0) 9612 o12c.workgroup (W001) 9612 o12c.workgroup 9606 o12c.workgroup (SCMN) 9606 o12c.workgroup (GEN0) 9606 [email protected] 9612 o12c.workgroup (SCMN) 9612 o12c.workgroup (DIAG) 9606 [email protected] 9612 o12c.workgroup (DIA0) 9606 [email protected] 9606 o12c.workgroup (CKPT) 9606 [email protected] 9612 o12c.workgroup (RECO) 9606 o12c.workgroup (LREG) 9606 o12c.workgroup (RBAL) 9606 o12c.workgroup (ASMB) 9612 [email protected] 9612 [email protected] 9612 o12c.workgroup (D000) 9612 o12c.workgroup (S000) 9612 o12c.workgroup (N000) 9612 o12c.workgroup (MARK)
10079
CJQ0 THREAD
oracle@ol6-
12080
W001 THREAD
oracle@ol6-
12782
THREAD
oracle@ol6-
9606
SCMN THREAD
oracle@ol6-
9608
GEN0 THREAD
oracle@ol6-
9609 (MMAN) 9612 9616
MMAN
THREAD
SCMN THREAD
oracle@ol6-
DIAG THREAD
oracle@ol6-
9618 (DBRM) 9619
DBRM
9622 (LGWR) 9623
LGWR
9624 (SMON) 9625
SMON
THREAD
DIA0 THREAD
oracle@ol6-
THREAD
CKPT THREAD
oracle@ol6-
THREAD
RECO THREAD
oracle@ol6-
9626
LREG THREAD
oracle@ol6-
9627
RBAL THREAD
oracle@ol6-
9628
ASMB THREAD
oracle@ol6-
9629 (MMON) 9630 (MMNL) 9631
MMON
THREAD
MMNL
THREAD
D000 THREAD
oracle@ol6-
9632
S000 THREAD
oracle@ol6-
9635
N000 THREAD
oracle@ol6-
9639
MARK THREAD
oracle@ol6-
http://www.hendrydatabase.blogspot.com.au 13
9612 9715 [email protected] (TMON) 9612 9719 o12c.workgroup (TT00) 9612 9727 o12c.workgroup (SMCO) 9612 9737 o12c.workgroup (W000) 9612 9747 o12c.workgroup (AQPC) 9612 9761 o12c.workgroup (P000) 9612 9762 o12c.workgroup (P001) 9612 9763 o12c.workgroup (P002) 9612 9764 o12c.workgroup (P003) 9612 9784 o12c.workgroup (QM02) 9612 9786 o12c.workgroup (Q002) 9612 9787 o12c.workgroup (Q003)
TMON
THREAD
TT00 THREAD
oracle@ol6-
SMCOTHREAD
oracle@ol6-
W000 THREAD
oracle@ol6-
AQPC THREAD
oracle@ol6-
P000 THREAD
oracle@ol6-
P001 THREAD
oracle@ol6-
P002 THREAD
oracle@ol6-
P003 THREAD
oracle@ol6-
QM02 THREAD
oracle@ol6-
Q002 THREAD
oracle@ol6-
Q003 THREAD
oracle@ol6-
At unix level, the running database processes are reduced from 46 to 8. [oracle@ol6-o12c ~]$ ps -eaf | grep cdbo12c | grep -v grep oracle 9595 1 0 08:49 ? 00:00:00 ora_pmon_cdbo12c oracle 9597 1 0 08:49 ? 00:00:01 ora_psp0_cdbo12c oracle 9602 1 5 08:49 ? 00:02:52 ora_vktm_cdbo12c oracle 9606 1 0 08:49 ? 00:00:06 ora_u004_cdbo12c oracle 9612 1 3 08:49 ? 00:01:30 ora_u005_cdbo12c oracle 9621 1 0 08:49 ? 00:00:00 ora_dbw0_cdbo12c oracle 9634 1 0 08:49 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) SQL> select BACKGROUND, EXECUTION_TYPE, count(*) from v$process group by background, EXECUTION_TYPE; B EXECUTION_ COUNT(*) - ---------- ---------1 PROCESS 4 1 THREAD 27 NONE 1 THREAD 8 SQL> select server, count(*) from v$session group by server; SERVER COUNT(*) --------- ---------http://www.hendrydatabase.blogspot.com.au 14
DEDICATED
32
All the user processes are still dedicated. Just made a connection to sql developer [oracle@ol6-o12c ~]$ ps -eaf | grep cdbo12c | grep -v grep oracle 9595 1 0 08:49 ? 00:00:00 ora_pmon_cdbo12c oracle 9597 1 0 08:49 ? 00:00:01 ora_psp0_cdbo12c oracle 9602 1 5 08:49 ? 00:03:36 ora_vktm_cdbo12c oracle 9606 1 0 08:49 ? 00:00:07 ora_u004_cdbo12c oracle 9612 1 2 08:49 ? 00:01:38 ora_u005_cdbo12c oracle 9621 1 0 08:49 ? 00:00:00 ora_dbw0_cdbo12c oracle 9634 1 0 08:49 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 22260 1 0 09:49 ? 00:00:00 oracle+ASM_o000_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
Just made a connection to oem database express, the processes remain same, threads are up. [oracle@ol6-o12c ~]$ ps -eaf | grep cdbo12c | grep -v grep oracle 9595 1 0 08:49 ? 00:00:00 ora_pmon_cdbo12c oracle 9597 1 0 08:49 ? 00:00:01 ora_psp0_cdbo12c oracle 9602 1 5 08:49 ? 00:04:02 ora_vktm_cdbo12c oracle 9606 1 0 08:49 ? 00:00:08 ora_u004_cdbo12c oracle 9612 1 3 08:49 ? 00:02:11 ora_u005_cdbo12c oracle 9621 1 0 08:49 ? 00:00:00 ora_dbw0_cdbo12c oracle 9634 1 0 08:49 ? 00:00:00 oracle+ASM_asmb_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 22260 1 0 09:49 ? 00:00:00 oracle+ASM_o000_cdbo12c (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) [oracle@ol6-o12c ~]$ ps -eaf | grep cdbo12c | grep -v grep |wc -l 8 SQL> select BACKGROUND, EXECUTION_TYPE, count(*) from v$process group by background, EXECUTION_TYPE; B EXECUTION_ COUNT(*) - ---------- ---------1 PROCESS 4 1 THREAD 28 NONE 1 THREAD 14
http://www.hendrydatabase.blogspot.com.au 15
Don’t use unix kill command to kill the process / session in this Multithreaded model as you may be killing several sessions. Use Oracle sid, serial# instead as usual. SET LINESIZE 140 COLUMN username FORMAT A15 COLUMN osuser FORMAT A15 COLUMN spid FORMAT A10 COLUMN stid FORMAT A10 SELECT s.username, s.osuser, s.sid, s.serial#, s.program, p.spid, p.stid, s.status FROM v$session s, v$process p WHERE s.paddr = p.addr and s.username is not null ORDER BY s.username, s.osuser; USERNAMEOSUSER SID SERIAL# PROGRAM SPID STID STATUS --------------- --------------- ---------- ---------- ------------------------------------------------ --------- ---------- -------SYS Hendry 40 1217 SQL Developer 9612 22485 INACTIVE SYS oracle 57 1171 [email protected] (TNS V1-V3) 9612 26808 ACTIVE SQL> alter system kill session '40,1217'; System altered.
what are the benefits for Oracle Multithreaded Model? When you are planning to consolidate many databases into container databases, it can certainly bring down the oracle shared processes/memory footprint at the OS level and reduce context switches overhead. The thread switching latency is the time needed by the operating system to switch the CPU to another thread. In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a
http://www.hendrydatabase.blogspot.com.au 16
thread from different process (because it requires more complicated process context switch). http://en.wikipedia.org/wiki/Thread_switching_latency
Threads An application's parallelism is the degree of parallel execution acheived. In the real world, this is limited by the number of processors available in the hardware configuration. Concurrency is the maximum acheivable parallelism in a theoretical machine that has an unlimited number of processors. Threads are frequently used to increase an application's concurrency. A thread represents a relatively independent set of instructions within a program. A thread is a control point within a process. It shares global resources within the context of the process (address space, open files, user credentials, quotas, etc). Threads also have private resources (program counter, stack, register context, etc). The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a bound thread creation. Even within a single-processor environment, multiple threads are advantageous because one thread may be able to progress even though another thread is blocked while waiting for a resource. Interprocess communication also takes considerably less time for threads than for processes, since global data can be shared instantly. http://www.princeton.edu/~unix/Solaris/troubleshoot/process.html Issue with Multithread Model The OS Authentication doesn’t work. You need to supply a password to connect as sys user. This means you have to rewrite some of your scripts until this is addressed by oracle.
[oracle@ol6-o12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Fri Aug 9 10:12:41 2013 Copyright (c) 1982, 2013, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: sys as sysdba Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production http://www.hendrydatabase.blogspot.com.au 17
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options
http://www.hendrydatabase.blogspot.com.au 18
![[Read] Ebook Oracle Database 12c Release 2 Oracle ...](https://p.pdfkul.com/img/300x300/read-ebook-oracle-database-12c-release-2-oracle-_5a3466e41723ddbf9dbe7f96.jpg)
