Oracle 12c - Multi threaded Model

For the databases running on UNIX and Linux operating sysems, 12c release introduces new multi threaded model for the execution of oracle processes.

By default multiprocess execution model is enabled for the 12c database. In order to switch to the multithreaded model, the following parameter needs to be set

---

We also need to configure the listener to include the following parameter to allow DB connections using listener to spawn multi threads

With the mutlithreaded model, system resources are utilized better when compared to the traditionl multiprocess model. One Significant difference you see with this mode is the reduction in the number of processes that are spawned on the server. 

For the installation that I am using, with the default mode, there are 40 plus processes running in the background. Whereas in the new mode, it shows only 5 processes ( as seen at the end of the note)

SQL> show parameter thread

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu         integer     2
thread                     integer     0
threaded_execution             boolean     FALSE

Background Processes List

[oracle@kirlinc kiran]$ ps -ef|grep -v grep|grep ora_|awk -F " " '{ print $8 }'|awk 'NR%5!=0{printf "%s ",$0;next}1'
ora_pmon_ora12 ora_psp0_ora12 ora_vktm_ora12 ora_gen0_ora12 ora_mman_ora12
ora_diag_ora12 ora_dbrm_ora12 ora_dia0_ora12 ora_dbw0_ora12 ora_lgwr_ora12
ora_ckpt_ora12 ora_smon_ora12 ora_reco_ora12 ora_lreg_ora12 ora_mmon_ora12
ora_mmnl_ora12 ora_d000_ora12 ora_tmon_ora12 ora_tt00_ora12 ora_smco_ora12
ora_w000_ora12 ora_aqpc_ora12 ora_p000_ora12 ora_p001_ora12 ora_p002_ora12
ora_p003_ora12 ora_qm02_ora12 ora_q002_ora12 ora_q003_ora12 ora_cjq0_ora12
ora_w001_ora12 ora_w002_ora12 ora_w003_ora12 ora_w004_ora12 ora_w005_ora12
ora_w006_ora12 ora_w007_ora12 ora_w008_ora12 ora_w009_ora12 ora_s000_ora12
ora_p004_ora12 ora_p005_ora12 ora_p006_ora12 ora_p007_ora12 

Another important detail to be noted with the multi-threaded execution enabled is that you can not use os_authentication feature as of the latest release. Instead, you will need to login with the password.

SQL> alter system set threaded_execution=true scope=spfile;
System altered.

SQL> startup force
ORA-01017: invalid username/password; logon denied

In the above screenshot, you see that database can not be started as the Operating System Authentication stopped working and failed with "Invalid username/password" error.

SQL> conn sys as sysdba
Enter password: 
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

The previous startup command started only the instances processes, but failed with authentication error when before attempting to mount and open the database. We will need to login with the password to bring up the database

SQL> create user c##admin identified by admin container=all;
create user c##admin identified by admin container=all
                                   *
ERROR at line 1:
ORA-01109: database not open

SQL> select count(*) from cdb_objects;
select count(*) from cdb_objects
                     *
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed
tables or views only

SQL> shutdown abort
ORACLE instance shut down.

connect using the password..

SQL> conn sys as sysdba
Enter password: 
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2289016 bytes
Variable Size          989856392 bytes
Database Buffers      654311424 bytes
Redo Buffers            7061504 bytes
Database mounted.
Database opened.
SQL> select count(*) from cdb_objects;

  COUNT(*)
----------
    181526

SQL> 

You can simply generate a new password file using orapwd command and login with the new password.

[oracle@kirlinc dbs]$ sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Tue Mar 11 17:05:25 2014

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORA-01017: invalid username/password; logon denied
SQL> shutdown abort
ORA-01031: insufficient privileges
SQL> 

==> generate new password file

[oracle@kirlinc dbs]$ orapwd file=orapwora12 password=sys1234

SQL> conn sys/sys1234 as sysdba
Connected.
SQL> startup force
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size            2289016 bytes
Variable Size          989856392 bytes
Database Buffers      654311424 bytes
Redo Buffers            7061504 bytes
Database mounted.
Database opened.
SQL> 

==> Database with the new mode has been started..let's check the back ground processes now.

[kiran@kirlinc ~]$ pidstat|grep ora_|awk -F " " '{ print $9 }'|awk 'NR%5!=0{printf "%s ",$0;next}1'
ora_pmon_ora12 ora_psp0_ora12 ora_vktm_ora12 ora_scmn_ora12 ora_scmn_ora12
ora_dbw0_ora12 [kiran@kirlinc ~]$ 

As you see there are only 5 of the thread processes running for the whole database. You can query v$process view to find more information about the running thread processes.

SQL> select count(*),execution_type from v$process group by execution_type;

  COUNT(*) EXECUTION_
---------- ----------
     4 PROCESS
     1 NONE
    35 THREAD

SQL> select program from v$process where execution_type='PROCESS';

PROGRAM
------------------------------------------------
oracle'@'kirlinc.home (PMON)
oracle'@'kirlinc.home (PSP0)
oracle'@'kirlinc.home (VKTM)
oracle'@'kirlinc.home (DBW0)

Not all of the processes on the server are executed with threaded model.. There are still some processes running as PROCESSES instead of threads, this is an expected behavior as of this release.

Cheers!