8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multithreaded Model using THREADED_EXECUTION in Oracle Database 12c Release 1 (12.1)
Related articles.
Background
On UNIX/Linux systems the Oracle database uses a multiprocess model. For example, a basic installation of the database on a Linux system will have a process list something like this.
$ ps -ef | grep [o]ra_ oracle 15356 1 0 10:53 ? 00:00:00 ora_pmon_db12c oracle 15358 1 0 10:53 ? 00:00:00 ora_psp0_db12c oracle 15360 1 8 10:53 ? 00:01:27 ora_vktm_db12c oracle 15364 1 0 10:53 ? 00:00:00 ora_gen0_db12c oracle 15366 1 0 10:53 ? 00:00:00 ora_mman_db12c oracle 15370 1 0 10:53 ? 00:00:00 ora_diag_db12c oracle 15372 1 0 10:53 ? 00:00:00 ora_dbrm_db12c oracle 15374 1 0 10:53 ? 00:00:00 ora_dia0_db12c oracle 15376 1 0 10:53 ? 00:00:00 ora_dbw0_db12c oracle 15378 1 0 10:53 ? 00:00:00 ora_lgwr_db12c oracle 15380 1 0 10:53 ? 00:00:00 ora_ckpt_db12c oracle 15382 1 0 10:53 ? 00:00:00 ora_smon_db12c oracle 15384 1 0 10:53 ? 00:00:00 ora_reco_db12c oracle 15386 1 0 10:53 ? 00:00:00 ora_lreg_db12c oracle 15388 1 0 10:53 ? 00:00:03 ora_mmon_db12c oracle 15390 1 0 10:53 ? 00:00:00 ora_mmnl_db12c oracle 15392 1 0 10:53 ? 00:00:00 ora_d000_db12c oracle 15394 1 0 10:53 ? 00:00:00 ora_s000_db12c oracle 15407 1 0 10:54 ? 00:00:00 ora_tmon_db12c oracle 15409 1 0 10:54 ? 00:00:00 ora_tt00_db12c oracle 15411 1 0 10:54 ? 00:00:00 ora_smco_db12c oracle 15413 1 0 10:54 ? 00:00:00 ora_fbda_db12c oracle 15415 1 0 10:54 ? 00:00:00 ora_aqpc_db12c oracle 15419 1 0 10:54 ? 00:00:00 ora_p000_db12c oracle 15421 1 0 10:54 ? 00:00:00 ora_p001_db12c oracle 15423 1 0 10:54 ? 00:00:00 ora_p002_db12c oracle 15425 1 0 10:54 ? 00:00:00 ora_p003_db12c oracle 15435 1 0 10:54 ? 00:00:00 ora_cjq0_db12c oracle 15459 1 0 10:54 ? 00:00:00 ora_qm02_db12c oracle 15463 1 0 10:54 ? 00:00:00 ora_q002_db12c oracle 15465 1 0 10:54 ? 00:00:00 ora_q003_db12c oracle 15612 1 0 11:04 ? 00:00:00 ora_w000_db12c oracle 15679 1 0 11:10 ? 00:00:00 ora_j000_db12c oracle 15681 1 0 11:10 ? 00:00:00 ora_j001_db12c oracle 15683 1 0 11:10 ? 00:00:00 ora_w001_db12c $
Even with this multiprocess model, some of the individual processes work internally in a multithreaded manner.
In contrast, the Oracle database runs as a single multithreaded process on Windows, with each of the UNIX/Linux processes running on one or more threads. Oracle 12c includes the ability to run the database on UNIX/Linux environments with a multithreaded model, similar to how it runs under Windows.
THREADED_EXECUTION
The choice of threading model is dictated by the THREADED_EXECUTION
initialization parameter.
THREADED_EXECUTION=FALSE
: The default value causes Oracle to run using the multiprocess model.THREADED_EXECUTION=TRUE
: Oracle runs with the multithreaded model.
To switch to the multithreaded model, simply set the THREADED_EXECUTION
parameter and restart the database.
CONN sys AS SYSDBA ALTER SYSTEM SET threaded_execution=TRUE SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP;
Once the database is started, we can see the number of operating system processes is drastically reduced.
$ ps -ef | grep [o]ra_ oracle 15839 1 0 11:26 ? 00:00:00 ora_pmon_db12c oracle 15841 1 0 11:26 ? 00:00:00 ora_psp0_db12c oracle 15843 1 8 11:26 ? 00:00:03 ora_vktm_db12c oracle 15847 1 0 11:26 ? 00:00:00 ora_u004_db12c oracle 15853 1 34 11:26 ? 00:00:13 ora_u005_db12c oracle 15859 1 0 11:26 ? 00:00:00 ora_dbw0_db12c $
In addition, the following setting should be added to the "$ORACLE_HOME/network/admin/listener.ora" file to allow new threads to be spawned to support connections made through the listener. Substitute <listener-name> with the correct listener name.
DEDICATED_THROUGH_BROKER_<listener-name>=ON
To revert to the multiprocess model, simply switch the initialization parameter back and restart the database.
CONN sys AS SYSDBA ALTER SYSTEM SET threaded_execution=FALSE SCOPE=SPFILE; SHUTDOWN IMMEDIATE; STARTUP;
Remember to remove the "listener.ora" parameter.
OS Authentication
OS Authentication is not supported with the multithreaded model. This is a feature, not a bug. Looking back at the previous examples, connections are made using "SYS AS SYSDBA" rather than "/ AS SYSDBA" when the multithreaded model is used. Attempting to use the OS authentication results in errors.
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jul 4 11:28:16 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 With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL>
The documentation suggests you will receive an ORA-01031 "insufficient privileges" error.
Killing Sessions
The V$PROCESS
view includes a new column called STID
, which displays the Session Thread ID.
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#, 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; USERNAME OSUSER SID SERIAL# SPID STID STATUS --------------- --------------- ---------- ---------- ---------- ---------- -------- SYS oracle 35 3 18844 18901 ACTIVE TEST oracle 40 37 18844 19020 INACTIVE SQL>
Killing sessions from within Oracle is unchanged, since you can still target the SID
and SERIAL#
.
SQL> ALTER SYSTEM KILL SESSION '40, 37'; System altered. SQL>
What we must not do is use the UNIX/Linux kill
command to kill the OS process using the Session Process ID (SPID
), or we will kill multiple sessions, not just the one we are interested in.
$ ps -ef | grep 18844 | grep -v grep oracle 18844 1 1 16:27 ? 00:00:22 ora_u005_db12c $
Considerations
The only convincing reason I've heard for using this feature is when you are consolidating lots of instances onto a single server without using the multitennant option. Without the multithreaded model, the number of OS processes could get very high.
If you had a hardware architecture that coped with threads better than processes, there may be some benefit also.
In a RAC environment, all nodes must use the same threading model.
For more information see:
- THREADED_EXECUTION
- Multiprocess and Multithreaded Oracle Database Systems
- Killing Oracle Sessions (ALTER SYSTEM KILL / DISCONNECT SESSION)
Hope this helps. Regards Tim...