The Basics
Oracle 12c database introduced the Multi-Threaded architecture of processes. The multi-threaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces. It applies to both BACKGROUND PROCESSES and SERVER PROCESSES. By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes.
This feature can be helpful to reduce CPU and memory usage because it reduces the number of Oracle processes. It is disabled by default. You can enable this feature by setting ‘threaded_execution = TRUE‘.
When this initialization parameter is set to TRUE, the DEDICATED_THROUGH_BROKER_listener-name=ON parameter should be added to the listener.ora file, where listener-name is the name of the database listener and LOCAL_LISTENER initialization parameter should be set to a TNS name entry corresponding to your instance service. This enables the server to spawn threads when connections to the database are requested through the listener.
Example
1. Multi-threaded mode is disabled by default as shown below:
SQL> show parameter threaded NAME TYPE VALUE ------------------------------------ ----------- ---------------- threaded_execution boolean FALSE
SQL> !ps -ef|grep orcl oracle 2590 1 0 17:17 ? 00:00:00 ora_pmon_orcl oracle 2592 1 0 17:17 ? 00:00:00 ora_psp0_orcl oracle 2594 1 8 17:17 ? 00:00:05 ora_vktm_orcl oracle 2598 1 0 17:17 ? 00:00:00 ora_gen0_orcl oracle 2600 1 0 17:17 ? 00:00:00 ora_mman_orcl oracle 2604 1 0 17:17 ? 00:00:00 ora_diag_orcl oracle 2606 1 0 17:17 ? 00:00:00 ora_dbrm_orcl oracle 2608 1 0 17:17 ? 00:00:00 ora_vkrm_orcl oracle 2610 1 0 17:17 ? 00:00:00 ora_dia0_orcl oracle 2612 1 0 17:17 ? 00:00:00 ora_dbw0_orcl oracle 2614 1 0 17:17 ? 00:00:00 ora_lgwr_orcl oracle 2616 1 0 17:17 ? 00:00:00 ora_ckpt_orcl oracle 2618 1 0 17:17 ? 00:00:00 ora_smon_orcl oracle 2620 1 0 17:17 ? 00:00:00 ora_reco_orcl oracle 2622 1 0 17:17 ? 00:00:00 ora_lreg_orcl oracle 2624 1 0 17:17 ? 00:00:00 ora_pxmn_orcl oracle 2626 1 1 17:17 ? 00:00:01 ora_mmon_orcl oracle 2628 1 0 17:17 ? 00:00:00 ora_mmnl_orcl oracle 2630 1 0 17:17 ? 00:00:00 ora_d000_orcl oracle 2632 1 0 17:17 ? 00:00:00 ora_s000_orcl oracle 2643 1 0 17:18 ? 00:00:00 ora_tmon_orcl oracle 2645 1 0 17:18 ? 00:00:00 ora_tt00_orcl oracle 2647 1 0 17:18 ? 00:00:00 ora_smco_orcl oracle 2649 1 0 17:18 ? 00:00:00 ora_w000_orcl oracle 2651 1 0 17:18 ? 00:00:00 ora_w001_orcl oracle 2653 1 0 17:18 ? 00:00:00 ora_aqpc_orcl oracle 2657 1 0 17:18 ? 00:00:00 ora_p000_orcl oracle 2659 1 0 17:18 ? 00:00:00 ora_p001_orcl oracle 2661 1 0 17:18 ? 00:00:00 ora_p002_orcl oracle 2663 1 0 17:18 ? 00:00:00 ora_p003_orcl oracle 2665 1 1 17:18 ? 00:00:00 ora_cjq0_orcl oracle 2732 1 3 17:18 ? 00:00:00 ora_j000_orcl oracle 2735 1 0 17:18 ? 00:00:00 ora_j001_orcl oracle 2737 1 1 17:18 ? 00:00:00 ora_j002_orcl oracle 2741 1 1 17:18 ? 00:00:00 ora_j003_orcl oracle 2743 1 0 17:18 ? 00:00:00 ora_j004_orcl oracle 2745 1 0 17:18 ? 00:00:00 ora_j005_orcl oracle 2747 1 1 17:18 ? 00:00:00 ora_j006_orcl oracle 2749 1 0 17:18 ? 00:00:00 ora_j007_orcl oracle 2755 1 1 17:18 ? 00:00:00 ora_j008_orcl oracle 2763 1 0 17:18 ? 00:00:00 ora_j009_orcl oracle 2772 1 1 17:18 ? 00:00:00 ora_j010_orcl oracle 2782 1 1 17:18 ? 00:00:00 ora_j011_orcl oracle 2784 1 1 17:18 ? 00:00:00 ora_j012_orcl oracle 2789 1 1 17:18 ? 00:00:00 ora_j013_orcl oracle 2791 1 1 17:18 ? 00:00:00 ora_j014_orcl oracle 2793 1 2 17:18 ? 00:00:00 ora_j015_orcl oracle 2795 1 0 17:18 ? 00:00:00 ora_j016_orcl oracle 2797 1 0 17:18 ? 00:00:00 ora_j017_orcl oracle 2804 1 0 17:18 ? 00:00:00 ora_qm02_orcl oracle 2806 1 0 17:18 ? 00:00:00 ora_qm03_orcl oracle 2808 1 0 17:18 ? 00:00:00 ora_q002_orcl oracle 2810 1 0 17:18 ? 00:00:00 ora_q003_orcl oracle 2812 1 1 17:18 ? 00:00:00 ora_q004_orcl oracle 2837 1 12 17:18 ? 00:00:00 ora_m002_orcl oracle 2843 1 10 17:18 ? 00:00:00 ora_m004_orcl oracle 2849 1 12 17:18 ? 00:00:00 ora_m000_orcl oracle 2851 1 16 17:18 ? 00:00:00 ora_m006_orcl
2. Let’s enable multi-threaded mode. The instance should be restarted.
SQL> conn / as sysdba SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=SPFILE; SQL> shutdown immediate
SQL> conn sys as sysdba <---- "conn / as sysdba" is NOT allowed in multi-threaded mode. Enter password:
Start the instance and verify the threaded_execution parameter value.
SQL> startup SQL> show parameter threaded NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ threaded_execution boolean TRUE
4. The number of background processes has decreased. Some background processes(pmon, dbw, psp, vktm) behave as before. The other background processes belong to multi-threaded processes with names ora_uxxx_[SID].
SQL> !ps -ef | grep orcl2
oracle 3185 1 0 23:00 ? 00:00:00 ora_pmon_orcl
oracle 3187 1 0 23:00 ? 00:00:00 ora_psp0_orcl
oracle 3189 1 8 23:00 ? 00:03:45 ora_vktm_orcl
oracle 3193 1 0 23:00 ? 00:00:03 ora_u004_orcl
oracle 3199 1 1 23:00 ? 00:00:39 ora_u005_orcl
oracle 3206 1 0 23:00 ? 00:00:00 ora_dbw0_orcl
5. You can find the thread id (STID) for each background process from V$PROCESS. Multiple STID’s have the same SPID. For example, in this case, SPID 3193 has 8 threads and SPID 3199 has 34 threads.
SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid;
SPID STID PNAME PROGRAM EXECUTION_TYPE
----- ----- ----- -------------------- --------------
PSEUDO NONE
3185 3185 PMON oracle@oel6 (PMON) PROCESS
3187 3187 PSP0 oracle@oel6 (PSP0) PROCESS
3189 3189 VKTM oracle@oel6 (VKTM) PROCESS
3206 3206 DBW0 oracle@oel6 (DBW0) PROCESS
3193 3193 SCMN oracle@oel6 (SCMN) THREAD
3193 3195 GEN0 oracle@oel6 (GEN0) THREAD
3193 3196 MMAN oracle@oel6 (MMAN) THREAD
3193 3202 DBRM oracle@oel6 (DBRM) THREAD
3193 3207 LGWR oracle@oel6 (LGWR) THREAD
3193 3208 CKPT oracle@oel6 (CKPT) THREAD
3193 3209 SMON oracle@oel6 (SMON) THREAD
3193 3211 LREG oracle@oel6 (LREG) THREAD
3199 3199 SCMN oracle@oel6 (SCMN) THREAD
3199 3201 DIAG oracle@oel6 (DIAG) THREAD
3199 3203 VKRM oracle@oel6 (VKRM) THREAD
3199 3204 DIA0 oracle@oel6 (DIA0) THREAD
3199 3210 RECO oracle@oel6 (RECO) THREAD
3199 3212 PXMN oracle@oel6 (PXMN) THREAD
3199 3213 MMON oracle@oel6 (MMON) THREAD
3199 3214 MMNL oracle@oel6 (MMNL) THREAD
3199 3215 D000 oracle@oel6 (D000) THREAD
3199 3216 S000 oracle@oel6 (S000) THREAD
3199 3217 N000 oracle@oel6 (N000) THREAD
3199 3224 TMON oracle@oel6 (TMON) THREAD
3199 3225 TT00 oracle@oel6 (TT00) THREAD
3199 3226 SMCO oracle@oel6 (SMCO) THREAD
3199 3227 W000 oracle@oel6 (W000) THREAD
3199 3228 W001 oracle@oel6 (W001) THREAD
3199 3229 AQPC oracle@oel6 (AQPC) THREAD
3199 3230 CJQ0 oracle@oel6 (CJQ0) THREAD
3199 3232 P000 oracle@oel6 (P000) THREAD
3199 3233 P001 oracle@oel6 (P001) THREAD
3199 3234 P002 oracle@oel6 (P002) THREAD
3199 3235 P003 oracle@oel6 (P003) THREAD
3199 3363 QM02 oracle@oel6 (QM02) THREAD
3199 3366 Q003 oracle@oel6 (Q003) THREAD
3199 3368 Q004 oracle@oel6 (Q004) THREAD
3199 3474 oracle@oel6 THREAD <-- Dedicated server thread created by "SYS" connection
3199 3491 W002 oracle@oel6 (W002) THREAD
3199 3540 W003 oracle@oel6 (W003) THREAD
3199 3543 W004 oracle@oel6 (W004) THREAD
3199 3880 W005 oracle@oel6 (W005) THREAD
3199 3937 W006 oracle@oel6 (W006) THREAD
3199 4311 W007 oracle@oel6 (W007) THREAD
3199 4428 J000 oracle@oel6 (J000) THREAD
3199 4429 J001 oracle@oel6 (J001) THREAD
48 rows selected.
Please make a note that this multi-threading shouldn’t be confused with Shared server(MTS) architecture and both are different.