[20241212]Oracle 多线程测试.txt

来源：这里教程网时间：2026-03-03 20:59:54 作者：

[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。

编辑推荐：

返回列表

[20241212]Oracle 多线程测试.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推