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.

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

 

There are 33 processes (without any user connection).

Let’s switch to “Multithreaded” mode :

First we modify the “thread_execution” parameter:

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

A restart of the listener is mandatory.

That’s all.

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

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:

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:

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 15 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

%d bloggers like this: