LATEST TOPICS

Oracle Database 12c: The Multi-Threaded Process Model

History

Oracle Database on Unix machines (Linux, Solaris, AIX, etc..) have always been a multiprocess database with each new version or release adding a new set of processes to the database architecture. However, On the contrary Oracle Database on Windows machines always runs a single (Multi-threaded) process.

Multi-threading has a wide range of advantages over the multiprocess architecture. A process in general context is an address space and creating a new process requires creating an address space in the memory. On the contrary creating a thread is less expensive as it doesn’t require a new address space and will run in the current process address space. Further, the time taken to switch between threads is much less than the time taken to switch between processes mainly due to the fact that switching between threads do not require to switching between address spaces.

What is new?

The wait is finally over. Oracle has introduced the Multi-threaded architecture for Unix systems with the Oracle Database Release 12c, where a Oracle Database on Unix machines can utilize a Multi-threaded process model.

Pre 12c Process Model architecture

Prior to Oracle Database 12c, a single database consists of a number of Operating System processes (multiprocess) like PMON, SMON, CKPT, LGWR, etc. On my lab server, I have a simple database with default configuration (including ASM) and I could see following list of OS processes mapped to the database.

[oracle@labserver ~]$ ps -ef | grep labdb
oracle    5250     1  0 04:51 ?        00:00:00 ora_pmon_labdb
oracle    5254     1  0 04:51 ?        00:00:00 ora_psp0_labdb
oracle    5258     1  2 04:51 ?        00:00:03 ora_vktm_labdb
oracle    5264     1  0 04:51 ?        00:00:00 ora_gen0_labdb
oracle    5268     1  0 04:51 ?        00:00:00 ora_mman_labdb
oracle    5276     1  0 04:51 ?        00:00:00 ora_diag_labdb
oracle    5280     1  0 04:51 ?        00:00:00 ora_dbrm_labdb
oracle    5284     1  0 04:51 ?        00:00:00 ora_dia0_labdb
oracle    5288     1  0 04:51 ?        00:00:00 ora_dbw0_labdb
oracle    5292     1  0 04:51 ?        00:00:00 ora_lgwr_labdb
oracle    5296     1  0 04:51 ?        00:00:00 ora_ckpt_labdb
oracle    5300     1  0 04:51 ?        00:00:00 ora_smon_labdb
oracle    5304     1  0 04:51 ?        00:00:00 ora_reco_labdb
oracle    5308     1  0 04:51 ?        00:00:00 ora_lreg_labdb
oracle    5312     1  0 04:51 ?        00:00:00 ora_rbal_labdb
oracle    5316     1  0 04:51 ?        00:00:00 ora_asmb_labdb
oracle    5320     1  1 04:51 ?        00:00:02 ora_mmon_labdb
oracle    5322     1  0 04:51 ?        00:00:00 oracle+ASM_asmb_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5326     1  0 04:51 ?        00:00:00 ora_mmnl_labdb
oracle    5330     1  0 04:51 ?        00:00:00 ora_d000_labdb
oracle    5336     1  0 04:51 ?        00:00:00 ora_s000_labdb
oracle    5340     1  0 04:51 ?        00:00:00 ora_mark_labdb
oracle    5354     1  0 04:51 ?        00:00:00 ora_ocf0_labdb
oracle    5358     1  0 04:51 ?        00:00:00 oracle+ASM_ocf0_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5360     1  0 04:51 ?        00:00:00 ora_o000_labdb
oracle    5364     1  0 04:51 ?        00:00:00 oracle+ASM_o000_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5386     1  0 04:51 ?        00:00:00 ora_o001_labdb
oracle    5390     1  0 04:51 ?        00:00:00 oracle+ASM_o001_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5398     1  0 04:51 ?        00:00:00 ora_tmon_labdb
oracle    5402     1  0 04:51 ?        00:00:00 ora_arc0_labdb
oracle    5406     1  0 04:51 ?        00:00:00 ora_o002_labdb
oracle    5410     1  0 04:51 ?        00:00:00 oracle+ASM_o002_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5414     1  0 04:51 ?        00:00:00 ora_arc1_labdb
oracle    5418     1  0 04:51 ?        00:00:00 ora_arc2_labdb
oracle    5422     1  0 04:51 ?        00:00:00 ora_arc3_labdb
oracle    5426     1  0 04:51 ?        00:00:00 ora_tt00_labdb
oracle    5436     1  0 04:51 ?        00:00:00 ora_smco_labdb
oracle    5441     1  0 04:51 ?        00:00:00 ora_fbda_labdb
oracle    5445     1  0 04:51 ?        00:00:00 ora_w000_labdb
oracle    5449     1  0 04:51 ?        00:00:00 ora_aqpc_labdb
oracle    5453     1  0 04:51 ?        00:00:00 oraclelabdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5462     1  0 04:51 ?        00:00:01 ora_cjq0_labdb
oracle    5466     1  0 04:51 ?        00:00:00 ora_qm02_labdb
oracle    5474     1  0 04:51 ?        00:00:00 ora_q002_labdb
oracle    5478     1  0 04:51 ?        00:00:00 ora_q003_labdb
oracle    5536     1  0 04:51 ?        00:00:00 ora_p000_labdb
oracle    5546     1  0 04:51 ?        00:00:00 ora_p001_labdb
oracle    5558     1  0 04:51 ?        00:00:00 ora_p002_labdb
oracle    5570     1  0 04:51 ?        00:00:00 ora_p003_labdb
oracle    5652  5169  0 04:54 pts/1    00:00:00 grep labdb

[oracle@labserver ~]$ ps -ef | grep labdb | wc -l
50

I have around 50 odd OS processes mapped to my database. Again as the client connections come, the number of process will keep growing with each new client connection.

How to configure Multithreading for Oracle Database

Configuring Multi-threaded process model is very simple. In Oracle Database 12c, Multi-threading is facilitated by means of the database initialization parameter THREADED_EXECUTION

THREADED_EXECUTION=FALSE or TRUE

FALSE: This is the default value and causes Oracle to run with Multiprocess architecture.
TRUE: If the value is set to TRUE, it implements Multi-threaded architecture for the database. 

However, this configuration is not enough for obtaining a complete Multi-threaded process model. Please continue to next section for details.

How it works

When, we set the parameter THREADED_EXECUTION=TRUE; by default it converts almost all of the background processes (like CKPT, LGWR, DBWR, SMON, etc.) into THREADS.

With respect to User processes (client connections), Multi-threading is facilitated by means of dedicated connection brokers (Nnnn). USE_DEDICATED_BROKER initialization parameter determines, how the dedicated servers are spawned. This database initialization parameter determines whether to use a dedicated connection broker or not. If USE_DEDICATED_BROKER is set to FALSE (default) dedicated connection broker is not used and the OS processes are spawned by Client (for local connections) or by listener (for remote connections). If USE_DEDICATED_BROKER is set to TRUE, dedicated connection broker is used and it spawns OS threads for client connections.

Setting THREADED_EXECUTION=TRUE by default sets USE_DEDICATED_BROKER=TRUE to activate the dedicated connection broker. Further, the dedicated connection brokers (agents to spawn OS threads) are controlled by the initialization parameter CONNECTION_BROKERS. By default Brokers are configured for DEDICATED and EMON Broker types. For details on CONNECTION_BROKERS, please refer here

Threading Local Client Connections:
For Local Connections, the client directly contacts the dedicated connection broker, which in turn spawns a OS thread for the client session.

Threading Remote Client Connections:
For remote connections, the client contacts the listener which hands over the connection to a dedicated connection broker and the connection broker in turn spawns a dedicated server (OS Thread). Unlike Listener, the dedicated connection broker is a database process.

However, the remote database connections can’t leverage the dedicated connection broker directly as the connections are requested through Listener. To leverage the dedicated connection broker and in turn use threading for the remote database connections, we need to add DEDICATED_THROUGH_BROKER_[Listener_Name] = ON in the LISTENER.ORA of the database Listener. Setting this parameter in LISTENER.ORA enables the use of dedicated connection broker by the Listener to spawn new threads.

Therefore, here is the complete set of configuration settings to obtain a complete Multi-threaded process model.

    Set initialization parameter THREADED_EXECUTION=TRUE
    Set initialization parameter USE_DEDICATED_BROKER=TRUE (By default set when setting THREADED_EXECUTION=TRUE)
    Add DEDICATED_THROUGH_BROKER_[Listener_Name]=ON in LISTENER.ORA of the database Listener.

Demonstration

Cross-checking the current process model

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------
db_unique_name                       string      labdb

SQL> show parameter threaded_execution

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------
threaded_execution                   boolean     FALSE

SQL> show parameter use_dedicated_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------
use_dedicated_broker                 boolean     FALSE

SQL> show parameter connection_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),((TYPE=EMON)(BROKERS=1))

I have a default process model (Multiprocess) for my database LABDB.

Lets change the process model to Multi-threaded architecture.

												  
SQL> alter system set threaded_execution=TRUE scope=spfile;

System altered.

---- 
---- Database Restart is required as THREADED_EXECUTION is a static parameter
----

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2291472 bytes
Variable Size             276826352 bytes
Database Buffers          343932928 bytes
Redo Buffers                3276800 bytes
Database mounted.
Database opened.

Lets validate the Multi-threaded Process Model configuration.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------------
db_name                              string      labdb

SQL> show parameter threaded_execution

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------
threaded_execution                   boolean     TRUE

SQL> show parameter use_dedicated_broker

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------
use_dedicated_broker                 boolean     TRUE

SQL> show parameter connection_brokers

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------
connection_brokers                   string      ((TYPE=DEDICATED)(BROKERS=1)),((TYPE=EMON)(BROKERS=1))
SQL>

Lets check the count of the OS processes

[oracle@labserver ~]$ ps -ef | grep labdb
oracle    5872     1  0 04:58 ?        00:00:00 ora_pmon_labdb
oracle    5876     1  0 04:58 ?        00:00:00 ora_psp0_labdb
oracle    5880     1  2 04:58 ?        00:00:07 ora_vktm_labdb
oracle    5886     1  0 04:58 ?        00:00:02 ora_u004_labdb
oracle    5900     1  6 04:58 ?        00:00:19 ora_u005_labdb
oracle    5914     1  0 04:58 ?        00:00:00 ora_dbw0_labdb
oracle    5946     1  0 04:58 ?        00:00:00 oracle+ASM_asmb_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5980     1  0 04:58 ?        00:00:00 oracle+ASM_ocf0_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5985     1  0 04:58 ?        00:00:00 oracle+ASM_o000_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    5994     1  0 04:58 ?        00:00:00 oracle+ASM_o001_labdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6036     1  0 04:58 ?        00:00:00 oraclelabdb (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

As we can see the number of database processes are significantly reduced due to the Multi-threaded process model. We have only the PMON (Process Monitor), PSP0 (Process Spawner), VKTM (Virtual Keeper of Time) and DBW0 (Database Writer) running as the OS process. Rest of the background processes are running as OS threads.

What about the u004 and u005 process? These processes were not there before converting to the Multi-threaded model.

These are the container processes where the other processes execute as threads. This means all the database processes (like SMON, LGWR, CJQ0, CKPT, etc) which were running earlier as OS processes are now running as OS threads under these container processes (Unnn)

Now, let see if Multi-threading is working for client connections.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 10 13:41:34 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: abbas/abbas@labdb
Last Successful login time: Mon Nov 10 2014 05:36:48 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


SQL> select p.spid from v$process p, v$session s
  2  where p.addr=s.paddr and s.sid=(select sys_context('userenv','sid') from dual);

SPID
------------------------
9137

SQL> !ps -ef | grep 9137
oracle    9137     1  0 13:41 ?        00:00:00 oraclelabdb (LOCAL=NO)
oracle    9243  9133  0 13:45 pts/1    00:00:00 /bin/bash -c ps -ef | grep 9137

As we can see client connections coming through listener are not getting spawned as thread. This is because the Listener is not enabled to hand over connection to the dedicated connection broker.

We need to add the DEDICATED_THROUGH_BROKER_[Listener_Name]=ON in the LISTENER.ORA file. Lets update the LISTENER.ORA file and reload the Listener.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = labserver.home.com)(PORT = 1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent
DEDICATED_THROUGH_BROKER_LISTENER=ON 				#Added manually to enable dedicated connection broker

Lets validate now, if Multi-threading is working for client connections.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 10 13:50:45 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: abbas/abbas@labdb
Last Successful login time: Mon Nov 10 2014 13:41:39 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select p.spid from v$process p, v$session s
  2  where p.addr=s.paddr and s.sid=(select sys_context('userenv','sid') from dual);

SPID
------------------------
8334

SQL> !ps -ef | grep 8334
oracle    8334     1  2 13:18 ?        00:00:54 ora_u005_labdb
oracle    9433  9419  0 13:51 pts/1    00:00:00 /bin/bash -c ps -ef | grep 8334

As expected the client session is spawned as a thread under the container process (u005).

We can also query the v$process VIEW to get the thread details and the parent process under which the threads are spawned.

SQL> select SPID as "Process ID" , STID as "Thread ID",
  2  PNAME as "Process Name", EXECUTION_TYPE as "Process Type"
  3  from v$process order by 4,1,2;

Process ID               Thread ID                Proce Process Ty
------------------------ ------------------------ ----- ----------
                                                        NONE
8306                     8306                     PMON  PROCESS
8310                     8310                     PSP0  PROCESS
8314                     8314                     VKTM  PROCESS
8348                     8348                     DBW0  PROCESS
8485                     8485                           PROCESS
8320                     8320                     SCMN  THREAD
8320                     8324                     GEN0  THREAD
8320                     8327                     MMAN  THREAD
8320                     8341                     DBRM  THREAD
8320                     8351                     LGWR  THREAD
8320                     8354                     CKPT  THREAD
8320                     8357                     SMON  THREAD
8320                     8363                     LREG  THREAD
8320                     8366                     RBAL  THREAD
8320                     8369                     ASMB  THREAD
8334                     8334                     SCMN  THREAD
8334                     8338                     DIAG  THREAD
8334                     8344                     DIA0  THREAD
8334                     8360                     RECO  THREAD
8334                     8372                     MMON  THREAD
8334                     8377                     MMNL  THREAD
8334                     8380                     D000  THREAD
8334                     8385                     S000  THREAD
8334                     8388                     MARK  THREAD
8334                     8391                     N000  THREAD
8334                     8454                     TMON  THREAD
8334                     8457                     ARC0  THREAD
8334                     8460                     ARC1  THREAD
8334                     8463                     ARC2  THREAD
8334                     8466                     ARC3  THREAD
8334                     8469                     TT00  THREAD
8334                     8472                     SMCO  THREAD
8334                     8475                     FBDA  THREAD
8334                     8478                     W000  THREAD
8334                     8481                     AQPC  THREAD
8334                     8492                     CJQ0  THREAD
8334                     8519                     QM02  THREAD
8334                     8528                     Q002  THREAD
8334                     8534                     Q003  THREAD
8334                     8543                     P000  THREAD
8334                     8546                     P001  THREAD
8334                     8549                     P002  THREAD
8334                     8552                     P003  THREAD
8334                     8747                     W001  THREAD
8334                     8924                     W002  THREAD
8334                     9422                           THREAD

47 rows selected.

As we can see from the output, almost all of the database processes are implemented as OS threads.

Tracing a Threaded session

I was wondering, if the Multi-threaded model has changed the tracing mechanism of the sessions. So, I tried tracing my session as shown here

[oracle@labserver trace]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 10 14:01:07 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: abbas/abbas
Last Successful login time: Mon Nov 10 2014 13:50:52 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


SQL> alter session set tracefile_identifier='tracethread';

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> select sysdate from dual;

SYSDATE
---------
10-NOV-14

SQL> exit

[oracle@labserver trace]$ ls -lrt *tracethread*
-rw-r----- 1 oracle oinstall  144 Nov 10 14:01 labdb_ora_8334_9632_tracethread.trm
-rw-r----- 1 oracle oinstall 2153 Nov 10 14:01 labdb_ora_8334_9632_tracethread.trc

Tracing method has not changed with the threading concept. It is just that the trace file would contain the parent process and thread number for identification.

Terminating a Threaded session

We can terminate a session from the database using the old known method ‘alter system kill session’ without any issue as the method is based on SID and SERIAL#.

SQL> select s.sid,s.serial#,s.username,s.status,p.spid,p.stid,p.execution_type
  2  from v$session s, v$process p
  3  where s.PADDR=p.ADDR
  4  and s.username='ABBAS';

       SID    SERIAL# USERNAME                       STATUS   SPID                     STID                     EXECUTION_
---------- ---------- ------------------------------ -------- ------------------------ ------------------------ ----------
        24        217 ABBAS                          INACTIVE 8334                     9755                     THREAD

SQL> alter system kill session '24,217';

System altered.

SQL> select s.sid,s.serial#,s.username,s.status,p.spid,p.stid,p.execution_type
  2  from v$session s, v$process p
  3  where s.PADDR=p.ADDR
  4  and s.username='ABBAS';

no rows selected

However, killing a threaded session from the OS with the infamous ‘Kill -9’ command would not be possible as killing the thread would result in termination of its parent process and in turn may lead to database termination.

OS Authentication

When we setup a Multi-threaded process model, OS Authentication would not work as each connection must to go through the dedicated connection broker. Connecting to a Multi-threaded database with OS authentication would lead to following errors.

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 10 14:21:30 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

The solution is not use OS authentication and rather use a password while connecting to the Multi-threaded database

[oracle@labserver ~]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Mon Nov 10 14:21:30 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter user-name: / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: sys/oracle as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

References

THREADED_EXECUTION
CONNECTION_BROKERS

4 Comments
  1. Saha
    • Abu Fazal Abbas
  2. Saha
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed