Oracle Database 12c: Multithreaded Execution (or how make processes decrease)

Posted on by

http://subeteanime.blogspot.ch/ (cc) Too many background processes

Oracle instances on Unix/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):

If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).

 

Enabling the Multithreaded Execution

By default this feature is not enabled, so you have to set it explicitly:

And in parallel, you’ll need to add this line to the listener.ora:

After a restart, the instance will show only a bunch of processes:

The remaining processes

So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?

 

Where can I find the information on the other processes?

They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.

 

What about the User processes?

Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:

On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O

On the session side however, all the user processes are DEDICATED.

 

 A huge side effect

By using the multithreaded execution, the operating system authentication doesn’t work.

Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.

What about performance?

In theory, threads should be faster and with a lower footprint:

The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.

http://www.princeton.edu/~unix/Solaris/troubleshoot/process.html

and

In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch).
http://en.wikipedia.org/wiki/Thread_switching_latency

In practice, I’ll do some tests and let you know! 🙂

 

What about the good old OS kill command to terminate processes?

Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!

Cheers

—

Ludo

The following two tabs change content below.
My Twitter profileMy LinkedIn profile

Ludovico

Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

6 thoughts on “Oracle Database 12c: Multithreaded Execution (or how make processes decrease)”

  1. What do you mean re-enables? I have got 12.1.0.2 with the latest PSU and after the threaded_execution has been set to true I can no longer use os authentication “/as sysdba”

    Reply ↓

  4. Following a tweet by @martinberx (thank you Martin) I’ve decided to investigate a little more. Now I think I’ve got the rid of how it works…

    THREADED_EXECUTION = FALSE and LOCAL CONNECTION
    The client spawns directly an Oracle Server Process that attaches to the shared memory.

    THREADED_EXECUTION = FALSE and REMOTE CONNECTION
    The client contacts the listener. The listener spawns an Oracle Server Process that attaches to the shared memory.

    THREADED_EXECUTION = TRUE and LOCAL CONNECTION
    The client contacts a connection broker (Nnnn) that verifies the authentication with the passwordfile and then spawns a new THREAD in an existing OS process ((SCMN) in v$process or (Unnn) thgough a ps).
    That’s why THREADED_EXECUTION automatically sets USE_DEDICATED_BROKER to TRUE: to have a connection broker active even if not using connection pooling.

    THREADED_EXECUTION = TRUE and REMOTE CONNECTION
    The client contacts the listener that forward the connection request to a connection broker (Nnnn).
    Then the path is the same: The broker verifies the authentication with the passwordfile and then spawns a new THREAD in an existing OS process.
    The listener requires that the parameter DEDICATED_THROUGH_BROKER_listener is enabled to allow the listener to forward the requests to a broker (if it exists!) instead of spawning a process directly.

    One more information… the (LREG) is a new process that registers the instances and services to the listener, that’s what the PMON was doing up to release 11gR2… so the PMON has been demoted a little 🙂

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.