Multithreaded Architecture in Oracle12c.

Oracle August 10, 2013

Until Oracle11g, Oracle databases  on Unix/Linux OS followed process based architecture.  Starting with Oracle12c,  databases on Unix/Linux systems can be configured to support multithreaded architecture. In other words one process can have multiple operating system threads in separate address spaces.   Threads are very useful whenever a process has multiple tasks to be performed independent of others. Also  multiple threads within the same processes allows sharing of resources such as memory and CPU. So far with out much testing , I feel the benefits of multithreaded architecture  is mainly for multiple instance servers because of reduced memory/CPU and other resource consumption.

This is not to be confused with shared servers or Mutlithreaded servers. This feature can be implemented by setting initialization parameter THREADED_EXECUTION to TRUE and bouncing the database.

On my test database , the number of process with thread based architecture was reduced by 87%.  Even though Oracle does not guarantee  any given process to have a process architecture  v.s threaded architecture, I was able to consistently able to get the same results over multiple shutdowns on a single  instance server.

Number of process with process based systems(48 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle    1880     1  0 Aug09 ?        00:00:00 ora_w006_orcl12c
oracle    2018     1  0 Aug09 ?        00:00:00 ora_w007_orcl12c
oracle    3765     1  0 06:00 ?        00:00:00 ora_vkrm_orcl12c
oracle    4812     1  0 Aug09 ?        00:00:00 ora_w008_orcl12c
oracle    4947     1  0 Aug09 ?        00:00:00 ora_w009_orcl12c
oracle   27527     1  0 Aug09 ?        00:00:01 ora_pmon_orcl12c
oracle   27529     1  0 Aug09 ?        00:00:01 ora_psp0_orcl12c
oracle   27531     1  0 Aug09 ?        00:00:00 ora_vktm_orcl12c
oracle   27535     1  0 Aug09 ?        00:00:00 ora_gen0_orcl12c
oracle   27537     1  0 Aug09 ?        00:00:00 ora_mman_orcl12c
oracle   27541     1  0 Aug09 ?        00:00:00 ora_diag_orcl12c
oracle   27543     1  0 Aug09 ?        00:00:00 ora_dbrm_orcl12c
oracle   27545     1  0 Aug09 ?        00:00:36 ora_dia0_orcl12c
oracle   27547     1  0 Aug09 ?        00:00:02 ora_dbw0_orcl12c
oracle   27549     1  0 Aug09 ?        00:00:02 ora_lgwr_orcl12c
oracle   27551     1  0 Aug09 ?        00:00:02 ora_ckpt_orcl12c
oracle   27553     1  0 Aug09 ?        00:00:00 ora_lg00_orcl12c
oracle   27555     1  0 Aug09 ?        00:00:00 ora_lg01_orcl12c
oracle   27557     1  0 Aug09 ?        00:00:00 ora_smon_orcl12c
oracle   27559     1  0 Aug09 ?        00:00:00 ora_reco_orcl12c
oracle   27561     1  0 Aug09 ?        00:00:00 ora_lreg_orcl12c
oracle   27563     1  0 Aug09 ?        00:00:46 ora_mmon_orcl12c
oracle   27565     1  0 Aug09 ?        00:00:24 ora_mmnl_orcl12c
oracle   27567     1  0 Aug09 ?        00:00:00 ora_d000_orcl12c
oracle   27569     1  0 Aug09 ?        00:00:00 ora_s000_orcl12c
oracle   27581     1  0 Aug09 ?        00:00:00 ora_tmon_orcl12c
oracle   27583     1  0 Aug09 ?        00:00:00 ora_tt00_orcl12c
oracle   27585     1  0 Aug09 ?        00:00:00 ora_smco_orcl12c
oracle   27587     1  0 Aug09 ?        00:00:00 ora_fbda_orcl12c
oracle   27589     1  0 Aug09 ?        00:00:00 ora_aqpc_orcl12c
oracle   27593     1  0 Aug09 ?        00:00:00 ora_p000_orcl12c
oracle   27595     1  0 Aug09 ?        00:00:00 ora_p001_orcl12c
oracle   27597     1  0 Aug09 ?        00:00:00 ora_p002_orcl12c
oracle   27599     1  0 Aug09 ?        00:00:00 ora_p003_orcl12c
oracle   27601     1  0 Aug09 ?        00:00:00 ora_p004_orcl12c
oracle   27603     1  0 Aug09 ?        00:00:00 ora_p005_orcl12c
oracle   27605     1  0 Aug09 ?        00:00:00 ora_p006_orcl12c
oracle   27607     1  0 Aug09 ?        00:00:00 ora_p007_orcl12c
oracle   27609     1  0 Aug09 ?        00:00:13 ora_cjq0_orcl12c
oracle   27644     1  0 Aug09 ?        00:00:00 ora_w000_orcl12c
oracle   27646     1  0 Aug09 ?        00:00:00 ora_qm02_orcl12c
oracle   27650     1  0 Aug09 ?        00:00:00 ora_q002_orcl12c
oracle   27652     1  0 Aug09 ?        00:00:00 ora_q003_orcl12c
oracle   28199     1  0 Aug09 ?        00:00:00 ora_w001_orcl12c
oracle   29096     1  0 Aug09 ?        00:00:00 ora_w002_orcl12c
oracle   31615     1  0 Aug09 ?        00:00:00 ora_w003_orcl12c
oracle   31726     1  0 Aug09 ?        00:00:00 ora_w004_orcl12c
oracle   31732     1  0 Aug09 ?        00:00:00 ora_w005_orcl12c

Number of process with thread based systems(6 processes)

oracle@myserver:orcl12c:/app/oracle$ ps -ef | grep orcl12c
oracle   15016     1  0 21:17 ?        00:00:00 ora_pmon_orcl12c
oracle   15018     1  0 21:17 ?        00:00:00 ora_psp0_orcl12c
oracle   15020     1  0 21:17 ?        00:00:00 ora_vktm_orcl12c
oracle   15024     1  1 21:17 ?        00:00:01 ora_u004_orcl12c
oracle   15030     1  0 21:17 ?        00:00:00 ora_u005_orcl12c
oracle   15036     1  0 21:17 ?        00:00:00 ora_dbw0_orcl12c
oracle@myserver:orcl12c:/app/oracle$ oracle@myserver:orcl12c:/app/oracle$

So what are implications of threaded architecture?

 

1. OS Authentication

I was not able to connect as “sys as sysdba” without password after  enabling multithreaded architecture.  As of now OS authentication is not supported with threaded architecture.

oracle@myserver:orcl12c:/app/oracle$
oracle@myserver:orcl12c:/app/oracle$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 10 21:17:29 2013

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

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

Enter user-name: sys/mypasswd as sysdba

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

2. Killing Sessions

There is no change in behavior to kill sessions from the database  as the sessions are killed based on serial#  using “ALTER SYSTEM KILL SESSION ‘SID, SERIAL#’. However you loose the flexibility of killing specific sessions based on process id using OS  “kill” command if the process have multiple threads. All processes and threads can be viewed by querying V$PROCESS which has a new column STID that provides information about the threads. See sample output below.

SQL> SELECT SPID, STID, PROGRAM FROM V$PROCESS ORDER BY  1;

SPID                     STID                     PROGRAM
———————— ———————— ————————————————
15137                    15137                    oracle@ps-zsign-db1 (PMON)
15139                    15139                    oracle@ps-zsign-db1 (PSP0)
15141                    15141                    oracle@ps-zsign-db1 (VKTM)
15145                    15158                    oracle@ps-zsign-db1 (LGWR)
15145                    15147                    oracle@ps-zsign-db1 (GEN0)
15145                    15145                    oracle@ps-zsign-db1 (SCMN)
15145                    15162                    oracle@ps-zsign-db1 (SMON)
15145                    15164                    oracle@ps-zsign-db1 (LREG)
15145                    15154                    oracle@ps-zsign-db1 (DBRM)
15145                    15159                    oracle@ps-zsign-db1 (CKPT)
15145                    15160                    oracle@ps-zsign-db1 (LG00)
15145                    15161                    oracle@ps-zsign-db1 (LG01)
15145                    15148                    oracle@ps-zsign-db1 (MMAN)
15151                    15155                    oracle@ps-zsign-db1 (DIA0)
15151                    15151                    oracle@ps-zsign-db1 (SCMN)
15151                    15153                    oracle@ps-zsign-db1 (DIAG)
15151                    15213                    oracle@ps-zsign-db1 (QM02)
15151                    15167                    oracle@ps-zsign-db1 (D000)
15151                    15216                    oracle@ps-zsign-db1 (Q003)
15151                    15163                    oracle@ps-zsign-db1 (RECO)
15151                    15165                    oracle@ps-zsign-db1 (MMON)
15151                    15166                    oracle@ps-zsign-db1 (MMNL)
15151                    15792                    oracle@ps-zsign-db1
15151                    15168                    oracle@ps-zsign-db1 (S000)
15151                    15169                    oracle@ps-zsign-db1 (N000)
15151                    15176                    oracle@ps-zsign-db1 (TMON)
15151                    15177                    oracle@ps-zsign-db1 (TT00)
15151                    15178                    oracle@ps-zsign-db1 (SMCO)
15151                    15179                    oracle@ps-zsign-db1 (FBDA)
15151                    15180                    oracle@ps-zsign-db1 (AQPC)
15151                    15206                    oracle@ps-zsign-db1 (W000)
15151                    15182                    oracle@ps-zsign-db1 (CJQ0)
15151                    15183                    oracle@ps-zsign-db1 (P000)
15151                    15184                    oracle@ps-zsign-db1 (P001)
15151                    15185                    oracle@ps-zsign-db1 (P002)
15151                    15186                    oracle@ps-zsign-db1 (P003)
15151                    15187                    oracle@ps-zsign-db1 (P004)
15151                    15188                    oracle@ps-zsign-db1 (P005)
15151                    15189                    oracle@ps-zsign-db1 (P006)
15151                    15190                    oracle@ps-zsign-db1 (P007)
15151                    15207                    oracle@ps-zsign-db1 (VKRM)
15151                    15643                    oracle@ps-zsign-db1 (W001)
15151                    15215                    oracle@ps-zsign-db1 (Q002) 15157                    15157                    oracle@ps-zsign-db1 (DBW0)

3. Listener Changes

Finally update listener.ora file to set DEDICATED_THROUGH_BROKER_listener-name=ON to enables the server process to spawn threads when connections to the database are requested through the listener.

Tagged: , , , , ,

%d bloggers like this: