[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参数。
--//测试更正我以前的观点,不能杀线程。
posted @   lfree  阅读(4)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
历史上的今天:
2022-12-14 [20221130]测试访问视图v$session几种情况的性能差异.txt
点击右上角即可分享
微信分享提示