Administration : THREADED_EXECUTION new param and new model for BG processes

By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes. Thus, an "Oracle process" is not always equivalent to an "operating system process."

Parameter THREADED_EXECUTION specifies whether to enable the multithreaded Oracle model. Starting in Oracle Database 12c, the multithreaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces.

There is new column stid in v$process which provides the threads that spawned for the oracle process ID,

Further you may also observed there are process LG00, LG01 which are lgwr threads works for PDB's if threaded execution set to true. However there are benefits and also some considerations which updated below.

Source Jonathan Lewis Blog for this query and output

	select
	    spid, stid, program, background
	from
	    V$process
	where
	    spid != stid
	and spid in (
		select spid
		from v$process
		group by spid
		having count(*) > 1
	    )
	order by
	    spid, stid, program
	;

	SPID       STID       PROGRAM                        B
	---------- ---------- ------------------------------ -
	813        817        oracle@linux01 (GEN0)          1
		   820        oracle@linux01 (MMAN)          1
		   834        oracle@linux01 (DBRM)          1
		   844        oracle@linux01 (LGWR)          1
		   847        oracle@linux01 (CKPT)          1
		   850        oracle@linux01 (LG00)          1
		   853        oracle@linux01 (LG01)          1
		   856        oracle@linux01 (SMON)          1
		   862        oracle@linux01 (LREG)          1

	827        1022       oracle@linux01
		   1025       oracle@linux01 (QM02)          1
		   1031       oracle@linux01 (Q002)          1
		   1034       oracle@linux01 (Q003)          1
		   831        oracle@linux01 (DIAG)          1
		   837        oracle@linux01 (DIA0)          1
		   859        oracle@linux01 (RECO)          1
		   865        oracle@linux01 (MMON)          1
		   868        oracle@linux01 (MMNL)          1
		   871        oracle@linux01 (D000)
		   874        oracle@linux01 (S000)
		   877        oracle@linux01 (N000)
		   898        oracle@linux01 (P000)
		   901        oracle@linux01 (P001)
		   904        oracle@linux01 (TMON)          1
		   907        oracle@linux01 (TT00)          1
		   910        oracle@linux01 (SMCO)          1
		   913        oracle@linux01 (FBDA)          1
		   916        oracle@linux01 (AQPC)          1

Issues:-
1) You cannot connect to database with sys / as sysdba, you will need to provide the password for sys connection

2) 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. This enables the server to spawn threads when connections to the database are requested through the listener.