Oracle 12c Database: Moving to Multithreaded Configuration

Before we look into the new Oracle 12c Multithreaded configuration option, lets first take a look at what normally happens at the OS level when Oracle is running.


Related Articles
Oracle 12c Database Installation On Solaris 11 Using VirtualBox
Oracle 12c Database: New Features

Process Based Configuration

As we all know LINUX and all UNIX based systems are process based operating systems. They create additional process for every task, even though it serves the same application. This architecture provides stability but can also introduce other problems. For example, context switching adds more time to processing because you switch back and forth from one process to the other, rather than switching from one thread to the other within the same process. The later is what takes place in a thread based operating system like Windows.


Additionally when the number of these processes grow, you may experience performance issues because of too many processes, each of them having their own separate contexts. Oracle database running on Linux uses the same architecture and every process like LGWR, DBWR, SMON has it’s own OS processes. The resources consumed are visible in OS level process monitoring tools.


Oracle 12c database has the same architecture. The below command was run on fresh install of 12c database.

$ ps -ef | grep [o]ra_

oracle 2191 1 0 06:07 ? 00:00:00 ora_pmon_cdb12c
oracle 2193 1 0 06:07 ? 00:00:00 ora_psp0_cdb12c
oracle 2195 1 31 06:07 ? 00:00:20 ora_vktm_cdb12c
oracle 2199 1 0 06:07 ? 00:00:00 ora_gen0_cdb12c
oracle 2201 1 0 06:07 ? 00:00:00 ora_mman_cdb12c
oracle 2205 1 0 06:07 ? 00:00:00 ora_diag_cdb12c
oracle 2207 1 0 06:07 ? 00:00:00 ora_dbrm_cdb12c
oracle 2209 1 0 06:07 ? 00:00:00 ora_dia0_cdb12c
oracle 2211 1 0 06:07 ? 00:00:00 ora_dbw0_cdb12c
oracle 2213 1 0 06:07 ? 00:00:00 ora_lgwr_cdb12c
oracle 2215 1 0 06:07 ? 00:00:00 ora_ckpt_cdb12c
oracle 2217 1 0 06:07 ? 00:00:00 ora_smon_cdb12c
oracle 2219 1 0 06:07 ? 00:00:00 ora_reco_cdb12c
oracle 2221 1 0 06:07 ? 00:00:00 ora_lreg_cdb12c
oracle 2223 1 5 06:07 ? 00:00:03 ora_mmon_cdb12c
oracle 2225 1 0 06:07 ? 00:00:00 ora_mmnl_cdb12c
oracle 2227 1 0 06:07 ? 00:00:00 ora_d000_cdb12c
oracle 2229 1 0 06:07 ? 00:00:00 ora_s000_cdb12c
oracle 2241 1 0 06:07 ? 00:00:00 ora_tmon_cdb12c
oracle 2243 1 0 06:07 ? 00:00:00 ora_tt00_cdb12c
oracle 2245 1 0 06:07 ? 00:00:00 ora_smco_cdb12c
oracle 2247 1 0 06:07 ? 00:00:00 ora_aqpc_cdb12c
oracle 2266 1 0 06:07 ? 00:00:00 ora_w000_cdb12c
oracle 2270 1 6 06:07 ? 00:00:03 ora_p000_cdb12c
oracle 2272 1 6 06:07 ? 00:00:03 ora_p001_cdb12c
oracle 2274 1 0 06:07 ? 00:00:00 ora_p002_cdb12c
oracle 2276 1 0 06:07 ? 00:00:00 ora_p003_cdb12c
oracle 2279 1 0 06:07 ? 00:00:00 ora_qm02_cdb12c
oracle 2283 1 0 06:07 ? 00:00:00 ora_q002_cdb12c
oracle 2285 1 0 06:07 ? 00:00:00 ora_q003_cdb12c
oracle 2309 1 0 06:07 ? 00:00:00 ora_p004_cdb12c
oracle 2311 1 1 06:07 ? 00:00:00 ora_p005_cdb12c
oracle 2329 1 5 06:08 ? 00:00:01 ora_cjq0_cdb12c
oracle 2333 1 2 06:08 ? 00:00:00 ora_p006_cdb12c
oracle 2335 1 2 06:08 ? 00:00:00 ora_p007_cdb12c
oracle 2338 1 0 06:08 ? 00:00:00 ora_vkrm_cdb12c
oracle 2346 1 0 06:08 ? 00:00:00 ora_p008_cdb12c
oracle 2348 1 0 06:08 ? 00:00:00 ora_p009_cdb12c
oracle 2350 1 0 06:08 ? 00:00:00 ora_p00a_cdb12c
oracle 2352 1 0 06:08 ? 00:00:00 ora_p00b_cdb12c
oracle 2354 1 2 06:08 ? 00:00:00 ora_j000_cdb12c
oracle 2356 1 4 06:08 ? 00:00:00 ora_j001_cdb12c
oracle 2358 1 1 06:08 ? 00:00:00 ora_j002_cdb12c
oracle 2360 1 2 06:08 ? 00:00:00 ora_j003_cdb12c
oracle 2362 1 3 06:08 ? 00:00:00 ora_j004_cdb12c
oracle 2364 1 2 06:08 ? 00:00:00 ora_j005_cdb12c
oracle 2366 1 2 06:08 ? 00:00:00 ora_j006_cdb12c
oracle 2368 1 3 06:08 ? 00:00:00 ora_j007_cdb12c
oracle 2370 1 1 06:08 ? 00:00:00 ora_j008_cdb12c
oracle 2372 1 2 06:08 ? 00:00:00 ora_j009_cdb12c
oracle 2374 1 1 06:08 ? 00:00:00 ora_j010_cdb12c
oracle 2376 1 1 06:08 ? 00:00:00 ora_j011_cdb12c
oracle 2378 1 2 06:08 ? 00:00:00 ora_j012_cdb12c
oracle 2380 1 2 06:08 ? 00:00:00 ora_j013_cdb12c
oracle 2382 1 1 06:08 ? 00:00:00 ora_j014_cdb12c
oracle 2384 1 3 06:08 ? 00:00:00 ora_j015_cdb12c
oracle 2386 1 1 06:08 ? 00:00:00 ora_j016_cdb12c
oracle 2388 1 1 06:08 ? 00:00:00 ora_j017_cdb12c
oracle 2390 1 2 06:08 ? 00:00:00 ora_j018_cdb12c
oracle 2392 1 1 06:08 ? 00:00:00 ora_j019_cdb12c
oracle 2394 1 1 06:08 ? 00:00:00 ora_j020_cdb12c
oracle 2396 1 0 06:08 ? 00:00:00 ora_j021_cdb12c
oracle 2398 1 5 06:08 ? 00:00:00 ora_m000_cdb12c

$ ps -ef | grep [o]ra_|wc
40 320 2560

As you can see every Oracle background process like PMON, LGWR etc has an equivalent OS level process. As a result there are quite a lot of processes, 40 to be exact.

Multithreaded Configuration

Starting in Oracle 12c database, you can change this and enable the new Multithreaded configuration! And that too by merely setting one parameter. You can change the architecture by making Oracle database use thread based architecture instead of process based. Once changed, all Oracle processes will be threads within a few Oracle processes. Thus if CPU moves from one Oracle process to the other, the time between the context switch will be reduced significantly as the switching from one thread to the other is within the same process. During testing, this has resulted in up to 30% performance improvements. You can use the following command in SQL*PLUS to view the current value of THREADED_EXECUTION parameter.

SQL> SHOW PARAMETER thread;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 0
threaded_execution boolean FALSE

The default value is FALSE which indicates that Oracle is not being executed by OS in threaded mode. You can change this by executing the following command.


SQL> ALTER SYSTEM SET threaded_execution=true scope=spfile;
System altered.

Once you restart the database, Oracle will be running in threaded execution mode. This can be verified by using the above command and also by checking the OS level process monitor. The number of processes should have been reduced significantly.


P.S. If you encounter “ORA-01017: invalid username/password; logon denied” while restarting database then please refer to the end of this article under OS Authentication.

SQL> SHOW PARAMETER thread;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 0
threaded_execution boolean TRUE

Also for all incoming requests to make use of this architecture you need to set the below mentioned parameter in Listener.ora file.

DEDICATED_THROUGH_BROKER_<listener-name>=ON

You need to provide your own Listener name at the end.


Now we will run the OS command again to check how many processes are created.

$ ps -ef | grep [o]ra_
oracle 2544 1 0 06:25 ? 00:00:00 ora_pmon_cdb12c
oracle 2546 1 0 06:25 ? 00:00:00 ora_psp0_cdb12c
oracle 2548 1 25 06:25 ? 00:01:47 ora_vktm_cdb12c
oracle 2552 1 0 06:25 ? 00:00:01 ora_u004_cdb12c
oracle 2558 1 9 06:25 ? 00:00:41 ora_u005_cdb12c
oracle 2564 1 0 06:25 ? 00:00:00 ora_dbw0_cdb12c

$ ps -ef | grep [o]ra_|wc
6 48 384

The number of processes have been reduced from 40 to 6. That is a huge plus and that’s because there isn’t a separate process for every Oracle process. Most of the Oracle processes are now threads within these 6 processes.


On the flip side you will not be able to see or perform any actions on individual processes at OS level. This also means that you will not be able to kill any specific Oracle session from OS level commands. And neither you should try to. Because killing one OS process may end many Oracle sessions. They should only be done from inside DBA domain.


To view the the individual threads which are running you can use the following command.

SQL> SELECT pname, pid, sosid FROM v$process;
PNAME PID SOSID
----- ---------- ------------------------
1
PMON 2 2544
PSP0 3 2546
VKTM 4 2548
GEN0 5 2552_2554
SCMN 6 2552_2552
MMAN 7 2552_2555
8 2558_2580
DIAG 9 2558_2560
SCMN 10 2558_2558
DBRM 11 2552_2561
DIA0 12 2558_2562
DBW0 13 2564
LGWR 14 2552_2565
CKPT 15 2552_2566
SMON 16 2552_2567
RECO 17 2558_2568
LREG 18 2552_2569
MMON 19 2558_2570
MMNL 20 2558_2571
D000 21 2558_2572
S000 22 2558_2573
N000 23 2558_2574
TMON 24 2558_2581
TT00 25 2558_2582
SMCO 26 2558_2583
AQPC 27 2558_2584
W000 28 2558_2590
P000 29 2558_2586
P001 30 2558_2587
P002 31 2558_2588
P003 32 2558_2589
CJQ0 33 2558_2613
QM02 34 2558_2608
P004 35 2558_2602
P006 36 2558_2615
P005 37 2558_2603
W001 38 2558_2680
P007 39 2558_2616
Q002 40 2558_2610
VKRM 41 2558_2623
Q003 42 2558_2611
P008 44 2558_2682
P009 45 2558_2683
P00A 46 2558_2684
P00B 47 2558_2685

46 rows selected.

As you can see all 40 odd processes are running as for as Oracle is concerned. The SOSID column will show you whether a particular Oracle process is a process at OS level or it is a thread inside a process. The number before “_” is the process ID and number after “_” is thread ID inside that process. So first number will always be one of Process IDs found at OS level and in our case they will be 6 unique values.


The ID after the “_” is the actual thread ID and if it is missing then it means that specific Oracle process is a process at OS level and not a thread.

OS Authentication

When you enable Multithreaded configuration then you are not allowed to log into Oracle using OS level authentication. The following error will occur if you try to.

$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sat Jul 6 06:40:39 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied

Use the following method to log in as SYS.

Enter user-name: sys as sysdba
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>

Learn more on about Moving Objects and Datafile online in Oracle 12c.

Leave a Reply

Your email address will not be published.