About Enabling Support for threaded_execution Initialization Parameter

Starting with Oracle Database 21c, Oracle Database supports the threaded_execution initialization parameter.

This parameter is set to true by default on the Oracle Database server instance. Like in earlier releases, even with the initialization parameter threaded_execution = true, the Oracle Database server instance on Microsoft Windows remains a service having a single multi-threaded operating system process.

On Microsoft Windows, threaded_execution = false is not supported, hence this parameter must be set to true only. Oracle recommends that you do not change the default value of true which is set automatically.

Starting with Oracle Database 21c, when you start the Oracle Database server with the default setting threaded_execution = true you observe the following changes:

V$PROCESS View Changes

Starting with Oracle Database 21c, the STID column in V$PROCESS is populated. The SPID is an operating system process identifier. Since Oracle Database server instance process on Windows is service-based, this is Oracle Database instance’s process identifier. This is unlike the Oracle Database prior to Oracle Database 21c, where SPID had a unique operating system thread (Oracle process) identifier.

The STID is an operating system thread identifier mapped to the threads running in the operating system process, and the SPID and STID together uniquely identify an Oracle process. The STID is unique for every Oracle background and foreground except for foregrounds started by the listener. In those cases, the SPID is the operating system thread (Oracle process) identifier and STID is 0.

Snapshot of the V$PROCESS for Oracle Database 21c on Microsoft Windows is as follows:

Table 1-2 V$PROCESS for Oracle Database 21c

PID	SPID	STID	PNAME
1	5300	6312	PMON
2	5300	6148	SCMN
3	5300	4348	CLMN
4	5300	6584	PSP0
5	5300	5220	VKTM
.
.
.
71	4872	0

PID 71 in the above list having STID = 0 is spawned by the listener instead of the Connection broker process. For these PIDs, SPID indicates a unique operating system Thread (Oracle process) identifier.

A snapshot of V$PROCESS prior to Oracle Database 21c, where Oracle process is an operating system thread (Oracle process) identifier is as follows:

Table 1-3 V$PROCESS prior to Oracle Database 21c

PID	SPID	STID	PNAME
1
2	9748	0	PMON
3	4804	0	CLMN
4	5564	0	PSP0
5	8872	0	VKTM

Dedicated Connection Broker

Starting with Oracle Database 21c, the Dedicated Connection Broker is turned on by default. Hence, you notice an additional background trace file similar to tg_n000_3992_3100.trc, where tg is the ORACLE_SID, 3992 is the SPID, and 3100 is the STID. To disable Dedication Connection Broker explicitly, set use_dedicated_broker = FALSE in the server initialization parameter file.

When the Dedicated Connection Broker runs on the Oracle Database server instance:

1. The bequeath client connections to the server result in spawning a new server thread (foreground) by the Dedicated Connection Broker process.
2. The Dedicated Connection Broker can spawn a server thread (foreground) in case of TNS connections from the client. For this, set DEDICATED_THROUGH_BROKER_listener-name = on in the listener.ora file and restart the listener.

Trace File Name Changes

Starting with Oracle Database 21c, trace file names can have the following formats:

1. tg_pmon_3992_9408.trc, where tg is the ORACLE_SID, 3992 is the operating system process ID, and 9408 is the operating system thread (Oracle process) identifier as described in the V$PROCESS view.

   Trace file names of all the background Oracle processes and foreground processes started by the connection broker in the server instance contain a sub-string of the form pid_tid.

2. tg_ora_4872.trc, where tg is the ORACLE_SID and 4872 is the operating system thread (Oracle process) identifier.

   This format is used in the following scenarios:

   • a. By default, DEDICATED_THROUGH_BROKER_listener-name parameter is not turned ON and hence the TNS connections result in the server foreground created by the listener. This behaviour is similar to the Oracle Database releases prior to Oracle Database 21c.
   • b. For a given client, when you need a listener instead of a broker spawn a foreground server process, explicitly set the environment variable ORA_SERVER_THREAD_ENABLED = FALSE in the client's environment.