近期一些典型的Case
招行問題分析及建議
網(wǎng)站的建設創(chuàng)新互聯(lián)公司專注網(wǎng)站定制,經(jīng)驗豐富,不做模板,主營網(wǎng)站定制開發(fā).小程序定制開發(fā),H5頁面制作!給你煥然一新的設計體驗!已為崗亭等企業(yè)提供專業(yè)服務。
針對近期工作中出現(xiàn)的問題，我選取了一些典型案例并加上自己的分析。
1. ftpdbn1-deadlock問題：
通過打開event monitor抓取的數(shù)據(jù)，發(fā)現(xiàn)是由一個存儲過程大量執(zhí)行同一個表的delete語句導致的死鎖。存儲過程定義如下：
CREATEPROCEDURE CIFDBO.SP_FRM_E_FMEVRFCA_LOG_ADD_V1 (
IN pMDL_INS_COD CIFDBO.MDL_INS_COD_ARRAY,
......
IN pREC_CNT CIFDBO.INT_ARRAY,
OUT pRtCode INTEGER
)
SPECIFIC SP_FRM_E_FMEVRFCA_LOG_ADD_V1
P1:BEGIN
DECLARE vEVT_ID CHAR(20);
DECLARE i, n INTEGER;
DECLARE vTimestamp TIMESTAMP;
DECLARE vDateDelete TIMESTAMP;
DECLARE SQLCODE INTEGER;
DECLAREEXIT HANDLERFOR SQLEXCEPTION
BEGIN
SET pRtCode= SQLCODE;
END;
SET pRtCode=-1;
SET vTimestamp=CURRENT TIMESTAMP;
SET vDateDelete= vTimestamp-3 MONTHS;
DELETEFROM FRM.FMEVRFCA_LOGWHERE CRT_TIM< vDateDelete;
SET n= CARDINALITY(pTBL_NAM);
SET i=1;
WHILE (i<= n) DO
INSERTINTO FRM.FMEVRFCA_LOG
(
MDL_INS_COD ,
SEQ_NO ,
TBL_NAM ,
OPR_COD ,
UPD_TIM ,
REM_KEY ,
REC_CNT ,
CRT_TIM
)
VALUES
(
pMDL_INS_COD[i],
pSEQ_NO[i] ,
pTBL_NAM[i] ,
pOPR_COD[i] ,
pUPD_TIM[i] ,
pREM_KEY[i] ,
pREC_CNT[i] ,
vTimestamp
);
SET i= i+1;
END WHILE;
SET pRtCode=0;
END P1
優(yōu)化建議： 為避免將delete語句移出存儲過程，由一個單獨的task定期執(zhí)行！
2. osfdb01-ORSDB鎖升級
造成鎖升級的SQL語句（鎖升級發(fā)生在表OWK.EMP_STAFF_ORG上）：
SELECT tsk.RSP_TSK_CODE, tsk.PRJ_PUB_CODE, tsk.RSP_PRJ_CODE, prj.RSP_PRJ_NAME, tsk.RSM_ID, tsk.RSM_NAME, tsk.RSM_ORG, prj.RSP_FREQ, pub.BRANCH_ID,pub.BRANCH_NAME, tsk.CHK_OBJ, tsk.ATT_NAME, tsk.CHK_DATE, tsk.PLM_RSV, tsk.REMARK, tsk.DUE_DATE, tsk.UPDATE_USER, (case when (tsk.RSP_TSK_STATUS='FINISHED'or tsk.RSP_TSK_STATUS='ODFINISHED') then tsk.UPDATE_TIMEelsenullend)as UPDATE_TIME, tsk.RSP_TSK_STATUS
FROM OBS.RSP_TASK tsk leftjoin OBS.RSP_PRJ_PUB pubon tsk.PRJ_PUB_CODE= pub.PRJ_PUB_CODE leftjoin OBS.RSP_PRJ prjon tsk.RSP_PRJ_CODE= prj.RSP_PRJ_CODE
WHERE 1=1AND tsk.RSM_IDIN (
select sta_idfrom OWK.EMP_STAFFwhere sta_id='157495'union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106075%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106110%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/112405%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/116955%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106085%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists (select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106095%') )
AND tsk.DUE_DATE>='2017-07-01'
AND tsk.DUE_DATE<='2017-07-31'
ORDERBYYEAR(tsk.CREATE_TIME)DESC, pub.BRANCH_IDDESC
抓出關于表OWK.EMP_STAFF_ORG部分的SQL
select sta_idfrom OWK.EMP_STAFFwhere sta_id='157495'union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106075%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106110%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/112405%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/116955%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106085%')union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand org.PTHlike'100001/105990/106095%')
用db2expln得到它的執(zhí)行計劃：
Optimizer Plan:
Rows
Operator
(ID)
Cost
6.79546
RETURN
( 1)
6214.79
|
6.79546
TBSCAN
( 2)
6214.79
|
6.79546
SORT
( 3)
6214.79
|
41.2667
UNION
( 4)
6214.77
+-----------------------+------------------------+-----------------+-----+-----------------------+---------------+-------------------+
6.79546 6.28938 6.79546 6.79546 6.79546 1 6.79546
HSJOIN HSJOIN HSJOIN HSJOIN HSJOIN IXSCAN HSJOIN
( 5) ( 8) (11) (14) (17) (20) (21)
1034.53 1034.53 1034.53 1034.53 1034.53 7.58089 1034.53
/ \ / \ / \ / \ / \ | / \
102296 1.08021 102296 0.999762 102296 1.08021 102296 1.08021 102296 1.08021 101993 102296 1.08021
TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN TBSCAN Index: TBSCAN TBSCAN
( 6) ( 7) ( 9) (10) (12) (13) (15) (16) (18) (19) OWK (22) (23)
293.6 735.957 293.6 735.957 293.6 735.957 293.6 735.957 293.6 735.957 P_STAFF_ID 293.6 735.957
| | | | | | | | | | | |
102296 16261 102296 16261 102296 16261 102296 16261 102296 16261 102296 16261
Table: Table: Table: Table: Table: Table: Table: Table: Table: Table: Table: Table:
OWK OWK OWK OWK OWK OWK OWK OWK OWK OWK OWK OWK
EMP_STAFF_ORG EMP_ORG EMP_STAFF_ORG EMP_ORG EMP_STAFF_ORG EMP_ORG EMP_STAFF_ORG EMP_ORG EMP_STAFF_ORG EMP_ORG EMP_STAFF_ORG EMP_ORG
Estimated Cost = 6214.787109
將SQL改編如下：
select sta_idfrom OWK.EMP_STAFFwhere sta_id='157495'union
select sta.sta_idfrom OWK.EMP_STAFF_ORG stawhere sta.MFLAG=1andexists
(select1from OWK.EMP_ORG orgwhere sta.ORG_CODE= org.ORG_CODEand
substr(org.PTH,1,20)in ('100001/105990/106075','100001/105990/106110','100001/105990/112405','100001/105990/116955','100001/105990/106085','100001/105990/106095'))
用db2expln得到新SQL的執(zhí)行計劃：
Optimizer Plan:
Rows
Operator
(ID)
Cost
40.2545
RETURN
( 1)
1041.11
|
40.2545
TBSCAN
( 2)
1041.11
|
40.2545
SORT
( 3)
1041.11
|
41.2545
UNION
( 4)
1041.09
/ \
40.2545 1
HSJOIN IXSCAN
( 5) ( 8)
1033.51 7.58089
/ \ |
102296 6.39886 101993
TBSCAN TBSCAN Index:
( 6) ( 7) OWK
293.6 734.935 P_STAFF_ID
| |
102296 16261
Table: Table:
OWK OWK
EMP_STAFF_ORG EMP_ORG
Estimated Cardinality = 40.254482
簡單改寫后，返回的結果集不變，但效率提高了很多?？梢奡QL語句
3. didisrvdb02-邏輯讀高問題：
邏輯讀高的SQL：
update (select TRN_STATUS,REAL_SERIAL
from DPAY.TAB_OUSYS_INFO1
where TRN_STATUS=:L0and CUST_ACCNO=:L1and REAL_SERIAL=:L2and ID>=:L3and ID<=:L4
orderby idascfetch first5000 rowsonly)
set TRN_STATUS=:L5 ,REAL_SERIAL=:L6
查看表 DPAY.TAB_OUSYS_INFO1上的索引：
索引名 索引包含的列
SQL160106192202630 +MERCH_DATE+MERCH_SERIAL
OUSYS1_INDEX_1 +ID+TRN_BATCH
OUSYS1_INDEX_2 +TRN_STATUS+CUST_ACCNO+REAL_SERIAL+SEND_FLAG
OUSYS1_INDEX_3 +TRN_STATUS+MERCH_DATE+ID
OUSYS1_INDEX_4 +ID
表的行數(shù)及索引的鍵值情況如下：
db2 "select count(1) from DPAY.TAB_OUSYS_INFO1"
2685595
db2 "select count(distinct SEND_FLAG) from DPAY.TAB_OUSYS_INFO1"
1
db2 "select count(distinct TRN_STATUS) from DPAY.TAB_OUSYS_INFO1"
4
db2 "select count(distinct CUST_ACCNO) from DPAY.TAB_OUSYS_INFO1"
1
db2 "select count(distinct REAL_SERIAL) from DPAY.TAB_OUSYS_INFO1"
1247
db2 "select count(distinct ID) from DPAY.TAB_OUSYS_INFO1"
2685074
db2 "select count(distinct MERCH_DATE) from DPAY.TAB_OUSYS_INFO1"
6
db2 "select count(distinct MERCH_SERIAL) from DPAY.TAB_OUSYS_INFO1"
2685339
從以上的數(shù)據(jù)可以看出，此表上的索引建立的很不合理。根據(jù)索引建立原則，我們應該選擇強鍵值列作為索引列，而且越強的越要越放在前面，所以此表上的索引應該做如下優(yōu)化：
l 對索引SQL160106192202630進行改造，使索引包含的列為+MERCH_SERIAL或+MERCH_SERIAL+MERCH_DATE，這樣根據(jù)MERCH_SERIAL檢索時也可以用到此索引。
l 索引OUSYS1_INDEX_1可以保留，但意義不大，因為ID已經(jīng)是強鍵值列。
l 對索引OUSYS1_INDEX_2進行改造，只保留REAL_SERIAL列，至少也應該將REAL_SERIAL列放在最前面。
l 索引OUSYS1_INDEX_3可以刪除，至少也應該將ID列放在最前面。
l 保留OUSYS1_INDEX_4索引。
數(shù)據(jù)庫活動時間為2016-01-06-20.19.41：
db2 "SELECT SUBSTR(DB_NAME, 1, 20) AS DB_NAME, DB_STATUS, SERVER_PLATFORM, DB_LOCATION, DB_CONN_TIME FROM SYSIBMADM.SNAPDB"
DB_NAME DB_STATUS SERVER_PLATFORM DB_LOCATION DB_CONN_TIME
-------------------------------------------------------------------------------------------------------------------------------- ---------------- --------------- ------------ --------------------------
DIDIPRI ACTIVE AIX64 LOCAL 2016-01-06-20.19.41.644178
檢查數(shù)據(jù)庫的索引使用情況：
db2 "SELECT VARCHAR(S.INDSCHEMA, 20) AS INDSCHEMA,VARCHAR(S.INDNAME, 20) AS INDNAME,T.DATA_PARTITION_ID, T.MEMBER,T.INDEX_SCANS,T.INDEX_ONLY_SCANS FROM TABLE(MON_GET_INDEX(null,null, -2)) as T, SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID and S.INDSCHEMA not like 'SYS%' ORDER BY INDEX_SCANS DESC"|more
數(shù)據(jù)庫中非系統(tǒng)索引共336個：
db2 "SELECT count(*) FROM TABLE(MON_GET_INDEX(null,null, -2)) as T, SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID and S.INDSCHEMA not like 'SYS%'"
336
有99個索引自數(shù)據(jù)庫激活以來從未使用：
db2 "SELECT count(*) FROM TABLE(MON_GET_INDEX(null,null, -2)) as T, SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID and S.INDSCHEMA not like 'SYS%' and T.INDEX_SCANS=0"
99
4. 問題分析及建議：
根據(jù)以上的問題分析，應用方面存在如下幾個問題：
l 只考慮功能的實現(xiàn)，對是否會造成鎖競爭，SQL語句執(zhí)行效率是否底下考慮不足。
l 數(shù)據(jù)庫存在大量的無效索引和不合理的索引。
幾點建議如下：
l 在數(shù)據(jù)庫報告中增加“從未使用的索引”項，并考慮刪除以節(jié)省空間和提高效率。
l 對開發(fā)人員進行鎖機制，SQL優(yōu)化，建立高效索引方面的培訓，以從源頭解決問題，減少運維壓力。
分享題目：近期一些典型的Case
分享地址：http://jinyejixie.com/article24/ipepje.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供域名注冊、定制網(wǎng)站、建站公司、移動網(wǎng)站建設、微信小程序、企業(yè)建站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 六個微信小程序的運營推廣方法你知道嗎？ 2014-11-20
- 你的微商城如何比得了我的微信小程序商城，可同步可更新? 2022-11-04
- 微信小程序能改善企業(yè)現(xiàn)狀的優(yōu)勢有哪些？ 2016-10-16
- 微信小程序如何使用如何開發(fā) 2021-04-07
- 微信小程序未來發(fā)展優(yōu)勢有哪些？ 2022-08-13
- 傳統(tǒng)企業(yè)借助APP應用轉型需要思考什么 2022-06-23
- 成都網(wǎng)站建設淺析微信小程序之商家加盟原因 2016-09-24
- 這4點告訴你微信小程序應該怎么設計 2021-02-22
- 微信小程序指向的“新零售模式”是什么樣 2022-11-15
- 微信小程序開發(fā)后應該如何運營？ 2020-12-16
- 微信小程序對商家的四大好處-大連微信開發(fā) 2022-07-14
- 一般人不知道的微信小程序8大功能，怪不得我生意比你的好！ 2022-11-10