PLSQL JSON類接口優(yōu)化
點(diǎn)擊上方“IT那活兒”公眾號(hào)，關(guān)注后了解更多內(nèi)容，不管IT什么活兒，干就完了！！！數(shù)據(jù)庫(kù)版本：12.1.0.2上午還沒(méi)到單位，客戶就發(fā)微信告知趕緊到單位處理一個(gè)請(qǐng)求慢的問(wèn)題，原本十幾秒鐘就能完成的請(qǐng)求，現(xiàn)在兩三天跑不完，第一時(shí)間想到的可能就是plsql代碼塊中SQL執(zhí)行出現(xiàn)了問(wèn)題，很有可能是執(zhí)行計(jì)劃發(fā)生了改變，到單位上手處理之后，發(fā)現(xiàn)事件沒(méi)有自己想的那么簡(jiǎn)單，plsql代碼塊中所有的SQL執(zhí)行速度都很快，1s中之內(nèi)就能返回結(jié)果。但是請(qǐng)求確確實(shí)實(shí)跑了兩三天才完成。下圖所示中標(biāo)記的為業(yè)務(wù)通過(guò)程序記錄的執(zhí)行調(diào)用時(shí)間，6000/60/24=4.xx.最慢的時(shí)候程序都已經(jīng)跑了四天多。
2.1 plsql代碼塊中的相關(guān)SQL已經(jīng)進(jìn)行了排查，并未發(fā)現(xiàn)有什么異常SQL，SQL邏輯很簡(jiǎn)單。查看PLSQL對(duì)應(yīng)的等待事件，這里發(fā)現(xiàn)是db file sequential read（單塊讀），通過(guò)對(duì)應(yīng)的p1.p2發(fā)現(xiàn)一直在等待獲取file=63 block=1141320這個(gè)數(shù)據(jù)塊，并且根據(jù)下面查詢，發(fā)現(xiàn)對(duì)于單個(gè)block請(qǐng)求時(shí)間長(zhǎng)達(dá)幾十秒鐘。2.2 針對(duì)對(duì)應(yīng)的等待事件p1.p2,查詢對(duì)應(yīng)的block屬于那個(gè)對(duì)象，這里我們發(fā)現(xiàn)是CUX_DWMS_CKD_JOB_LOT_IFACE表單塊讀較慢。因查詢語(yǔ)句太長(zhǎng)這里不方便貼出，具體查看語(yǔ)句見(jiàn)如下地址：
https://www.cnblogs.com/hanglinux/p/16302543.html
2.3 分析到這里我懷疑可能是行鏈接或者是行遷移導(dǎo)致的性能問(wèn)題，通過(guò)@$ORACLE_HOME/rdbms/admin/utlchain.sql以及chain.sql對(duì)相關(guān)問(wèn)題表進(jìn)行了分析，并未發(fā)現(xiàn)有行鏈接或者行遷移的情況，并針對(duì)表信息進(jìn)行了查詢以及相關(guān)plsql代碼快塊中的執(zhí)行計(jì)劃進(jìn)行了排查。SQL> @chine
Enter the schema name to check for Row Chaining (RETURN for All): CUX
Enter the table name to check (RETURN for All tables owned by CUX): CUX_DWMS_CKD_JOB_LOT_IFACE
No Chained Rows found in the CUX owned Tables!
###表歷史統(tǒng)計(jì)相關(guān)信息：###表段相關(guān)信息，這里發(fā)現(xiàn)這個(gè)表非常的小，僅僅有3M，對(duì)于x8一體機(jī)而言即便是循環(huán)嵌套的全表掃描，也不可能這么慢，更何況是走的索引掃描。###plsql代碼塊中表相關(guān)查詢語(yǔ)句以及執(zhí)行計(jì)劃如下，執(zhí)行計(jì)劃也是相當(dāng)?shù)暮茫竭@里問(wèn)題分析不下去了，整整糾結(jié)了一上午。chain.sql相關(guān)腳本地址如下：
https://www.cnblogs.com/hanglinux/p/16302598.html
2.4 通過(guò)ash報(bào)告獲取當(dāng)前負(fù)載信息##這里發(fā)現(xiàn)改存儲(chǔ)過(guò)程正在慢的點(diǎn)在于apps.json.put在plsql代碼快中，我們發(fā)現(xiàn)了如下信息，使用json結(jié)構(gòu)構(gòu)造數(shù)據(jù)，通過(guò)上層查詢的數(shù)據(jù)，放入json結(jié)構(gòu)代碼中，然后進(jìn)程轉(zhuǎn)存，正常情況下都是使用java、python或者其他的開(kāi)發(fā)工具進(jìn)行構(gòu)造，因?yàn)檫壿嫸际谴鎯?chǔ)過(guò)程寫(xiě)的，所以對(duì)于構(gòu)造數(shù)據(jù)業(yè)務(wù)模塊中都統(tǒng)一使用json結(jié)構(gòu)進(jìn)行了構(gòu)造，在和開(kāi)發(fā)的溝通中，我們也定位到plsql執(zhí)行慢的地方在如下代碼塊。2.5 通過(guò)對(duì)plsql代碼塊進(jìn)行日志處理，進(jìn)一步打印每次循環(huán)的時(shí)間，每次嵌套大概執(zhí)行的時(shí)間是14s。上述問(wèn)題表CUX_DWMS_CKD_JOB_LOT_IFACE大概返回2000-3000行數(shù)據(jù)，也就是說(shuō)這個(gè)嵌套要執(zhí)行2000-3000次，這里簡(jiǎn)單的計(jì)算就是大概十幾個(gè)小時(shí)，但是實(shí)際情況下，隨著嵌套循環(huán)的進(jìn)行，后續(xù)的嵌套會(huì)越來(lái)越慢，嵌套的執(zhí)行時(shí)間也會(huì)越來(lái)越長(zhǎng)，最終導(dǎo)致整個(gè)請(qǐng)求在2-3天內(nèi)無(wú)法執(zhí)行完畢。2.6 定位到慢的代碼，進(jìn)行分析了json結(jié)構(gòu)塊代碼中的如下部分，每次內(nèi)層嵌套完畢之后，都需要put對(duì)如下結(jié)構(gòu)代碼進(jìn)行覆蓋，結(jié)合ash報(bào)告中的TOP PL/SQL Procedures中的信息，我們也是發(fā)現(xiàn)這里的apps.json.put執(zhí)行時(shí)間較慢。l_lotline_json := json();
通過(guò)以上分析，我們建議將l_lotline_json := json();代碼結(jié)構(gòu)直接放到下層循環(huán)中，每次新建對(duì)象，不需要put覆蓋之前的數(shù)據(jù)，和業(yè)務(wù)人員進(jìn)行溝通，再次執(zhí)行，請(qǐng)求已經(jīng)恢復(fù)到十幾秒內(nèi)完成。因?yàn)樽约簩?duì)于開(kāi)發(fā)不熟，對(duì)于這個(gè)問(wèn)題，專門(mén)詢問(wèn)了開(kāi)發(fā)的同學(xué)，開(kāi)發(fā)同學(xué)如下解釋：按照java的理論新建一個(gè)對(duì)象消耗的時(shí)間更多，如果把l_lotline_json:= json()如下二層嵌套中，每次就需要新建一個(gè)對(duì)象，不需要put覆蓋之前的數(shù)據(jù)。但在Oracle實(shí)際執(zhí)行過(guò)程中確確實(shí)實(shí)是因?yàn)閜ut覆蓋對(duì)象導(dǎo)致的業(yè)務(wù)延遲，原來(lái)的業(yè)務(wù)執(zhí)行的時(shí)候也是沒(méi)有問(wèn)題的，其他的類似業(yè)務(wù)也沒(méi)有問(wèn)題，單單對(duì)于這個(gè)plsql程序出現(xiàn)了情況，這里我也不是很懂。修改后的代碼如下：這里是修改后的代碼記錄執(zhí)行時(shí)間，說(shuō)明：12號(hào)的其他執(zhí)行時(shí)間是前臺(tái)取消業(yè)務(wù)記錄的時(shí)間，并未執(zhí)行完畢。
4.1 統(tǒng)計(jì)hash_value那個(gè)語(yǔ)句執(zhí)行的多select t.SQL_HASH_VALUE,t.SQL_ID,count(1) from v$session t
where t.STATUS=ACTIVE and t.sql_hash_value<>0 and
t.program not like oracle@c6ogx6a (W% group by
t.SQL_HASH_VALUE,t.SQL_ID order by 3 desc;
4.2 通過(guò)ash_top_plsq定位正在執(zhí)行的子程序set linesize 120
col entry_package for a25
col entry_procedure for a25
col cur_package for a25
col cur_procedure for a25
col calling_code for a70
select
count(*),
sql_id,
procs1.object_name || decode(procs1.procedure_name,,,.)||
procs1.procedure_name || ||
decode(procs2.object_name,procs1.object_name,,
decode(procs2.object_name,,, => ||procs2.object_name))
||
decode(procs2.procedure_name,procs1.procedure_name,,
decode(procs2.procedure_name,,,null,,.)||procs2.procedure_name)
"calling_code"
from v$active_session_history ash,
all_procedures procs1,
all_procedures procs2
where
ash.PLSQL_ENTRY_OBJECT_ID = procs1.object_id (+)
and ash.PLSQL_ENTRY_SUBPROGRAM_ID = procs1.SUBPROGRAM_ID (+)
and ash.PLSQL_OBJECT_ID = procs2.object_id (+)
and ash.PLSQL_SUBPROGRAM_ID = procs2.SUBPROGRAM_ID (+)
and ash.sample_time > sysdate - &minutes/(60*24)
group by procs1.object_name, procs1.procedure_name,
procs2.object_name, procs2.procedure_name,sql_id
order by count(*)
/
4.3 snapper.sql腳本也可以查詢相關(guān)信息##腳本調(diào)用查詢的其實(shí)也是v$active_session_history.PLSQL_ENTRY_OBJECT_ID、PLSQL_ENTRY_OBJECT_ID、PLSQL_SUBPROGRAM_ID相關(guān)信息，并不能獲取正在慢的程序接口類的結(jié)構(gòu)。
https://tanelpoder.com
4.4 通過(guò)ash報(bào)告中的TOP PL/SQL Procedures定位正在慢的程序接口##這里我通過(guò)10046獲取到到相關(guān)的內(nèi)部執(zhí)行SQL，但是一些變量值我有點(diǎn)拿不準(zhǔn)，也就不貼出來(lái)了。觀察Oracle內(nèi)部其實(shí)調(diào)用了一個(gè)內(nèi)部程序包dbms_ash_internal來(lái)獲取的子程序以及具體的程序結(jié)構(gòu)代碼塊##感興趣的可以看看，記錄在如下地址中：
https://www.cnblogs.com/hanglinux/p/16303325.html
4.5 erp相關(guān)plsql優(yōu)化對(duì)于erp系統(tǒng)而言，正常都有一個(gè)SQL可以按照請(qǐng)求號(hào)，來(lái)查詢正在調(diào)用的sid，以及正在執(zhí)行的plsql中的sql_id信息，但有時(shí)候也是會(huì)出現(xiàn)無(wú)法查詢到plsql代碼中具體SQL的尷尬案例。比如這次分析的案例，通過(guò)客戶提供的SQL只能檢查到正在執(zhí)行的plsql代碼sql_id，內(nèi)部的SQL執(zhí)行確確實(shí)實(shí)找不到，如果發(fā)生此類情況，可能就是正在調(diào)用某個(gè)接口或者子程序吧。本文作者：李行行（上海新炬王翦團(tuán)隊(duì)）
本文來(lái)源：“IT那活兒”公眾號(hào)
文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/129318.html
