# 一 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

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

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

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

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

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/

ITPub博客

oracle 多线程简介

最新文章