Home > Database, Oracle > Oracle Multithreaded : does it worth a try ?

Oracle Multithreaded : does it worth a try ?

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):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
[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:

1
2
3
4
5
6
7
8
9
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:

1
2
3
4
5
6
7
8
9
10
11
12
13
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
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.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: