Oracle 12c 新特性之多线程数据库操作
之前我们学习Oracle基础的时候，有一个概念，叫多进程和多线程。在Unix/Linux等环境下面。数据库是以多进程的方式运行的，当一个会话连接进来，就会通过监听，然后在服务器上创建一个进程。而在Windows上面它是以多线程的方式来运行的。一个进程有很多个thread线程。而在12c这个版本上面，Oracle在Unix/Linux平台上做出了一些改变，引入了多线程的方式。通过参数threaded_execution，我们可以控制数据库是以多进程方式运行还是以多线程方式运行，默认该参数是false数据库以多进程方式运行。
1.查看参数的默认值，和数据库进程状态.
[oracle@ol6~]$sqlplus/assysdba SQL*Plus:Release12.2.0.1.0ProductiononFriAug315:57:592018 Copyright(c)1982,2016,Oracle.Allrightsreserved. Connectedto: OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProduction SQL>showparameterthreaded NAMETYPEVALUE ----------------------------------------------------------------------------- threaded_executionbooleanFALSE [root@ol6~]#ps-ef|grepcdb1 oracle177310Aug02?00:00:04ora_pmon_cdb1 oracle177510Aug02?00:00:01ora_clmn_cdb1 oracle177710Aug02?00:00:13ora_psp0_cdb1 oracle178610Aug02?00:17:01ora_vktm_cdb1 oracle179010Aug02?00:00:08ora_gen0_cdb1 oracle179210Aug02?00:00:01ora_mman_cdb1 oracle179610Aug02?00:00:19ora_gen1_cdb1 oracle180010Aug02?00:00:03ora_diag_cdb1 oracle180210Aug02?00:00:01ora_ofsd_cdb1 oracle180610Aug02?00:00:29ora_dbrm_cdb1 oracle180810Aug02?00:01:14ora_vkrm_cdb1 oracle181010Aug02?00:00:03ora_svcb_cdb1 oracle181210Aug02?00:00:10ora_pman_cdb1 oracle181410Aug02?00:00:48ora_dia0_cdb1 oracle181610Aug02?00:00:08ora_dbw0_cdb1 oracle181810Aug02?00:00:10ora_lgwr_cdb1 oracle182010Aug02?00:00:18ora_ckpt_cdb1 oracle182210Aug02?00:00:01ora_smon_cdb1 oracle182410Aug02?00:00:04ora_smco_cdb1 oracle182610Aug02?00:00:00ora_reco_cdb1 oracle183010Aug02?00:00:03ora_lreg_cdb1 oracle183410Aug02?00:00:01ora_pxmn_cdb1 oracle183810Aug02?00:00:20ora_mmon_cdb1 oracle184010Aug02?00:00:26ora_mmnl_cdb1 oracle184210Aug02?00:00:00ora_d000_cdb1 oracle184410Aug02?00:00:00ora_s000_cdb1 oracle184610Aug02?00:00:00ora_tmon_cdb1 oracle186910Aug02?00:00:00ora_tt00_cdb1 oracle187110Aug02?00:00:00ora_tt01_cdb1 oracle187310Aug02?00:00:02ora_tt02_cdb1 oracle187510Aug02?00:00:00ora_aqpc_cdb1 oracle187910Aug02?00:00:02ora_p000_cdb1 oracle188110Aug02?00:00:02ora_p001_cdb1 oracle188310Aug02?00:00:02ora_p002_cdb1 oracle188510Aug02?00:00:02ora_p003_cdb1 oracle203910Aug02?00:02:36ora_cjq0_cdb1 oracle210910Aug02?00:00:01ora_qm02_cdb1 oracle211310Aug02?00:00:00ora_q002_cdb1 oracle212010Aug02?00:00:02ora_q005_cdb1 oracle240761015:56?00:00:00ora_w000_cdb1 oracle241491015:56?00:00:00ora_q003_cdb1 oracle241541015:56?00:00:00ora_q004_cdb1 oracle241611015:56?00:00:00ora_q006_cdb1 oracle241651015:56?00:00:00ora_w001_cdb1 oracle243171015:57?00:00:00ora_w002_cdb1 oracle2442224421015:57?00:00:00oraclecdb1(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) root2450424458015:58pts/100:00:00grepcdb1 oracle2877810Aug02?00:00:01ora_q001_cdb1 oracle29034290330Aug02?00:00:00oraclecdb1(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
2.修改参数重启数据库
SQL>ALTERSYSTEMSETthreaded_execution=trueSCOPE=SPFILE; Systemaltered. SQL>shutdownimmediate; Databaseclosed. Databasedismounted. ORACLEinstanceshutdown. [oracle@ol6~]$sqlplus/assysdba SQL*Plus:Release12.2.0.1.0ProductiononFriAug315:59:472018 Copyright(c)1982,2016,Oracle.Allrightsreserved. Connectedtoanidleinstance. SQL>startup ERROR: ORA-01017:invalidusername/password;logondenied
当重启数据库的时候会遇到一些障碍，这里居然报无效的用户名和密码。这是因为我们修改了线程模式导致的，在这个时候我们需要通过先sqlplus/nolog，然后在conn的方式进行连接。
[oracle@ol6~]$sqlplus/nolog SQL*Plus:Release12.2.0.1.0ProductiononFriAug316:02:152018 Copyright(c)1982,2016,Oracle.Allrightsreserved. SQL>connsysassysdba Enterpassword: Connected. SQL>shutdownimmediate; ORA-01507:databasenotmounted ORACLEinstanceshutdown. SQL>startup ORACLEinstancestarted. TotalSystemGlobalArea629145600bytes FixedSize8795760bytes VariableSize322963856bytes DatabaseBuffers293601280bytes RedoBuffers3784704bytes Databasemounted. Databaseopened. SQL>showparameterthreaded NAMETYPEVALUE ----------------------------------------------------------------------------- threaded_executionbooleanTRUE
3.修改完参数之后查看进程状态。
后台进程的数量减少了。一些后台进程（pmon，dbw，lgwr，psp，vktm）的行为与以前一样。奇怪的是居然smon也没了。其他backgtound进程属于名为ora_uxxx_的多线程进程。
[root@ol6~]#ps-ef|grepcdb1 oracle252361016:03?00:00:00ora_pmon_cdb1 oracle252381016:03?00:00:00ora_u002_cdb1 oracle252421016:03?00:00:00ora_psp0_cdb1 oracle252441016:03?00:00:00ora_vktm_cdb1 oracle252511016:03?00:00:00ora_gen1_cdb1 oracle2525512116:03?00:00:14ora_u006_cdb1 oracle252591016:03?00:00:00ora_ofsd_cdb1 oracle252681016:03?00:00:00ora_dbw0_cdb1 oracle252701016:03?00:00:00ora_lgwr_cdb1 root2562924458016:04pts/100:00:00grepcdb1
4.通过系统视图查看进程状态。
这里我们可以发现execution_type,一部分已经变成了THREAD,我们的SMON也变成了THREAD状态。
SQL>selectspid,stid,pname,program,execution_typefromv$processorderbyexecution_type,spid,stid; SPIDSTIDPNAMEPROGRAMEXECUTION_ --------------------------------------------------------------------------------------------------------------- PSEUDONONE 2523625236PMONoracle@ol6.localdomain(PMON)PROCESS 2524225242PSP0oracle@ol6.localdomain(PSP0)PROCESS 2524425244VKTMoracle@ol6.localdomain(VKTM)PROCESS 2526825268DBW0oracle@ol6.localdomain(DBW0)PROCESS 2523825238SCMNoracle@ol6.localdomain(SCMN)THREAD 2523825240CLMNoracle@ol6.localdomain(CLMN)THREAD 2523825247GEN0oracle@ol6.localdomain(GEN0)THREAD 2523825248MMANoracle@ol6.localdomain(MMAN)THREAD 2523825262DBRMoracle@ol6.localdomain(DBRM)THREAD 2523825265PMANoracle@ol6.localdomain(PMAN)THREAD 2523825273CKPToracle@ol6.localdomain(CKPT)THREAD 2523825274SMONoracle@ol6.localdomain(SMON)THREAD 2523825278LREGoracle@ol6.localdomain(LREG)THREAD 2525125251SCMNoracle@ol6.localdomain(SCMN)THREAD 2525125253GEN1oracle@ol6.localdomain(GEN1)THREAD 2525525255SCMNoracle@ol6.localdomain(SCMN)THREAD 2525525257DIAGoracle@ol6.localdomain(DIAG)THREAD 2525525263VKRMoracle@ol6.localdomain(VKRM)THREAD 2525525264SVCBoracle@ol6.localdomain(SVCB)THREAD 2525525266DIA0oracle@ol6.localdomain(DIA0)THREAD 2525525275SMCOoracle@ol6.localdomain(SMCO)THREAD 2525525276RECOoracle@ol6.localdomain(RECO)THREAD 2525525277W000oracle@ol6.localdomain(W000)THREAD 2525525279W001oracle@ol6.localdomain(W001)THREAD 2525525280PXMNoracle@ol6.localdomain(PXMN)THREAD 2525525282MMONoracle@ol6.localdomain(MMON)THREAD 2525525283MMNLoracle@ol6.localdomain(MMNL)THREAD 2525525284D000oracle@ol6.localdomain(D000)THREAD 2525525285S000oracle@ol6.localdomain(S000)THREAD 2525525286TMONoracle@ol6.localdomain(TMON)THREAD 2525525287N000oracle@ol6.localdomain(N000)THREAD 2525525296oracle@ol6.localdomainTHREAD 2525525297oracle@ol6.localdomainTHREAD 2525525298oracle@ol6.localdomainTHREAD 2525525299oracle@ol6.localdomainTHREAD 2525525300oracle@ol6.localdomainTHREAD 2525525301oracle@ol6.localdomainTHREAD 2525525302oracle@ol6.localdomainTHREAD 2525525303oracle@ol6.localdomainTHREAD 2525525304oracle@ol6.localdomainTHREAD 2525525305oracle@ol6.localdomainTHREAD 2525525306oracle@ol6.localdomainTHREAD 2525525307oracle@ol6.localdomainTHREAD 2525525308oracle@ol6.localdomainTHREAD 2525525309oracle@ol6.localdomainTHREAD 2525525310oracle@ol6.localdomainTHREAD 2525525311oracle@ol6.localdomainTHREAD 2525525312oracle@ol6.localdomainTHREAD 2525525313oracle@ol6.localdomainTHREAD 2525525314oracle@ol6.localdomainTHREAD 2525525315oracle@ol6.localdomainTHREAD 2525525319TT00oracle@ol6.localdomain(TT00)THREAD 2525525320TT01oracle@ol6.localdomain(TT01)THREAD 2525525321TT02oracle@ol6.localdomain(TT02)THREAD 2525525330oracle@ol6.localdomainTHREAD 2525525331oracle@ol6.localdomainTHREAD 2525525332oracle@ol6.localdomainTHREAD 2525525333oracle@ol6.localdomainTHREAD 2525525334oracle@ol6.localdomainTHREAD 2525525335oracle@ol6.localdomainTHREAD 2525525336oracle@ol6.localdomainTHREAD 2525525337oracle@ol6.localdomainTHREAD 2525525338oracle@ol6.localdomainTHREAD 2525525339oracle@ol6.localdomainTHREAD 2525525340AQPCoracle@ol6.localdomain(AQPC)THREAD 2525525342P000oracle@ol6.localdomain(P000)THREAD 2525525343P001oracle@ol6.localdomain(P001)THREAD 2525525344P002oracle@ol6.localdomain(P002)THREAD 2525525345P003oracle@ol6.localdomain(P003)THREAD 2525525491CJQ0oracle@ol6.localdomain(CJQ0)THREAD 2525525528QM02oracle@ol6.localdomain(QM02)THREAD 2525525530Q002oracle@ol6.localdomain(Q002)THREAD 2525525531Q003oracle@ol6.localdomain(Q003)THREAD 2525525532Q004oracle@ol6.localdomain(Q004)THREAD 2525525533Q005oracle@ol6.localdomain(Q005)THREAD 2525525534Q006oracle@ol6.localdomain(Q006)THREAD 2525525535Q007oracle@ol6.localdomain(Q007)THREAD 2525525536Q008oracle@ol6.localdomain(Q008)THREAD 2525525633W002oracle@ol6.localdomain(W002)THREAD 2525925259SCMNoracle@ol6.localdomain(SCMN)THREAD 2525925261OFSDoracle@ol6.localdomain(OFSD)THREAD 2527025270SCMNoracle@ol6.localdomain(SCMN)THREAD 2527025272LGWRoracle@ol6.localdomain(LGWR)THREAD
5.通过监听连接，可以看到仍然是进程模式。
[oracle@ol6~]$sqlplus/nolog SQL*Plus:Release12.2.0.1.0ProductiononFriAug316:14:482018 Copyright(c)1982,2016,Oracle.Allrightsreserved. SQL>connectsys/oracleassysdba Connected. SQL> SQL>connectsys/oracle@pdbassysdba Connected. [root@ol6~]#ps-ef|grepcdb1 oracle252361016:03?00:00:00ora_pmon_cdb1 oracle252381016:03?00:00:00ora_u002_cdb1 oracle252421016:03?00:00:00ora_psp0_cdb1 oracle252441016:03?00:00:06ora_vktm_cdb1 oracle252511016:03?00:00:00ora_gen1_cdb1 oracle252551216:03?00:00:17ora_u006_cdb1 oracle252591016:03?00:00:00ora_ofsd_cdb1 oracle252681016:03?00:00:00ora_dbw0_cdb1 oracle252701016:03?00:00:00ora_lgwr_cdb1 oracle272201016:15?00:00:00oraclecdb1(LOCAL=NO) root2727024458016:15pts/100:00:00grepcdb1
6.如果要通过监听的方式连接变成线程模式，需要修改监听的参数DEDICATED_THROUGH_BROKER_listener-name，并重启监听才行。
[oracle@ol6admin]$vilistener.ora #listener.oraNetworkConfigurationFile:/u01/app/oracle/product/12.2.0.1/db_1/network/admin/listener.ora #GeneratedbyOracleconfigurationtools. 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>selectspidfromv$processwhereaddrin(selectpaddrfromv$sessionwheresid=28); SPID ------------------------ 25255 SQL>/ SPIDSTIDPNAMEPROGRAMEXECUTION_ --------------------------------------------------------------------------------------------------------------- PSEUDONONE 2523625236PMONoracle@ol6.localdomain(PMON)PROCESS 2524225242PSP0oracle@ol6.localdomain(PSP0)PROCESS 2524425244VKTMoracle@ol6.localdomain(VKTM)PROCESS 2526825268DBW0oracle@ol6.localdomain(DBW0)PROCESS 2722027220oracle@ol6.localdomainPROCESS 2523825238SCMNoracle@ol6.localdomain(SCMN)THREAD 2523825240CLMNoracle@ol6.localdomain(CLMN)THREAD 2523825247GEN0oracle@ol6.localdomain(GEN0)THREAD 2523825248MMANoracle@ol6.localdomain(MMAN)THREAD 2523825262DBRMoracle@ol6.localdomain(DBRM)THREAD 2523825265PMANoracle@ol6.localdomain(PMAN)THREAD 2523825273CKPToracle@ol6.localdomain(CKPT)THREAD 2523825274SMONoracle@ol6.localdomain(SMON)THREAD 2523825278LREGoracle@ol6.localdomain(LREG)THREAD 2525125251SCMNoracle@ol6.localdomain(SCMN)THREAD 2525125253GEN1oracle@ol6.localdomain(GEN1)THREAD 2525525255SCMNoracle@ol6.localdomain(SCMN)THREAD 2525525257DIAGoracle@ol6.localdomain(DIAG)THREAD 2525525263VKRMoracle@ol6.localdomain(VKRM)THREAD 2525525264SVCBoracle@ol6.localdomain(SVCB)THREAD 2525525266DIA0oracle@ol6.localdomain(DIA0)THREAD 2525525275SMCOoracle@ol6.localdomain(SMCO)THREAD 2525525276RECOoracle@ol6.localdomain(RECO)THREAD 2525525280PXMNoracle@ol6.localdomain(PXMN)THREAD 2525525282MMONoracle@ol6.localdomain(MMON)THREAD 2525525283MMNLoracle@ol6.localdomain(MMNL)THREAD 2525525284D000oracle@ol6.localdomain(D000)THREAD 2525525285S000oracle@ol6.localdomain(S000)THREAD 2525525286TMONoracle@ol6.localdomain(TMON)THREAD 2525525287N000oracle@ol6.localdomain(N000)THREAD 2525525296oracle@ol6.localdomainTHREAD 2525525297oracle@ol6.localdomainTHREAD 2525525300oracle@ol6.localdomainTHREAD 2525525301oracle@ol6.localdomainTHREAD 2525525302oracle@ol6.localdomainTHREAD 2525525304oracle@ol6.localdomainTHREAD 2525525306oracle@ol6.localdomainTHREAD 2525525307oracle@ol6.localdomainTHREAD 2525525308oracle@ol6.localdomainTHREAD 2525525309oracle@ol6.localdomainTHREAD 2525525310oracle@ol6.localdomainTHREAD 2525525311oracle@ol6.localdomainTHREAD 2525525314oracle@ol6.localdomainTHREAD 2525525315oracle@ol6.localdomainTHREAD 2525525319TT00oracle@ol6.localdomain(TT00)THREAD 2525525320TT01oracle@ol6.localdomain(TT01)THREAD 2525525321TT02oracle@ol6.localdomain(TT02)THREAD 2525525330oracle@ol6.localdomainTHREAD 2525525331oracle@ol6.localdomainTHREAD 2525525332oracle@ol6.localdomainTHREAD 2525525333oracle@ol6.localdomainTHREAD 2525525334oracle@ol6.localdomainTHREAD 2525525336oracle@ol6.localdomainTHREAD 2525525337oracle@ol6.localdomainTHREAD 2525525338oracle@ol6.localdomainTHREAD 2525525339oracle@ol6.localdomainTHREAD 2525525340AQPCoracle@ol6.localdomain(AQPC)THREAD 2525525342P000oracle@ol6.localdomain(P000)THREAD 2525525343P001oracle@ol6.localdomain(P001)THREAD 2525525344P002oracle@ol6.localdomain(P002)THREAD 2525525345P003oracle@ol6.localdomain(P003)THREAD 2525525491CJQ0oracle@ol6.localdomain(CJQ0)THREAD 2525525528QM02oracle@ol6.localdomain(QM02)THREAD 2525525530Q002oracle@ol6.localdomain(Q002)THREAD 2525525533Q005oracle@ol6.localdomain(Q005)THREAD 2525525535Q007oracle@ol6.localdomain(Q007)THREAD 2525526267W003oracle@ol6.localdomain(W003)THREAD 2525526842W004oracle@ol6.localdomain(W004)THREAD 2525527011W005oracle@ol6.localdomain(W005)THREAD 2525527239W006oracle@ol6.localdomain(W006)THREAD 2525925259SCMNoracle@ol6.localdomain(SCMN)THREAD 2525925261OFSDoracle@ol6.localdomain(OFSD)THREAD 2527025270SCMNoracle@ol6.localdomain(SCMN)THREAD 2527025272LGWRoracle@ol6.localdomain(LGWR)THREAD
当然需要注意的一点是，如果在AIX上使用线程模式，需要安装补丁BUG22226365–THREADED_EXECUTION=TRUE–SCMNPROCESSRESMEMORYINCREASES。
总结
以上所述是小编给大家介绍的Oracle12c新特性之多线程数据库操作，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！