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

当前第2页 返回上一页

这里我们可以发现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                      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
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          25262          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 (AQPC)          THREAD
25255          25342          P000 oracle@ol6.localdomain (P000)          THREAD
25255          25343          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

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

[oracle@ol6 ~]$ sqlplus /nolog
SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 3 16:14:48 2018
Copyright (c) 1982, 2016, Oracle. All rights reserved.
SQL> 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

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
------------------------
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          25262          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 (AQPC)          THREAD
25255          25342          P000 oracle@ol6.localdomain (P000)          THREAD
25255          25343          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

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

总结

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

标签：Oracle

返回前面的内容

相关阅读 >>

随机获取oracle数据库中的任意一行数据(rownum)示例介绍

plsql登录oracle数据库报错ora-12154tns无法解析指定的连接标识符已解决（本地未安装oracle需要连接服务器上的）

oracle 安装和卸载问题收集（集合篇）第16页

oracle数据库rownum和row_number的不同点

oracle数据库的启动与关闭方法

oracle监听器服务不能启动的解决方法

oracle select执行顺序的详解

oracle 8i在p4上的安装

查看oracle执行计划的方法

oracle在plsql中嵌入sql语句

更多相关阅读请进入《oracle》频道 >>

书籍

数据库系统概念 第6版

￥41.1元   机械工业出版社

本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。

转载请注明出处：木庄网络博客 » Oracle 12c 新特性之多线程数据库操作