[20241212]Oracle 多线程测试.txt
[20241212]Oracle 多线程测试.txt
--//oracle从12c版本开始支持多线程，各种原因从来没有测试，好像最近很少有人提及，或许使用oracle的人越来越少，或者许多人根
--//本没有这样的需求，印象最深的是启用后必须使用口令登录，即使是sys用户.
--//自己还是看一些文章，测试看看。
1.环境：
SYS@book> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> @hidez threaded_execution
SYS@book> @ pr
==============================
NUM : 990
N_HEX : 3DE
NAME : threaded_execution
DESCRIPTION : Threaded Execution Mode
DEFAULT_VALUE : TRUE
SESSION_VALUE : FALSE
SYSTEM_VALUE : FALSE
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
PL/SQL procedure successfully completed.
--//缺省FALSE。
SYS@book> alter system set threaded_execution=true scope=spfile;
System altered.
$ cat pp.txt
SELECT s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid,
p.execution_type
FROM v$session s, v$process p
WHERE s.sid = &&1
AND s.paddr = p.addr;
2.开始测试：
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ERROR:
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied
--//不必担心，需要使用sys加口令登录。
$ rlsql -l sys/bookbook as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 12 16:10:36 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SYS@book> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
--//仅仅启动到nomount。
SYS@book> alter database mount ;
Database altered.
SYS@book> alter database open ;
Database altered.
$ ps -ef | egrep "UI[D]|ora[_]"
UID PID PPID C STIME TTY TIME CMD
oracle 3799 1 0 16:08 ? 00:00:00 ora_pmon_book
oracle 3803 1 0 16:08 ? 00:00:01 ora_u002_book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
oracle 3808 1 0 16:08 ? 00:00:00 ora_psp0_book
oracle 3812 1 0 16:08 ? 00:00:01 ora_vktm_book
oracle 3825 1 0 16:08 ? 00:00:00 ora_gen1_book
oracle 3831 1 4 16:08 ? 00:00:12 ora_u006_book
oracle 3834 1 0 16:08 ? 00:00:00 ora_ofsd_book
oracle 3837 1 0 16:08 ? 00:00:00 ora_dbw0_book
oracle 3841 1 0 16:08 ? 00:00:00 ora_lgwr_book
oracle 3871 1 0 16:08 ? 00:00:00 ora_bg00_book
oracle 3882 1 0 16:08 ? 00:00:00 ora_bg01_book
oracle 3891 1 0 16:08 ? 00:00:00 ora_u00c_book
--//仅仅看到这些进程。可以发现一些以前没有出现的进程比如u00N进程。ps加入-L参数可以看到线程号。
$ ps -eLf | egrep "UI[D]|ora[_]u002"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3803 1 3803 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3804 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3817 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3820 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3826 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3833 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3836 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3843 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3858 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3861 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3862 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 4040 0 12 16:13 ? 00:00:00 ora_u002_book
--//可以发现LWP列对应线程号，NLWP对应数量。这样的方式以前一些sql脚本查询仅仅知道spid。以scott用户登录PDB。
--//rlsql scott/book@book01p
SCOTT@book01p> @ spid
==============================
SID : 275
SERIAL# : 34688
PROCESS : 4093
SERVER : DEDICATED
SPID : 4095
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PID : 78
P_SERIAL# : 3
KILL_COMMAND : alter system kill session '275,34688' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 275
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 275
SERIAL# : 34688
CON_ID : 3
SPID : 4095
SOSID : 4095
STID : 4095
EXECUTION_TYPE : PROCESS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
--// SPID , SOSID ,STID 三者值一样，说明并没有使用线程。
$ ps -eLf | egrep -i "[U]ID|[4]095"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4095 1 4095 0 1 16:17 ? 00:00:00 oraclebook (LOCAL=NO)
--//可以发现并没有使用线程。
--//要想用户登录使用线程必须修改监听配置文件，加入。
DEDICATED_THROUGH_BROKER_listener =ON
--//注意21c使用Read-Only Oracle Homes，监听配置文件不在原来位置，我的测试环境在
$ locate --regex listener.ora$
/u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/u01/app/oracle/product/21.0.0/dbhome_1/network/admin/samples/listener.ora
--//停止监听，修改监听配置文件，再重启启动监听(输出略)。scott用户登录退出重新登录：
SCOTT@book01p> @ spid
==============================
SID : 276
SERIAL# : 56799
PROCESS : 4616
SERVER : DEDICATED
SPID : 4618
PID : 78
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '276,56799' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 276
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 276
SERIAL# : 56799
CON_ID : 3
SPID : 4618
SOSID : 4618_4619
STID : 4619
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//注意现在SPID=4618，stid=4619。再使用scott登录1个新的会话。
SCOTT@book01p> @ pp.txt 404
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 404
SERIAL# : 14329
CON_ID : 3
SPID : 4618
SOSID : 4618_4629
STID : 4629
EXECUTION_TYPE : THREAD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
$ ps -eLf | egrep -i "[U]ID|461[8]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4618 1 4618 0 3 16:40 ? 00:00:00 ora_u000_book
oracle 4618 1 4619 0 3 16:40 ? 00:00:00 ora_u000_book
oracle 4618 1 4629 0 3 16:40 ? 00:00:00 ora_u000_book
--//可以发现使用2个会话，并且使用线程。现在就不能简单的kill 4618，这样如果存在多个线程，可能许多会话会全部kill，看看kill
--//-9 4619发生什么情况。
$ kill -9 4619
$ ps -eLf | egrep -i "[U]ID|461[8]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//只要一个线程kill，整个相关线程包含主进程全部都被kill。也许正是这个原因导致很少采用的缘故。
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4618 Thread ID: 4619
Session ID: 276 Serial number: 56799
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4618 Thread ID: 4629
Session ID: 404 Serial number: 14329
--//注意看提示。
3.继续测试：
--//重新登陆：
SCOTT@book01p> @ pp.txt 19
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 19
SERIAL# : 60052
CON_ID : 3
SPID : 4730
SOSID : 4730_4731
STID : 4731
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
$ strace -f -p 4731 -y -Ttt 2>&1 | tee /tmp/test.txt
Process 4731 attached with 3 threads
[pid 4752] 16:51:44.858251 read(18<socket:[29176]>, <unfinished ...>
[pid 4731] 16:51:44.858513 read(13<socket:[29150]>, <unfinished ...>
[pid 4730] 16:51:44.858556 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.230285>
[pid 4730] 16:51:48.089368 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.016547>
[pid 4730] 16:51:51.106292 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.021049>
[pid 4730] 16:51:54.127456 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.002734>
--//可以发现对应主进程4730调用semtimedop，休眠3秒，如果线程有sql语句执行必须会被唤醒，这样如果多个相同主进程的线程执行
--//sql语句存在一个协调调度问题。
--//drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=2e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
$ cat m9.txt
set verify on
variable v_method varchar2(20)
exec :v_method := '&&2';
define t=&&1;
@@ ma.txt 1 &&2
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;
commit ;
@@ ma.txt &&t &&2
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= :v_method;
commit;
quit
[oracle@centtest IP=56.101 ~/study/202410 ] $ cat ma.txt
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i;
END LOOP;
END;
/
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 CCCC > /dev/null;zzdate
trunc(sysdate)+17/24+06/1440+59/86400 1733994419.410840721
trunc(sysdate)+17/24+07/1440+31/86400 1733994451.309702554
--//使用top查看：
top - 17:06:15 up 1:35, 1 user, load average: 2.52, 1.35, 0.73
Tasks: 164 total, 1 running, 163 sleeping, 0 stopped, 0 zombie
%Cpu(s): 97.5 us, 0.8 sy, 0.0 ni, 0.5 id, 0.0 wa, 0.0 hi, 1.2 si, 0.0 st
KiB Mem : 8010488 total, 4202600 free, 2044268 used, 1763620 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 5644572 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4730 oracle 20 0 2390620 108416 46960 S 396.3 1.4 5:13.37 ora_u000_book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3812 oracle -2 0 1302296 18708 14540 S 1.7 0.2 0:14.30 ora_vktm_book
2843 oracle 20 0 118764 3340 1660 S 0.0 0.0 0:00.04 -bash
$ strace -f -p 4730 -c
Process 4730 attached with 11 threads
^CProcess 4730 detached
Process 4752 detached
Process 4946 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
51.70 5.708983 6 940107 getrusage
45.81 5.057840 133101 38 30 semtimedop
2.48 0.273415 104 2630 select
0.01 0.000676 6 111 munmap
0.00 0.000302 38 8 madvise
0.00 0.000220 2 136 read
0.00 0.000180 0 424 48 stat
0.00 0.000082 0 352 geteuid
0.00 0.000021 0 72 close
0.00 0.000021 3 8 timer_delete
0.00 0.000018 1 32 write
0.00 0.000014 0 31 rt_sigprocmask
0.00 0.000013 0 32 open
0.00 0.000010 0 72 lseek
0.00 0.000008 0 23 mmap
0.00 0.000008 0 32 getrlimit
0.00 0.000007 0 40 lstat
0.00 0.000004 0 16 semctl
0.00 0.000004 0 16 gettid
0.00 0.000003 0 16 fstat
0.00 0.000002 0 16 rt_sigaction
0.00 0.000002 1 3 io_setup
0.00 0.000001 0 5 rt_sigreturn
0.00 0.000001 0 24 fcntl
0.00 0.000000 0 16 setsockopt
0.00 0.000000 0 3 io_destroy
0.00 0.000000 0 8 epoll_ctl
------ ----------- ----------- --------- --------- ----------------
100.00 11.041835 944271 78 total
--//链接http://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/提到的情况
[root@rico fd]# strace -cp 12165
Process 12165 attached
^CProcess 12165 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
84.22 0.113706 0 980840 poll
10.37 0.014000 7000 2 read
5.41 0.007310 1218 6 semtimedop
0.00 0.000000 0 2 write
0.00 0.000000 0 1 semctl
0.00 0.000000 0 419 getrusage
0.00 0.000000 0 12 times
------ ----------- ----------- --------- --------- ----------------
100.00 0.135016 981282 total
[root@rico fd]# strace -p 12165 -o /tmp/threaded_exec.out
Process 12165 attached
^CProcess 12165 detached
[root@rico fd]# grep poll /tmp/threaded_exec.out | tail
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
--//可以推测21c使用semtimedop调用代替poll。
--//重复测试：
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 CCCC > /dev/null;zzdate
trunc(sysdate)+17/24+11/1440+42/86400 -1733994702.866654595
trunc(sysdate)+17/24+12/1440+14/86400 1733994734.657435531
--//Sum = 31.790780936
SYS@book> @ ashtop event 1=1 trunc(sysdate)+17/24+11/1440+42/86400 trunc(sysdate)+17/24+12/1440+14/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
219 6.8 91% | 2024-12-12 17:11:43 2024-12-12 17:12:13 112 31 139
21 .7 9% | cursor: pin S 2024-12-12 17:11:51 2024-12-12 17:12:10 1 10 10
--//停止监听，修改监听注解DEDICATED_THROUGH_BROKER_listener =ON，再重新启动监听，重复测试：
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 DDDD > /dev/null;zzdate
trunc(sysdate)+17/24+19/1440+15/86400 -1733995155.460912071
trunc(sysdate)+17/24+19/1440+47/86400 1733995187.632465143
--//Sum = 32.171553072,虚拟机器太不稳定。
SCOTT@book01p> @ ashtop event 1=1 trunc(sysdate)+17/24+19/1440+15/86400 trunc(sysdate)+17/24+19/1440+47/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
218 6.8 88% | 2024-12-12 17:19:16 2024-12-12 17:19:46 105 31 135
29 .9 12% | cursor: pin S 2024-12-12 17:19:18 2024-12-12 17:19:46 1 10 10
1 .0 0% | log file sync 2024-12-12 17:19:15 2024-12-12 17:19:15 1 1 1
--//从性能讲差别不大。
SCOTT@book01p> Select method,count(*),round(avg(TIME_ELA),2),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),2) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
CCCC 8 3095 24760
DDDD 8 3121.88 24975
总结：
1.参考链接：
http://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/
https://martincarstenbach.wordpress.com/2014/07/21/implications-of-threaded_execution-true-in-12c/
2.采用后sys启动数据库必须使用用户+口令模式，实际上关库也是一样。
3.kill 其中1个线程，整个相关线程全部kill。
4.不建议在生产系统使用。
5.补充后台进程实际上scmn
SYS@book> @ bgx scmn
PROGRAM MODULE ACTION SID PID SPID
---------------------- ------------ ------ --- ------- --------
oracle@centtest (SCMN) 1 4 3803
oracle@centtest (SCMN) 382 11 3825
oracle@centtest (SCMN) 383 15 3831
oracle@centtest (SCMN) 129 17 3834
oracle@centtest (SCMN) 276 78 4730
oracle@centtest (SCMN) 8 36 3871
oracle@centtest (SCMN) 10 40 3882
oracle@centtest (SCMN) 263 46 3891
oracle@centtest (SCMN) 130 25 3841
9 rows selected.
6.测试后注意修改回来。
SYS@book> alter system reset threaded_execution;
System altered.
--//修改监听文件，注解DEDICATED_THROUGH_BROKER_listener =ON。
--//oracle从12c版本开始支持多线程，各种原因从来没有测试，好像最近很少有人提及，或许使用oracle的人越来越少，或者许多人根
--//本没有这样的需求，印象最深的是启用后必须使用口令登录，即使是sys用户.
--//自己还是看一些文章，测试看看。
1.环境：
SYS@book> @ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@book> @hidez threaded_execution
SYS@book> @ pr
==============================
NUM : 990
N_HEX : 3DE
NAME : threaded_execution
DESCRIPTION : Threaded Execution Mode
DEFAULT_VALUE : TRUE
SESSION_VALUE : FALSE
SYSTEM_VALUE : FALSE
ISSES_MODIFIABLE : FALSE
ISSYS_MODIFIABLE : FALSE
PL/SQL procedure successfully completed.
--//缺省FALSE。
SYS@book> alter system set threaded_execution=true scope=spfile;
System altered.
$ cat pp.txt
SELECT s.username, s.sid, s.serial#, s.con_id, p.spid, p.sosid, p.stid,
p.execution_type
FROM v$session s, v$process p
WHERE s.sid = &&1
AND s.paddr = p.addr;
2.开始测试：
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ERROR:
ORA-01017: invalid username/password; logon denied
ORA-01017: invalid username/password; logon denied
--//不必担心，需要使用sys加口令登录。
$ rlsql -l sys/bookbook as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Dec 12 16:10:36 2024
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SYS@book> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
--//仅仅启动到nomount。
SYS@book> alter database mount ;
Database altered.
SYS@book> alter database open ;
Database altered.
$ ps -ef | egrep "UI[D]|ora[_]"
UID PID PPID C STIME TTY TIME CMD
oracle 3799 1 0 16:08 ? 00:00:00 ora_pmon_book
oracle 3803 1 0 16:08 ? 00:00:01 ora_u002_book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
oracle 3808 1 0 16:08 ? 00:00:00 ora_psp0_book
oracle 3812 1 0 16:08 ? 00:00:01 ora_vktm_book
oracle 3825 1 0 16:08 ? 00:00:00 ora_gen1_book
oracle 3831 1 4 16:08 ? 00:00:12 ora_u006_book
oracle 3834 1 0 16:08 ? 00:00:00 ora_ofsd_book
oracle 3837 1 0 16:08 ? 00:00:00 ora_dbw0_book
oracle 3841 1 0 16:08 ? 00:00:00 ora_lgwr_book
oracle 3871 1 0 16:08 ? 00:00:00 ora_bg00_book
oracle 3882 1 0 16:08 ? 00:00:00 ora_bg01_book
oracle 3891 1 0 16:08 ? 00:00:00 ora_u00c_book
--//仅仅看到这些进程。可以发现一些以前没有出现的进程比如u00N进程。ps加入-L参数可以看到线程号。
$ ps -eLf | egrep "UI[D]|ora[_]u002"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3803 1 3803 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3804 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3817 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3820 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3826 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3833 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3836 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3843 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3858 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3861 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 3862 0 12 16:08 ? 00:00:00 ora_u002_book
oracle 3803 1 4040 0 12 16:13 ? 00:00:00 ora_u002_book
--//可以发现LWP列对应线程号，NLWP对应数量。这样的方式以前一些sql脚本查询仅仅知道spid。以scott用户登录PDB。
--//rlsql scott/book@book01p
SCOTT@book01p> @ spid
==============================
SID : 275
SERIAL# : 34688
PROCESS : 4093
SERVER : DEDICATED
SPID : 4095
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PID : 78
P_SERIAL# : 3
KILL_COMMAND : alter system kill session '275,34688' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 275
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 275
SERIAL# : 34688
CON_ID : 3
SPID : 4095
SOSID : 4095
STID : 4095
EXECUTION_TYPE : PROCESS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
--// SPID , SOSID ,STID 三者值一样，说明并没有使用线程。
$ ps -eLf | egrep -i "[U]ID|[4]095"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4095 1 4095 0 1 16:17 ? 00:00:00 oraclebook (LOCAL=NO)
--//可以发现并没有使用线程。
--//要想用户登录使用线程必须修改监听配置文件，加入。
DEDICATED_THROUGH_BROKER_listener =ON
--//注意21c使用Read-Only Oracle Homes，监听配置文件不在原来位置，我的测试环境在
$ locate --regex listener.ora$
/u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
/u01/app/oracle/product/21.0.0/dbhome_1/network/admin/samples/listener.ora
--//停止监听，修改监听配置文件，再重启启动监听(输出略)。scott用户登录退出重新登录：
SCOTT@book01p> @ spid
==============================
SID : 276
SERIAL# : 56799
PROCESS : 4616
SERVER : DEDICATED
SPID : 4618
PID : 78
P_SERIAL# : 5
KILL_COMMAND : alter system kill session '276,56799' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 276
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 276
SERIAL# : 56799
CON_ID : 3
SPID : 4618
SOSID : 4618_4619
STID : 4619
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//注意现在SPID=4618，stid=4619。再使用scott登录1个新的会话。
SCOTT@book01p> @ pp.txt 404
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 404
SERIAL# : 14329
CON_ID : 3
SPID : 4618
SOSID : 4618_4629
STID : 4629
EXECUTION_TYPE : THREAD
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
$ ps -eLf | egrep -i "[U]ID|461[8]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4618 1 4618 0 3 16:40 ? 00:00:00 ora_u000_book
oracle 4618 1 4619 0 3 16:40 ? 00:00:00 ora_u000_book
oracle 4618 1 4629 0 3 16:40 ? 00:00:00 ora_u000_book
--//可以发现使用2个会话，并且使用线程。现在就不能简单的kill 4618，这样如果存在多个线程，可能许多会话会全部kill，看看kill
--//-9 4619发生什么情况。
$ kill -9 4619
$ ps -eLf | egrep -i "[U]ID|461[8]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//只要一个线程kill，整个相关线程包含主进程全部都被kill。也许正是这个原因导致很少采用的缘故。
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4618 Thread ID: 4619
Session ID: 276 Serial number: 56799
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4618 Thread ID: 4629
Session ID: 404 Serial number: 14329
--//注意看提示。
3.继续测试：
--//重新登陆：
SCOTT@book01p> @ pp.txt 19
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 19
SERIAL# : 60052
CON_ID : 3
SPID : 4730
SOSID : 4730_4731
STID : 4731
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
$ strace -f -p 4731 -y -Ttt 2>&1 | tee /tmp/test.txt
Process 4731 attached with 3 threads
[pid 4752] 16:51:44.858251 read(18<socket:[29176]>, <unfinished ...>
[pid 4731] 16:51:44.858513 read(13<socket:[29150]>, <unfinished ...>
[pid 4730] 16:51:44.858556 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.230285>
[pid 4730] 16:51:48.089368 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.016547>
[pid 4730] 16:51:51.106292 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.021049>
[pid 4730] 16:51:54.127456 semtimedop(262144, {{82, -1, 0}}, 1, {3, 0}) = -1 EAGAIN (Resource temporarily unavailable) <3.002734>
--//可以发现对应主进程4730调用semtimedop，休眠3秒，如果线程有sql语句执行必须会被唤醒，这样如果多个相同主进程的线程执行
--//sql语句存在一个协调调度问题。
--//drop table t purge ;
create table t as select rownum id ,'test' pad from dual connect by level<=2e5;
create unique index pk_t on t(id);
exec dbms_stats.gather_table_stats(user, 't', method_opt=>'for all columns size 1');
$ cat m9.txt
set verify on
variable v_method varchar2(20)
exec :v_method := '&&2';
define t=&&1;
@@ ma.txt 1 &&2
insert into job_times values ( sys_context ('userenv', 'sid') ,dbms_utility.get_time ,'&&2') ;
commit ;
@@ ma.txt &&t &&2
update job_times set time_ela = dbms_utility.get_time - time_ela where sid=sys_context ('userenv', 'sid') and method= :v_method;
commit;
quit
[oracle@centtest IP=56.101 ~/study/202410 ] $ cat ma.txt
DECLARE
l_count PLS_INTEGER;
BEGIN
FOR i IN 1..&&1
LOOP
EXECUTE IMMEDIATE 'Select /*+ &2 */ count(*) from t where id = :j ' INTO l_count USING i;
END LOOP;
END;
/
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 CCCC > /dev/null;zzdate
trunc(sysdate)+17/24+06/1440+59/86400 1733994419.410840721
trunc(sysdate)+17/24+07/1440+31/86400 1733994451.309702554
--//使用top查看：
top - 17:06:15 up 1:35, 1 user, load average: 2.52, 1.35, 0.73
Tasks: 164 total, 1 running, 163 sleeping, 0 stopped, 0 zombie
%Cpu(s): 97.5 us, 0.8 sy, 0.0 ni, 0.5 id, 0.0 wa, 0.0 hi, 1.2 si, 0.0 st
KiB Mem : 8010488 total, 4202600 free, 2044268 used, 1763620 buff/cache
KiB Swap: 1048572 total, 1048572 free, 0 used. 5644572 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4730 oracle 20 0 2390620 108416 46960 S 396.3 1.4 5:13.37 ora_u000_book
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
3812 oracle -2 0 1302296 18708 14540 S 1.7 0.2 0:14.30 ora_vktm_book
2843 oracle 20 0 118764 3340 1660 S 0.0 0.0 0:00.04 -bash
$ strace -f -p 4730 -c
Process 4730 attached with 11 threads
^CProcess 4730 detached
Process 4752 detached
Process 4946 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
51.70 5.708983 6 940107 getrusage
45.81 5.057840 133101 38 30 semtimedop
2.48 0.273415 104 2630 select
0.01 0.000676 6 111 munmap
0.00 0.000302 38 8 madvise
0.00 0.000220 2 136 read
0.00 0.000180 0 424 48 stat
0.00 0.000082 0 352 geteuid
0.00 0.000021 0 72 close
0.00 0.000021 3 8 timer_delete
0.00 0.000018 1 32 write
0.00 0.000014 0 31 rt_sigprocmask
0.00 0.000013 0 32 open
0.00 0.000010 0 72 lseek
0.00 0.000008 0 23 mmap
0.00 0.000008 0 32 getrlimit
0.00 0.000007 0 40 lstat
0.00 0.000004 0 16 semctl
0.00 0.000004 0 16 gettid
0.00 0.000003 0 16 fstat
0.00 0.000002 0 16 rt_sigaction
0.00 0.000002 1 3 io_setup
0.00 0.000001 0 5 rt_sigreturn
0.00 0.000001 0 24 fcntl
0.00 0.000000 0 16 setsockopt
0.00 0.000000 0 3 io_destroy
0.00 0.000000 0 8 epoll_ctl
------ ----------- ----------- --------- --------- ----------------
100.00 11.041835 944271 78 total
--//链接http://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/提到的情况
[root@rico fd]# strace -cp 12165
Process 12165 attached
^CProcess 12165 detached
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
84.22 0.113706 0 980840 poll
10.37 0.014000 7000 2 read
5.41 0.007310 1218 6 semtimedop
0.00 0.000000 0 2 write
0.00 0.000000 0 1 semctl
0.00 0.000000 0 419 getrusage
0.00 0.000000 0 12 times
------ ----------- ----------- --------- --------- ----------------
100.00 0.135016 981282 total
[root@rico fd]# strace -p 12165 -o /tmp/threaded_exec.out
Process 12165 attached
^CProcess 12165 detached
[root@rico fd]# grep poll /tmp/threaded_exec.out | tail
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
poll([{fd=63, events=POLLIN|POLLRDNORM}], 1, 0) = 0 (Timeout)
--//可以推测21c使用semtimedop调用代替poll。
--//重复测试：
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 CCCC > /dev/null;zzdate
trunc(sysdate)+17/24+11/1440+42/86400 -1733994702.866654595
trunc(sysdate)+17/24+12/1440+14/86400 1733994734.657435531
--//Sum = 31.790780936
SYS@book> @ ashtop event 1=1 trunc(sysdate)+17/24+11/1440+42/86400 trunc(sysdate)+17/24+12/1440+14/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
219 6.8 91% | 2024-12-12 17:11:43 2024-12-12 17:12:13 112 31 139
21 .7 9% | cursor: pin S 2024-12-12 17:11:51 2024-12-12 17:12:10 1 10 10
--//停止监听，修改监听注解DEDICATED_THROUGH_BROKER_listener =ON，再重新启动监听，重复测试：
$ zzdate;seq 8 | xargs -P 8 -IQ sqlplus -s -l scott/book@book01p @m9.txt 8e5 DDDD > /dev/null;zzdate
trunc(sysdate)+17/24+19/1440+15/86400 -1733995155.460912071
trunc(sysdate)+17/24+19/1440+47/86400 1733995187.632465143
--//Sum = 32.171553072,虚拟机器太不稳定。
SCOTT@book01p> @ ashtop event 1=1 trunc(sysdate)+17/24+19/1440+15/86400 trunc(sysdate)+17/24+19/1440+47/86400
Total Distinct Distinct Distinct
Seconds AAS %This EVENT FIRST_SEEN LAST_SEEN Execs Seen Tstamps Execs Seen1
--------- ------- ------- ------------------------------------------ ------------------- ------------------- ---------- -------- -----------
218 6.8 88% | 2024-12-12 17:19:16 2024-12-12 17:19:46 105 31 135
29 .9 12% | cursor: pin S 2024-12-12 17:19:18 2024-12-12 17:19:46 1 10 10
1 .0 0% | log file sync 2024-12-12 17:19:15 2024-12-12 17:19:15 1 1 1
--//从性能讲差别不大。
SCOTT@book01p> Select method,count(*),round(avg(TIME_ELA),2),sum(TIME_ELA) from job_times group by method order by 3 ;
METHOD COUNT(*) ROUND(AVG(TIME_ELA),2) SUM(TIME_ELA)
-------------------- ---------- ---------------------- -------------
CCCC 8 3095 24760
DDDD 8 3121.88 24975
总结：
1.参考链接：
http://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/
https://martincarstenbach.wordpress.com/2014/07/21/implications-of-threaded_execution-true-in-12c/
2.采用后sys启动数据库必须使用用户+口令模式，实际上关库也是一样。
3.kill 其中1个线程，整个相关线程全部kill。
4.不建议在生产系统使用。
5.补充后台进程实际上scmn
SYS@book> @ bgx scmn
PROGRAM MODULE ACTION SID PID SPID
---------------------- ------------ ------ --- ------- --------
oracle@centtest (SCMN) 1 4 3803
oracle@centtest (SCMN) 382 11 3825
oracle@centtest (SCMN) 383 15 3831
oracle@centtest (SCMN) 129 17 3834
oracle@centtest (SCMN) 276 78 4730
oracle@centtest (SCMN) 8 36 3871
oracle@centtest (SCMN) 10 40 3882
oracle@centtest (SCMN) 263 46 3891
oracle@centtest (SCMN) 130 25 3841
9 rows selected.
6.测试后注意修改回来。
SYS@book> alter system reset threaded_execution;
System altered.
--//修改监听文件，注解DEDICATED_THROUGH_BROKER_listener =ON。
