这篇文章将为大家详细讲解有关Oracle 12c新特性之多线程数据库的示例分析，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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

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

[oracle@ol6~]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononFriAug315:57:592018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedto:OracleDatabase12cEnterpriseEditionRelease12.2.0.1.0-64bitProductionSQL>showparameterthreadedNAMETYPEVALUE-----------------------------------------------------------------------------threaded_executionbooleanFALSE[root@ol6~]#ps-ef|grepcdb1oracle177310Aug02?00:00:04ora_pmon_cdb1oracle177510Aug02?00:00:01ora_clmn_cdb1oracle177710Aug02?00:00:13ora_psp0_cdb1oracle178610Aug02?00:17:01ora_vktm_cdb1oracle179010Aug02?00:00:08ora_gen0_cdb1oracle179210Aug02?00:00:01ora_mman_cdb1oracle179610Aug02?00:00:19ora_gen1_cdb1oracle180010Aug02?00:00:03ora_diag_cdb1oracle180210Aug02?00:00:01ora_ofsd_cdb1oracle180610Aug02?00:00:29ora_dbrm_cdb1oracle180810Aug02?00:01:14ora_vkrm_cdb1oracle181010Aug02?00:00:03ora_svcb_cdb1oracle181210Aug02?00:00:10ora_pman_cdb1oracle181410Aug02?00:00:48ora_dia0_cdb1oracle181610Aug02?00:00:08ora_dbw0_cdb1oracle181810Aug02?00:00:10ora_lgwr_cdb1oracle182010Aug02?00:00:18ora_ckpt_cdb1oracle182210Aug02?00:00:01ora_smon_cdb1oracle182410Aug02?00:00:04ora_smco_cdb1oracle182610Aug02?00:00:00ora_reco_cdb1oracle183010Aug02?00:00:03ora_lreg_cdb1oracle183410Aug02?00:00:01ora_pxmn_cdb1oracle183810Aug02?00:00:20ora_mmon_cdb1oracle184010Aug02?00:00:26ora_mmnl_cdb1oracle184210Aug02?00:00:00ora_d000_cdb1oracle184410Aug02?00:00:00ora_s000_cdb1oracle184610Aug02?00:00:00ora_tmon_cdb1oracle186910Aug02?00:00:00ora_tt00_cdb1oracle187110Aug02?00:00:00ora_tt01_cdb1oracle187310Aug02?00:00:02ora_tt02_cdb1oracle187510Aug02?00:00:00ora_aqpc_cdb1oracle187910Aug02?00:00:02ora_p000_cdb1oracle188110Aug02?00:00:02ora_p001_cdb1oracle188310Aug02?00:00:02ora_p002_cdb1oracle188510Aug02?00:00:02ora_p003_cdb1oracle203910Aug02?00:02:36ora_cjq0_cdb1oracle210910Aug02?00:00:01ora_qm02_cdb1oracle211310Aug02?00:00:00ora_q002_cdb1oracle212010Aug02?00:00:02ora_q005_cdb1oracle240761015:56?00:00:00ora_w000_cdb1oracle241491015:56?00:00:00ora_q003_cdb1oracle241541015:56?00:00:00ora_q004_cdb1oracle241611015:56?00:00:00ora_q006_cdb1oracle241651015:56?00:00:00ora_w001_cdb1oracle243171015:57?00:00:00ora_w002_cdb1oracle2442224421015:57?00:00:00oraclecdb1(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))root2450424458015:58pts/100:00:00grepcdb1oracle2877810Aug02?00:00:01ora_q001_cdb1oracle29034290330Aug02?00:00:00oraclecdb1(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

2.修改参数重启数据库

SQL>ALTERSYSTEMSETthreaded_execution=trueSCOPE=SPFILE;Systemaltered.SQL>shutdownimmediate;Databaseclosed.Databasedismounted.ORACLEinstanceshutdown.[oracle@ol6~]$sqlplus/assysdbaSQL*Plus:Release12.2.0.1.0ProductiononFriAug315:59:472018Copyright(c)1982,2016,Oracle.Allrightsreserved.Connectedtoanidleinstance.SQL>startupERROR:ORA-01017:invalidusername/password;logondenied

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

[oracle@ol6~]$sqlplus/nologSQL*Plus:Release12.2.0.1.0ProductiononFriAug316:02:152018Copyright(c)1982,2016,Oracle.Allrightsreserved.SQL>connsysassysdbaEnterpassword:Connected.SQL>shutdownimmediate;ORA-01507:databasenotmountedORACLEinstanceshutdown.SQL>startupORACLEinstancestarted.TotalSystemGlobalArea629145600bytesFixedSize8795760bytesVariableSize322963856bytesDatabaseBuffers293601280bytesRedoBuffers3784704bytesDatabasemounted.Databaseopened.SQL>showparameterthreadedNAMETYPEVALUE-----------------------------------------------------------------------------threaded_executionbooleanTRUE

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

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

[root@ol6~]#ps-ef|grepcdb1oracle252361016:03?00:00:00ora_pmon_cdb1oracle252381016:03?00:00:00ora_u002_cdb1oracle252421016:03?00:00:00ora_psp0_cdb1oracle252441016:03?00:00:00ora_vktm_cdb1oracle252511016:03?00:00:00ora_gen1_cdb1oracle2525512116:03?00:00:14ora_u006_cdb1oracle252591016:03?00:00:00ora_ofsd_cdb1oracle252681016:03?00:00:00ora_dbw0_cdb1oracle252701016:03?00:00:00ora_lgwr_cdb1root2562924458016:04pts/100:00:00grepcdb1

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

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

SQL>selectspid,stid,pname,program,execution_typefromv$processorderbyexecution_type,spid,stid;SPIDSTIDPNAMEPROGRAMEXECUTION_---------------------------------------------------------------------------------------------------------------PSEUDONONE2523625236PMONoracle@ol6.localdomain(PMON)PROCESS2524225242PSP0oracle@ol6.localdomain(PSP0)PROCESS2524425244VKTMoracle@ol6.localdomain(VKTM)PROCESS2526825268DBW0oracle@ol6.localdomain(DBW0)PROCESS2523825238SCMNoracle@ol6.localdomain(SCMN)THREAD2523825240CLMNoracle@ol6.localdomain(CLMN)THREAD2523825247GEN0oracle@ol6.localdomain(GEN0)THREAD2523825248MMANoracle@ol6.localdomain(MMAN)THREAD2523825262DBRMoracle@ol6.localdomain(DBRM)THREAD2523825265PMANoracle@ol6.localdomain(PMAN)THREAD2523825273CKPToracle@ol6.localdomain(CKPT)THREAD2523825274SMONoracle@ol6.localdomain(SMON)THREAD2523825278LREGoracle@ol6.localdomain(LREG)THREAD2525125251SCMNoracle@ol6.localdomain(SCMN)THREAD2525125253GEN1oracle@ol6.localdomain(GEN1)THREAD2525525255SCMNoracle@ol6.localdomain(SCMN)THREAD2525525257DIAGoracle@ol6.localdomain(DIAG)THREAD2525525263VKRMoracle@ol6.localdomain(VKRM)THREAD2525525264SVCBoracle@ol6.localdomain(SVCB)THREAD2525525266DIA0oracle@ol6.localdomain(DIA0)THREAD2525525275SMCOoracle@ol6.localdomain(SMCO)THREAD2525525276RECOoracle@ol6.localdomain(RECO)THREAD2525525277W000oracle@ol6.localdomain(W000)THREAD2525525279W001oracle@ol6.localdomain(W001)THREAD2525525280PXMNoracle@ol6.localdomain(PXMN)THREAD2525525282MMONoracle@ol6.localdomain(MMON)THREAD2525525283MMNLoracle@ol6.localdomain(MMNL)THREAD2525525284D000oracle@ol6.localdomain(D000)THREAD2525525285S000oracle@ol6.localdomain(S000)THREAD2525525286TMONoracle@ol6.localdomain(TMON)THREAD2525525287N000oracle@ol6.localdomain(N000)THREAD2525525296oracle@ol6.localdomainTHREAD2525525297oracle@ol6.localdomainTHREAD2525525298oracle@ol6.localdomainTHREAD2525525299oracle@ol6.localdomainTHREAD2525525300oracle@ol6.localdomainTHREAD2525525301oracle@ol6.localdomainTHREAD2525525302oracle@ol6.localdomainTHREAD2525525303oracle@ol6.localdomainTHREAD2525525304oracle@ol6.localdomainTHREAD2525525305oracle@ol6.localdomainTHREAD2525525306oracle@ol6.localdomainTHREAD2525525307oracle@ol6.localdomainTHREAD2525525308oracle@ol6.localdomainTHREAD2525525309oracle@ol6.localdomainTHREAD2525525310oracle@ol6.localdomainTHREAD2525525311oracle@ol6.localdomainTHREAD2525525312oracle@ol6.localdomainTHREAD2525525313oracle@ol6.localdomainTHREAD2525525314oracle@ol6.localdomainTHREAD2525525315oracle@ol6.localdomainTHREAD2525525319TT00oracle@ol6.localdomain(TT00)THREAD2525525320TT01oracle@ol6.localdomain(TT01)THREAD2525525321TT02oracle@ol6.localdomain(TT02)THREAD2525525330oracle@ol6.localdomainTHREAD2525525331oracle@ol6.localdomainTHREAD2525525332oracle@ol6.localdomainTHREAD2525525333oracle@ol6.localdomainTHREAD2525525334oracle@ol6.localdomainTHREAD2525525335oracle@ol6.localdomainTHREAD2525525336oracle@ol6.localdomainTHREAD2525525337oracle@ol6.localdomainTHREAD2525525338oracle@ol6.localdomainTHREAD2525525339oracle@ol6.localdomainTHREAD2525525340AQPCoracle@ol6.localdomain(AQPC)THREAD2525525342P000oracle@ol6.localdomain(P000)THREAD2525525343P001oracle@ol6.localdomain(P001)THREAD2525525344P002oracle@ol6.localdomain(P002)THREAD2525525345P003oracle@ol6.localdomain(P003)THREAD2525525491CJQ0oracle@ol6.localdomain(CJQ0)THREAD2525525528QM02oracle@ol6.localdomain(QM02)THREAD2525525530Q002oracle@ol6.localdomain(Q002)THREAD2525525531Q003oracle@ol6.localdomain(Q003)THREAD2525525532Q004oracle@ol6.localdomain(Q004)THREAD2525525533Q005oracle@ol6.localdomain(Q005)THREAD2525525534Q006oracle@ol6.localdomain(Q006)THREAD2525525535Q007oracle@ol6.localdomain(Q007)THREAD2525525536Q008oracle@ol6.localdomain(Q008)THREAD2525525633W002oracle@ol6.localdomain(W002)THREAD2525925259SCMNoracle@ol6.localdomain(SCMN)THREAD2525925261OFSDoracle@ol6.localdomain(OFSD)THREAD2527025270SCMNoracle@ol6.localdomain(SCMN)THREAD2527025272LGWRoracle@ol6.localdomain(LGWR)THREAD

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

[oracle@ol6~]$sqlplus/nologSQL*Plus:Release12.2.0.1.0ProductiononFriAug316:14:482018Copyright(c)1982,2016,Oracle.Allrightsreserved.SQL>connectsys/oracleassysdbaConnected.SQL>SQL>connectsys/oracle@pdbassysdbaConnected.[root@ol6~]#ps-ef|grepcdb1oracle252361016:03?00:00:00ora_pmon_cdb1oracle252381016:03?00:00:00ora_u002_cdb1oracle252421016:03?00:00:00ora_psp0_cdb1oracle252441016:03?00:00:06ora_vktm_cdb1oracle252511016:03?00:00:00ora_gen1_cdb1oracle252551216:03?00:00:17ora_u006_cdb1oracle252591016:03?00:00:00ora_ofsd_cdb1oracle252681016:03?00:00:00ora_dbw0_cdb1oracle252701016:03?00:00:00ora_lgwr_cdb1oracle272201016: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------------------------25255SQL>/SPIDSTIDPNAMEPROGRAMEXECUTION_---------------------------------------------------------------------------------------------------------------PSEUDONONE2523625236PMONoracle@ol6.localdomain(PMON)PROCESS2524225242PSP0oracle@ol6.localdomain(PSP0)PROCESS2524425244VKTMoracle@ol6.localdomain(VKTM)PROCESS2526825268DBW0oracle@ol6.localdomain(DBW0)PROCESS2722027220oracle@ol6.localdomainPROCESS2523825238SCMNoracle@ol6.localdomain(SCMN)THREAD2523825240CLMNoracle@ol6.localdomain(CLMN)THREAD2523825247GEN0oracle@ol6.localdomain(GEN0)THREAD2523825248MMANoracle@ol6.localdomain(MMAN)THREAD2523825262DBRMoracle@ol6.localdomain(DBRM)THREAD2523825265PMANoracle@ol6.localdomain(PMAN)THREAD2523825273CKPToracle@ol6.localdomain(CKPT)THREAD2523825274SMONoracle@ol6.localdomain(SMON)THREAD2523825278LREGoracle@ol6.localdomain(LREG)THREAD2525125251SCMNoracle@ol6.localdomain(SCMN)THREAD2525125253GEN1oracle@ol6.localdomain(GEN1)THREAD2525525255SCMNoracle@ol6.localdomain(SCMN)THREAD2525525257DIAGoracle@ol6.localdomain(DIAG)THREAD2525525263VKRMoracle@ol6.localdomain(VKRM)THREAD2525525264SVCBoracle@ol6.localdomain(SVCB)THREAD2525525266DIA0oracle@ol6.localdomain(DIA0)THREAD2525525275SMCOoracle@ol6.localdomain(SMCO)THREAD2525525276RECOoracle@ol6.localdomain(RECO)THREAD2525525280PXMNoracle@ol6.localdomain(PXMN)THREAD2525525282MMONoracle@ol6.localdomain(MMON)THREAD2525525283MMNLoracle@ol6.localdomain(MMNL)THREAD2525525284D000oracle@ol6.localdomain(D000)THREAD2525525285S000oracle@ol6.localdomain(S000)THREAD2525525286TMONoracle@ol6.localdomain(TMON)THREAD2525525287N000oracle@ol6.localdomain(N000)THREAD2525525296oracle@ol6.localdomainTHREAD2525525297oracle@ol6.localdomainTHREAD2525525300oracle@ol6.localdomainTHREAD2525525301oracle@ol6.localdomainTHREAD2525525302oracle@ol6.localdomainTHREAD2525525304oracle@ol6.localdomainTHREAD2525525306oracle@ol6.localdomainTHREAD2525525307oracle@ol6.localdomainTHREAD2525525308oracle@ol6.localdomainTHREAD2525525309oracle@ol6.localdomainTHREAD2525525310oracle@ol6.localdomainTHREAD2525525311oracle@ol6.localdomainTHREAD2525525314oracle@ol6.localdomainTHREAD2525525315oracle@ol6.localdomainTHREAD2525525319TT00oracle@ol6.localdomain(TT00)THREAD2525525320TT01oracle@ol6.localdomain(TT01)THREAD2525525321TT02oracle@ol6.localdomain(TT02)THREAD2525525330oracle@ol6.localdomainTHREAD2525525331oracle@ol6.localdomainTHREAD2525525332oracle@ol6.localdomainTHREAD2525525333oracle@ol6.localdomainTHREAD2525525334oracle@ol6.localdomainTHREAD2525525336oracle@ol6.localdomainTHREAD2525525337oracle@ol6.localdomainTHREAD2525525338oracle@ol6.localdomainTHREAD2525525339oracle@ol6.localdomainTHREAD2525525340AQPCoracle@ol6.localdomain(AQPC)THREAD2525525342P000oracle@ol6.localdomain(P000)THREAD2525525343P001oracle@ol6.localdomain(P001)THREAD2525525344P002oracle@ol6.localdomain(P002)THREAD2525525345P003oracle@ol6.localdomain(P003)THREAD2525525491CJQ0oracle@ol6.localdomain(CJQ0)THREAD2525525528QM02oracle@ol6.localdomain(QM02)THREAD2525525530Q002oracle@ol6.localdomain(Q002)THREAD2525525533Q005oracle@ol6.localdomain(Q005)THREAD2525525535Q007oracle@ol6.localdomain(Q007)THREAD2525526267W003oracle@ol6.localdomain(W003)THREAD2525526842W004oracle@ol6.localdomain(W004)THREAD2525527011W005oracle@ol6.localdomain(W005)THREAD2525527239W006oracle@ol6.localdomain(W006)THREAD2525925259SCMNoracle@ol6.localdomain(SCMN)THREAD2525925261OFSDoracle@ol6.localdomain(OFSD)THREAD2527025270SCMNoracle@ol6.localdomain(SCMN)THREAD2527025272LGWRoracle@ol6.localdomain(LGWR)THREAD

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

关于“Oracle 12c新特性之多线程数据库的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。