Multithreaded Model using THREADED_EXECUTION in Oracle Database 12c Release 1 (12.1)

• Background
• THREADED_EXECUTION
• OS Authentication
• Killing Sessions
• Considerations

Related articles.

• Killing Oracle Sessions

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=NO : The default value causes Oracle to run using the multiprocess model.
• THREADED_EXECUTION=YES : 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