• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Oracle 12c New Feature – Multi-Threaded architecture of processes

By admin

The Basics

Oracle 12c database introduced the Multi-Threaded architecture of processes. The multi-threaded Oracle model enables Oracle processes on UNIX and Linux to run as operating system threads in separate address spaces. It applies to both BACKGROUND PROCESSES and SERVER PROCESSES. By default, some background processes on UNIX and Linux always use threaded execution; the remaining Oracle processes run as operating system processes.

This feature can be helpful to reduce CPU and memory usage because it reduces the number of Oracle processes. It is disabled by default. You can enable this feature by setting ‘threaded_execution = TRUE‘.

When this initialization parameter is set to TRUE, the DEDICATED_THROUGH_BROKER_listener-name=ON parameter should be added to the listener.ora file, where listener-name is the name of the database listener and LOCAL_LISTENER initialization parameter should be set to a TNS name entry corresponding to your instance service. This enables the server to spawn threads when connections to the database are requested through the listener.

Example

1. Multi-threaded mode is disabled by default as shown below:

SQL> show parameter threaded

NAME                                         TYPE           VALUE
------------------------------------ ----------- ----------------
threaded_execution                       boolean       FALSE
SQL> !ps -ef|grep orcl
oracle    2590     1  0 17:17 ?        00:00:00 ora_pmon_orcl
oracle    2592     1  0 17:17 ?        00:00:00 ora_psp0_orcl
oracle    2594     1  8 17:17 ?        00:00:05 ora_vktm_orcl
oracle    2598     1  0 17:17 ?        00:00:00 ora_gen0_orcl
oracle    2600     1  0 17:17 ?        00:00:00 ora_mman_orcl
oracle    2604     1  0 17:17 ?        00:00:00 ora_diag_orcl
oracle    2606     1  0 17:17 ?        00:00:00 ora_dbrm_orcl
oracle    2608     1  0 17:17 ?        00:00:00 ora_vkrm_orcl
oracle    2610     1  0 17:17 ?        00:00:00 ora_dia0_orcl
oracle    2612     1  0 17:17 ?        00:00:00 ora_dbw0_orcl
oracle    2614     1  0 17:17 ?        00:00:00 ora_lgwr_orcl
oracle    2616     1  0 17:17 ?        00:00:00 ora_ckpt_orcl
oracle    2618     1  0 17:17 ?        00:00:00 ora_smon_orcl
oracle    2620     1  0 17:17 ?        00:00:00 ora_reco_orcl
oracle    2622     1  0 17:17 ?        00:00:00 ora_lreg_orcl
oracle    2624     1  0 17:17 ?        00:00:00 ora_pxmn_orcl
oracle    2626     1  1 17:17 ?        00:00:01 ora_mmon_orcl
oracle    2628     1  0 17:17 ?        00:00:00 ora_mmnl_orcl
oracle    2630     1  0 17:17 ?        00:00:00 ora_d000_orcl
oracle    2632     1  0 17:17 ?        00:00:00 ora_s000_orcl
oracle    2643     1  0 17:18 ?        00:00:00 ora_tmon_orcl
oracle    2645     1  0 17:18 ?        00:00:00 ora_tt00_orcl
oracle    2647     1  0 17:18 ?        00:00:00 ora_smco_orcl
oracle    2649     1  0 17:18 ?        00:00:00 ora_w000_orcl
oracle    2651     1  0 17:18 ?        00:00:00 ora_w001_orcl
oracle    2653     1  0 17:18 ?        00:00:00 ora_aqpc_orcl
oracle    2657     1  0 17:18 ?        00:00:00 ora_p000_orcl
oracle    2659     1  0 17:18 ?        00:00:00 ora_p001_orcl
oracle    2661     1  0 17:18 ?        00:00:00 ora_p002_orcl
oracle    2663     1  0 17:18 ?        00:00:00 ora_p003_orcl
oracle    2665     1  1 17:18 ?        00:00:00 ora_cjq0_orcl
oracle    2732     1  3 17:18 ?        00:00:00 ora_j000_orcl
oracle    2735     1  0 17:18 ?        00:00:00 ora_j001_orcl
oracle    2737     1  1 17:18 ?        00:00:00 ora_j002_orcl
oracle    2741     1  1 17:18 ?        00:00:00 ora_j003_orcl
oracle    2743     1  0 17:18 ?        00:00:00 ora_j004_orcl
oracle    2745     1  0 17:18 ?        00:00:00 ora_j005_orcl
oracle    2747     1  1 17:18 ?        00:00:00 ora_j006_orcl
oracle    2749     1  0 17:18 ?        00:00:00 ora_j007_orcl
oracle    2755     1  1 17:18 ?        00:00:00 ora_j008_orcl
oracle    2763     1  0 17:18 ?        00:00:00 ora_j009_orcl
oracle    2772     1  1 17:18 ?        00:00:00 ora_j010_orcl
oracle    2782     1  1 17:18 ?        00:00:00 ora_j011_orcl
oracle    2784     1  1 17:18 ?        00:00:00 ora_j012_orcl
oracle    2789     1  1 17:18 ?        00:00:00 ora_j013_orcl
oracle    2791     1  1 17:18 ?        00:00:00 ora_j014_orcl
oracle    2793     1  2 17:18 ?        00:00:00 ora_j015_orcl
oracle    2795     1  0 17:18 ?        00:00:00 ora_j016_orcl
oracle    2797     1  0 17:18 ?        00:00:00 ora_j017_orcl
oracle    2804     1  0 17:18 ?        00:00:00 ora_qm02_orcl
oracle    2806     1  0 17:18 ?        00:00:00 ora_qm03_orcl
oracle    2808     1  0 17:18 ?        00:00:00 ora_q002_orcl
oracle    2810     1  0 17:18 ?        00:00:00 ora_q003_orcl
oracle    2812     1  1 17:18 ?        00:00:00 ora_q004_orcl
oracle    2837     1 12 17:18 ?        00:00:00 ora_m002_orcl
oracle    2843     1 10 17:18 ?        00:00:00 ora_m004_orcl
oracle    2849     1 12 17:18 ?        00:00:00 ora_m000_orcl
oracle    2851     1 16 17:18 ?        00:00:00 ora_m006_orcl

2. Let’s enable multi-threaded mode. The instance should be restarted.

SQL> conn / as sysdba
SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=SPFILE;
SQL> shutdown immediate
SQL> conn sys as sysdba     <---- "conn / as sysdba" is NOT allowed in multi-threaded mode.
Enter password: 
Threaded_execution=true Prevents OS Login As Sysdba in Oracle Database 12c

Start the instance and verify the threaded_execution parameter value.

SQL> startup
SQL> show parameter threaded

NAME                                         TYPE           VALUE
------------------------------------ ----------- ------------------------------
threaded_execution                       boolean       TRUE

4. The number of background processes has decreased. Some background processes(pmon, dbw, psp, vktm) behave as before. The other background processes belong to multi-threaded processes with names ora_uxxx_[SID].

SQL> !ps -ef | grep orcl2
oracle    3185     1  0 23:00 ?        00:00:00 ora_pmon_orcl
oracle    3187     1  0 23:00 ?        00:00:00 ora_psp0_orcl
oracle    3189     1  8 23:00 ?        00:03:45 ora_vktm_orcl
oracle    3193     1  0 23:00 ?        00:00:03 ora_u004_orcl
oracle    3199     1  1 23:00 ?        00:00:39 ora_u005_orcl  
oracle    3206     1  0 23:00 ?        00:00:00 ora_dbw0_orcl

5. You can find the thread id (STID) for each background process from V$PROCESS. Multiple STID’s have the same SPID. For example, in this case, SPID 3193 has 8 threads and SPID 3199 has 34 threads.

SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid;

SPID   STID     PNAME    PROGRAM               EXECUTION_TYPE
-----  -----    -----    --------------------  --------------
                         PSEUDO                 NONE
3185   3185      PMON    oracle@oel6 (PMON)     PROCESS
3187   3187      PSP0    oracle@oel6 (PSP0)     PROCESS
3189   3189      VKTM    oracle@oel6 (VKTM)     PROCESS
3206   3206      DBW0    oracle@oel6 (DBW0)     PROCESS
3193   3193      SCMN    oracle@oel6 (SCMN)     THREAD
3193   3195      GEN0    oracle@oel6 (GEN0)     THREAD
3193   3196      MMAN    oracle@oel6 (MMAN)     THREAD
3193   3202      DBRM    oracle@oel6 (DBRM)     THREAD
3193   3207      LGWR    oracle@oel6 (LGWR)     THREAD
3193   3208      CKPT    oracle@oel6 (CKPT)     THREAD
3193   3209      SMON    oracle@oel6 (SMON)     THREAD
3193   3211      LREG    oracle@oel6 (LREG)     THREAD
3199   3199      SCMN    oracle@oel6 (SCMN)     THREAD
3199   3201      DIAG    oracle@oel6 (DIAG)     THREAD
3199   3203      VKRM    oracle@oel6 (VKRM)     THREAD
3199   3204      DIA0    oracle@oel6 (DIA0)     THREAD
3199   3210      RECO    oracle@oel6 (RECO)     THREAD
3199   3212      PXMN    oracle@oel6 (PXMN)     THREAD
3199   3213      MMON    oracle@oel6 (MMON)     THREAD
3199   3214      MMNL    oracle@oel6 (MMNL)     THREAD
3199   3215      D000    oracle@oel6 (D000)     THREAD
3199   3216      S000    oracle@oel6 (S000)     THREAD
3199   3217      N000    oracle@oel6 (N000)     THREAD
3199   3224      TMON    oracle@oel6 (TMON)     THREAD
3199   3225      TT00    oracle@oel6 (TT00)     THREAD
3199   3226      SMCO    oracle@oel6 (SMCO)     THREAD
3199   3227      W000    oracle@oel6 (W000)     THREAD
3199   3228      W001    oracle@oel6 (W001)     THREAD
3199   3229      AQPC    oracle@oel6 (AQPC)     THREAD
3199   3230      CJQ0    oracle@oel6 (CJQ0)     THREAD
3199   3232      P000    oracle@oel6 (P000)     THREAD
3199   3233      P001    oracle@oel6 (P001)     THREAD
3199   3234      P002    oracle@oel6 (P002)     THREAD
3199   3235      P003    oracle@oel6 (P003)     THREAD
3199   3363      QM02    oracle@oel6 (QM02)     THREAD
3199   3366      Q003    oracle@oel6 (Q003)     THREAD
3199   3368      Q004    oracle@oel6 (Q004)     THREAD
3199   3474              oracle@oel6            THREAD   <-- Dedicated server thread created by "SYS" connection
3199   3491      W002    oracle@oel6 (W002)     THREAD
3199   3540      W003    oracle@oel6 (W003)     THREAD
3199   3543      W004    oracle@oel6 (W004)     THREAD
3199   3880      W005    oracle@oel6 (W005)     THREAD
3199   3937      W006    oracle@oel6 (W006)     THREAD
3199   4311      W007    oracle@oel6 (W007)     THREAD
3199   4428      J000    oracle@oel6 (J000)     THREAD
3199   4429      J001    oracle@oel6 (J001)     THREAD
  
48 rows selected.

Please make a note that this multi-threading shouldn’t be confused with Shared server(MTS) architecture and both are different.

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. How to Backup and Restore Java Classes and Privileges only in Oracle Database
  2. How to Create Interval-Reference Partitioned Tables in Oracle 12c
  3. Oracle Database : What Is The Search Order For The LDAP.ORA File
  4. How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
  5. Script to monitor RMAN Backup and Restore Operations
  6. How to Monitor Process Memory Usage on Oracle Pluggable Databases
  7. Oracle Database 12.2 RMAN Cross Platform Tablespace Transport Over Network
  8. How to move spfile from ASM to filesystem
  9. How to rename database and then move datafile online in Oracle Database 12.2
  10. Oracle RMAN interview questions

You May Also Like

Primary Sidebar

Recent Posts

  • What are different Oracle Database Vault Roles
  • Unable to export realm protected table using data pump
  • Beginners Guide to Oracle Database Vault
  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary