Oracle 12c 新特性之多线程数据库操作

数据库  /  管理员 发布于 6年前   252

之前我们学习Oracle基础的时候，有一个概念，叫多进程和多线程。在Unix/Linux等环境下面。数据库是以多进程的方式运行的，当一个会话连接进来，就会通过监听，然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c这个版本上面，Oracle在Unix/Linux平台上做出了一些改变，引入了多线程的方式。通过参数threaded_execution，我们可以控制数据库是以多进程方式运行还是以多线程方式运行，默认该参数是false数据库以多进程方式运行。

1.查看参数的默认值，和数据库进程状态.

[oracle@ol6 ~]$ sqlplus / as sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:57:59 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to:Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit ProductionSQL> show parameter threaded NAME     TYPE    VALUE------------------------------------ ----------- ------------------------------threaded_execution          boolean   FALSE[root@ol6 ~]# ps -ef | grep cdb1oracle  1773   1 0 Aug02 ?    00:00:04 ora_pmon_cdb1oracle  1775   1 0 Aug02 ?    00:00:01 ora_clmn_cdb1oracle  1777   1 0 Aug02 ?    00:00:13 ora_psp0_cdb1oracle  1786   1 0 Aug02 ?    00:17:01 ora_vktm_cdb1oracle  1790   1 0 Aug02 ?    00:00:08 ora_gen0_cdb1oracle  1792   1 0 Aug02 ?    00:00:01 ora_mman_cdb1oracle  1796   1 0 Aug02 ?    00:00:19 ora_gen1_cdb1oracle  1800   1 0 Aug02 ?    00:00:03 ora_diag_cdb1oracle  1802   1 0 Aug02 ?    00:00:01 ora_ofsd_cdb1oracle  1806   1 0 Aug02 ?    00:00:29 ora_dbrm_cdb1oracle  1808   1 0 Aug02 ?    00:01:14 ora_vkrm_cdb1oracle  1810   1 0 Aug02 ?    00:00:03 ora_svcb_cdb1oracle  1812   1 0 Aug02 ?    00:00:10 ora_pman_cdb1oracle  1814   1 0 Aug02 ?    00:00:48 ora_dia0_cdb1oracle  1816   1 0 Aug02 ?    00:00:08 ora_dbw0_cdb1oracle  1818   1 0 Aug02 ?    00:00:10 ora_lgwr_cdb1oracle  1820   1 0 Aug02 ?    00:00:18 ora_ckpt_cdb1oracle  1822   1 0 Aug02 ?    00:00:01 ora_smon_cdb1oracle  1824   1 0 Aug02 ?    00:00:04 ora_smco_cdb1oracle  1826   1 0 Aug02 ?    00:00:00 ora_reco_cdb1oracle  1830   1 0 Aug02 ?    00:00:03 ora_lreg_cdb1oracle  1834   1 0 Aug02 ?    00:00:01 ora_pxmn_cdb1oracle  1838   1 0 Aug02 ?    00:00:20 ora_mmon_cdb1oracle  1840   1 0 Aug02 ?    00:00:26 ora_mmnl_cdb1oracle  1842   1 0 Aug02 ?    00:00:00 ora_d000_cdb1oracle  1844   1 0 Aug02 ?    00:00:00 ora_s000_cdb1oracle  1846   1 0 Aug02 ?    00:00:00 ora_tmon_cdb1oracle  1869   1 0 Aug02 ?    00:00:00 ora_tt00_cdb1oracle  1871   1 0 Aug02 ?    00:00:00 ora_tt01_cdb1oracle  1873   1 0 Aug02 ?    00:00:02 ora_tt02_cdb1oracle  1875   1 0 Aug02 ?    00:00:00 ora_aqpc_cdb1oracle  1879   1 0 Aug02 ?    00:00:02 ora_p000_cdb1oracle  1881   1 0 Aug02 ?    00:00:02 ora_p001_cdb1oracle  1883   1 0 Aug02 ?    00:00:02 ora_p002_cdb1oracle  1885   1 0 Aug02 ?    00:00:02 ora_p003_cdb1oracle  2039   1 0 Aug02 ?    00:02:36 ora_cjq0_cdb1oracle  2109   1 0 Aug02 ?    00:00:01 ora_qm02_cdb1oracle  2113   1 0 Aug02 ?    00:00:00 ora_q002_cdb1oracle  2120   1 0 Aug02 ?    00:00:02 ora_q005_cdb1oracle  24076   1 0 15:56 ?    00:00:00 ora_w000_cdb1oracle  24149   1 0 15:56 ?    00:00:00 ora_q003_cdb1oracle  24154   1 0 15:56 ?    00:00:00 ora_q004_cdb1oracle  24161   1 0 15:56 ?    00:00:00 ora_q006_cdb1oracle  24165   1 0 15:56 ?    00:00:00 ora_w001_cdb1oracle  24317   1 0 15:57 ?    00:00:00 ora_w002_cdb1oracle  24422 24421 0 15:57 ?    00:00:00 oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))root   24504 24458 0 15:58 pts/1  00:00:00 grep cdb1oracle  28778   1 0 Aug02 ?    00:00:01 ora_q001_cdb1oracle  29034 29033 0 Aug02 ?    00:00:00 oraclecdb1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

2.修改参数重启数据库

SQL> 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 sysdbaSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 15:59:47 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.Connected to an idle instance.SQL> startupERROR:ORA-01017: invalid username/password; logon denied

当重启数据库的时候会遇到一些障碍，这里居然报无效的用户名和密码。这是因为我们修改了线程模式导致的，在这个时候我们需要通过先sqlplus /nolog，然后在conn的方式进行连接。

[oracle@ol6 ~]$ sqlplus /nologSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:02:15 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.SQL> conn sys as sysdbaEnter password: Connected.SQL> shutdown immediate; ORA-01507: database not mountedORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 629145600 bytesFixed Size         8795760 bytesVariable Size       322963856 bytesDatabase Buffers     293601280 bytesRedo Buffers        3784704 bytesDatabase mounted.Database opened.SQL> show parameter threadedNAME     TYPE    VALUE------------------------------------ ----------- ------------------------------threaded_execution          boolean   TRUE

3.修改完参数之后查看进程状态。

后台进程的数量减少了。一些后台进程（pmon，dbw，lgwr，psp，vktm）的行为与以前一样。奇怪的是居然smon也没了。其他backgtound进程属于名为ora_uxxx_ 的多线程进程。

[root@ol6 ~]# ps -ef | grep cdb1oracle  25236   1 0 16:03 ?    00:00:00 ora_pmon_cdb1oracle  25238   1 0 16:03 ?    00:00:00 ora_u002_cdb1oracle  25242   1 0 16:03 ?    00:00:00 ora_psp0_cdb1oracle  25244   1 0 16:03 ?    00:00:00 ora_vktm_cdb1oracle  25251   1 0 16:03 ?    00:00:00 ora_gen1_cdb1oracle  25255   1 21 16:03 ?    00:00:14 ora_u006_cdb1oracle  25259   1 0 16:03 ?    00:00:00 ora_ofsd_cdb1oracle  25268   1 0 16:03 ?    00:00:00 ora_dbw0_cdb1oracle  25270   1 0 16:03 ?    00:00:00 ora_lgwr_cdb1root   25629 24458 0 16:04 pts/1  00:00:00 grep cdb1

4.通过系统视图查看进程状态。

这里我们可以发现execution_type,一部分已经变成了THREAD,我们的SMON也变成了THREAD状态。

SQL> select spid, stid, pname, program, execution_type from v$process order by execution_type, spid, stid;SPID           STID           PNAME PROGRAM         EXECUTION_------------------------ ------------------------ ----- ------------------------------------------------ ----------    PSEUDO          NONE25236          25236          PMON oracle@ol6.localdomain (PMON)          PROCESS25242          25242          PSP0 oracle@ol6.localdomain (PSP0)          PROCESS25244          25244          VKTM oracle@ol6.localdomain (VKTM)          PROCESS25268          25268          DBW0 oracle@ol6.localdomain (DBW0)          PROCESS25238          25238          SCMN oracle@ol6.localdomain (SCMN)          THREAD25238          25240          CLMN oracle@ol6.localdomain (CLMN)          THREAD25238          25247          GEN0 oracle@ol6.localdomain (GEN0)          THREAD25238          25248          MMAN oracle@ol6.localdomain (MMAN)          THREAD25238          25262          DBRM oracle@ol6.localdomain (DBRM)          THREAD25238          25265          PMAN oracle@ol6.localdomain (PMAN)          THREAD25238          25273          CKPT oracle@ol6.localdomain (CKPT)          THREAD25238          25274          SMON oracle@ol6.localdomain (SMON)          THREAD25238          25278          LREG oracle@ol6.localdomain (LREG)          THREAD25251          25251          SCMN oracle@ol6.localdomain (SCMN)          THREAD25251          25253          GEN1 oracle@ol6.localdomain (GEN1)          THREAD25255          25255          SCMN oracle@ol6.localdomain (SCMN)          THREAD25255          25257          DIAG oracle@ol6.localdomain (DIAG)          THREAD25255          25263          VKRM oracle@ol6.localdomain (VKRM)          THREAD25255          25264          SVCB oracle@ol6.localdomain (SVCB)          THREAD25255          25266          DIA0 oracle@ol6.localdomain (DIA0)          THREAD25255          25275          SMCO oracle@ol6.localdomain (SMCO)          THREAD25255          25276          RECO oracle@ol6.localdomain (RECO)          THREAD25255          25277          W000 oracle@ol6.localdomain (W000)          THREAD25255          25279          W001 oracle@ol6.localdomain (W001)          THREAD25255          25280          PXMN oracle@ol6.localdomain (PXMN)          THREAD25255          25282          MMON oracle@ol6.localdomain (MMON)          THREAD25255          25283          MMNL oracle@ol6.localdomain (MMNL)          THREAD25255          25284          D000 oracle@ol6.localdomain (D000)          THREAD25255          25285          S000 oracle@ol6.localdomain (S000)          THREAD25255          25286          TMON oracle@ol6.localdomain (TMON)          THREAD25255          25287          N000 oracle@ol6.localdomain (N000)          THREAD25255          25296 oracle@ol6.localdomain  THREAD25255          25297 oracle@ol6.localdomain  THREAD25255          25298 oracle@ol6.localdomain  THREAD25255          25299 oracle@ol6.localdomain  THREAD25255          25300 oracle@ol6.localdomain  THREAD25255          25301 oracle@ol6.localdomain  THREAD25255          25302 oracle@ol6.localdomain  THREAD25255          25303 oracle@ol6.localdomain  THREAD25255          25304 oracle@ol6.localdomain  THREAD25255          25305 oracle@ol6.localdomain  THREAD25255          25306 oracle@ol6.localdomain  THREAD25255          25307 oracle@ol6.localdomain  THREAD25255          25308 oracle@ol6.localdomain  THREAD25255          25309 oracle@ol6.localdomain  THREAD25255          25310 oracle@ol6.localdomain  THREAD25255          25311 oracle@ol6.localdomain  THREAD25255          25312 oracle@ol6.localdomain  THREAD25255          25313 oracle@ol6.localdomain  THREAD25255          25314 oracle@ol6.localdomain  THREAD25255          25315 oracle@ol6.localdomain  THREAD25255          25319          TT00 oracle@ol6.localdomain (TT00)          THREAD25255          25320          TT01 oracle@ol6.localdomain (TT01)          THREAD25255          25321          TT02 oracle@ol6.localdomain (TT02)          THREAD25255          25330 oracle@ol6.localdomain  THREAD25255          25331 oracle@ol6.localdomain  THREAD25255          25332 oracle@ol6.localdomain  THREAD25255          25333 oracle@ol6.localdomain  THREAD25255          25334 oracle@ol6.localdomain  THREAD25255          25335 oracle@ol6.localdomain  THREAD25255          25336 oracle@ol6.localdomain  THREAD25255          25337 oracle@ol6.localdomain  THREAD25255          25338 oracle@ol6.localdomain  THREAD25255          25339 oracle@ol6.localdomain  THREAD25255          25340          AQPC oracle@ol6.localdomain (AQPC)          THREAD25255          25342          P000 oracle@ol6.localdomain (P000)          THREAD25255          25343          P001 oracle@ol6.localdomain (P001)          THREAD25255          25344          P002 oracle@ol6.localdomain (P002)          THREAD25255          25345          P003 oracle@ol6.localdomain (P003)          THREAD25255          25491          CJQ0 oracle@ol6.localdomain (CJQ0)          THREAD25255          25528          QM02 oracle@ol6.localdomain (QM02)          THREAD25255          25530          Q002 oracle@ol6.localdomain (Q002)          THREAD25255          25531          Q003 oracle@ol6.localdomain (Q003)          THREAD25255          25532          Q004 oracle@ol6.localdomain (Q004)          THREAD25255          25533          Q005 oracle@ol6.localdomain (Q005)          THREAD25255          25534          Q006 oracle@ol6.localdomain (Q006)          THREAD25255          25535          Q007 oracle@ol6.localdomain (Q007)          THREAD25255          25536          Q008 oracle@ol6.localdomain (Q008)          THREAD25255          25633          W002 oracle@ol6.localdomain (W002)          THREAD25259          25259          SCMN oracle@ol6.localdomain (SCMN)          THREAD25259          25261          OFSD oracle@ol6.localdomain (OFSD)          THREAD25270          25270          SCMN oracle@ol6.localdomain (SCMN)          THREAD25270          25272          LGWR oracle@ol6.localdomain (LGWR)          THREAD

5.通过监听连接，可以看到仍然是进程模式。

[oracle@ol6 ~]$ sqlplus /nologSQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:14:48 2018Copyright (c) 1982, 2016, Oracle. All rights reserved.SQL> connect sys/oracle as sysdbaConnected.SQL> SQL> connect sys/oracle@pdb as sysdbaConnected.[root@ol6 ~]# ps -ef | grep cdb1oracle  25236   1 0 16:03 ?    00:00:00 ora_pmon_cdb1oracle  25238   1 0 16:03 ?    00:00:00 ora_u002_cdb1oracle  25242   1 0 16:03 ?    00:00:00 ora_psp0_cdb1oracle  25244   1 0 16:03 ?    00:00:06 ora_vktm_cdb1oracle  25251   1 0 16:03 ?    00:00:00 ora_gen1_cdb1oracle  25255   1 2 16:03 ?    00:00:17 ora_u006_cdb1oracle  25259   1 0 16:03 ?    00:00:00 ora_ofsd_cdb1oracle  25268   1 0 16:03 ?    00:00:00 ora_dbw0_cdb1oracle  25270   1 0 16:03 ?    00:00:00 ora_lgwr_cdb1oracle  27220   1 0 16:15 ?    00:00:00 oraclecdb1 (LOCAL=NO)root   27270 24458 0 16:15 pts/1  00:00:00 grep cdb1

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 tools.LISTENER = (DESCRIPTION_LIST =  (DESCRIPTION =   (ADDRESS = (PROTOCOL = TCP)(HOST = ol6.localdomain)(PORT = 1521))   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  ) )DEDICATED_THROUGH_BROKER_listener-name=ON

7.再次通过监听连接到数据库,发现新连接上的会话已经变成了线程模式。

SQL> select spid from v$process where addr in (select paddr from v$session where sid=28);SPID------------------------25255SQL> /SPID           STID           PNAME PROGRAM         EXECUTION_------------------------ ------------------------ ----- ------------------------------------------------ ----------    PSEUDO          NONE25236          25236          PMON oracle@ol6.localdomain (PMON)          PROCESS25242          25242          PSP0 oracle@ol6.localdomain (PSP0)          PROCESS25244          25244          VKTM oracle@ol6.localdomain (VKTM)          PROCESS25268          25268          DBW0 oracle@ol6.localdomain (DBW0)          PROCESS27220          27220 oracle@ol6.localdomain  PROCESS25238          25238          SCMN oracle@ol6.localdomain (SCMN)          THREAD25238          25240          CLMN oracle@ol6.localdomain (CLMN)          THREAD25238          25247          GEN0 oracle@ol6.localdomain (GEN0)          THREAD25238          25248          MMAN oracle@ol6.localdomain (MMAN)          THREAD25238          25262          DBRM oracle@ol6.localdomain (DBRM)          THREAD25238          25265          PMAN oracle@ol6.localdomain (PMAN)          THREAD25238          25273          CKPT oracle@ol6.localdomain (CKPT)          THREAD25238          25274          SMON oracle@ol6.localdomain (SMON)          THREAD25238          25278          LREG oracle@ol6.localdomain (LREG)          THREAD25251          25251          SCMN oracle@ol6.localdomain (SCMN)          THREAD25251          25253          GEN1 oracle@ol6.localdomain (GEN1)          THREAD25255          25255          SCMN oracle@ol6.localdomain (SCMN)          THREAD25255          25257          DIAG oracle@ol6.localdomain (DIAG)          THREAD25255          25263          VKRM oracle@ol6.localdomain (VKRM)          THREAD25255          25264          SVCB oracle@ol6.localdomain (SVCB)          THREAD25255          25266          DIA0 oracle@ol6.localdomain (DIA0)          THREAD25255          25275          SMCO oracle@ol6.localdomain (SMCO)          THREAD25255          25276          RECO oracle@ol6.localdomain (RECO)          THREAD25255          25280          PXMN oracle@ol6.localdomain (PXMN)          THREAD25255          25282          MMON oracle@ol6.localdomain (MMON)          THREAD25255          25283          MMNL oracle@ol6.localdomain (MMNL)          THREAD25255          25284          D000 oracle@ol6.localdomain (D000)          THREAD25255          25285          S000 oracle@ol6.localdomain (S000)          THREAD25255          25286          TMON oracle@ol6.localdomain (TMON)          THREAD25255          25287          N000 oracle@ol6.localdomain (N000)          THREAD25255          25296 oracle@ol6.localdomain  THREAD25255          25297 oracle@ol6.localdomain  THREAD25255          25300 oracle@ol6.localdomain  THREAD25255          25301 oracle@ol6.localdomain  THREAD25255          25302 oracle@ol6.localdomain  THREAD25255          25304 oracle@ol6.localdomain  THREAD25255          25306 oracle@ol6.localdomain  THREAD25255          25307 oracle@ol6.localdomain  THREAD25255          25308 oracle@ol6.localdomain  THREAD25255          25309 oracle@ol6.localdomain  THREAD25255          25310 oracle@ol6.localdomain  THREAD25255          25311 oracle@ol6.localdomain  THREAD25255          25314 oracle@ol6.localdomain  THREAD25255          25315 oracle@ol6.localdomain  THREAD25255          25319          TT00 oracle@ol6.localdomain (TT00)          THREAD25255          25320          TT01 oracle@ol6.localdomain (TT01)          THREAD25255          25321          TT02 oracle@ol6.localdomain (TT02)          THREAD25255          25330 oracle@ol6.localdomain  THREAD25255          25331 oracle@ol6.localdomain  THREAD25255          25332 oracle@ol6.localdomain  THREAD25255          25333 oracle@ol6.localdomain  THREAD25255          25334 oracle@ol6.localdomain  THREAD25255          25336 oracle@ol6.localdomain  THREAD25255          25337 oracle@ol6.localdomain  THREAD25255          25338 oracle@ol6.localdomain  THREAD25255          25339 oracle@ol6.localdomain  THREAD25255          25340          AQPC oracle@ol6.localdomain (AQPC)          THREAD25255          25342          P000 oracle@ol6.localdomain (P000)          THREAD25255          25343          P001 oracle@ol6.localdomain (P001)          THREAD25255          25344          P002 oracle@ol6.localdomain (P002)          THREAD25255          25345          P003 oracle@ol6.localdomain (P003)          THREAD25255          25491          CJQ0 oracle@ol6.localdomain (CJQ0)          THREAD25255          25528          QM02 oracle@ol6.localdomain (QM02)          THREAD25255          25530          Q002 oracle@ol6.localdomain (Q002)          THREAD25255          25533          Q005 oracle@ol6.localdomain (Q005)          THREAD25255          25535          Q007 oracle@ol6.localdomain (Q007)          THREAD25255          26267          W003 oracle@ol6.localdomain (W003)          THREAD25255          26842          W004 oracle@ol6.localdomain (W004)          THREAD25255          27011          W005 oracle@ol6.localdomain (W005)          THREAD25255          27239          W006 oracle@ol6.localdomain (W006)          THREAD25259          25259          SCMN oracle@ol6.localdomain (SCMN)          THREAD25259          25261          OFSD oracle@ol6.localdomain (OFSD)          THREAD25270          25270          SCMN oracle@ol6.localdomain (SCMN)          THREAD25270          25272          LGWR oracle@ol6.localdomain (LGWR)          THREAD

当然需要注意的一点是，如果在AIX上使用线程模式，需要安装补丁BUG 22226365 C THREADED_EXECUTION=TRUE C SCMN PROCESS RES MEMORY INCREASES。

总结

以上所述是小编给大家介绍的Oracle 12c 新特性之多线程数据库操作，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对站的支持！


  • 上一条：
    Oracle GoldenGate同步服务归档空间维护【推荐】
    下一条：
    oracle数据库实现获取时间戳的无参函数

    • 昵称：

    邮箱：

    0条评论 (评论内容有缓存机制,请悉知!)
    Top

    Copyright·© 2019 侯体宗版权所有· 粤ICP备20027696号 PHP交流群

    侯体宗的博客