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