{"id":298,"date":"2013-06-26T09:07:43","date_gmt":"2013-06-26T07:07:43","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=298"},"modified":"2020-08-18T16:57:52","modified_gmt":"2020-08-18T14:57:52","slug":"oracle-database-12c-multithreaded-execution-or-how-make-processes-decrease","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/oracle-database-12c-multithreaded-execution-or-how-make-processes-decrease\/","title":{"rendered":"Oracle Database 12c: Multithreaded Execution (or how make processes decrease)"},"content":{"rendered":"

\"http:\/\/subeteanime.blogspot.ch\/<\/a> Too many background processes<\/strong><\/p>\n

Oracle instances on Unix\/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user process, but this is prehistory…). The background processes number has increased to support all the new features of Oracle, up to this new Oracle 12c release. On a simple database installation you’ll be surprised to have this output from a ps command (38 processes):<\/p>\n

# ps -eaf | grep CLASSIC | grep -v grep\r\n\r\noracle 3582 1 0 21:59 ? 00:00:00 ora_pmon_CLASSIC \r\noracle 3584 1 0 21:59 ? 00:00:00 ora_psp0_CLASSIC \r\noracle 3590 1 4 21:59 ? 00:00:51 ora_vktm_CLASSIC \r\noracle 3596 1 0 21:59 ? 00:00:00 ora_gen0_CLASSIC \r\noracle 3599 1 0 21:59 ? 00:00:00 ora_mman_CLASSIC \r\noracle 3608 1 0 21:59 ? 00:00:00 ora_diag_CLASSIC \r\noracle 3612 1 0 21:59 ? 00:00:00 ora_dbrm_CLASSIC \r\noracle 3616 1 0 21:59 ? 00:00:00 ora_dia0_CLASSIC \r\noracle 3620 1 0 21:59 ? 00:00:00 ora_dbw0_CLASSIC \r\noracle 3624 1 0 21:59 ? 00:00:04 ora_lgwr_CLASSIC \r\noracle 3628 1 0 21:59 ? 00:00:00 ora_ckpt_CLASSIC \r\noracle 3632 1 0 21:59 ? 00:00:00 ora_smon_CLASSIC \r\noracle 3636 1 0 21:59 ? 00:00:00 ora_reco_CLASSIC \r\noracle 3640 1 0 21:59 ? 00:00:00 ora_lreg_CLASSIC \r\noracle 3644 1 0 21:59 ? 00:00:00 ora_rbal_CLASSIC \r\noracle 3648 1 0 21:59 ? 00:00:00 ora_asmb_CLASSIC \r\noracle 3652 1 0 21:59 ? 00:00:01 ora_mmon_CLASSIC \r\noracle 3659 1 0 21:59 ? 00:00:00 ora_mmnl_CLASSIC \r\noracle 3664 1 0 21:59 ? 00:00:00 ora_d000_CLASSIC \r\noracle 3667 1 0 21:59 ? 00:00:00 ora_s000_CLASSIC \r\noracle 3672 1 0 21:59 ? 00:00:00 ora_mark_CLASSIC \r\noracle 3707 1 0 21:59 ? 00:00:01 ora_o000_CLASSIC \r\noracle 3717 1 0 21:59 ? 00:00:01 ora_o001_CLASSIC \r\noracle 3725 1 0 21:59 ? 00:00:00 ora_tmon_CLASSIC \r\noracle 3729 1 0 21:59 ? 00:00:00 ora_tt00_CLASSIC \r\noracle 3736 1 0 21:59 ? 00:00:00 ora_smco_CLASSIC \r\noracle 3738 1 0 22:00 ? 00:00:00 ora_w000_CLASSIC \r\noracle 3749 1 0 22:00 ? 00:00:00 ora_fbda_CLASSIC \r\noracle 3751 1 0 22:00 ? 00:00:00 ora_aqpc_CLASSIC \r\noracle 3757 1 0 22:00 ? 00:00:00 ora_qm02_CLASSIC \r\noracle 3759 1 0 22:00 ? 00:00:00 ora_p000_CLASSIC \r\noracle 3763 1 0 22:00 ? 00:00:00 ora_p001_CLASSIC \r\noracle 3765 1 0 22:00 ? 00:00:00 ora_q002_CLASSIC \r\noracle 3767 1 0 22:00 ? 00:00:00 ora_p002_CLASSIC \r\noracle 3769 1 0 22:00 ? 00:00:00 ora_q003_CLASSIC \r\noracle 3771 1 0 22:00 ? 00:00:00 ora_p003_CLASSIC \r\noracle 3774 1 0 22:00 ? 00:00:00 ora_cjq0_CLASSIC \r\noracle 3801 1 0 22:00 ? 00:00:02 ora_vkrm_CLASSIC<\/pre>\n
If you have consolidated many databases without the pluggable database feature, you’ll end up to have several hundreds of processes even without users connected. But Oracle 12c now introduce the possibility to start an instance using multithreading instead of the traditional processes. This could lead to some optimizations due to the shared process memory, and reduced context switches overhead, I presume (need to test it).<\/p>\n
 <\/p>\n
Enabling the Multithreaded Execution<\/strong><\/p>\n
<\/strong>By default this feature is not enabled, so you have to set it explicitly:<\/p>\n
SQL> alter system set threaded_execution=true scope=spfile;\r\n\r\nSystem altered.\r\n\r\nSQL><\/pre>\n
And in parallel, you’ll need to add this line to the listener.ora:<\/p>\n
DEDICATED_THROUGH_BROKER_listener=on<\/pre>\n
After a restart, the instance will show only a bunch of processes:<\/p>\n
# ps -eaf | grep CLASSIC | grep -v grep\r\noracle 4792 1 0 22:25 ? 00:00:00 ora_pmon_CLASSIC\r\noracle 4794 1 0 22:25 ? 00:00:00 ora_psp0_CLASSIC\r\noracle 4800 1 2 22:25 ? 00:00:01 ora_vktm_CLASSIC\r\noracle 4804 1 1 22:25 ? 00:00:00 ora_u004_CLASSIC\r\noracle 4810 1 7 22:25 ? 00:00:03 ora_u005_CLASSIC\r\noracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC\r\noracle 4884 1 0 23:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))<\/pre>\n
The remaining processes<\/strong><\/p>\n
So we have the Process Monitor (pmon), the Process Spawner (psp0), the Virtual Keeper of Time (vktm), the Database Writer (dbw0) and two new multithreaded processes (u004) and (u005). “U” can stand for User or Unified?<\/p>\n
 <\/p>\n
Where can I find the information on the other processes?<\/strong><\/p>\n
They still exist in the v$process view, thus leading to some confusion when talking about Oracle Processes with your sysadmins… The new EXECUTION_TYPE column show if the Oracle Process is executed as a thread or as an OS process, and the SPID let us know which process actually executes it.<\/p>\n
  PID SPID      PNAME EXECUTION_\r\n----- --------- ----- ----------\r\n    2 4792      PMON  PROCESS   \r\n    3 4794      PSP0  PROCESS   \r\n    4 4800      VKTM  PROCESS   \r\n    5 4804      GEN0  THREAD    \r\n    6 4804      SCMN  THREAD    \r\n   18 4804      LREG  THREAD    \r\n   19 4804      RBAL  THREAD    \r\n   20 4804      ASMB  THREAD    \r\n   11 4804      DBRM  THREAD    \r\n   14 4804      LGWR  THREAD    \r\n   15 4804      CKPT  THREAD    \r\n   16 4804      SMON  THREAD    \r\n    7 4804      MMAN  THREAD    \r\n   17 4810      RECO  THREAD    \r\n   12 4810      DIA0  THREAD    \r\n   10 4810      SCMN  THREAD    \r\n    9 4810      DIAG  THREAD    \r\n   25 4810      N000  THREAD    \r\n   50 4810      Q002  THREAD    \r\n   49 4810      W004  THREAD    \r\n   21 4810      MMON  THREAD    \r\n   22 4810      MMNL  THREAD    \r\n   23 4810      D000  THREAD    \r\n   24 4810      S000  THREAD    \r\n   51 4810      Q003  THREAD    \r\n   26 4810      MARK  THREAD    \r\n   27 4810      W001  THREAD    \r\n   28 4810            THREAD    \r\n   29 4810            THREAD    \r\n   30 4810      TMON  THREAD    \r\n   31 4810      TT00  THREAD    \r\n   32 4810      SMCO  THREAD    \r\n   33 4810      FBDA  THREAD    \r\n   34 4810      W000  THREAD    \r\n   35 4810      AQPC  THREAD    \r\n   36 4810      CJQ0  THREAD    \r\n   37 4810      P000  THREAD    \r\n   38 4810      P001  THREAD    \r\n   39 4810      P002  THREAD    \r\n   40 4810      P003  THREAD    \r\n   41 4810      VKRM  THREAD    \r\n   42 4810            THREAD    \r\n   43 4810      O000  THREAD    \r\n   45 4810      W002  THREAD    \r\n   46 4810      QM02  THREAD    \r\n   47 4810      W003  THREAD    \r\n   13 4818      DBW0  PROCESS   \r\n    8 4884            PROCESS   \r\n    1                 NONE<\/pre>\n
 <\/p>\n
What about the User processes?<\/strong><\/p>\n
Well, I’ve spawned 200 user processes with sqlplus, and got 200 threads:<\/p>\n
SQL> select BACKGROUND, EXECUTION_TYPE, count(*)\r\n2> from v$process group by background, EXECUTION_TYPE;\r\n\r\nB EXECUTION_   COUNT(*)\r\n- ---------- ----------\r\n1 PROCESS             4\r\n1 THREAD             34\r\n  PROCESS             1\r\n  NONE                1\r\n  THREAD            200<\/pre>\n
On the OS side, I’ve registered an additional process to distribute the load of the new user processes. Damn, I start to being confusional using the term “process” o_O<\/p>\n
[oracle@luc12c01 ~]$ ps -eaf | grep CLASSIC | grep -v grep\r\n\r\noracle 4792 1 0 22:25 ? 00:00:01 ora_pmon_CLASSIC\r\noracle 4794 1 0 22:25 ? 00:00:01 ora_psp0_CLASSIC\r\noracle 4800 1 2 22:25 ? 00:03:28 ora_vktm_CLASSIC\r\noracle 4804 1 0 22:25 ? 00:00:08 ora_u004_CLASSIC\r\noracle 4810 1 0 22:25 ? 00:01:09 ora_u005_CLASSIC\r\noracle 4818 1 0 22:25 ? 00:00:00 ora_dbw0_CLASSIC\r\noracle 4884 1 0 22:25 ? 00:00:01 oracleCLASSIC (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))\r\noracle 8083 1 1 23:50 ? 00:00:03 ora_u010_CLASSIC<\/pre>\n
On the session side however, all the user processes are DEDICATED.<\/p>\n
SQL> select server, count(*) from v$session group by server;\r\n\r\nSERVER COUNT(*)\r\n--------- ----------\r\nDEDICATED 232<\/pre>\n
 <\/p>\n
\u00a0A huge side effect<\/strong><\/p>\n
By using the multithreaded execution, the operating system authentication doesn’t work.<\/p>\n
[oracle@luc12c01 ~]$ sqlplus \/ as sysdba\r\n\r\nSQL*Plus: Release 12.1.0.1.0 Production on Fri May 10 01:14:17 2013\r\n\r\nCopyright (c) 1982, 2013, Oracle. All rights reserved.\r\n\r\nERROR:\r\nORA-01017: invalid username\/password; logon denied\r\n\r\nEnter user-name:<\/pre>\n
Unless Oracle will review it’s authentication mechanism in a future patchset, you’ll need to rely on the password file and use the password to connect to the instance as sysdba, even locally.<\/p>\n
What about performance?<\/strong><\/p>\n
In theory, threads should be faster and with a lower footprint:<\/p>\n
The main benefit of threads (as compared to multiple processes) is that the context switches are much cheaper than those required to change current processes. Sun reports that a fork() takes 30 times as long as an unbound thread creation and 5 times as long as a boundthread creation.<\/p>\n
http:\/\/www.princeton.edu\/~unix\/Solaris\/troubleshoot\/process.html<\/a><\/p><\/blockquote>\n
and<\/p>\n
In some operating systems running on some hardware, switching between threads belonging to the same process is much faster than switching to a thread from different process (because it requires more complicated process context switch).
\nhttp:\/\/en.wikipedia.org\/wiki\/<\/wbr>Thread_switching_latency<\/a><\/p><\/blockquote>\n
In practice, I’ll do some tests and let you know! \ud83d\ude42<\/p>\n
 <\/p>\n
What about the good old OS kill command to terminate processes?<\/strong><\/p>\n
Good question! Currently I have not found any references to an orakill command (that exists on Windows). Hope it will arrive soon!<\/p>\n
Cheers<\/p>\n
—<\/p>\n
Ludo<\/p>\n","protected":false},"excerpt":{"rendered":"
Too many background processes Oracle instances on Unix\/Linux servers have been composed historically by separated server processes to allow the database to be multi-user, in opposite with Windows that has always been multithread (Oracle 7 on MS-DOS was a single-user … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,326,3,52,330,132],"tags":[84,80,22,75,83],"class_list":["post-298","post","type-post","status-publish","format-standard","hentry","category-linux","category-oracle","category-oracledb","category-12c","category-oracle-inst-upg","category-triblog","tag-multithread","tag-new-features","tag-oracle-database","tag-oracle-database-12c","tag-threaded_execution"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/298","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=298"}],"version-history":[{"count":14,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/298\/revisions"}],"predecessor-version":[{"id":1988,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/298\/revisions\/1988"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=298"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=298"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=298"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}