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!
spbutterworth said
Excellent example of threads for 12c – especially that very last part about thread kills.
12c | Oracle Scratchpad said
[…] Bach has published a note with a few extra details on threaded execution which you may find […]