ORACLE 12C M.T.S. model

Until Oracle Database 12c, Oracle processes did not run as threads on UNIX and Linux systems. Every dedicated connection was represented by an operating system process. In systems where high workload was an issue, the only way to reduce CPU and memory usage was to switch from dedicated connections to shared connections using the multithreaded server (MTS) feature.
Starting in Oracle Database 12c the multithreaded database model was introduced, enabling database processes to be executed as operating system threads and reducing the number of dedicated process necessary to manage database services.
By default, every new 12c instance is created to be run in process mode. You must update your database instance parameter to switch from PROCESS mode to THREAD mode.
SQL> show parameter threaded
NAME TYPE VALUE
———————————— ———– ——-
threaded_execution boolean FALSE
We can list current database sessions and verify their corresponding execution types, query V$PROCESS catalog view :
SQL> SELECT spid, stid, pname, program, execution_type
2 FROM v$process
3 ORDER BY execution_type, pname, program;
SPID STID PNAME PROGRAM EXECUTION_
—– —– —– ———————————– ———-
PSEUDO NONE
8100 8100 AQPC oracle@ora12c.node.com (AQPC) PROCESS
8156 8156 CJQ0 oracle@ora12c.node.com (CJQ0) PROCESS
7905 7905 CKPT oracle@ora12c.node.com (CKPT) PROCESS
7934 7934 D000 oracle@ora12c.node.com (D000) PROCESS
7888 7888 DBRM oracle@ora12c.node.com (DBRM) PROCESS
7897 7897 DBW0 oracle@ora12c.node.com (DBW0) PROCESS
7895 7895 DIA0 oracle@ora12c.node.com (DIA0) PROCESS
7886 7886 DIAG oracle@ora12c.node.com (DIAG) PROCESS
7880 7880 GEN0 oracle@ora12c.node.com (GEN0) PROCESS
7907 7907 LG00 oracle@ora12c.node.com (LG00) PROCESS
7912 7912 LG01 oracle@ora12c.node.com (LG01) PROCESS
7899 7899 LGWR oracle@ora12c.node.com (LGWR) PROCESS
7920 7920 LREG oracle@ora12c.node.com (LREG) PROCESS
7882 7882 MMAN oracle@ora12c.node.com (MMAN) PROCESS
7932 7932 MMNL oracle@ora12c.node.com (MMNL) PROCESS
7927 7927 MMON oracle@ora12c.node.com (MMON) PROCESS
7997 7997 P000 oracle@ora12c.node.com (P000) PROCESS
7999 7999 P001 oracle@ora12c.node.com (P001) PROCESS
8158 8158 P002 oracle@ora12c.node.com (P002) PROCESS
8160 8160 P003 oracle@ora12c.node.com (P003) PROCESS
8162 8162 P004 oracle@ora12c.node.com (P004) PROCESS
8164 8164 P005 oracle@ora12c.node.com (P005) PROCESS
8166 8166 P006 oracle@ora12c.node.com (P006) PROCESS
8171 8171 P007 oracle@ora12c.node.com (P007) PROCESS
7866 7866 PMON oracle@ora12c.node.com (PMON) PROCESS
7868 7868 PSP0 oracle@ora12c.node.com (PSP0) PROCESS
7923 7923 PXMN oracle@ora12c.node.com (PXMN) PROCESS
8145 8145 Q002 oracle@ora12c.node.com (Q002) PROCESS
8149 8149 Q003 oracle@ora12c.node.com (Q003) PROCESS
8140 8140 QM02 oracle@ora12c.node.com (QM02) PROCESS
7918 7918 RECO oracle@ora12c.node.com (RECO) PROCESS
7936 7936 S000 oracle@ora12c.node.com (S000) PROCESS
8048 8048 SMCO oracle@ora12c.node.com (SMCO) PROCESS
7910 7910 SMON oracle@ora12c.node.com (SMON) PROCESS
8007 8007 TMON oracle@ora12c.node.com (TMON) PROCESS
8021 8021 TT00 oracle@ora12c.node.com (TT00) PROCESS
7890 7890 VKRM oracle@ora12c.node.com (VKRM) PROCESS
7876 7876 VKTM oracle@ora12c.node.com (VKTM) PROCESS
12587 12587 W002 oracle@ora12c.node.com (W002) PROCESS
12516 12516 W003 oracle@ora12c.node.com (W003) PROCESS
13292 13292 oracle@ora12c.node.com (TNS V1-V3) PROCESS
42 rows selected.
Note that all database programs are running in PROCESS mode.
Let’s change our database instance configuration by enabling thread mode and then restarting the instance:
SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=spfile;
System altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 2634022912 bytes
Fixed Size 2927864 bytes
Variable Size 1560281864 bytes
Database Buffers 1056964608 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.
Important: Thread mode does not allow “conn / as sysdba” connections.
Login to database instance using the following command:
SQL> conn sys as sysdba
Enter password:
Connected.
SQL>
Let’s query v$process catalog view again:
SQL> SELECT spid, stid, pname, program, execution_type
2 FROM v$process
3 ORDER BY execution_type, pname, program;
SPID STID PNAME PROGRAM EXECUTION_
—– —– —– ———————————– ———-
PSEUDO NONE
19520 19520 DBW0 oracle@ora12c.node.com (DBW0) PROCESS
19497 19497 PMON oracle@ora12c.node.com (PMON) PROCESS
19499 19499 PSP0 oracle@ora12c.node.com (PSP0) PROCESS
19503 19503 VKTM oracle@ora12c.node.com (VKTM) PROCESS
19513 19574 AQPC oracle@ora12c.node.com (AQPC) THREAD
19513 19596 CJQ0 oracle@ora12c.node.com (CJQ0) THREAD
19507 19522 CKPT oracle@ora12c.node.com (CKPT) THREAD
19513 19532 D000 oracle@ora12c.node.com (D000) THREAD
19507 19516 DBRM oracle@ora12c.node.com (DBRM) THREAD
19513 19518 DIA0 oracle@ora12c.node.com (DIA0) THREAD
19513 19515 DIAG oracle@ora12c.node.com (DIAG) THREAD
19507 19509 GEN0 oracle@ora12c.node.com (GEN0) THREAD
19507 19523 LG00 oracle@ora12c.node.com (LG00) THREAD
19507 19525 LG01 oracle@ora12c.node.com (LG01) THREAD
19507 19521 LGWR oracle@ora12c.node.com (LGWR) THREAD
19507 19528 LREG oracle@ora12c.node.com (LREG) THREAD
19507 19510 MMAN oracle@ora12c.node.com (MMAN) THREAD
19513 19531 MMNL oracle@ora12c.node.com (MMNL) THREAD
19513 19530 MMON oracle@ora12c.node.com (MMON) THREAD
19513 19534 N000 oracle@ora12c.node.com (N000) THREAD
19513 19588 P000 oracle@ora12c.node.com (P000) THREAD
19513 19589 P001 oracle@ora12c.node.com (P001) THREAD
19513 19590 P002 oracle@ora12c.node.com (P002) THREAD
19513 19591 P003 oracle@ora12c.node.com (P003) THREAD
19513 19592 P004 oracle@ora12c.node.com (P004) THREAD
19513 19593 P005 oracle@ora12c.node.com (P005) THREAD
19513 19594 P006 oracle@ora12c.node.com (P006) THREAD
19513 19595 P007 oracle@ora12c.node.com (P007) THREAD
19513 19529 PXMN oracle@ora12c.node.com (PXMN) THREAD
19513 19602 Q002 oracle@ora12c.node.com (Q002) THREAD
19513 19603 Q003 oracle@ora12c.node.com (Q003) THREAD
19513 19600 QM02 oracle@ora12c.node.com (QM02) THREAD
19513 19527 RECO oracle@ora12c.node.com (RECO) THREAD
19513 19533 S000 oracle@ora12c.node.com (S000) THREAD
19513 19513 SCMN oracle@ora12c.node.com (SCMN) THREAD
19507 19507 SCMN oracle@ora12c.node.com (SCMN) THREAD
19513 19561 SMCO oracle@ora12c.node.com (SMCO) THREAD
19507 19524 SMON oracle@ora12c.node.com (SMON) THREAD
19513 19551 TMON oracle@ora12c.node.com (TMON) THREAD
19513 19552 TT00 oracle@ora12c.node.com (TT00) THREAD
19513 19517 VKRM oracle@ora12c.node.com (VKRM) THREAD
19513 19562 W000 oracle@ora12c.node.com (W000) THREAD
19513 19563 W001 oracle@ora12c.node.com (W001) THREAD
19513 19550 oracle@ora12c.node.com THREAD
45 rows selected.
As expected, almost all programs are running in THREAD mode instead of PROCESS mode (only 4 programs remains running under the PROCESS mode).