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 and, 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. This new feature is an excellent tool to implement in database environments that are severely limited in CPU and RAM resources.
By default, every new 12c instance is created to be run in process mode. So, this means that you must update your database instance setup accordingly in order to switch from PROCESS mode to THREAD mode.
Let’s verify our current database configuration. Login as a DBA account and execute the following command:
SQL> show parameter threaded NAME TYPE VALUE ------------------------------------ ----------- ------- threaded_execution boolean FALSE
The current state is “FALSE” as expected.
Now, let’s collect some data before we switch from PROCESS to THREAD mode.
Execute the following command in order to obtain the total running processes that belongs to a given database instance (in this example we will using “db02″ as SID, change this definition according your instance name):
[oracle@ora12c ~]$ ps -ef |grep ora_ |grep _db02 |wc -l 40
In this environment, 40 operating system processes are currently running. Your process list would be similar to:
[oracle@ora12c ~]$ ps -ef |grep ora_ |grep _db02 oracle 7866 1 0 14:28 ? 00:00:01 ora_pmon_db02 oracle 7868 1 0 14:28 ? 00:00:06 ora_psp0_db02 oracle 7876 1 17 14:28 ? 00:15:52 ora_vktm_db02 oracle 7880 1 0 14:28 ? 00:00:01 ora_gen0_db02 oracle 7882 1 0 14:28 ? 00:00:03 ora_mman_db02 ..... oracle 29084 1 0 15:59 ? 00:00:00 ora_j000_db02 oracle 29086 1 0 15:59 ? 00:00:00 ora_j001_db02 oracle 29184 1 0 16:00 ? 00:00:00 ora_j002_db02
We can list current database sessions and verify their corresponding execution types, query V$PROCESS catalog view by executing the following command:
SQL> set linesize 200
SQL> set pagesize 50
SQL> col spid format a5
SQL> col pname format a5
SQL> col program format a35
SQL> col execution format a10
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.
The STID column represents the operating system thread identifier. When the Oracle multiprocess/multithread feature is enabled, RDBMS processes are mapped to threads running in operating system processes, and the SPID and STID together uniquely identify an RDBMS process.
If you are running your database on a Linux system, then you can obtain your current database memory utilization by executing the following command:
$ ps aux |grep ora_ |grep -v grep |awk '{ SUM += $6 } END { print SUM/1024 }'
1467.19
In this example, our database instance has 1500 MB of resident set size (RSS) memory utilization.
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. If you try to connect using this method you will obtain an error message like the following:
SQL> conn / as sysdba ORA-01017: invalid username/password; logon denied
Instead, login to database instance using the following command (or using any other SYSDBA account):
SQL> conn sys as sysdba Enter password: Connected. SQL>
Let’s query v$process catalog view again:
SQL> set linesize 200
SQL> set pagesize 50
SQL> col spid format a5
SQL> col stid format a5
SQL> col pname format a5
SQL> col program format a35
SQL> col execution format a10
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)
Now, let’s verify how many database instance process are running:
[oracle@ora12c ~]$ ps -ef |grep ora_ |grep _db02 oracle 19497 1 0 14:12 ? 00:00:00 ora_pmon_db02 oracle 19499 1 0 14:12 ? 00:00:00 ora_psp0_db02 oracle 19503 1 7 14:12 ? 00:00:26 ora_vktm_db02 oracle 19507 1 0 14:12 ? 00:00:02 ora_u004_db02 oracle 19513 1 3 14:12 ? 00:00:13 ora_u005_db02 oracle 19520 1 0 14:12 ? 00:00:00 ora_dbw0_db02
Note that we have just 6 operating system processes currently running!
In a future post we will address how to manage database connections from client applications in order to improve their performance using THREAD mode database architecture.
Link to script that contains the examples in this post.