Oracle Multithreaded : does it worth a try ?

multi-thread

Yes, you should have a look at this very new Database 12c feature. I did, and I was quite surprised by the effect on the overall performance of my test database.

 

Why use “Oracle Multithreaded”?

There are still some applications (like those written in PHP or your old home-designed applications) that don’t use “application server connection pooling”. Those applications may be very session-consuming which means process-consuming in our case.

As you know, the more processes, the more context switches. This has a performance cost that you should always avoid.

There are a few solutions to your problem (in historical order):

Solution

Description Pros Cons
Shared Servers You set up dispatchers that pool sessions on processes at database level Works since 8i.Works great for long running transactions. Not efficient with short database activity sessions.
Database Resident Connection Pooling (DRCP) DRCP pools database server processes and sessions which are shared across connections from multiple application processes (4) Works since 11g.Pools can be shared by applications (1).Very Efficient. Doesn’t pool background processes (more than 40 per instance in 12c).Not supported by all connection drivers (2).You have to configure the client’s connection side.
Multithreaded configuration You configure your database to group Oracle processes in system processes using threads (3). No application change.No limitations.Pool all the processes (background and foreground). Not available before DB12C.
(1)    If you have 10 application servers each handling 20 connections in a pool but only 25 active sessions at any time at database level, you better set up a DRCP of 30 processes that service all this connections
(2)    Oracle Database should be 11g or above. PHP OCI driver should be OCI8 1.3 or higher.
(3)    Windows Platform works in a multithreaded mode in all Oracle Database versions, the real change is for Unix/Linux Platforms since Database 12c
(4)    Extract from http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf

You may find that DRCP helps more improving performance that Multithreaded. But that’s not the point. First there are situations where you won’t be able to use DRCP and moreover you can use both of it (even if I doubt multithreaded will make a difference on this last case – I haven’t tested it).

 

How do you set it up?

This is the easiest part of the job, there’s only one parameter to change at instance level and one line to add at listener level.

First let’s have a look at background processes on an “out of the box” DB12c environment (Oracle Enterprise Linux 6 x64):

[oracle@oel6-db12C-tbo ~]$ ps -ef|grep MYCDB

oracle    2736     1  0 11:18 ?        00:00:00 ora_pmon_MYCDB

oracle    2738     1  0 11:18 ?        00:00:00 ora_psp0_MYCDB

oracle    2740     1 27 11:18 ?        00:00:40 ora_vktm_MYCDB

oracle    2744     1  0 11:18 ?        00:00:00 ora_gen0_MYCDB

oracle    2746     1  0 11:18 ?        00:00:00 ora_mman_MYCDB

oracle    2750     1  0 11:18 ?        00:00:00 ora_diag_MYCDB

oracle    2752     1  0 11:18 ?        00:00:00 ora_dbrm_MYCDB

oracle    2754     1  0 11:18 ?        00:00:00 ora_dia0_MYCDB

oracle    2756     1  0 11:18 ?        00:00:00 ora_dbw0_MYCDB

oracle    2758     1  0 11:18 ?        00:00:00 ora_lgwr_MYCDB

oracle    2760     1  0 11:18 ?        00:00:00 ora_ckpt_MYCDB

oracle    2762     1  0 11:18 ?        00:00:00 ora_smon_MYCDB

oracle    2764     1  0 11:18 ?        00:00:00 ora_reco_MYCDB

oracle    2766     1  0 11:18 ?        00:00:00 ora_lreg_MYCDB

oracle    2768     1  1 11:18 ?        00:00:02 ora_mmon_MYCDB

oracle    2770     1  0 11:18 ?        00:00:00 ora_mmnl_MYCDB

oracle    2772     1  0 11:18 ?        00:00:00 ora_d000_MYCDB

oracle    2774     1  0 11:18 ?        00:00:00 ora_s000_MYCDB

oracle    2776     1  0 11:18 ?        00:00:00 ora_n000_MYCDB

oracle    2788     1  0 11:18 ?        00:00:00 ora_tmon_MYCDB

oracle    2790     1  0 11:18 ?        00:00:00 ora_tt00_MYCDB

oracle    2792     1  0 11:18 ?        00:00:00 ora_smco_MYCDB

oracle    2794     1  0 11:18 ?        00:00:00 ora_aqpc_MYCDB

oracle    2798     1  3 11:18 ?        00:00:03 ora_p000_MYCDB

oracle    2800     1  4 11:18 ?        00:00:06 ora_p001_MYCDB

oracle    2802     1  0 11:18 ?        00:00:00 ora_p002_MYCDB

oracle    2804     1  0 11:18 ?        00:00:00 ora_w000_MYCDB

oracle    2806     1  0 11:18 ?        00:00:00 ora_p003_MYCDB

oracle    2808     1  1 11:18 ?        00:00:01 ora_cjq0_MYCDB

oracle    2830     1  0 11:18 ?        00:00:00 ora_qm02_MYCDB

oracle    2834     1  0 11:18 ?        00:00:00 ora_q002_MYCDB

oracle    2836     1  0 11:18 ?        00:00:00 ora_q003_MYCDB

oracle    2848     1  0 11:18 ?        00:00:00 ora_p004_MYCDB

oracle    2850     1  0 11:18 ?        00:00:00 ora_p005_MYCDB

oracle    2852     1  0 11:18 ?        00:00:00 ora_p006_MYCDB

oracle    2854     1  0 11:18 ?        00:00:00 ora_p007_MYCDB

 

There are 33 processes (without any user connection).

Let’s switch to “Multithreaded” mode :

First we modify the “thread_execution” parameter:

SQL> show parameter threaded_execution

NAME                                                         TYPE  VALUE

------------------------------------ ----------- ------------------------------

threaded_execution                                boolean            FALSE

SQL> alter system set threaded_execution=TRUE scope=spfile;

A restart of the instance is mandatory.

Next we configure the listener (one of its jobs is to spawn processes when a connection is requested), adding the last line to the configuration file (listener.ora):

# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oel6-db12C-tbo)(PORT = 1521))
    )
  )
DEDICATED_THROUGH_BROKER_LISTENER=ON

A restart of the listener is mandatory.

That’s all.

Now let’s see how it impacts the operating system:

[oracle@oel6-db12C-tbo admin]$ ps -ef|grep MYCDB

oracle    5225     1  0 11:45 ?        00:00:00 ora_pmon_MYCDB

oracle    5227     1  0 11:45 ?        00:00:00 ora_psp0_MYCDB

oracle    5229     1 30 11:45 ?        00:00:10 ora_vktm_MYCDB

oracle    5233     1  2 11:45 ?        00:00:00 ora_u004_MYCDB

oracle    5238     1 53 11:45 ?        00:00:18 ora_u005_MYCDB

oracle    5245     1  0 11:45 ?        00:00:00 ora_dbw0_MYCDB

There are only 6 processes (instead of 33 before the change).

 

A few things to know about “multithreaded configurations”

You can’t use OS authentication anymore:

[oracle@oel6-db12C-tbo admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 11:48:05 2013

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

ERROR:

ORA-01017: invalid username/password; logon denied

 [oracle@oel6-db12C-tbo admin]$

[oracle@oel6-db12C-tbo admin]$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Sep 13 11:48:13 2013

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

Enter password:

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

SQL>

This is the expected behavior, not a bug.

Do not kill sessions using Unix “kill” command to kill OS processes, sessions are grouped into processes where internal and user sessions are mixed:

SQL> select s.username,sid as session_id, spid as process_id, stid as thread_id,
pname as process_name from v$process p, v$session s  WHERE  s.paddr = p.addr order by process_id,
thread_id; (results are filtered on spid 10959);

username     sid process_id   thread_id     process_name           
------------ --- ---------------- ----------------- ----           
            1018 10959            10959             SCMN           
             765 10959            10961             DIAG           
               2 10959            10963             DIA0           
             257 10959            10971             RECO           
             767 10959            10973             MMON           
            1020 10959            10974             MMNL           
             514 10959            10984             TMON           
             768 10959            10985             TT00           
            1021 10959            10986             SMCO           
            1275 10959            10987             FBDA           
               4 10959            10988             AQPC           
             258 10959            10989             W000           
             515 10959            11015             CJQ0           
             771 10959            11030             QM02           
            1277 10959            11032             Q002           
               6 10959            11033             Q003           
SYS          510 10959            11093                            
            1029 10959            11129             W001           
             268 10959            11182             W002           
            1023 10959            11193             W003           
SOE          521 10959            11251                            
SOE          775 10959            11252                            
SOE         1279 10959            11253                            
SOE            9 10959            11254                            
SOE          262 10959            11255                            
SOE          516 10959            11256                            
SOE          770 10959            11257                            
SOE         1028 10959            11258                            
SOE         1283 10959            11259                            
SOE            8 10959            11260

How does it impact performance?

In order to compare both configurations (with and without multithreaded enabled) we used the well-known “Swingbench” tool (which does not support DRCP by the way).

We did several tests that can be resumed in these reports (the X-axis is the number of user sessions):

process

The number of processes stays low in multithreaded mode: no more than 17 for 400 user sessions.

You don’t see any data in “normal mode” for more than 300 users, this is because it hanged after that. We had the same problem with the multithreaded mode but with more than 400 hundred sessions which means that we are 25% more scalable.

transactions per second

 

In multithreaded mode we can support more transactions per second and without any saturation effect, again we are more scalable.

 

Which conclusion?

Well, it looks like with multithreaded mode we are more scalable than without in those cases where there’s no available connection pooling.

This is quite easy to setup, test and use.

What about your environments?

Thomas Bordeau

About Thomas Bordeau

Thomas Bordeau has written 10 post in this blog.

Directeur Technique Région Ouest chez EasyTeam

One thought on “Oracle Multithreaded : does it worth a try ?

  1. Pingback: #DATABASE #ORACLE by Thomas Bordeau : Oracle Multithreaded : does it worth a try ? | Database Scene

Leave a Reply