12c New Feature: Multi-Process Multi-Threaded Oracle


Starting with 12c the multithreaded Oracle model allows you to start threads within OS processes. This model is similar to windows where you have only one process and different threads from this one process.

This Architecture have some Benefits:

  • CPU usage reduction
  • Memory usage reduction
  • Better performance for parallel executions / operations
  • Better system reliability

Same note from the documentation:

Starting with 12.1 PMON, DBW, VKTM and PSP are always running as OS process. This can change in future releases of the 12c database.

The first pitfall is, when you try to configure your database for such use. You need a connection which is authenticated trough the password file. Otherwise you will get are error similar to this.

[oracle@# ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Tue Jul 9 22:29:13 2013

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

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

SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-01017: invalid username/password; logon denied

OK, we correct this when we login without OS authentication:

[oracle@# ~]$ sqlplus sys as sysdba

SQL*Plus: Release Production on Tue Jul 9 22:32:36 2013

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

Enter password: 
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  279752704 bytes
Fixed Size            2287480 bytes
Variable Size          201328776 bytes
Database Buffers       71303168 bytes
Redo Buffers            4833280 bytes
Database mounted.
Database opened

We started the database with the new configuration, you can check first your Oracle processes. I will not post a sample of the old style because i belive every DBA should know that. The Output of your ps command should look like this example.

[oracle@# ~]$ ps -ef | grep db12
oracle   30335     1  0 22:32 ?        00:00:00 ora_pmon_db1211
oracle   30339     1  0 22:32 ?        00:00:00 ora_psp0_db1211
oracle   30343     1  0 22:32 ?        00:00:00 ora_vktm_db1211
oracle   30349     1  0 22:32 ?        00:00:00 ora_u004_db1211
oracle   30363     1 32 22:32 ?        00:00:19 ora_u005_db1211
oracle   30377     1  0 22:32 ?        00:00:00 ora_dbw0_db1211
grid     30571     1  0 22:33 ?        00:00:00 oracle+ASM1_asmb_db1211 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid     30598     1  0 22:33 ?        00:00:00 oracle+ASM1_ocf0_db1211 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))

So here we are. Many Processes disapear, because they are now a thread and not a process. But how to check this? One way is to select the v$process view. Let us check the PID 30349 (process ora_u004_db1211) from the above example.

SQL> select spid, stid, pname from v$process where spid = 30349;

SPID             STID              PNAME
------------------------ ------------------------ -----
30349             30353              GEN0
30349             30349              SCMN
30349             30356              MMAN
30349             30392              SMON
30349             30370              DBRM
30349             30380              LGWR
30349             30383              CKPT
30349             30386              LG00
30349             30389              LG01
30349             30398              LREG
30349             30574              RBAL
30349             30567              ASMB

You can now see your old Oracle processes as thread.

But remeber:

  • An Oracle process running as a thread still uses PGA for private memory
  • An Oracle process running as a thread still uses SGA
  • You need a password file, otherwise you will get a ORA-1031

So last question (or the first for me). What will this mean for example, if you do a SQL Trace. Will more then one session spool into the same Tracefile.
The answer is simple. NO

The Tracefile Name has the SPID and the STID so it will be unique.


[oracle@x trace]$ ls -ltr db1211_ora_30363_30769*
-rw-r----- 1 oracle oinstall   271 Jul  9 22:42 db1211_ora_30363_30769.trm
-rw-r----- 1 oracle oinstall 15719 Jul  9 22:42 db1211_ora_30363_30769.trc

As always comments are welcome

Ein Gedanke zu “12c New Feature: Multi-Process Multi-Threaded Oracle

  1. Pingback: Upgrade Oracle9i/10g/11g OCA to Oracle Database 12c OCP 1Z0-067 Dumps With VCE and PDF Download (Question 16 – Question 30) | All PassLeader Oracle Dumps with VCE and PDF for Free

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:


Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s