[20241214]Oracle 多线程测试(补充).txt
[20241214]Oracle 多线程测试(补充).txt
--//补充测试alter system kill session 'sid,serial#' immediate;杀线程的情况是否可行。
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;
--//修改/u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora监听文件,加入如下:
DEDICATED_THROUGH_BROKER_listener =ON
2.测试1:
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 390
SERIAL# : 62466
PROCESS : 3755
SERVER : DEDICATED
SPID : 3757
PID : 75
P_SERIAL# : 2
KILL_COMMAND : alter system kill session '390,62466' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 390
SCOTT@book01p> @pr
==============================
USERNAME : SCOTT
SID : 390
SERIAL# : 62466
CON_ID : 3
SPID : 3757
SOSID : 3757_3758
STID : 3758
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//再启动一个会话,过程略:
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 3 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 3758 0 3 10:13 ? 00:00:00 ora_u000_book
~~~~~~~~~~~~~~~~~~~~~~~~~~
oracle 3757 1 3812 0 3 10:15 ? 00:00:00 ora_u000_book
--//session 3:
SYS@book> alter system kill session '390,62466' immediate;
System altered.
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 2 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 3812 0 2 10:15 ? 00:00:00 ora_u000_book
--//ok,视乎这样kill线程没有问题。
--//看看另外的会话执行sql语句是否正常。
--//session 2:
SCOTT@book01p> @ spid
==============================
SID : 405
SERIAL# : 11539
PROCESS : 3811
SERVER : DEDICATED
SPID : 3757
PID : 51
P_SERIAL# : 4
KILL_COMMAND : alter system kill session '405,11539' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> select sysdate from dual ;
SYSDATE
-------------------
2024-12-14 10:17:07
SCOTT@book01p> @ pp.txt 405
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 405
SERIAL# : 11539
CON_ID : 3
SPID : 3757
SOSID : 3757_3812
STID : 3812
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//session 1:
SCOTT@book01p> @pr
ERROR:
ORA-03114: not connected to ORACLE
--//注意提示是ora-03114,一般是ora-3113.
$ oerr ora 3114
03114, 00000, "not connected to ORACLE"
// *Cause:
// *Action:
$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
// First, check for network problems and review the SQL*Net setup.
// Also, look in the alert.log file for any errors. Finally, test to
// see whether the server process is dead and whether a trace file
// was generated at failure time.
3.测试2(kill不加参数看看):
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 3 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 4038 0 3 10:27 ? 00:00:00 ora_u000_book
oracle 3757 1 4108 0 3 10:28 ? 00:00:00 ora_u000_book
$ kill 4108
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 2 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 4038 0 2 10:27 ? 00:00:00 ora_u000_book
--//session 1:
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3757 Thread ID: 4108
Session ID: 28 Serial number: 50318
--//注意提示是ora-03113.
--//简单使用kill也可以.
--//session 2:
SCOTT@book01p> select sysdate from dual ;
SYSDATE
-------------------
2024-12-14 10:30:58
$ kill -9 4038
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//加入-9参数就不行。
4.补充kill命令相关知识:
The command kill sends the specified signal to the specified process or process group. If no signal is specified, the
TERM signal is sent. The TERM signal will kill processes which do not catch this signal. For other processes, it may
be necessary to use the KILL (9) signal, since this signal cannot be caught.
$ kill -l
1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL 5) SIGTRAP
6) SIGABRT 7) SIGBUS 8) SIGFPE 9) SIGKILL 10) SIGUSR1
11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
16) SIGSTKFLT 17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU 25) SIGXFSZ
26) SIGVTALRM 27) SIGPROF 28) SIGWINCH 29) SIGIO 30) SIGPWR
31) SIGSYS 34) SIGRTMIN 35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3
38) SIGRTMIN+4 39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8
43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7
58) SIGRTMAX-6 59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2
63) SIGRTMAX-1 64) SIGRTMAX
--//不加参数缺省TERM是15.
--//实际上监听进程也是开启线程模式。
$ ps -eLf | egrep "UI[D]|tn[s]lsnr"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4838 1 4838 4 2 10:55 ? 00:00:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 4838 1 4839 0 2 10:55 ? 00:00:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
$ kill 4839
$ ps -eLf | egrep "UI[D]|tn[s]lsnr"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//同样的方式kill监听线程就不行。
5.总结:
--//看来前面的测试不全面,作为补充,使用alter system kill session 'sid,serial#' immediate;的方式杀线程没有问题。
--//kill 也可以杀线程,实际上kill进程总是习惯性的使用-9参数。
--//测试更正我以前的观点,不能杀线程。
--//补充测试alter system kill session 'sid,serial#' immediate;杀线程的情况是否可行。
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;
--//修改/u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora监听文件,加入如下:
DEDICATED_THROUGH_BROKER_listener =ON
2.测试1:
--//session 1:
SCOTT@book01p> @ spid
==============================
SID : 390
SERIAL# : 62466
PROCESS : 3755
SERVER : DEDICATED
SPID : 3757
PID : 75
P_SERIAL# : 2
KILL_COMMAND : alter system kill session '390,62466' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> @ pp.txt 390
SCOTT@book01p> @pr
==============================
USERNAME : SCOTT
SID : 390
SERIAL# : 62466
CON_ID : 3
SPID : 3757
SOSID : 3757_3758
STID : 3758
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//再启动一个会话,过程略:
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 3 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 3758 0 3 10:13 ? 00:00:00 ora_u000_book
~~~~~~~~~~~~~~~~~~~~~~~~~~
oracle 3757 1 3812 0 3 10:15 ? 00:00:00 ora_u000_book
--//session 3:
SYS@book> alter system kill session '390,62466' immediate;
System altered.
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 2 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 3812 0 2 10:15 ? 00:00:00 ora_u000_book
--//ok,视乎这样kill线程没有问题。
--//看看另外的会话执行sql语句是否正常。
--//session 2:
SCOTT@book01p> @ spid
==============================
SID : 405
SERIAL# : 11539
PROCESS : 3811
SERVER : DEDICATED
SPID : 3757
PID : 51
P_SERIAL# : 4
KILL_COMMAND : alter system kill session '405,11539' immediate;
PL/SQL procedure successfully completed.
SCOTT@book01p> select sysdate from dual ;
SYSDATE
-------------------
2024-12-14 10:17:07
SCOTT@book01p> @ pp.txt 405
SCOTT@book01p> @ pr
==============================
USERNAME : SCOTT
SID : 405
SERIAL# : 11539
CON_ID : 3
SPID : 3757
SOSID : 3757_3812
STID : 3812
EXECUTION_TYPE : THREAD
PL/SQL procedure successfully completed.
--//session 1:
SCOTT@book01p> @pr
ERROR:
ORA-03114: not connected to ORACLE
--//注意提示是ora-03114,一般是ora-3113.
$ oerr ora 3114
03114, 00000, "not connected to ORACLE"
// *Cause:
// *Action:
$ oerr ora 3113
03113, 00000, "end-of-file on communication channel"
// *Cause: The connection between Client and Server process was broken.
// *Action: There was a communication error that requires further investigation.
// First, check for network problems and review the SQL*Net setup.
// Also, look in the alert.log file for any errors. Finally, test to
// see whether the server process is dead and whether a trace file
// was generated at failure time.
3.测试2(kill不加参数看看):
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 3 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 4038 0 3 10:27 ? 00:00:00 ora_u000_book
oracle 3757 1 4108 0 3 10:28 ? 00:00:00 ora_u000_book
$ kill 4108
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 3757 1 3757 0 2 10:13 ? 00:00:00 ora_u000_book
oracle 3757 1 4038 0 2 10:27 ? 00:00:00 ora_u000_book
--//session 1:
SCOTT@book01p> select sysdate from dual ;
select sysdate from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 3757 Thread ID: 4108
Session ID: 28 Serial number: 50318
--//注意提示是ora-03113.
--//简单使用kill也可以.
--//session 2:
SCOTT@book01p> select sysdate from dual ;
SYSDATE
-------------------
2024-12-14 10:30:58
$ kill -9 4038
$ ps -eLf | egrep "[U]ID|375[7]"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//加入-9参数就不行。
4.补充kill命令相关知识:
The command kill sends the specified signal to the specified process or process group. If no signal is specified, the
TERM signal is sent. The TERM signal will kill processes which do not catch this signal. For other processes, it may
be necessary to use the KILL (9) signal, since this signal cannot be caught.
$ kill -l
1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL 5) SIGTRAP
6) SIGABRT 7) SIGBUS 8) SIGFPE 9) SIGKILL 10) SIGUSR1
11) SIGSEGV 12) SIGUSR2 13) SIGPIPE 14) SIGALRM 15) SIGTERM
16) SIGSTKFLT 17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU 25) SIGXFSZ
26) SIGVTALRM 27) SIGPROF 28) SIGWINCH 29) SIGIO 30) SIGPWR
31) SIGSYS 34) SIGRTMIN 35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3
38) SIGRTMIN+4 39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8
43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12 47) SIGRTMIN+13
48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14 51) SIGRTMAX-13 52) SIGRTMAX-12
53) SIGRTMAX-11 54) SIGRTMAX-10 55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7
58) SIGRTMAX-6 59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2
63) SIGRTMAX-1 64) SIGRTMAX
--//不加参数缺省TERM是15.
--//实际上监听进程也是开启线程模式。
$ ps -eLf | egrep "UI[D]|tn[s]lsnr"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
oracle 4838 1 4838 4 2 10:55 ? 00:00:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
oracle 4838 1 4839 0 2 10:55 ? 00:00:00 /u01/app/oracle/product/21.0.0/dbhome_1/bin/tnslsnr LISTENER -inherit
$ kill 4839
$ ps -eLf | egrep "UI[D]|tn[s]lsnr"
UID PID PPID LWP C NLWP STIME TTY TIME CMD
--//同样的方式kill监听线程就不行。
5.总结:
--//看来前面的测试不全面,作为补充,使用alter system kill session 'sid,serial#' immediate;的方式杀线程没有问题。
--//kill 也可以杀线程,实际上kill进程总是习惯性的使用-9参数。
--//测试更正我以前的观点,不能杀线程。
标签:
oracle
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
2022-12-14 [20221130]测试访问视图v$session几种情况的性能差异.txt