Martins Blog

Trying to explain complex things in simple terms

Implications of threaded_execution = true in 12c

Posted by Martin Bach on July 21, 2014

I had an interesting discussion as part of my latest presentation at the UKOUG RAC CIA & Database Combined SIG. Part of my talk was about the implications of the new threaded execution model in Oracle.

Since “we do not use Windows” (except for gaming) I can’t compare the Windows thread model to the new 12c implementation on UNIX. There are however interesting implications when switching to the new model, some of which I’d like to demonstrate here. First of all, threaded execution is not enabled by default. With 12.1.0.1.3 on top of Oracle Restart you get the either all or a subset of the following background and auxiliary processes for a CDB:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    6041     1  6041  0    1 08:56 ?        00:00:00 ora_pmon_CDB1
oracle    6043     1  6043  0    1 08:56 ?        00:00:00 ora_psp0_CDB1
oracle    6045     1  6045  2    1 08:56 ?        00:00:08 ora_vktm_CDB1
oracle    6049     1  6049  0    1 08:56 ?        00:00:00 ora_gen0_CDB1
oracle    6051     1  6051  0    1 08:56 ?        00:00:00 ora_mman_CDB1
oracle    6055     1  6055  0    1 08:56 ?        00:00:00 ora_diag_CDB1
oracle    6057     1  6057  0    1 08:56 ?        00:00:00 ora_dbrm_CDB1
oracle    6059     1  6059  0    1 08:56 ?        00:00:00 ora_dia0_CDB1
oracle    6061     1  6061  0    1 08:56 ?        00:00:00 ora_dbw0_CDB1
oracle    6063     1  6063  0    1 08:56 ?        00:00:00 ora_lgwr_CDB1
oracle    6065     1  6065  0    1 08:56 ?        00:00:00 ora_ckpt_CDB1
oracle    6067     1  6067  0    1 08:56 ?        00:00:00 ora_lg00_CDB1
oracle    6069     1  6069  0    1 08:56 ?        00:00:00 ora_lg01_CDB1
oracle    6071     1  6071  0    1 08:56 ?        00:00:00 ora_smon_CDB1
oracle    6073     1  6073  0    1 08:56 ?        00:00:00 ora_reco_CDB1
oracle    6075     1  6075  0    1 08:56 ?        00:00:00 ora_lreg_CDB1
oracle    6077     1  6077  0    1 08:56 ?        00:00:00 ora_rbal_CDB1
oracle    6079     1  6079  0    1 08:56 ?        00:00:00 ora_asmb_CDB1
oracle    6081     1  6081  0    1 08:56 ?        00:00:00 ora_mmon_CDB1
oracle    6083     1  6083  0    1 08:56 ?        00:00:00 ora_mmnl_CDB1
grid      6086     1  6086  0    1 08:56 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6087     1  6087  0    1 08:56 ?        00:00:00 ora_d000_CDB1
oracle    6089     1  6089  0    1 08:56 ?        00:00:00 ora_s000_CDB1
oracle    6092     1  6092  0    1 08:56 ?        00:00:00 ora_mark_CDB1
oracle    6094     1  6094  0    1 08:56 ?        00:00:00 ora_ocf0_CDB1
oracle    6109     1  6109  0    1 08:56 ?        00:00:00 ora_o000_CDB1
grid      6111     1  6111  0    1 08:56 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      6113     1  6113  0    1 08:56 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6118     1  6118  0    1 08:56 ?        00:00:00 ora_nss2_CDB1
oracle    6148     1  6148  0    1 08:56 ?        00:00:01 ora_p000_CDB1
oracle    6150     1  6150  0    1 08:56 ?        00:00:02 ora_p001_CDB1
oracle    6154     1  6154  0    1 08:56 ?        00:00:00 ora_o001_CDB1
grid      6156     1  6156  0    1 08:56 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6158     1  6158  0    1 08:56 ?        00:00:00 ora_tmon_CDB1
oracle    6160     1  6160  0    1 08:56 ?        00:00:00 ora_arc0_CDB1
oracle    6162     1  6162  0    1 08:56 ?        00:00:00 ora_arc1_CDB1
oracle    6164     1  6164  0    1 08:56 ?        00:00:00 ora_arc2_CDB1
oracle    6166     1  6166  0    1 08:56 ?        00:00:00 ora_arc3_CDB1
oracle    6169     1  6169  0    1 08:57 ?        00:00:00 ora_tt00_CDB1
oracle    6171     1  6171  0    1 08:57 ?        00:00:00 ora_smco_CDB1
oracle    6176     1  6176  0    1 08:57 ?        00:00:00 ora_aqpc_CDB1
oracle    6180     1  6180  0    1 08:57 ?        00:00:00 ora_p002_CDB1
oracle    6182     1  6182  0    1 08:57 ?        00:00:00 ora_p003_CDB1
oracle    6184     1  6184  0    1 08:57 ?        00:00:00 ora_p004_CDB1
oracle    6186     1  6186  0    1 08:57 ?        00:00:00 ora_p005_CDB1
oracle    6188     1  6188  0    1 08:57 ?        00:00:00 ora_p006_CDB1
oracle    6190     1  6190  0    1 08:57 ?        00:00:00 ora_p007_CDB1
oracle    6192     1  6192  0    1 08:57 ?        00:00:00 ora_qm02_CDB1
oracle    6194     1  6194  0    1 08:57 ?        00:00:00 ora_qm00_CDB1
oracle    6196     1  6196  0    1 08:57 ?        00:00:00 ora_q002_CDB1
oracle    6200     1  6200  0    1 08:57 ?        00:00:00 ora_q004_CDB1
oracle    6202     1  6202  0    1 08:57 ?        00:00:00 ora_q005_CDB1
oracle    6204     1  6204  0    1 08:57 ?        00:00:00 ora_q006_CDB1
oracle    6206     1  6206  0    1 08:57 ?        00:00:00 ora_q007_CDB1
oracle    6208     1  6208  0    1 08:57 ?        00:00:00 ora_q008_CDB1
oracle    6210     1  6210  0    1 08:57 ?        00:00:00 ora_q009_CDB1
oracle    6212     1  6212  0    1 08:57 ?        00:00:00 ora_q00a_CDB1
oracle    6292     1  6292  0    1 08:57 ?        00:00:00 ora_w000_CDB1
oracle    6318     1  6318  0    1 08:57 ?        00:00:00 ora_cjq0_CDB1
oracle    6320     1  6320  0    1 08:57 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    6322     1  6322  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6324     1  6324  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6326     1  6326  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6407     1  6407  0    1 08:57 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    6438     1  6438  0    1 08:57 ?        00:00:00 ora_p008_CDB1
oracle    6440     1  6440  0    1 08:57 ?        00:00:00 ora_p009_CDB1
oracle    6442     1  6442  0    1 08:57 ?        00:00:01 oracleCDB1 (LOCAL=NO)
oracle    6490     1  6490  0    1 08:58 ?        00:00:00 ora_p00a_CDB1
oracle    6492     1  6492  0    1 08:58 ?        00:00:00 ora_p00b_CDB1
oracle    6640  5653  6640  0    1 09:01 pts/0    00:00:00 egrep -i UID|CDB1

Quite a few more than in 9i it seems. Most of the background processes are documented in the database reference. As you can see, there are only processes (-> NLWP = 1), no threads. If you wondered about the strange output, bear with me, there is a reason I chose those flags to the ps command.

The change with the threaded model

Let’s enable threaded execution. It’s simple:

SYS@CDB$ROOT> alter system set threaded_execution=true scope=spfile;

System altered.

SYS@CDB$ROOT> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@CDB$ROOT> startup
ORA-01017: invalid username/password; logon denied

There’s the first surprise. Not to worry, it’s just that OS level authentication doesn’t work anymore, but you can continue by providing your username and password:

SYS@CDB$ROOT> conn sys as sysdba
Enter password:
Connected.
SYS@CDB$ROOT> alter database mount;

Database altered.

SYS@CDB$ROOT> alter database open;

Database altered.

SYS@CDB$ROOT>

What does that mean for our processes? First let’s use the old way of checking for Oracle processes using just ps -ef:

[oracle@london ~]$ ps -ef | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID  C STIME TTY          TIME CMD
oracle    7138     1  0 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  0 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  2 09:04 ?        00:00:05 ora_vktm_CDB1
oracle    7146     1  0 09:04 ?        00:00:01 ora_u004_CDB1
oracle    7152     1  4 09:04 ?        00:00:08 ora_u005_CDB1
oracle    7158     1  0 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  0 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7202     1  0 09:04 ?        00:00:00 oracle+ASM_ocf0_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  0 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  0 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  0 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7407     1  0 09:06 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  0 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  0 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  3 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  0 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7691  5653  0 09:08 pts/0    00:00:00 egrep -i UID|CDB1
[oracle@london ~]$

The list is considerably shorter, and you will notice some strange process names: u004 and u005. If you squint you see that PMON, PSP0, VKTM and DBW0 are still processes. I am checking for threads (or light weight processes as they are also called) in the next step:

[oracle@london ~]$ ps -eLf | egrep -i "UID|$ORACLE_SID"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7138     1  7138  0    1 09:04 ?        00:00:00 ora_pmon_CDB1
oracle    7140     1  7140  0    1 09:04 ?        00:00:00 ora_psp0_CDB1
oracle    7142     1  7142  2    1 09:04 ?        00:00:11 ora_vktm_CDB1
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7152     1  7152  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7153  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7154  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7156  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7164  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7168  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7169  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7170  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7172  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7174  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7175  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7224  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7227  0   46 09:04 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7249  0   46 09:05 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7361  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7362  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7365  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7366  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7367  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7396  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7397  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7404  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7405  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7416  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7418  0   46 09:06 ?        00:00:01 ora_u005_CDB1
oracle    7152     1  7419  0   46 09:06 ?        00:00:02 ora_u005_CDB1
oracle    7152     1  7420  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7421  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7422  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7423  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7424  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7425  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7426  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7427  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7428  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7430  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7431  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7432  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7433  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7434  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7435  0   46 09:06 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7576  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7618  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7619  0   46 09:07 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7696  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7152     1  7697  0   46 09:08 ?        00:00:00 ora_u005_CDB1
oracle    7158     1  7158  0    1 09:04 ?        00:00:00 ora_dbw0_CDB1
grid      7173     1  7173  0    1 09:04 ?        00:00:00 oracle+ASM_asmb_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7226     1  7226  0    1 09:04 ?        00:00:00 oracle+ASM_o000_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7229     1  7229  0    1 09:04 ?        00:00:00 oracle+ASM_o001_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
grid      7401     1  7401  0    1 09:06 ?        00:00:00 oracle+ASM_o002_cdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7494     1  7494  0    1 09:06 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7526     1  7526  0    1 09:07 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7573     1  7573  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7575     1  7575  1    1 09:07 ?        00:00:02 oracleCDB1 (LOCAL=NO)
oracle    7578     1  7578  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7592     1  7592  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7594     1  7594  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7596     1  7596  0    1 09:07 ?        00:00:00 oracleCDB1 (LOCAL=NO)
oracle    7810     1  7810  0    1 09:10 ?        00:00:00 oracleCDB1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7840  5653  7840  0    1 09:11 pts/0    00:00:00 egrep -i UID|CDB1

This is where it’s interesting. First let’s match this to the database, focusing on the background processes.

SYS@CDB$ROOT> select pname, pid, sosid, spid, stid, execution_type
  2  from v$process where background = 1
  3  order by pname
  4  /

PNAME        PID SOSID                    SPID                     STID                     EXECUTION_
----- ---------- ------------------------ ------------------------ ------------------------ ----------
AQPC          43 7152_7416                7152                     7416                     THREAD
ARC0          35 7152_7362                7152                     7362                     THREAD
ARC1          36 7152_7365                7152                     7365                     THREAD
ARC2          37 7152_7366                7152                     7366                     THREAD
ARC3          38 7152_7367                7152                     7367                     THREAD
ASMB          22 7146_7167                7146                     7167                     THREAD
CJQ0          68 7152_7576                7152                     7576                     THREAD
CKPT          15 7146_7160                7146                     7160                     THREAD
DBRM          11 7146_7155                7146                     7155                     THREAD
DBW0          13 7158                     7158                     7158                     PROCESS
DIA0          12 7152_7156                7152                     7156                     THREAD
DIAG           9 7152_7154                7152                     7154                     THREAD
GEN0           5 7146_7148                7146                     7148                     THREAD
LG00          16 7146_7161                7146                     7161                     THREAD
LG01          18 7146_7163                7146                     7163                     THREAD
LGWR          14 7146_7159                7146                     7159                     THREAD
LREG          20 7146_7165                7146                     7165                     THREAD
MARK          28 7152_7175                7152                     7175                     THREAD
MMAN           7 7146_7149                7146                     7149                     THREAD
MMNL          24 7152_7169                7152                     7169                     THREAD
MMON          23 7152_7168                7152                     7168                     THREAD
NSS2          33 7152_7249                7152                     7249                     THREAD
O002          40 7152_7397                7152                     7397                     THREAD
PMON           2 7138                     7138                     7138                     PROCESS
PSP0           3 7140                     7140                     7140                     PROCESS
Q002          54 7152_7428                7152                     7428                     THREAD
Q004          56 7152_7430                7152                     7430                     THREAD
Q005          57 7152_7431                7152                     7431                     THREAD
Q006          58 7152_7432                7152                     7432                     THREAD
Q007          59 7152_7433                7152                     7433                     THREAD
Q008          60 7152_7434                7152                     7434                     THREAD
Q009          61 7152_7435                7152                     7435                     THREAD
QM00          53 7152_7427                7152                     7427                     THREAD
QM02          44 7152_7426                7152                     7426                     THREAD
RBAL          21 7146_7166                7146                     7166                     THREAD
RECO          19 7152_7164                7152                     7164                     THREAD
SCMN           6 7146_7146                7146                     7146                     THREAD
SCMN          10 7152_7152                7152                     7152                     THREAD
SMCO          41 7152_7404                7152                     7404                     THREAD
SMON          17 7146_7162                7146                     7162                     THREAD
TMON          34 7152_7361                7152                     7361                     THREAD
TT00          39 7152_7396                7152                     7396                     THREAD
VKTM           4 7142                     7142                     7142                     PROCESS
W000          42 7152_7405                7152                     7405                     THREAD

44 rows selected.

Here you clearly see which one of the background processes is a thread, and which isn’t. The threads will have multiple STIDs per SPID, or thread IDs to the process:

SYS@CDB$ROOT> select count(spid),spid,execution_type from v$process where background = 1 group by spid, execution_type;

COUNT(SPID) SPID                     EXECUTION_
----------- ------------------------ ----------
          1 7158                     PROCESS
         12 7146                     THREAD
          1 7138                     PROCESS
          1 7140                     PROCESS
          1 7142                     PROCESS
         29 7152                     THREAD

6 rows selected.

Circling back to the OS level for SPID 7146-can you see the threads there as well? You can:

[oracle@london ~]$ ps -eLf | egrep -i "UID|7146"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle    7146     1  7146  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7147  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7148  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7149  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7155  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7159  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7160  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7161  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7162  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7163  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7165  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7166  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    7146     1  7167  0   13 09:04 ?        00:00:00 ora_u004_CDB1
oracle    8156  6876  8156  0    1 09:19 pts/2    00:00:00 egrep -i UID|7146
[oracle@london ~]$

But what about user sessions?

Let’s connect to the database (a PDB in this case) as a user.

[oracle@london ~]$ sqlplus martin@localhost/pdb4
...
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 09:22:39 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Fri Jul 18 2014 09:21:27 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@PDB4> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
280
MARTIN@PDB4>

I can see the session from the CDB root:

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 280 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN            280        407          4 8672                     8672                     8672                     PROCESS

SYS@CDB$ROOT>

Interestingly – as the output shows – user processes seem to remain UNIX processes (there is a twist to that too, bear with me). Remember that this process has been created using a net service and the EZConnect syntax. What about my session that bypasses the net*8 layer? I connected using sqlplus sys as sysdba:

SYS@CDB$ROOT> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
------------------------------------------------------------------------------------------------------------------------------------------------------
249

SYS@CDB$ROOT> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME          SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
---------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
SYS               249        685          1 7152                     7152_8696                8696                     THREAD

SYS@CDB$ROOT>

Now that’s a thread. Surprised? Let’s test on a non CDB. First I try to connect by bypassing the listener:

[oracle@london ~]$ sqlplus martin

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:34:58 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:33:25 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

MARTIN@NCDB>
-- in a different session
SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 249 and s.paddr = p.addr;

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         13          0 14634                    14634_15337              15337                    THREAD

This session is a thread. Now using the listener:

[oracle@london ~]$ tnsping ncdb

TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 18-JUL-2014 10:36:33

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = london.example.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = NCDB)))
OK (0 msec)
[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:36:36 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:18 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
249

And what’s the guess?

SYS@NCDB> r
  1  select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3* where s.sid = 249 and s.paddr = p.addr

USERNAME                              SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
------------------------------ ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                                249         17          0 15507                    15507                    15507                    PROCESS

You were right-a process. But so far I haven’t shown you my listener.ora file. So far in the testing it didn’t use DEDICATED_THROUGH_BROKER_listener = ON. Let’s set this, and reload the listener. Here is the complete example:

[oracle@london ~]$ sqlplus martin@ncdb

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 18 10:45:18 2014

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter password:
Last Successful login time: Fri Jul 18 2014 10:36:38 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

MARTIN@NCDB> select sys_context('userenv','sid') from dual;

SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
14

-- and in another session

SYS@NCDB> select s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid, p.execution_type
  2  from v$session s, v$process p
  3  where s.sid = 14 and s.paddr = p.addr;

USERNAME               SID    SERIAL#     CON_ID SPID                     SOSID                    STID                     EXECUTION_
--------------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------ ----------
MARTIN                  14         25          0 14634                    14634_16287              16287                    THREAD

The listener parameter DEDICATED_THROUGH_BROKER_<listener_name> allows you to create user sessions as threads. And now to wrap up, how do you kill that session? On my Oracle Linux 6.5 system I killed the LWP:

[oracle@london ~]$ ps -eLf | egrep -i "UID|16287"
UID        PID  PPID   LWP  C NLWP STIME TTY          TIME CMD
oracle   14634     1 16287  0   32 10:45 ?        00:00:00 ora_u005_NCDB
oracle   16531 16503 16531  1    1 10:48 pts/4    00:00:00 egrep -i UID|16287
[oracle@london ~]$ kill -9 16287

Which terminates my session:

MARTIN@NCDB> select user from dual
  2  /
select user from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 14634 Thread ID: 16287
Session ID: 14 Serial number: 25

My database stayed up and running. Please do NEVER EVER kill the SPID when threaded_execution is enabled!!! You might bring the database down.

Disclaimer

As with everything on this blog, don’t take it literally-test it! Just because it worked for me on my VM with the specific set of packages does not mean this is applicable for other Linux or even Solaris/AIX!

About these ads

2 Responses to “Implications of threaded_execution = true in 12c”

  1. Excellent example of threads for 12c – especially that very last part about thread kills.

  2. […] Bach has published a note with a few extra details on threaded execution which you may find […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,328 other followers

%d bloggers like this: