Monday, July 08, 2013

Learned a little bit about Multithreading

I read Multithreaded Model using THREADED_EXECUTION in Oracle Database 12c Release 1 (12.1) on Oracle Base. Thank You for Good Article. That gave me many idea.
Multithreaded Oracle model.
SQL> show parameter threaded_execution

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
threaded_execution                   boolean     TRUE
SQL> !ps -ef |grep oracle | grep orcl
oracle    3093     1  0 12:41 ?        00:00:00 ora_pmon_orcl
oracle    3095     1  0 12:41 ?        00:00:00 ora_psp0_orcl
oracle    3097     1  9 12:41 ?        00:02:17 ora_vktm_orcl
oracle    3101     1  0 12:41 ?        00:00:02 ora_u004_orcl
oracle    3107     1  2 12:41 ?        00:00:36 ora_u005_orcl
oracle    3113     1  0 12:41 ?        00:00:00 ora_dbw0_orcl
oracle    3462  3454  0 13:06 pts/0    00:00:00 /bin/bash -c ps -ef |grep oracle | grep orcl
oracle    3465  3462  0 13:06 pts/0    00:00:00 grep orcl

From upon, I had not seen SMON process.
Oracle Note:
When the THREADED_EXECUTION initialization parameter is set to TRUE on Linux and UNIX, the DBW, PMON, PSP, and VKTM background processes run as operating system processes, and the other background processes run as operating system threads.
If Some people who wrote script to monitors Oracle Instance on Linux with "SMON" process. It will not work, if they enable the multithreaded Oracle model.
Came back to learn. Checked in V$PROCESS
SQL> SELECT SPID, STID, PROGRAM FROM V$PROCESS;

SPID     STID     PROGRAM
-------- -------- ------------------------------------------------
                  PSEUDO
3093     3093     oracle@test12c (PMON)
3095     3095     oracle@test12c (PSP0)
3097     3097     oracle@test12c (VKTM)
3101     3103     oracle@test12c (GEN0)
3101     3101     oracle@test12c (SCMN)
3101     3104     oracle@test12c (MMAN)
3107     3130     oracle@test12c (TMON)
3107     3109     oracle@test12c (DIAG)
3107     3107     oracle@test12c (SCMN)
3101     3110     oracle@test12c (DBRM)
3107     3111     oracle@test12c (DIA0)
3113     3113     oracle@test12c (DBW0)
3101     3114     oracle@test12c (LGWR)
3101     3115     oracle@test12c (CKPT)
3101     3116     oracle@test12c (SMON)
3107     3117     oracle@test12c (RECO)
3101     3118     oracle@test12c (LREG)
3107     3119     oracle@test12c (MMON)
3107     3120     oracle@test12c (MMNL)
3107     3121     oracle@test12c (D000)
3107     3122     oracle@test12c (S000)
3107     3123     oracle@test12c (N000)
3107     3458     oracle@test12c
3107     3131     oracle@test12c (TT00)
3107     3132     oracle@test12c (SMCO)
3107     3133     oracle@test12c (AQPC)
3107     3153     oracle@test12c (CJQ0)
3107     3135     oracle@test12c (P000)
3107     3136     oracle@test12c (P001)
3107     3137     oracle@test12c (P002)
3107     3138     oracle@test12c (P003)
3107     3160     oracle@test12c (QM02)
3107     3287     oracle@test12c (W001)
3107     3415     oracle@test12c (W002)
3107     3162     oracle@test12c (Q002)
3107     3163     oracle@test12c (Q003)
3107     3152     oracle@test12c (W000)
 With "ps" command.
To get info about threads:
          ps -eLf
          ps axms
So, I could check thread on Linux.
SQL> !ps -ef |grep oracle | grep orcl | grep 3101
oracle    3101     1  0 12:41 ?        00:00:02 ora_u004_orcl
oracle    3506  3454  0 13:16 pts/0    00:00:00 /bin/bash -c ps -ef |grep oracle | grep orcl | grep 3101

SQL> !ps -eLf |grep oracle | grep orcl | grep 3116
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    3101     1  3116  0    9 12:41 ?        00:00:00 ora_u004_orcl
oracle    3514  3454  3514  0    1 13:17 pts/0    00:00:00 /bin/bash -c ps -eLf |grep oracle | grep orcl | grep 3116
On Linux, there might use "strace" command to trace system calls and signals each thread.
SQL> l
  1  SELECT s.username,
  2   s.osuser,
  3   s.sid,
  4   s.serial#,
  5   p.spid,
  6   p.stid,
  7   s.status FROM
  8   v$session s,
  9   v$process p
 10   WHERE  s.paddr = p.addr
 11   AND    s.username = 'SYSTEM'
 12*  ORDER BY s.username, s.osuser
SQL> /

USERNAME        OSUSER                 SID    SERIAL# SPID       STID       STATUS
--------------- --------------- ---------- ---------- ---------- ---------- --------
SYSTEM          oracle                  29        997 3107       3678       INACTIVE

SQL> !ps -eLf |grep 3678
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    3107     1  3678  0   28 13:25 ?        00:00:00 ora_u005_orcl
oracle    3927  3650  3927  0    1 13:49 pts/1    00:00:00 /bin/bash -c ps -eLf |grep 3678
oracle    3929  3927  3929  0    1 13:49 pts/1    00:00:00 grep 3678

SQL> ! strace -p 3678
Process 3678 attached - interrupt to quit
read(122,

What Is Multi-Threading?

2 comments:

Saurabh K. Gupta said...

Nice writeup. In addition to this, once the threaded model has been enabled, OS authentication will not work thereafter. So you must login using actual credentials.

Surachart Opun said...

Thank You for your Note, Gupta.

Something pops up in my head, but not to do yet.
Write c pthread :)