Oracle 12c 新特性之多线程数据库操作
|
之前我们学习Oracle基础的时候，有一个概念，叫多进程和多线程。在Unix/Linux等环境下面。数据库是以多进程的方式运行的，当一个会话连接进来，就会通过监听，然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c这个版本上面，Oracle在Unix/Linux平台上做出了一些改变，引入了多线程的方式。通过参数threaded_execution，我们可以控制数据库是以多进程方式运行还是以多线程方式运行，默认该参数是false数据库以多进程方式运行。 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 24504 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)))
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当重启数据库的时候会遇到一些障碍，这里居然报无效的用户名和密码。这是因为我们修改了线程模式导致的，在这个时候我们需要通过先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.
Total 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
3.修改完参数之后查看进程状态。 后台进程的数量减少了。一些后台进程（pmon，dbw，lgwr，psp，vktm）的行为与以前一样。奇怪的是居然smon也没了。其他backgtound进程属于名为ora_uxxx_ 的多线程进程。 4.通过系统视图查看进程状态。 这里我们可以发现execution_type,一部分已经变成了THREAD,我们的SMON也变成了THREAD状态。 select spid,stid,pname,program,execution_type from v$process order by execution_type,spid,stid;SPID STID PNAME PROGRAM EXECUTION_ 25236 25236 PMON oracle@ol6.localdomain (PMON) PROCESS （编辑：大庆站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
