Multi-Process Multi-Threaded Oracle
If you are running Oracle databases on Unix or Linux you probably know that there are many processes even if no user is connected. But if you run Oracle on Microsoft Windows you only see one processes and all Actions are connected to it as threads. With Oracle 12c it’s now possibe to run an instance with very few processes and all tasks are running as threads. This is named “Multi-Threaded Oracle“.
How does it work?
To enable multi threading you have to set the Parameter threaded_execution to “TRUE“. After the instance restart only few processes still exist (for example pmon). But every user processes still runs with a dedicated process. This can be changed with the listener Parameter “DEDICATED_THROUGH_BROKER_LISTENER=ON“. Now you will only see very few processes with the ps command. But there is a little pitfall: if the instance is running in Multi-Threaded mode you can no longer connect to the database with an OS authentication like sqlplus / as sysdba. Instead you have to use username and password all the time. This is a restriction especially for scripts running on the same box or executed via ssh.
Example
Let’s first look at the “normal” processlist for an Oracle 12c database:
% ps -ef|grep WAGNER1 oracle 8994 1 0 12:36 ? 00:00:00 ora_pmon_WAGNER1 oracle 8996 1 0 12:36 ? 00:00:00 ora_psp0_WAGNER1 oracle 8998 1 1 12:36 ? 00:00:51 ora_vktm_WAGNER1 oracle 9002 1 0 12:36 ? 00:00:00 ora_gen0_WAGNER1 oracle 9004 1 0 12:36 ? 00:00:00 ora_mman_WAGNER1 oracle 9008 1 0 12:36 ? 00:00:00 ora_diag_WAGNER1 oracle 9010 1 0 12:36 ? 00:00:00 ora_dbrm_WAGNER1 oracle 9012 1 0 12:36 ? 00:00:02 ora_dia0_WAGNER1 oracle 9014 1 0 12:36 ? 00:00:00 ora_dbw0_WAGNER1 oracle 9016 1 0 12:36 ? 00:00:00 ora_lgwr_WAGNER1 oracle 9018 1 0 12:36 ? 00:00:00 ora_ckpt_WAGNER1 oracle 9020 1 0 12:36 ? 00:00:00 ora_smon_WAGNER1 oracle 9022 1 0 12:36 ? 00:00:00 ora_reco_WAGNER1 oracle 9024 1 0 12:36 ? 00:00:00 ora_lreg_WAGNER1 oracle 9026 1 0 12:36 ? 00:00:03 ora_mmon_WAGNER1 oracle 9028 1 0 12:36 ? 00:00:02 ora_mmnl_WAGNER1 oracle 9030 1 0 12:36 ? 00:00:00 ora_d000_WAGNER1 oracle 9032 1 0 12:36 ? 00:00:00 ora_s000_WAGNER1 oracle 9044 1 0 12:37 ? 00:00:00 ora_tmon_WAGNER1 oracle 9046 1 0 12:37 ? 00:00:00 ora_tt00_WAGNER1 oracle 9048 1 0 12:37 ? 00:00:00 ora_smco_WAGNER1 oracle 9050 1 0 12:37 ? 00:00:00 ora_fbda_WAGNER1 oracle 9052 1 0 12:37 ? 00:00:00 ora_w000_WAGNER1 oracle 9054 1 0 12:37 ? 00:00:00 ora_aqpc_WAGNER1 oracle 9056 1 0 12:37 ? 00:00:01 ora_cjq0_WAGNER1 oracle 9060 1 0 12:37 ? 00:00:00 ora_p000_WAGNER1 oracle 9062 1 0 12:37 ? 00:00:00 ora_p001_WAGNER1 oracle 9064 1 0 12:37 ? 00:00:00 ora_p002_WAGNER1 oracle 9066 1 0 12:37 ? 00:00:00 ora_p003_WAGNER1 oracle 9098 1 0 12:37 ? 00:00:00 ora_qm02_WAGNER1 oracle 9102 1 0 12:37 ? 00:00:00 ora_q002_WAGNER1 oracle 9104 1 0 12:37 ? 00:00:00 ora_q003_WAGNER1 oracle 9283 1 0 12:47 ? 00:00:00 ora_w001_WAGNER1 oracle 9520 1 0 13:07 ? 00:00:00 ora_w002_WAGNER1 ...
Now the instance parameter is set to true and the instance will be restarted:
SQL> ALTER SYSETM SET threaded_execution=TRUE scope=spfile; System altered. SQL> shutdown immediate ... SQL> startup ORA-01017: invalid username/password; logon denied
As you can see it’s no longer possible to use the OS authentication but you have to use a username and password instead.
% sqlplus sys/manager as sysdba
And the process list looks like follows:
% ps -ef|grep WAGNER oracle 19286 1 0 10:39 ? 00:00:00 ora_pmon_WAGNER1 oracle 19288 1 0 10:39 ? 00:00:00 ora_psp0_WAGNER1 oracle 19290 1 1 10:39 ? 00:00:02 ora_vktm_WAGNER1 oracle 19294 1 0 10:39 ? 00:00:00 ora_u004_WAGNER1 oracle 19300 1 3 10:39 ? 00:00:05 ora_u005_WAGNER1 oracle 19306 1 0 10:39 ? 00:00:00 ora_dbw0_WAGNER1 oracle 19369 1 0 10:41 ? 00:00:00 oracleWAGNER1 (LOCAL=NO) oracle 19373 1 0 10:42 ? 00:00:00 oracleWAGNER1 (LOCAL=NO) oracle 19376 1 0 10:42 ? 00:00:00 oracleWAGNER1 (LOCAL=NO)
The instance now has only 6 background processes but the user processes are still dedicated. With the new parameter in the listener this can be changed as well:
% cat listener.ora DEDICATED_THROUGH_BROKER_LISTENER=ON ... % lsnrctl stop % lsnrctl start % ps -ef |grep WAGNER oracle 19286 1 0 10:39 ? 00:00:00 ora_pmon_WAGNER1 oracle 19288 1 0 10:39 ? 00:00:00 ora_psp0_WAGNER1 oracle 19290 1 1 10:39 ? 00:00:12 ora_vktm_WAGNER1 oracle 19294 1 0 10:39 ? 00:00:00 ora_u004_WAGNER1 oracle 19300 1 1 10:39 ? 00:00:09 ora_u005_WAGNER1 oracle 19306 1 0 10:39 ? 00:00:00 ora_dbw0_WAGNER1
All the user processes have been gone and running as threads within the context of process 19300 (ora_u005_WAGNER1). This can be validated with the view v$processes:
SQL> SELECT spid, stid, pname, execution_type, program
FROM v$process
ORDER BY execution_type, stid;
SPID STID PNAME EXECUTION_ PROGRAM
------- -------- ----- ---------- --------------------------------------
NONE PSEUDO
19300 19510 W001 THREAD oracle@wagner.carajandb.intern (W001)
19300 19515 THREAD oracle@wagner.carajandb.intern
19300 19516 THREAD oracle@wagner.carajandb.intern
Conclusion
As the next step I will run some Benchmarks but I’m optimistic that similar to pluggable databases the reducement of processes can help running multiple databases on one single Server. The most critical part is the missing OS authentication as you will probably have to change a couple of scripts you’re running. I would appreciate and comments and experiences
RANJEET KUMAR SINGH
[color=blue]Very good explanation. Now MPMT will reduce usage of CPU and Memory. Thanks for your update.