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):
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.
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?
1 réflexion sur “Oracle Multithreaded : does it worth a try ?”
Ping : #DATABASE #ORACLE by Thomas Bordeau : Oracle Multithreaded : does it worth a try ? | Database Scene
Les commentaires sont fermés.