Monday, December 14, 2015

Oracle Multiprocess and Multithreaded Architecture

Introduction

The Oracle process architecture of Windows operating system is a different process architecture comparing to Linux/Unix operating systems. On Windows, each Oracle instance has one single process that is multithreded, i.e. each background process is a thread within the single "father" process, therefore the "processes" tab in the the task manager will display only one "oracle.exe" process per each instance, as you can see in the following screenshot (taken from one of our Oracle environments):




























On Linux/Unix on the other hand, each background process is a different OS process so by looking for all the processes you should expect to get a long list of Oracle processes, as you can see the following screenshot (taken from one of our Oracle on Linux environments):






















































Oracle 12c Multiprocess and Multithreaded Architecture

By default, the pre-12c Oracle process architecture remains the same in Oracle 12c. However, in Oracle 12c you can enable the multithreaded architecture on Unix/Linux that will cause Oracle processes on Linux/Unix to run as threads - similar to the process architecture on Windows. In order to do so, simply set the THREADED_EXECUTION initialization parameter to TRUE (default value is FALSE). This is a static parameter (SCOPE=SPFILE), therefore requires restarting the instance in order for the changes to take effect.

Demonstration

First, I will connect to one of our 12c Oracle Linux environments and set the THREADED_EXECUTION to be TRUE:
SQL> alter system set THREADED_EXECUTION=true scope=spfile;
System altered.

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

SQL> startup
Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             692060264 bytes
Database Buffers          373293056 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

Now I will check how many Oracle processes I have on the Linux environment:













As you can see, now there are much less processes than before. Most of the background processes now run as threads instead of processes but some of them might still run as operating system processes (like the PMON and the DB Writer processes).

Important things to keep in mind

  • OS authentication is not allowed with the multithreaded architecture. If you will try to connect via an OS authentication (for example, by connecting with "/ AS SYSDBA") you will encounter "ORA-01017: invalid username/password; logon denied", therefore you must use a password file for connecting with a SYSDBA/SYSOPER user.
  • When the THREADED_EXECUTION parameter is set to TRUE, you must set the DEDICATED_THROUGH_BROKER_LISTENER parameter to ON in your listener.​ora file. When that parameter is set, the listener knows that it should not spawn an OS process when a connect request is received, instead it passes the request to the database so that a database thread is spawned and answers the connection.​ 
  • In RAC environments all nodes must have the same value for the THREADED_EXECUTION parameter.

Summary

In this post I've reviewed the process architecture in Linux/Unix vs. Windows and demonstrated the new 12c Multithreaded option. This feature can be useful in order to reduce the number of OS processes in scenarios where you have many Oracle instances on the same machine which may cause a higher overhead as a result of a very high number of OS processes.

Useful Links


6 comments:

  1. Great pini ! short and to the point

    ReplyDelete
  2. Replies
    1. Thanks! I'm glad you find this information useful.

      Delete
  3. Good and valuable information about threads and processes.

    Thanks,
    Rodrigo Jorge

    ReplyDelete
    Replies
    1. Thanks for this feedback Rodrigo!
      I'm glad you found this information valuable :)

      Delete