大佬教程收集整理的这篇文章主要介绍了Oracle 12c 新特性之多线程数据库操作,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
@H_
618_0@之前我们学习
Oracle基础的时候,有一个概念,叫多进程和多线程。在Unix/Linux等环境下面。数据库是以多进程的方式运行的,当一个会话连接进来,就会通过监听,然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c这个版本上面,
Oracle在Unix/Linux平台上做出了一些改变,引入了多线程的方式。通过参数threaded_execution,我们可以控制数据库是以多进程方式运行还是以多线程方式运行,默认该参数是
false数据库以多进程方式运行。
@H_
618_0@
675_3@1.查看参数的默认值,和数据库进程状态.
show parameter threaded
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
threaded_execution
Boolean
falSE
[root@ol6 ~]# ps -ef | grep cdb1
Oracle 1773 1 0 Aug02 ? 00:00:04 ora_pmon_cdb1
Oracle 1775 1 0 Aug02 ? 00:00:01 ora_clmn_cdb1
Oracle 1777 1 0 Aug02 ? 00:00
:13 ora_psp0_cdb1
Oracle 1786 1 0 Aug02 ? 00
:17:01 ora_vktm_cdb1
Oracle 1790 1 0 Aug02 ? 00:00:08 ora_gen0_cdb1
Oracle 1792 1 0 Aug02 ? 00:00:01 ora_mman_cdb1
Oracle 1796 1 0 Aug02 ? 00:00
:19 ora_gen1_cdb1
Oracle 1800 1 0 Aug02 ? 00:00:03 ora_diag_cdb1
Oracle 1802 1 0 Aug02 ? 00:00:01 ora_ofsd_cdb1
Oracle 1806 1 0 Aug02 ? 00:00:29 ora_dbrm_cdb1
Oracle 1808 1 0 Aug02 ? 00:01
:14 ora_vkrm_cdb1
Oracle 1810 1 0 Aug02 ? 00:00:03 ora_svcb_cdb1
Oracle 1812 1 0 Aug02 ? 00:00
:10 ora_pman_cdb1
Oracle 1814 1 0 Aug02 ? 00:00
:48 ora_dia0_cdb1
Oracle 1816 1 0 Aug02 ? 00:00:08 ora_dbw0_cdb1
Oracle 1818 1 0 Aug02 ? 00:00
:10 ora_
LGWR_cdb1
Oracle 1820 1 0 Aug02 ? 00:00
:18 ora_ckpt_cdb1
Oracle 1822 1 0 Aug02 ? 00:00:01 ora_smon_cdb1
Oracle 1824 1 0 Aug02 ? 00:00:04 ora_smco_cdb1
Oracle 1826 1 0 Aug02 ? 00:00:00 ora_reco_cdb1
Oracle 1830 1 0 Aug02 ? 00:00:03 ora_lreg_cdb1
Oracle 1834 1 0 Aug02 ? 00:00:01 ora_pxmn_cdb1
Oracle 1838 1 0 Aug02 ? 00:00:20 ora_mmon_cdb1
Oracle 1840 1 0 Aug02 ? 00:00:26 ora_mmnl_cdb1
Oracle 1842 1 0 Aug02 ? 00:00:00 ora_d000_cdb1
Oracle 1844 1 0 Aug02 ? 00:00:00 ora_s000_cdb1
Oracle 1846 1 0 Aug02 ? 00:00:00 ora_tmon_cdb1
Oracle 1869 1 0 Aug02 ? 00:00:00 ora_tt00_cdb1
Oracle 1871 1 0 Aug02 ? 00:00:00 ora_tt01_cdb1
Oracle 1873 1 0 Aug02 ? 00:00:02 ora_tt02_cdb1
Oracle 1875 1 0 Aug02 ? 00:00:00 ora_aqpc_cdb1
Oracle 1879 1 0 Aug02 ? 00:00:02 ora_p000_cdb1
Oracle 1881 1 0 Aug02 ? 00:00:02 ora_p001_cdb1
Oracle 1883 1 0 Aug02 ? 00:00:02 ora_p002_cdb1
Oracle 1885 1 0 Aug02 ? 00:00:02 ora_p003_cdb1
Oracle 2039 1 0 Aug02 ? 00:02:36 ora_cjq0_cdb1
Oracle 2109 1 0 Aug02 ? 00:00:01 ora_qm02_cdb1
Oracle 2113 1 0 Aug02 ? 00:00:00 ora_q002_cdb1
Oracle 2120 1 0 Aug02 ? 00:00:02 ora_q005_cdb1
Oracle 24076 1 0 15:56 ? 00:00:00 ora_w000_cdb1
Oracle 24149 1 0 15:56 ? 00:00:00 ora_q003_cdb1
Oracle 24154 1 0 15:56 ? 00:00:00 ora_q004_cdb1
Oracle 24161 1 0 15:56 ? 00:00:00 ora_q006_cdb1
Oracle 24165 1 0 15:56 ? 00:00:00 ora_w001_cdb1
Oracle 24317 1 0 15:57 ? 00:00:00 ora_w002_cdb1
Oracle 24422 24421 0 15:57 ? 00:00:00
Oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root 2
4504 24458 0 15:58 pts/1 00:00:00 grep cdb1
Oracle 28778 1 0 Aug02 ? 00:00:01 ora_q001_cdb1
Oracle
29034
29033 0 Aug02 ? 00:00:00
Oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
@H_
618_0@
675_3@2.修改参数重启数据库
ALTER SYSTEM SET threaded_execution = true SCOPE = SPFILE;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
OracLE instance shut down.
[
Oracle@ol6 ~]$
sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:59
:47 2018
Copyright (
C) 1982,
Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ERROR:
ORA-01017: invalid username/
password; logon denied
@H_
618_0@当
重启数据库的时候会遇到一些障碍,这里居然报无效的
用户名和密码。这
是因为我们修改了线程模式导致的,在这个时候我们需要通过先
sqlplus /nolog,然后在conn的方式进行连接。
conn sys as sysdba
Enter
password:
Connected.
SQL> shutdown immediate;
ORA-01507: database not mounted
OracLE instance shut down.
SQL> startup
OracLE instance started.
@R_253_1
0586@l System Global Area 629145600 bytes
Fixed Size 8795760 bytes
Variable Size 322963856 bytes
Database Buffers 293601280 bytes
Redo Buffers 3784704 bytes
Database mounted.
Database opened.
SQL> show parameter threaded
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
threaded_execution
Boolean TRUE
@H_
618_0@
675_3@3.修改完参数之后查看进程状态。
@H_
618_0@后台进程的数量减少了。一些后台进程(pmon,dbw,
LGWR,psp,vktm)的行为与以前一样。奇怪的是居然smon也没了。其他
BACkgtound进程属于名为ora_uxxx_ 的多线程进程。
Oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1
Oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1
Oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1
Oracle 25244 1 0 16:03 ? 00:00:00 ora_vktm_cdb1
Oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1
Oracle 25255 1 21 16:03 ? 00:00
:14 ora_u006_cdb1
Oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1
Oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1
Oracle 25270 1 0 16:03 ? 00:00:00 ora_
LGWR_cdb1
root 2562
9 24458 0 16:04 pts/1 00:00:00 grep cdb1
@H_
618_0@
675_3@4.通过系统视图查看进程状态。
@H_
618_0@这里我们可以发现execution_type,一部分已经变成了THREAD,我们的SMON也变成了THREAD状态。
SELEct spid,stid,pname,program,execution_type from
v$process order by execution_type,spid,stid;
@H_
618_0@SPID STID PNAME PROGRAM EXECUTION_
PSEUDO NONE
@H_
618_0@25236 25236 PMON
Oracle@ol6.localdomain (PMON) PROCESS
25242 25242 PSP0
Oracle@ol6.localdomain (PSP0) PROCESS
25244 25244 VKTM
Oracle@ol6.localdomain (VKTM) PROCESS
25268 25268 DBW0
Oracle@ol6.localdomain (DBW0) PROCESS
25238 25238 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25238 25240 CLMN
Oracle@ol6.localdomain (CLMN) THREAD
25238 25247 GEN0
Oracle@ol6.localdomain (GEN0) THREAD
25238 25248 MMAN
Oracle@ol6.localdomain (MMAN) THREAD
25238 25
262 DBRM
Oracle@ol6.localdomain (DBRM) THREAD
25238 25265 PMAN
Oracle@ol6.localdomain (PMAN) THREAD
25238 25273 CKPT
Oracle@ol6.localdomain (CKPT) THREAD
25238 25274 SMON
Oracle@ol6.localdomain (SMON) THREAD
25238 25278 LREG
Oracle@ol6.localdomain (LREG) THREAD
25251 25251 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25251 25253 GEN1
Oracle@ol6.localdomain (GEN1) THREAD
25255 25255 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25255 25257 DIAG
Oracle@ol6.localdomain (DIAG) THREAD
25255 25263 VKRM
Oracle@ol6.localdomain (VKRM) THREAD
25255 25264 SVCB
Oracle@ol6.localdomain (SVCB) THREAD
25255 25266 DIA0
Oracle@ol6.localdomain (DIA0) THREAD
25255 25275 SMCO
Oracle@ol6.localdomain (SMCO) THREAD
25255 25276 RECO
Oracle@ol6.localdomain (RECO) THREAD
25255 25277 W000
Oracle@ol6.localdomain (W000) THREAD
25255 25279 W001
Oracle@ol6.localdomain (W001) THREAD
25255 25280 PXMN
Oracle@ol6.localdomain (PXMN) THREAD
25255 25282 MMON
Oracle@ol6.localdomain (MMON) THREAD
25255 25283 MMNL
Oracle@ol6.localdomain (MMNL) THREAD
25255 25284 D000
Oracle@ol6.localdomain (D000) THREAD
25255 25285 S000
Oracle@ol6.localdomain (S000) THREAD
25255 25286 TMON
Oracle@ol6.localdomain (TMON) THREAD
25255 25287 N000
Oracle@ol6.localdomain (N000) THREAD
25255 25296
Oracle@ol6.localdomain THREAD
25255 25297
Oracle@ol6.localdomain THREAD
25255 25298
Oracle@ol6.localdomain THREAD
25255 25299
Oracle@ol6.localdomain THREAD
25255 25300
Oracle@ol6.localdomain THREAD
25255 25301
Oracle@ol6.localdomain THREAD
25255 25302
Oracle@ol6.localdomain THREAD
25255 25303
Oracle@ol6.localdomain THREAD
25255 25304
Oracle@ol6.localdomain THREAD
25255 25305
Oracle@ol6.localdomain THREAD
25255 25306
Oracle@ol6.localdomain THREAD
25255 25307
Oracle@ol6.localdomain THREAD
25255 25308
Oracle@ol6.localdomain THREAD
25255 25309
Oracle@ol6.localdomain THREAD
25255 25310
Oracle@ol6.localdomain THREAD
25255 25311
Oracle@ol6.localdomain THREAD
25255 25312
Oracle@ol6.localdomain THREAD
25255 25313
Oracle@ol6.localdomain THREAD
25255 25314
Oracle@ol6.localdomain THREAD
25255 25315
Oracle@ol6.localdomain THREAD
25255 25319 TT00
Oracle@ol6.localdomain (TT00) THREAD
25255 25320 TT01
Oracle@ol6.localdomain (TT01) THREAD
25255 25321 TT02
Oracle@ol6.localdomain (TT02) THREAD
25255 25330
Oracle@ol6.localdomain THREAD
25255 25331
Oracle@ol6.localdomain THREAD
25255 25332
Oracle@ol6.localdomain THREAD
25255 25333
Oracle@ol6.localdomain THREAD
25255 25334
Oracle@ol6.localdomain THREAD
25255 25335
Oracle@ol6.localdomain THREAD
25255 25336
Oracle@ol6.localdomain THREAD
25255 25337
Oracle@ol6.localdomain THREAD
25255 25338
Oracle@ol6.localdomain THREAD
25255 25339
Oracle@ol6.localdomain THREAD
25255 25340 AQPC
Oracle@ol6.localdomain (AQP
C) THREAD
25255 25342 P000
Oracle@ol6.localdomain (P000) THREAD
25255 2
5343 P001
Oracle@ol6.localdomain (P001) THREAD
25255 25344 P002
Oracle@ol6.localdomain (P002) THREAD
25255 25345 P003
Oracle@ol6.localdomain (P003) THREAD
25255 25491 CJQ0
Oracle@ol6.localdomain (CJQ0) THREAD
25255 25528 QM02
Oracle@ol6.localdomain (QM02) THREAD
25255 25530 Q002
Oracle@ol6.localdomain (Q002) THREAD
25255 25531 Q003
Oracle@ol6.localdomain (Q003) THREAD
25255 25532 Q004
Oracle@ol6.localdomain (Q004) THREAD
25255 25533 Q005
Oracle@ol6.localdomain (Q005) THREAD
25255 25534 Q006
Oracle@ol6.localdomain (Q006) THREAD
25255 25535 Q007
Oracle@ol6.localdomain (Q007) THREAD
25255 25536 Q008
Oracle@ol6.localdomain (Q008) THREAD
25255 25633 W002
Oracle@ol6.localdomain (W002) THREAD
25259 25259 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25259 25261 OFSD
Oracle@ol6.localdomain (OFSD) THREAD
25270 25270 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25270 25272
LGWR Oracle@ol6.localdomain (
LGWR) THREAD
@H_
618_0@
675_3@5.通过监听连接,可以看到仍然是进程模式。
connect sys/
Oracle as sysdba
Connected.
SQL>
SQL> connect sys/
Oracle@pdb as sysdba
Connected.
[root@ol6 ~]# ps -ef | grep cdb1
Oracle 25236 1 0 16:03 ? 00:00:00 ora_pmon_cdb1
Oracle 25238 1 0 16:03 ? 00:00:00 ora_u002_cdb1
Oracle 25242 1 0 16:03 ? 00:00:00 ora_psp0_cdb1
Oracle 25244 1 0 16:03 ? 00:00:06 ora_vktm_cdb1
Oracle 25251 1 0 16:03 ? 00:00:00 ora_gen1_cdb1
Oracle 25255 1 2 16:03 ? 00:00
:17 ora_u006_cdb1
Oracle 25259 1 0 16:03 ? 00:00:00 ora_ofsd_cdb1
Oracle 25268 1 0 16:03 ? 00:00:00 ora_dbw0_cdb1
Oracle 25270 1 0 16:03 ? 00:00:00 ora_
LGWR_cdb1
Oracle 27220 1 0 16
:15 ? 00:00:00
Oraclecdb1 (LOCAL=NO)
root 27270 24458 0 16
:15 pts/1 00:00:00 grep cdb1
@H_
618_0@
675_3@6.如果要通过监听的方式连接变成线程模式,需要修改监听的参数DEDicATED_THROUGH_BROKER_listener-name,并重启监听才行。
Oracle@ol6 admin]$ vi
listener.ora
#
listener.ora Network Configuration File: /u01/app/
Oracle/product/12.2.0.1/db_1/network/admin/
listener.ora
# Generated by
Oracle configuration tool
s.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TC
p)(HOST = ol6.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IP
C)(KEY = EXTPROC1521))
)
)
DE
DicATED_THROUGH_BROKER_listener-name=ON
@H_
618_0@
675_3@7.再次通过监听连接到数据库,发现新连接上的会话已经变成了线程模式。
SELEct spid from
v$process where addr in
(SELEct
paddr from
v$session where sid=28
);
SPID
------------------------
25255
SQL> /
SPID STID PNAME PROGRAM EXECUTION_
------------------------ ------------------------ ----- ------------------------------------------------ ----------
PSEUDO NONE
25236 25236 PMON
Oracle@ol6.localdomain (PMON) PROCESS
25242 25242 PSP0
Oracle@ol6.localdomain (PSP0) PROCESS
25244 25244 VKTM
Oracle@ol6.localdomain (VKTM) PROCESS
25268 25268 DBW0
Oracle@ol6.localdomain (DBW0) PROCESS
27220 27220
Oracle@ol6.localdomain PROCESS
25238 25238 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25238 25240 CLMN
Oracle@ol6.localdomain (CLMN) THREAD
25238 25247 GEN0
Oracle@ol6.localdomain (GEN0) THREAD
25238 25248 MMAN
Oracle@ol6.localdomain (MMAN) THREAD
25238 25
262 DBRM
Oracle@ol6.localdomain (DBRM) THREAD
25238 25265 PMAN
Oracle@ol6.localdomain (PMAN) THREAD
25238 25273 CKPT
Oracle@ol6.localdomain (CKPT) THREAD
25238 25274 SMON
Oracle@ol6.localdomain (SMON) THREAD
25238 25278 LREG
Oracle@ol6.localdomain (LREG) THREAD
25251 25251 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25251 25253 GEN1
Oracle@ol6.localdomain (GEN1) THREAD
25255 25255 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25255 25257 DIAG
Oracle@ol6.localdomain (DIAG) THREAD
25255 25263 VKRM
Oracle@ol6.localdomain (VKRM) THREAD
25255 25264 SVCB
Oracle@ol6.localdomain (SVCB) THREAD
25255 25266 DIA0
Oracle@ol6.localdomain (DIA0) THREAD
25255 25275 SMCO
Oracle@ol6.localdomain (SMCO) THREAD
25255 25276 RECO
Oracle@ol6.localdomain (RECO) THREAD
25255 25280 PXMN
Oracle@ol6.localdomain (PXMN) THREAD
25255 25282 MMON
Oracle@ol6.localdomain (MMON) THREAD
25255 25283 MMNL
Oracle@ol6.localdomain (MMNL) THREAD
25255 25284 D000
Oracle@ol6.localdomain (D000) THREAD
25255 25285 S000
Oracle@ol6.localdomain (S000) THREAD
25255 25286 TMON
Oracle@ol6.localdomain (TMON) THREAD
25255 25287 N000
Oracle@ol6.localdomain (N000) THREAD
25255 25296
Oracle@ol6.localdomain THREAD
25255 25297
Oracle@ol6.localdomain THREAD
25255 25300
Oracle@ol6.localdomain THREAD
25255 25301
Oracle@ol6.localdomain THREAD
25255 25302
Oracle@ol6.localdomain THREAD
25255 25304
Oracle@ol6.localdomain THREAD
25255 25306
Oracle@ol6.localdomain THREAD
25255 25307
Oracle@ol6.localdomain THREAD
25255 25308
Oracle@ol6.localdomain THREAD
25255 25309
Oracle@ol6.localdomain THREAD
25255 25310
Oracle@ol6.localdomain THREAD
25255 25311
Oracle@ol6.localdomain THREAD
25255 25314
Oracle@ol6.localdomain THREAD
25255 25315
Oracle@ol6.localdomain THREAD
25255 25319 TT00
Oracle@ol6.localdomain (TT00) THREAD
25255 25320 TT01
Oracle@ol6.localdomain (TT01) THREAD
25255 25321 TT02
Oracle@ol6.localdomain (TT02) THREAD
25255 25330
Oracle@ol6.localdomain THREAD
25255 25331
Oracle@ol6.localdomain THREAD
25255 25332
Oracle@ol6.localdomain THREAD
25255 25333
Oracle@ol6.localdomain THREAD
25255 25334
Oracle@ol6.localdomain THREAD
25255 25336
Oracle@ol6.localdomain THREAD
25255 25337
Oracle@ol6.localdomain THREAD
25255 25338
Oracle@ol6.localdomain THREAD
25255 25339
Oracle@ol6.localdomain THREAD
25255 25340 AQPC
Oracle@ol6.localdomain (AQP
C) THREAD
25255 25342 P000
Oracle@ol6.localdomain (P000) THREAD
25255 2
5343 P001
Oracle@ol6.localdomain (P001) THREAD
25255 25344 P002
Oracle@ol6.localdomain (P002) THREAD
25255 25345 P003
Oracle@ol6.localdomain (P003) THREAD
25255 25491 CJQ0
Oracle@ol6.localdomain (CJQ0) THREAD
25255 25528 QM02
Oracle@ol6.localdomain (QM02) THREAD
25255 25530 Q002
Oracle@ol6.localdomain (Q002) THREAD
25255 25533 Q005
Oracle@ol6.localdomain (Q005) THREAD
25255 25535 Q007
Oracle@ol6.localdomain (Q007) THREAD
25255
26267 W003
Oracle@ol6.localdomain (W003) THREAD
25255 26842 W004
Oracle@ol6.localdomain (W004) THREAD
25255 27011 W005
Oracle@ol6.localdomain (W005) THREAD
25255 27239 W006
Oracle@ol6.localdomain (W006) THREAD
25259 25259 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25259 25261 OFSD
Oracle@ol6.localdomain (OFSD) THREAD
25270 25270 SCMN
Oracle@ol6.localdomain (SCMN) THREAD
25270 25272
LGWR Oracle@ol6.localdomain (
LGWR) THREAD
@H_
618_0@当然
需要注意的一点是,如果在AIX上使用线程模式,
需要安装补丁BUG 22
226365 – THREADED_EXECUTION=TRUE – SCMN PROCESS RES MEMORY INCREASES。
@H_
618_0@
675_3@总结
@H_
618_0@以上所述是小编给大家介绍的
Oracle 12c 新特性之多线程数据库操作,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对菜鸟教程网站的支持!
大佬总结
以上是大佬教程为你收集整理的Oracle 12c 新特性之多线程数据库操作全部内容,希望文章能够帮你解决Oracle 12c 新特性之多线程数据库操作所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。