oracle 多线程简介

来源：这里教程网时间：2026-03-03 21:17:26 作者：

# 一 oracle 多线程简介： Oracle 12c 数据库引入了进程的多线程架构。多线程 Oracle 模型使 UNIX 和 Linux 上的 Oracle 进程能够在单独的地址空间中作为操作系统线程运行。它适用于 BACKGROUND PROCESSES 和 SERVER PROCESSES。在这种情况下，UNIX 和 Linux 上的某些后台进程始终使用线程执行;其余的 Oracle 进程作为操作系统进程运行。此功能有助于减少 CPU 和内存使用量，因为它减少了 Oracle 进程的数量。默认情况下，它是禁用的。您可以通过设置 threaded_execution = TRUE 来启用此功能当此初始化参数设置为 TRUE 时，应将 DEDICATED_THROUGH_BROKER_listener-name=ON 参数添加到 listener.ora 文件中，其中 listener-name 是数据库侦听器的名称， LOCAL_LISTENER 初始化参数应设置为与您的实例服务对应的 TNS 名称条目。这使服务器能够在通过侦听器请求与数据库的连接时生成线程。 # 二测试oracle 多线程模式下面，我们看一下开启和未开启多线程架构的数据库的后台进程情况：注意：如果在 AIX 平台上使用此功能，则需要针对 BUG 22226365 - THREADED_EXECUTION=TRUE - SCMN PROCESS RES MEMORY INCREASE 的补丁 ## 1 环境介绍：操作系统环境： [root@zc ~]# cat /etc/redhat-release Red Hat Enterprise Linux Server release 7.6 (Maipo) 数据库环境： Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 ## 2 参数设置情况： SQL> show parameter threaded NAME TYPE VALUE threaded_execution boolean FALSE ## 3 查看进程情况： SQL> !ps -ef|grep test oracle 1912 1 0 Jan04 ? 00:06:28 ora_m000_test oracle 3432 1 0 14:57 ? 00:00:01 ora_m004_test oracle 7906 7905 0 15:29 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 8203 7905 0 15:31 pts/1 00:00:00 /bin/bash -c ps -ef|grep test oracle 8205 8203 0 15:31 pts/1 00:00:00 grep test oracle 18843 1 0 Jan06 ? 00:04:09 ora_m005_test oracle 22575 1 0 2024 ? 00:02:45 ora_pmon_test oracle 22577 1 0 2024 ? 00:01:00 ora_clmn_test oracle 22579 1 0 2024 ? 00:05:50 ora_psp0_test oracle 22582 1 0 2024 ? 00:10:03 ora_vktm_test oracle 22586 1 0 2024 ? 00:01:45 ora_gen0_test oracle 22588 1 0 2024 ? 00:00:56 ora_mman_test oracle 22592 1 0 2024 ? 00:10:05 ora_gen1_test oracle 22595 1 0 2024 ? 00:01:59 ora_diag_test oracle 22597 1 0 2024 ? 00:01:07 ora_ofsd_test oracle 22600 1 0 2024 ? 00:19:34 ora_dbrm_test oracle 22602 1 0 2024 ? 00:48:55 ora_vkrm_test oracle 22604 1 0 2024 ? 00:02:02 ora_svcb_test oracle 22606 1 0 2024 ? 00:03:54 ora_pman_test oracle 22608 1 0 2024 ? 01:00:15 ora_dia0_test oracle 22610 1 0 2024 ? 00:02:41 ora_dbw0_test oracle 22612 1 0 2024 ? 00:02:16 ora_lgwr_test oracle 22614 1 0 2024 ? 00:08:56 ora_ckpt_test oracle 22616 1 0 2024 ? 00:00:27 ora_lg00_test oracle 22618 1 0 2024 ? 00:01:06 ora_smon_test oracle 22620 1 0 2024 ? 00:00:26 ora_lg01_test oracle 22622 1 0 2024 ? 00:04:22 ora_smco_test oracle 22624 1 0 2024 ? 00:00:30 ora_reco_test oracle 22626 1 0 2024 ? 00:01:01 ora_w000_test oracle 22628 1 0 2024 ? 00:01:27 ora_lreg_test oracle 22630 1 0 2024 ? 00:01:01 ora_w001_test oracle 22632 1 0 2024 ? 00:00:54 ora_pxmn_test oracle 22634 1 0 2024 ? 00:32:12 ora_fenc_test oracle 22636 1 0 2024 ? 00:08:27 ora_mmon_test oracle 22638 1 0 2024 ? 00:22:22 ora_mmnl_test oracle 22640 1 0 2024 ? 00:00:47 ora_d000_test oracle 22642 1 0 2024 ? 00:00:39 ora_s000_test oracle 22644 1 0 2024 ? 00:00:34 ora_tmon_test oracle 22649 1 0 2024 ? 00:00:00 oracletest (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 22667 1 0 2024 ? 00:01:42 ora_p000_test oracle 22669 1 0 2024 ? 00:01:42 ora_p001_test oracle 22671 1 0 2024 ? 00:01:43 ora_p002_test oracle 22673 1 0 2024 ? 00:01:39 ora_p003_test oracle 22675 1 0 2024 ? 00:01:40 ora_p004_test oracle 22677 1 0 2024 ? 00:01:41 ora_p005_test oracle 22679 1 0 2024 ? 00:01:40 ora_p006_test oracle 22681 1 0 2024 ? 00:00:32 ora_tt00_test oracle 22683 1 0 2024 ? 00:00:25 ora_tt01_test oracle 22685 1 0 2024 ? 00:01:32 ora_tt02_test oracle 22688 1 0 2024 ? 00:00:26 ora_aqpc_test oracle 22692 1 0 2024 ? 00:34:58 ora_cjq0_test oracle 22696 1 0 2024 ? 00:01:39 ora_p007_test oracle 22698 1 0 2024 ? 00:01:39 ora_p008_test oracle 22700 1 0 2024 ? 00:01:38 ora_p009_test oracle 22702 1 0 2024 ? 00:01:38 ora_p00a_test oracle 22704 1 0 2024 ? 00:01:42 ora_p00b_test oracle 22706 1 0 2024 ? 00:01:39 ora_p00c_test oracle 22708 1 0 2024 ? 00:01:40 ora_p00d_test oracle 22710 1 0 2024 ? 00:01:39 ora_p00e_test oracle 22712 1 0 2024 ? 00:01:40 ora_p00f_test oracle 22714 1 0 2024 ? 00:01:00 ora_w002_test oracle 22930 1 0 2024 ? 00:01:01 ora_w003_test oracle 22936 1 0 2024 ? 00:01:00 ora_w004_test oracle 22941 1 0 2024 ? 00:00:35 ora_qm02_test oracle 22945 1 0 2024 ? 00:00:40 ora_q002_test oracle 22947 1 0 2024 ? 00:00:25 ora_q003_test oracle 24213 1 0 2024 ? 00:01:00 ora_w005_test oracle 24218 1 0 2024 ? 00:01:01 ora_w006_test oracle 24222 1 0 2024 ? 00:01:01 ora_w007_test oracle 24227 1 0 2024 ? 00:01:01 ora_w008_test oracle 24263 1 0 2024 ? 00:01:00 ora_w009_test oracle 24267 1 0 2024 ? 00:00:59 ora_w00a_test oracle 24274 1 0 2024 ? 00:00:59 ora_w00b_test oracle 24278 1 0 2024 ? 00:00:59 ora_w00c_test oracle 24283 1 0 2024 ? 00:00:58 ora_w00d_test oracle 24327 1 0 2024 ? 00:01:00 ora_w00e_test oracle 24332 1 0 2024 ? 00:01:01 ora_w00f_test oracle 25607 1 0 Jan08 ? 00:01:18 ora_m001_test oracle 26725 1 0 09:38 ? 00:00:01 oracletest (LOCAL=NO) oracle 27465 1 0 Jan04 ? 00:07:34 ora_m003_test oracle 30497 1 0 Jan06 ? 00:03:53 ora_m002_test ## 4 开启多线程模式再次查询 SQL> ALTER SYSTEM SET threaded_execution=true SCOPE=SPFILE; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 12c: Threaded_execution=true Prevents OS Login As Sysdba (Doc ID 1639445.1) 注意：这里再次开启数据库必须输入密码 [oracle@zc ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 9 15:37:15 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 1073737800 bytes Fixed Size 8904776 bytes Variable Size 360710144 bytes Database Buffers 696254464 bytes Redo Buffers 7868416 bytes Database mounted. Database opened. SQL> SQL> show parameter threaded_execution NAME TYPE VALUE threaded_execution boolean TRUE ## 5 查看进程情况 SQL> !ps -ef|grep test oracle 9266 1 0 15:37 ? 00:00:00 ora_pmon_test oracle 9268 1 0 15:37 ? 00:00:01 ora_u002_test oracle 9271 1 0 15:37 ? 00:00:00 ora_psp0_test oracle 9274 1 0 15:37 ? 00:00:00 ora_vktm_test oracle 9281 1 0 15:37 ? 00:00:00 ora_gen1_test oracle 9284 1 4 15:37 ? 00:00:06 ora_u006_test oracle 9287 1 0 15:37 ? 00:00:00 ora_ofsd_test oracle 9295 1 0 15:37 ? 00:00:00 ora_dbw0_test oracle 9297 1 0 15:37 ? 00:00:00 ora_lgwr_test oracle 9315 1 0 15:37 ? 00:00:00 ora_u00a_test oracle 9946 9253 0 15:40 pts/1 00:00:00 /bin/bash -c ps -ef|grep test oracle 9948 9946 0 15:40 pts/1 00:00:00 grep test 后台进程的数量减少了。一些后台进程（pmon、dbw、psp、vktm）的行为与以前一样。其他 backgtound 进程属于名称为 ora_uxxx_<SID> 的多线程进程。 ## 6 通过 V$PROCESS 查询进程和线程情况 SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid; PSEUDO NONE 9284 9326 oracle@zc PRESPAWNED THREAD 9284 9328 oracle@zc PRESPAWNED THREAD 9284 9329 oracle@zc PRESPAWNED THREAD 9284 9330 oracle@zc PRESPAWNED THREAD 9284 9331 oracle@zc PRESPAWNED THREAD 9284 9332 oracle@zc PRESPAWNED THREAD 9284 9333 oracle@zc PRESPAWNED THREAD 9284 9335 oracle@zc PRESPAWNED THREAD 9284 9336 oracle@zc PRESPAWNED THREAD 9284 9337 oracle@zc PRESPAWNED THREAD 9284 9339 oracle@zc PRESPAWNED THREAD 9284 9340 oracle@zc PRESPAWNED THREAD 9284 9341 oracle@zc PRESPAWNED THREAD 9284 9342 oracle@zc PRESPAWNED THREAD 9284 9343 oracle@zc PRESPAWNED THREAD 9284 9344 oracle@zc PRESPAWNED THREAD 9284 9345 oracle@zc PRESPAWNED THREAD 9284 9355 oracle@zc PRESPAWNED THREAD 9284 9356 oracle@zc PRESPAWNED THREAD 9284 9357 oracle@zc PRESPAWNED THREAD 9284 9358 oracle@zc PRESPAWNED THREAD 9284 9359 oracle@zc PRESPAWNED THREAD 9284 9360 oracle@zc PRESPAWNED THREAD 9284 9361 oracle@zc PRESPAWNED THREAD 9284 9362 oracle@zc PRESPAWNED THREAD 9284 9363 oracle@zc PRESPAWNED THREAD 9284 9364 oracle@zc PRESPAWNED THREAD 10130 10130 oracle@zc PROCESS 10133 10133 oracle@zc PROCESS 10136 10136 oracle@zc PROCESS 10138 10138 oracle@zc PROCESS 9266 9266 PMON oracle@zc (PMON) PROCESS 9271 9271 PSP0 oracle@zc (PSP0) PROCESS 9274 9274 VKTM oracle@zc (VKTM) PROCESS 9295 9295 DBW0 oracle@zc (DBW0) PROCESS 9268 9268 SCMN oracle@zc (SCMN) THREAD 9268 9269 CLMN oracle@zc (CLMN) THREAD 9268 9277 GEN0 oracle@zc (GEN0) THREAD 9268 9278 MMAN oracle@zc (MMAN) THREAD 9268 9289 DBRM oracle@zc (DBRM) THREAD 9268 9292 PMAN oracle@zc (PMAN) THREAD 9268 9299 CKPT oracle@zc (CKPT) THREAD 9268 9301 SMON oracle@zc (SMON) THREAD 9268 9306 LREG oracle@zc (LREG) THREAD 9268 9309 FENC oracle@zc (FENC) THREAD 9281 9281 SCMN oracle@zc (SCMN) THREAD 9281 9282 GEN1 oracle@zc (GEN1) THREAD 9284 9284 SCMN oracle@zc (SCMN) THREAD 9284 9285 DIAG oracle@zc (DIAG) THREAD 9284 9290 VKRM oracle@zc (VKRM) THREAD 9284 9291 SVCB oracle@zc (SVCB) THREAD 9284 9293 DIA0 oracle@zc (DIA0) THREAD 9284 9303 SMCO oracle@zc (SMCO) THREAD 9284 9304 RECO oracle@zc (RECO) THREAD 9284 9305 W000 oracle@zc (W000) THREAD 9284 9307 W001 oracle@zc (W001) THREAD 9284 9308 PXMN oracle@zc (PXMN) THREAD 9284 9310 MMON oracle@zc (MMON) THREAD 9284 9311 MMNL oracle@zc (MMNL) THREAD 9284 9312 D000 oracle@zc (D000) THREAD 9284 9313 S000 oracle@zc (S000) THREAD 9284 9317 N000 oracle@zc (N000) THREAD 9284 9322 oracle@zc THREAD 9284 9327 oracle@zc THREAD 9284 9347 M000 oracle@zc (M000) THREAD 9284 9348 M001 oracle@zc (M001) THREAD 9284 9365 W002 oracle@zc (W002) THREAD 9284 9366 AQPC oracle@zc (AQPC) THREAD 9284 9367 CJQ0 oracle@zc (CJQ0) THREAD 9284 9369 P000 oracle@zc (P000) THREAD 9284 9370 P001 oracle@zc (P001) THREAD 9284 9371 P002 oracle@zc (P002) THREAD 9284 9372 P003 oracle@zc (P003) THREAD 9284 9373 P004 oracle@zc (P004) THREAD 9284 9374 P005 oracle@zc (P005) THREAD 9284 9375 P006 oracle@zc (P006) THREAD 9284 9376 P007 oracle@zc (P007) THREAD 9284 9377 P008 oracle@zc (P008) THREAD 9284 9378 P009 oracle@zc (P009) THREAD 9284 9379 P00A oracle@zc (P00A) THREAD 9284 9380 P00B oracle@zc (P00B) THREAD 9284 9381 P00C oracle@zc (P00C) THREAD 9284 9382 P00D oracle@zc (P00D) THREAD 9284 9383 P00E oracle@zc (P00E) THREAD 9284 9384 P00F oracle@zc (P00F) THREAD 9284 9565 W003 oracle@zc (W003) THREAD 9284 9571 W004 oracle@zc (W004) THREAD 9284 9576 QM02 oracle@zc (QM02) THREAD 9284 9578 Q002 oracle@zc (Q002) THREAD 9284 9579 Q003 oracle@zc (Q003) THREAD 9284 9590 M002 oracle@zc (M002) THREAD 9287 9287 SCMN oracle@zc (SCMN) THREAD 9287 9288 OFSD oracle@zc (OFSD) THREAD 9297 9297 SCMN oracle@zc (SCMN) THREAD 9297 9298 LGWR oracle@zc (LGWR) THREAD 9297 9300 LG00 oracle@zc (LG00) THREAD 9297 9302 LG01 oracle@zc (LG01) THREAD 9315 9315 SCMN oracle@zc (SCMN) THREAD 9315 9316 TMON oracle@zc (TMON) THREAD 9315 9352 TT00 oracle@zc (TT00) THREAD 9315 9353 TT01 oracle@zc (TT01) THREAD 9315 9354 TT02 oracle@zc (TT02) THREAD 可以看到spid 9284 9268 分别对应了多个线程。 ## 7 查看监听的连接情况： [root@zc ~]# ps -ef|grep test|grep LOCAL oracle 10130 1 0 15:41 ? 00:00:00 oracletest (LOCAL=NO) oracle 10133 1 0 15:41 ? 00:00:00 oracletest (LOCAL=NO) oracle 10136 1 0 15:41 ? 00:00:00 oracletest (LOCAL=NO) oracle 10138 1 0 15:41 ? 00:00:00 oracletest (LOCAL=NO) 可以看到监听还是通过进程连接的 ## 8 修改监听配置文件 [grid@zc admin]$ pwd /u01/app/19.9.0/grid/network/admin [grid@zc admin]$ vi listener.ora [grid@zc admin]$ [grid@zc admin]$ pwd /u01/app/19.9.0/grid/network/admin [grid@zc admin]$ cat listener.ora Backup file is /u01/app/grid/crsdata/zc/output/listener.ora.bak.zc.grid line added by Agent listener.ora Network Configuration File: /u01/app/19.9.0/grid/network/admin/listener.ora Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = zc)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=ON # line added by Agent DEDICATED_THROUGH_BROKER_LISTENER=ON ## 9 重启监听 [grid@zc admin]$ lsnrctl stop LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JAN-2025 15:55:00 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zc)(PORT=1521))) lThe command completed successfully [grid@zc admin]$ lsnrctl start LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JAN-2025 15:55:03 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /u01/app/19.9.0/grid/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/19.9.0/grid/network/admin/listener.ora Log messages written to /u01/app/grid/diag/tnslsnr/zc/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zc)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zc)(PORT=1521))) STATUS of the LISTENER Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-JAN-2025 15:55:03 Uptime 0 days 0 hr. 0 min. 5 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19.9.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/zc/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zc)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) The listener supports no services The command completed successfully [grid@zc admin]$ lsnrctl status LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JAN-2025 15:55:14 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=zc)(PORT=1521))) STATUS of the LISTENER Alias LISTENER Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 09-JAN-2025 15:55:03 Uptime 0 days 0 hr. 0 min. 11 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/19.9.0/grid/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/zc/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=zc)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "test" has 1 instance(s). Instance "test", status READY, has 2 handler(s) for this service... Service "testXDB" has 1 instance(s). Instance "test", status READY, has 1 handler(s) for this service... The command completed successfully SQL> alter system register; System altered. ## 10 再次查看 SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid; PSEUDO NONE 9284 9326 oracle@zc PRESPAWNED THREAD 9284 9328 oracle@zc PRESPAWNED THREAD 9284 9329 oracle@zc PRESPAWNED THREAD 9284 9330 oracle@zc PRESPAWNED THREAD 9284 9331 oracle@zc PRESPAWNED THREAD 9284 9332 oracle@zc PRESPAWNED THREAD 9284 9333 oracle@zc PRESPAWNED THREAD 9284 9335 oracle@zc PRESPAWNED THREAD 9284 9336 oracle@zc PRESPAWNED THREAD 9284 9337 oracle@zc PRESPAWNED THREAD 9284 9339 oracle@zc PRESPAWNED THREAD 9284 9340 oracle@zc PRESPAWNED THREAD 9284 9341 oracle@zc PRESPAWNED THREAD 9284 9342 oracle@zc PRESPAWNED THREAD 9284 9343 oracle@zc PRESPAWNED THREAD 9284 9345 oracle@zc PRESPAWNED THREAD 9284 9355 oracle@zc PRESPAWNED THREAD 9284 9356 oracle@zc PRESPAWNED THREAD 9284 9357 oracle@zc PRESPAWNED THREAD 9284 9360 oracle@zc PRESPAWNED THREAD 9284 9361 oracle@zc PRESPAWNED THREAD 9284 9362 oracle@zc PRESPAWNED THREAD 9284 9363 oracle@zc PRESPAWNED THREAD 9284 9364 oracle@zc PRESPAWNED THREAD 9266 9266 PMON oracle@zc (PMON) PROCESS 9271 9271 PSP0 oracle@zc (PSP0) PROCESS 9274 9274 VKTM oracle@zc (VKTM) PROCESS 9295 9295 DBW0 oracle@zc (DBW0) PROCESS 9268 9268 SCMN oracle@zc (SCMN) THREAD 9268 9269 CLMN oracle@zc (CLMN) THREAD 9268 9277 GEN0 oracle@zc (GEN0) THREAD 9268 9278 MMAN oracle@zc (MMAN) THREAD 9268 9289 DBRM oracle@zc (DBRM) THREAD 9268 9292 PMAN oracle@zc (PMAN) THREAD 9268 9299 CKPT oracle@zc (CKPT) THREAD 9268 9301 SMON oracle@zc (SMON) THREAD 9268 9306 LREG oracle@zc (LREG) THREAD 9268 9309 FENC oracle@zc (FENC) THREAD 9281 9281 SCMN oracle@zc (SCMN) THREAD 9281 9282 GEN1 oracle@zc (GEN1) THREAD 9284 10929 W005 oracle@zc (W005) THREAD 9284 10933 W006 oracle@zc (W006) THREAD 9284 10937 M003 oracle@zc (M003) THREAD 9284 10939 W007 oracle@zc (W007) THREAD 9284 10942 W008 oracle@zc (W008) THREAD 9284 10946 W009 oracle@zc (W009) THREAD 9284 10950 W00A oracle@zc (W00A) THREAD 9284 10954 W00B oracle@zc (W00B) THREAD 9284 10958 W00C oracle@zc (W00C) THREAD 9284 10991 W00D oracle@zc (W00D) THREAD 9284 10995 W00E oracle@zc (W00E) THREAD 9284 11034 W00F oracle@zc (W00F) THREAD 9284 9284 SCMN oracle@zc (SCMN) THREAD 9284 9285 DIAG oracle@zc (DIAG) THREAD 9284 9290 VKRM oracle@zc (VKRM) THREAD 9284 9291 SVCB oracle@zc (SVCB) THREAD 9284 9293 DIA0 oracle@zc (DIA0) THREAD 9284 9303 SMCO oracle@zc (SMCO) THREAD 9284 9304 RECO oracle@zc (RECO) THREAD 9284 9305 W000 oracle@zc (W000) THREAD 9284 9307 W001 oracle@zc (W001) THREAD 9284 9308 PXMN oracle@zc (PXMN) THREAD 9284 9310 MMON oracle@zc (MMON) THREAD 9284 9311 MMNL oracle@zc (MMNL) THREAD 9284 9312 D000 oracle@zc (D000) THREAD 9284 9313 S000 oracle@zc (S000) THREAD 9284 9317 N000 oracle@zc (N000) THREAD 9284 9322 oracle@zc THREAD 9284 9327 oracle@zc THREAD 9284 9344 oracle@zc THREAD 9284 9347 M000 oracle@zc (M000) THREAD 9284 9348 M001 oracle@zc (M001) THREAD 9284 9359 oracle@zc THREAD 9284 9365 W002 oracle@zc (W002) THREAD 9284 9366 AQPC oracle@zc (AQPC) THREAD 9284 9367 CJQ0 oracle@zc (CJQ0) THREAD 9284 9369 P000 oracle@zc (P000) THREAD 9284 9370 P001 oracle@zc (P001) THREAD 9284 9371 P002 oracle@zc (P002) THREAD 9284 9372 P003 oracle@zc (P003) THREAD 9284 9373 P004 oracle@zc (P004) THREAD 9284 9374 P005 oracle@zc (P005) THREAD 9284 9375 P006 oracle@zc (P006) THREAD 9284 9376 P007 oracle@zc (P007) THREAD 9284 9377 P008 oracle@zc (P008) THREAD 9284 9378 P009 oracle@zc (P009) THREAD 9284 9379 P00A oracle@zc (P00A) THREAD 9284 9380 P00B oracle@zc (P00B) THREAD 9284 9381 P00C oracle@zc (P00C) THREAD 9284 9382 P00D oracle@zc (P00D) THREAD 9284 9383 P00E oracle@zc (P00E) THREAD 9284 9384 P00F oracle@zc (P00F) THREAD 9284 9565 W003 oracle@zc (W003) THREAD 9284 9571 W004 oracle@zc (W004) THREAD 9284 9576 QM02 oracle@zc (QM02) THREAD 9284 9578 Q002 oracle@zc (Q002) THREAD 9284 9579 Q003 oracle@zc (Q003) THREAD 9284 9590 M002 oracle@zc (M002) THREAD 9287 9287 SCMN oracle@zc (SCMN) THREAD 9287 9288 OFSD oracle@zc (OFSD) THREAD 9297 9297 SCMN oracle@zc (SCMN) THREAD 9297 9298 LGWR oracle@zc (LGWR) THREAD 9297 9300 LG00 oracle@zc (LG00) THREAD 9297 9302 LG01 oracle@zc (LG01) THREAD 9315 9315 SCMN oracle@zc (SCMN) THREAD 9315 9316 TMON oracle@zc (TMON) THREAD 9315 9352 TT00 oracle@zc (TT00) THREAD 9315 9353 TT01 oracle@zc (TT01) THREAD 9315 9354 TT02 oracle@zc (TT02) THREAD 我们的session对应的process 和 thread SQL> select spid, stid, pname, program, execution_type from v$process where addr in (select PADDR from v$session where username='SYSTEM' and program='plsqldev.exe') order by execution_type, spid, stid; 9284 9344 oracle@zc THREAD 9284 9359 oracle@zc THREAD [grid@zc admin]$ ps -ef|grep test|grep LOCAL [grid@zc admin]$ ps -ef|grep 9284 oracle 9284 1 1 15:37 ? 00:00:16 ora_u006_test grid 12297 11337 0 15:57 pts/2 00:00:00 grep --color=auto 9284 已经看不到local=No的进程了 # 三数据库线程研究 ## 1线程分析系统层面的线程情况 [grid@zc admin]$ ps -eLf | egrep "UI[D]|ora[_]u002" UID PID PPID LWP C NLWP STIME TTY TIME CMD oracle 9268 1 9268 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9269 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9277 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9278 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9289 0 10 15:37 ? 00:00:01 ora_u002_test oracle 9268 1 9292 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9299 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9301 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9306 0 10 15:37 ? 00:00:00 ora_u002_test oracle 9268 1 9309 0 10 15:37 ? 00:00:02 ora_u002_test 数据库中线程情况 9268 9268 SCMN oracle@zc (SCMN) THREAD 9268 9269 CLMN oracle@zc (CLMN) THREAD 9268 9277 GEN0 oracle@zc (GEN0) THREAD 9268 9278 MMAN oracle@zc (MMAN) THREAD 9268 9289 DBRM oracle@zc (DBRM) THREAD 9268 9292 PMAN oracle@zc (PMAN) THREAD 9268 9299 CKPT oracle@zc (CKPT) THREAD 9268 9301 SMON oracle@zc (SMON) THREAD 9268 9306 LREG oracle@zc (LREG) THREAD 9268 9309 FENC oracle@zc (FENC) THREAD ## 2测试杀掉线程 kill -9 我们先模拟一个进程登录数据库 [oracle@zc ~]$ sqlplus zc/zc@zc:1521/test SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 9 16:41:14 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Thu Jan 09 2025 10:25:48 +08:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> set linesize 1000 SQL> select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v$session where username='ZC') order by execution_type, spid, stid; SPID STID PNAME PROGRAM EXECUTION_TYPE 9284 9330 oracle@zc THREAD 9284 9363 oracle@zc THREAD 现在我们有两个数据库连接的线程。 ps -eLf | egrep -i "[U]ID|928[4]" [grid@zc admin]$ ps -eLf | egrep -i "[U]ID|928[4]" UID PID PPID LWP C NLWP STIME TTY TIME CMD oracle 9284 1 9284 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9285 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9290 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9291 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9293 0 82 15:37 ? 00:00:07 ora_u006_test oracle 9284 1 9303 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9304 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9305 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9307 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9308 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9310 0 82 15:37 ? 00:00:02 ora_u006_test oracle 9284 1 9311 0 82 15:37 ? 00:00:02 ora_u006_test oracle 9284 1 9312 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9313 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9317 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9322 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9326 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9327 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9328 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9329 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9330 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9331 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9332 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9333 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9335 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9336 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9337 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9339 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9340 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9341 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9342 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9343 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9344 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9345 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9347 0 82 15:37 ? 00:00:03 ora_u006_test oracle 9284 1 9348 0 82 15:37 ? 00:00:04 ora_u006_test oracle 9284 1 9355 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9356 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9357 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9359 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9360 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9361 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9362 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9363 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9364 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9365 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9366 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9367 0 82 15:37 ? 00:00:05 ora_u006_test oracle 9284 1 9369 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9370 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9371 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9372 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9373 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9374 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9375 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9376 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9377 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9378 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9379 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9380 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9381 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9382 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9383 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9384 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9565 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9571 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9576 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9578 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9579 0 82 15:37 ? 00:00:00 ora_u006_test oracle 9284 1 9590 0 82 15:37 ? 00:00:03 ora_u006_test oracle 9284 1 10929 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10933 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10937 0 82 15:47 ? 00:00:02 ora_u006_test oracle 9284 1 10939 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10942 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10946 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10950 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10954 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10958 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10991 0 82 15:47 ? 00:00:00 ora_u006_test oracle 9284 1 10995 0 82 15:48 ? 00:00:00 ora_u006_test oracle 9284 1 11034 0 82 15:48 ? 00:00:00 ora_u006_test [grid@zc admin]$ ps -eLf | egrep -i "[U]ID|928[4]"|grep 9363 oracle 9284 1 9363 0 82 15:37 ? 00:00:00 ora_u006_test 杀掉其中一个： [root@zc ~]# kill -9 9363 要杀的会话 SQL> select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid, stid; select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid, stid * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 9284 Thread ID: 9363 Session ID: 384 Serial number: 47329 会话2： SQL> select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid, stid; select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid, stid * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 9284 Thread ID: 9330 Session ID: 878 Serial number: 16006 ## 3测试杀掉线程 kill 9 再次测试下kill 不加 -9 选项老规则准备两个会话 SQL> select spid, stid, pname, program, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid, stid; SPID STID PNAME PROGRAM EXECUTION_TYPE 19185 19199 oracle@zc THREAD 19185 19209 oracle@zc THREAD SQL> select spid, stid, pname, program,ADDR, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid,ADDR, stid; SPID STID PNAME PROGRAM ADDR EXECUTION_TYPE 19185 19199 oracle@zc 00000000748440A8 THREAD 19185 19209 oracle@zc 0000000074851668 THREAD SQL> select sid,serial#,paddr from v$session where username='ZC'; SID SERIAL# PADDR 130 55132 00000000748440A8 378 61358 0000000074851668 杀掉线程 [root@zc ~]# kill 19209 当前线程： SQL>select sid,serial#,paddr from v$session where username='ZC'; select sid,serial#,paddr from v$session where username='ZC' * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 19185 Thread ID: 19209 Session ID: 378 Serial number: 61358 其他线程： SQL> select sid,serial#,paddr from v$session where username='ZC'; SID SERIAL# PADDR 130 55132 00000000748440A8 378 61358 0000000074851668 ## 4测试杀掉线程 alter system kill session 准备两个会话： SQL> set linesize 1000 SQL> select spid, stid, pname, program,ADDR, execution_type from v\$process where addr in (select PADDR from v\$session where username='ZC') order by execution_type, spid,ADDR, stid; SPID STID PNAME PROGRAM ADDR EXECUTION_TYPE 19185 19199 oracle@zc 00000000748440A8 THREAD 19185 19281 oracle@zc 0000000074862C48 THREAD SQL> select sid,serial#,paddr from v\$session where username='ZC'; SID SERIAL# PADDR 8 51153 0000000074862C48 130 55132 00000000748440A8 杀掉其中一个： alter system kill session '8,51153'; SQL> alter system kill session '8,51153'; System altered. 被杀的会话： SQL> select sid,serial#,paddr from v$session where username='ZC'; select sid,serial#,paddr from v$session where username='ZC' * ERROR at line 1: ORA-00028: your session has been killed 其他会话： SQL> select sid,serial#,paddr from v$session where username='ZC'; SID SERIAL# PADDR 130 55132 00000000748440A8 # 四参考文档： https://docs.oracle.com/database/121/REFRN/GUID-7A668A49-9FC5-4245-AD27-10D90E5AE8A8.htm#REFRN10335 12c: threaded_execution=true Prevents OS Login As Sysdba(Doc ID 1639445.1) Background Processes Not Visible In ps -ef Command in 12C(Doc ID 2251439.1) Managing Prespawned Processes(Doc ID 2164837.1) Increase In CPU Usage After Upgrade To 12C(Doc ID 2397287.1) Multi-Threaded Oracle Model : 12c New(Doc ID 1958348.1) http://blog.ora-600.pl/2015/12/17/oracle-12c-internals-of-threaded-execution/

编辑推荐：

返回列表

oracle 多线程简介

编辑推荐：

相关推荐

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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推