[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参数。
--//测试更正我以前的观点,不能杀线程。