生產(chǎn)某系統(tǒng)臨時表空間偶發(fā)性增長220G的問題分析與解決(終版)

生產(chǎn)某系統(tǒng)臨時表空間偶發(fā)性增長220G的問題分析與解決問題描述近日生產(chǎn)環(huán)境某系統(tǒng)臨時表空間迅速增長220G多，直到臨時表空間所在文件系統(tǒng)達到100%的情況，前段會話錯誤，回滾所有目標；此類事件發(fā)生多次，上一次發(fā)生在2個月前。生產(chǎn)環(huán)境中3個業(yè)務系統(tǒng)共用一套數(shù)據(jù)庫（Linux系統(tǒng)，數(shù)據(jù)版本V9.1.5）。每日半夜對其中某一系統(tǒng)自動跑批，自動跑批失敗后，第二天會人工干預，手動對前一日和當日的數(shù)據(jù)重新跑批。項目組反映該系統(tǒng)自動跑批時，沒有發(fā)生過任何問題，手動跑批時偶爾會出現(xiàn)臨時表空間迅速增長220G多的問題，項目組無法重現(xiàn)問題，相關SQL無大表查詢。臨時表空間滿后，回滾所有目標，會不斷釋放臨時空間，系統(tǒng)文件系統(tǒng)發(fā)生問題時抓取如下，已釋放近9G空間：文件系統(tǒng)容量已用可用已用%掛載點/dev/mapper/VolGroup00-LogVol0048G4.9G40G11%//dev/mapper/VolGroup00-LogVol0248G1.3G44G3%/opt/dev/cciss/c0d0p1965M25M891M3%/boottmpfs16G016G0%/dev/shm/dev/mapper/flsdbvg-dbdirl936G879G8.9G100%/data重建執(zhí)行計劃與問題分析此問題屬于偶發(fā)性問題，抓取SQL快照，查找到一條SQL有較多的臨時數(shù)據(jù)讀寫，臨時數(shù)據(jù)讀達到7334348次，Statementsorts和Statementsortoverflows為零，無索引讀；此外并無其他較大臨時數(shù)據(jù)讀的SQL，與項目組溝通確認這段時間確實在執(zhí)行該SQL。SQL部分快照如下所示：Numberofexecutions=1Rowsread=2742839Internalrowsupdated=0Rowswritten=14664357Statementsorts=0Statementsortoverflows=0Totalsorttime=0Bufferpooldatalogicalreads=8074Bufferpooldataphysicalreads=0Bufferpooltemporarydatalogicalreads=7334348Bufferpooltemporarydataphysicalreads=128Bufferpoolindexlogicalreads=0Bufferpoolindexphysicalreads=0Bufferpooltemporaryindexlogicalreads=0Bufferpooltemporaryindexphysicalreads=0Totalexecutiontime(sec.ms)=1995.748594Totalusercputime(sec.ms)=1310.840000Totalsystemcputime(sec.ms)=150.290000Statementtext=SELECTMRODS.ODS_RESGROUP.GROUPID,MRODS.ODS_RESGROUP.CODE,MRODS.ODS_RESRUN.RUNID,MRODS.ODS_RESAGGR.AGGRID,MRODS.ODS_RESRUN.POSDATE,MRODS.ODS_RESRUN.BASECCY,MRODS.ODS_RESRUN.RPTCCY,MRODS.ODS_RESAGGR.AGGR1,MRODS.ODS_RESAGGR.AGGR2,MRODS.ODS_RESAGGR.AGGR3,MRODS.ODS_RESAGGR.AGGR4,MRODS.ODS_RESAGGR.AGGR5,MRODS.ODS_RESAGGR.AGGR6,MRODS.ODS_RESAGGR.AGGR7,MRODS.ODS_RESAGGR.AGGR8,MRODS.ODS_RESAGGR.AGGR9,MRODS.ODS_RESCONTDEF.CODE,MRODS.ODS_RESCONTDEF.CONTDEFID,MRODS.ODS_RESCONT.AMOUNT,MRODS.ODS_SECP.CLPRICE_8,MRRPT.DIM_ETLDATADATE.ETLDATADATEFROMMRODS.ODS_RESGROUP,MRODS.ODS_RESRUN,MRODS.ODS_RESAGGR,MRODS.ODS_RESCONTDEF,MRODS.ODS_RESCONT,MRODS.ODS_SECP,MRRPT.DIM_DATE,MRRPT.DIM_ETLDATADATEWHEREMRODS.ODS_RESGROUP.GROUPID=MRODS.ODS_RESRUN.GROUPIDANDMRODS.ODS_RESRUN.RUNID=MRODS.ODS_RESAGGR.RUNIDANDMRODS.ODS_RESAGGR.AGGRID=MRODS.ODS_RESCONT.AGGRIDANDMRODS.ODS_RESCONT.CONTDEFID=MRODS.ODS_RESCONTDEF.CONTDEFIDANDMRODS.ODS_RESCONTDEF.RUNID=MRODS.ODS_RESAGGR.RUNIDANDMRODS.ODS_RESCONTDEF.CODE=MRODS.ODS_SECP.SECIDANDMRODS.ODS_RESGROUP.DATA_DATE=MRODS.ODS_RESRUN.DATA_DATEANDMRODS.ODS_RESRUN.DATA_DATE=MRODS.ODS_RESAGGR.DATA_DATEANDMRODS.ODS_RESAGGR.DATA_DATE=MRODS.ODS_RESCONTDEF.DATA_DATEANDMRODS.ODS_RESCONTDEF.DATA_DATE=MRODS.ODS_RESCONT.DATA_DATEANDMRODS.ODS_RESCONT.DATA_DATE=MRODS.ODS_SECP.DATA_DATEANDMRRPT.DIM_DATE.CALENDAR_DATE=MRODS.ODS_RESAGGR.DATA_DATEANDMRODS.ODS_SECP.EFFDATE=MRODS.ODS_RESRUN.posdateANDUPPER(MRODS.ODS_RESGROUP.CODE)='CASHFLOW_MARKET'AND(MRODS.ODS_RESAGGR.AGGR1ISNULLORMRODS.ODS_RESAGGR.AGGR1='')ANDMRODS.ODS_RESRUN.DATA_DATE<=MRRPT.DIM_ETLDATADATE.FILEDATEANDMRRPT.DIM_ETLDATADATE.LEVEL='base'通過快照查看共8個表做關聯(lián)查詢，統(tǒng)計信息日期為當天，統(tǒng)計值與實際值一致，8個表總大小370M。TABNAMECARDNPAGESFPAGESODS_RESRUN63391919ODS_SECP138239848164816ODS_RESCONT84170017011701ODS_RESGROUP001ODS_RESCONTDEF50808812461246ODS_RESAGGR103978441974197DIM_DATE74401932DIM_ETLDATADATE311觀察數(shù)據(jù)庫相關參數(shù)（如：SORTHEAP等）都是AUTOMATIC；數(shù)據(jù)和索引和BUFFERPOOL命中率都在90%以上，臨時表空間對應的bufferpool讀寫值較大，也說明了有SQL查詢存在sortheap溢出，具體如下所示：BufferpoolStatisticsforallbufferpools(whenBUFFERPOOLmonitorswitchisON):BPIDDatLRdsDatPRdsHitRatioTmpDatLRdsTmpDatPRdsHitRatio15983683112483899.79%789665343138292260.26%22718539252312684991.49%0000.00%根據(jù)項目組提供得信息，正常自動跑批情況下，不會出現(xiàn)文件系統(tǒng)達到100%的告警，手動跑批執(zhí)行SQL查詢時，有時會出現(xiàn)臨時表空間快速增長（不一定每次都發(fā)生），導致文件系統(tǒng)滿情況。根據(jù)跑批SQL快照截取，定位已出問題SQL。跑批正常時表ODS_RESGROUP有42條數(shù)據(jù)，手動跑批告警時查看ODS_RESGROUP表為0條，數(shù)據(jù)進一步分析需搭建測試環(huán)境，還原生產(chǎn)環(huán)境下SQL的執(zhí)行情況。2.1測試環(huán)境重新創(chuàng)建執(zhí)行計劃2.1.1收集生產(chǎn)統(tǒng)計信息和表數(shù)據(jù)該系統(tǒng)生產(chǎn)環(huán)境運行較長時間，已經(jīng)沒有測試環(huán)境，為還原生產(chǎn)環(huán)境SQL的執(zhí)行情況，需搭建和生產(chǎn)環(huán)境配置相同的環(huán)境，讓SQL執(zhí)行相同的執(zhí)行計劃。在理想的世界中，需要讓測試環(huán)境盡可能接近地匹配生產(chǎn)；也就是說，需要在兩個環(huán)境中使用完全相同的硬件、操作系統(tǒng)維護級別和配置、DB2級別和配置，以及在測試中使用與生產(chǎn)中相同的數(shù)據(jù)。然而，并非總是可以達到這種理想情況，測試機器硬件又很難與生產(chǎn)硬件匹配。我們可以在生產(chǎn)環(huán)境中導出影響執(zhí)行計劃生成的統(tǒng)計信息、環(huán)境參數(shù)及變量，在測試環(huán)境中導入，”欺騙”DB2優(yōu)化器，從而達到生成和生產(chǎn)環(huán)境相同的執(zhí)行計劃。db2look

實用程序可以用于達到該目標，收集生產(chǎn)環(huán)境變量和統(tǒng)計信息。重新創(chuàng)建優(yōu)化器問題的命令，生產(chǎn)環(huán)境中執(zhí)行如下：db2look-dKLBMR-l-ostorage.outdb2look-dKLBMR-f-fd-oconfig.outdb2look-dKLBMR-e-a-m-tMRODS.ODS_RESGROUP,MRODS.ODS_RESRUN,MRODS.ODS_RESAGGR,MRODS.ODS_RESCONTDEF,MRODS.ODS_RESCONT,MRODS.ODS_SECP,MRRPT.DIM_DATE,MRRPT.DIM_ETLDATADATE-otable.ddl下面是對以上對db2look命令中所用選項的描述：-l：生成數(shù)據(jù)庫布局。這是用于數(shù)據(jù)庫分區(qū)組、緩沖池和表空間的布局。-l

選項對于模擬生產(chǎn)環(huán)境十分重要。理想情況下，需要具有相同的緩沖池、數(shù)據(jù)庫分區(qū)組（如果處于多分區(qū)環(huán)境中）和表空間信息（包括臨時表空間）。但是，如果測試環(huán)境受到了內存約束，無法分配生產(chǎn)中所具有的大型緩沖池，那么就使用

db2fopt

命令，稍后將在本小節(jié)中詳細地討論該命令。下面是優(yōu)化器為表空間所使用的重要信息。這就是您需要確保在測試和生產(chǎn)中相同的信息。PREFETCHSIZE16EXTENTSIZE16OVERHEAD12.670000TRANSFERRATE0.180000-f：提取配置參數(shù)和注冊表變量。如果指定了該選項，就會忽略-wrapper和-server選項。-fd：為opt_buffpage和opt_sortheap

生成db2fopt語句，以及其他配置和注冊表設置。db2exfmt的輸出:UPDATEDBMCFGUSINGcpuspeed7.124525e-07;UPDATEDBMCFGUSINGintra_parallelNO;UPDATEDBMCFGUSINGcomm_bandwidth100.000000;UPDATEDBMCFGUSINGfederatedNO;UPDATEDBMCFGUSINGfed_noauthNO;db2foptfengxianupdateopt_buffpage100536;db2foptfengxianupdateopt_sortheap721;UPDATEDBCFGFORfengxianUSINGlocklist102400;UPDATEDBCFGFORfengxianUSINGdft_degree1;UPDATEDBCFGFORfengxianUSINGmaxlocks60;UPDATEDBCFGFORfengxianUSINGavg_appls1;UPDATEDBCFGFORfengxianUSINGstmtheap4096;UPDATEDBCFGFORfengxianUSINGdft_queryopt5;updatedbcfgFORfengxianusingDBHEAP2399updatedbcfgFORfengxianusingSTMTHEAP4096db2fopt命令告訴優(yōu)化器為“緩沖池大?。˙ufferpoolsize）”使用指定的值，而非將可用緩沖池變量的頁面加起來。由于測試系統(tǒng)上的內存約束，無法獲得大型的緩沖池，并且希望將大小配置得相同，實際上卻不是真正有這么大。使用將生成必要的

db2fopt

命令的

-fd

選項來告訴優(yōu)化器使用指定大小，而非基于對該數(shù)據(jù)庫可用的緩沖池進行計算。導出表數(shù)據(jù)，表數(shù)據(jù)是為以后SQL優(yōu)化所用，比較優(yōu)化前后的結果，若直接還原SQL的執(zhí)行計劃，則可以不用導出表數(shù)據(jù)。db2"exporttoMRODS.ODS_RESGROUP.delofdelselect*fromMRODS.ODS_RESGROUP"db2"exporttoMRODS.ODS_RESRUN.delofdelselect*fromMRODS.ODS_RESRUN"db2"exporttoMRODS.ODS_RESAGGR.delofdelselect*fromMRODS.ODS_RESAGGR"db2"exporttoMRODS.ODS_RESCONTDEF.delofdelselect*fromMRODS.ODS_RESCONTDEF"db2"exporttoMRODS.ODS_RESCONT.delofdelselect*fromMRODS.ODS_RESCONT"db2"exporttoMRODS.ODS_SECP.delofdelselect*fromMRODS.ODS_SECP"db2"exporttoMRRPT.DIM_DATE.delofdelselect*fromMRRPT.DIM_DATE"db2"exporttoMRRPT.DIM_ETLDATADATE.delofdelselect*fromMRRPT.DIM_ETLDATADATE"2.1.2搭建測試環(huán)境在測試環(huán)境上建庫時，必須保證具有和生產(chǎn)環(huán)境相同的字符集和區(qū)域，執(zhí)行生產(chǎn)中db2look命令生成的腳本和表數(shù)據(jù)。db2“createdatabasefengxianusingcodesetGBKterritorycncollateusingsystempagesize32768”db2-tvfstorage.outdb2-tvfconfig.outdb2-tvftable.ddldb2"improtfromMRODS.ODS_RESGROUP.delofdelinsertintoMRODS.ODS_RESGROUP"db2"improtfromMRODS.ODS_RESRUN.delofdelinsertintoMRODS.ODS_RESRUN"db2"improtfromMRODS.ODS_RESAGGR.delofdelinsertintoMRODS.ODS_RESAGGR"db2"improtfromMRODS.ODS_RESCONTDEF.delofdelinsertintoMRODS.ODS_RESCONTDEF"db2"improtfromMRODS.ODS_RESCONT.delofdelinsertintoMRODS.ODS_RESCONT"db2"improtfromMRODS.ODS_SECP.delofdelinsertintoMRODS.ODS_SECP"db2"improtfromMRRPT.DIM_DATE.delofdelinsertintoMRRPT.DIM_DATE"db2"improtfromMRRPT.DIM_ETLDATADATE.delofdelinsertintoMRRPT.DIM_ETLDATADATE"設置DB2COMM環(huán)境變量和SVCENAME等必要初始參數(shù)，重啟實例，連接數(shù)據(jù)庫，執(zhí)行/home/db2inst1/sqllib/misc/EXPLAIN.DDL。通過

db2exfmt命令

查看訪問計劃的輸出，觀察計劃頂部的下列內容，必須保證生產(chǎn)和測試環(huán)境顯示一致。DatabaseContext: Parallelism: None CPUSpeed: 7.124525e-07 CommSpeed: 100 BufferPoolsize: 100536 SortHeapsize: 697 DatabaseHeapsize: 2399 LockListsize: 102400 MaximumLockList: 60 AverageApplications: 1 LocksAvailable: 3932160PackageContext: SQLType: Dynamic OptimizationLevel: 5 Blocking: BlockAllCursors IsolationLevel: CursorStabilitySTATEMENT1SECTION201 QUERYNO: 1 QUERYTAG: CLP StatementType: Select Updatable: No Deletable: No QueryDegree: 1影響訪問計劃的注冊表設置，使db2exfmt導出的執(zhí)行計劃必須和生產(chǎn)一致，如下所示： 1)RETURN:(ReturnResult) CumulativeTotalCost: 18175.7 CumulativeCPUCost: 8.45606e+09 CumulativeI/OCost: 12019 CumulativeRe-TotalCost: 18168.1 CumulativeRe-CPUCost: 8.45601e+09 CumulativeRe-I/OCost: 12018 CumulativeFirstRowCost: 18175.7 EstimatedBufferpoolBuffers: 20 Arguments: BLDLEVEL:(Buildlevel) DB2v9.1.0.6:s081007 HEAPUSE:(MaximumStatementHeapUsage) 624Pages STMTHEAP:(Statementheapsize) 4096在測試環(huán)境生成跑批正常時的執(zhí)行計劃，測試環(huán)境導出的執(zhí)行計劃和生產(chǎn)中執(zhí)行計劃幾乎一樣，成功完成測試環(huán)境對生產(chǎn)環(huán)境SQL的模擬。將表ODS_RESGROUP數(shù)據(jù)清空時，測試執(zhí)行計劃和生產(chǎn)跑批失敗時的執(zhí)行計劃也一致。完整執(zhí)行計劃見附。2.2問題分析8個關聯(lián)表共370M，如何能使臨時表空間快速增加到220G？抓取出現(xiàn)問題時SQL的執(zhí)行計劃和自動跑批正常時的執(zhí)行計劃對比，出現(xiàn)問題時ODS_RESGROUP表為空，正常情況該表有42條數(shù)據(jù)，說明表數(shù)據(jù)量發(fā)生變化，平時該表數(shù)據(jù)不變。也就是說這個只有在跑批失敗時可能會出現(xiàn)不該變化的表發(fā)生了數(shù)據(jù)變化，也說明了觸發(fā)臨時表空間快速增長的條件是其中的8個表中某些關聯(lián)表數(shù)據(jù)發(fā)生改變時，導致DB2執(zhí)行了不同的執(zhí)行計劃。當ODS_RESGROUP表為空，多表關聯(lián)的結果為零，不可能導致臨時表空間快照增加，可能ODS_RESGROUP表為空之前有其他表數(shù)據(jù)變化。由于無法推斷出直接導致表空間暴漲時表數(shù)據(jù)的變化，為便于SQL分析，我們分析正常跑批時SQL的執(zhí)行計劃，8表關聯(lián)（HSJION、NLJION）會生產(chǎn)臨時中間數(shù)據(jù)，在正常跑批時SQL執(zhí)行應該比較低效，也已經(jīng)存在sortheap溢出，臨時表空間出現(xiàn)一定量的讀寫，已占用較多臨時表空間的磁盤空間，只是沒有達到告警閾值，沒引起人們關注。當數(shù)據(jù)發(fā)生變化時，有時會出現(xiàn)效率更低的執(zhí)行計劃，也就是說SQL低效執(zhí)行變得更低效，（同時可能多并發(fā)SQL也有臨時讀寫），臨時表空間繼續(xù)增加，最終導致文件系統(tǒng)滿。根據(jù)以上分析，我們分析跑批正常時得執(zhí)行計劃（以后均對跑批正常時的執(zhí)行計劃進行分析），提高SQL執(zhí)行效率，減少臨時數(shù)據(jù)溢出到表空間。通過對執(zhí)行計劃的分析，我們發(fā)現(xiàn)該執(zhí)行計劃有2個問題，第一是基值低估，第二是cost值較高。2.2.1基值低估我們從下到上逐一執(zhí)行訪問計劃的每一步關聯(lián)操作，觀察比較實際返回值和執(zhí)行計劃的返回值是否一致，若一致表示執(zhí)行計劃信息準確，若不一致表示基值低估。其中第9步的SQL返回值如下所示（完整執(zhí)行計劃見附）：db2"SELECTcount(*)FROMMRRPT.DIM_DATEASQ2,MRODS.ODS_RESCONTASQ4,MRODS.ODS_RESAGGRASQ6,MRODS.ODS_RESGROUPASQ8WHERE(Q2."CALENDAR_DATE"=Q6."DATA_DATE")AND(Q4."DATA_DATE"=Q6."DATA_DATE")AND(Q8."DATA_DATE"=Q6."DATA_DATE")AND(Q6."AGGRID"=Q4."AGGRID")AND(UPPER(Q8."CODE")='CASHFLOW_MARKET')AND(Q6."AGGR1"ISNULLOR(Q6."AGGR1"=''))"15163781record(s)selected.實際返回值為516378和執(zhí)行計劃中9064相差較大。從第9步開始基值低估，按執(zhí)行計劃再往上，每一步返回基值都存在低估（完整執(zhí)行計劃見附）。觀察第9步過濾因子，兩組條件謂詞的過濾因子都非常低。4)PredicateusedinJoinComparisonOperator:Equal(=)SubqueryInputRequired:NoFilterFactor:0.000134409PredicateText:(Q4."DATA_DATE"=Q2."CALENDAR_DATE")30)PredicateusedinJoinComparisonOperator:Equal(=)SubqueryInputRequired:NoFilterFactor:9.87151e-07PredicateText:(Q6."AGGRID"=Q4."AGGRID")單獨執(zhí)行條件語句，觀點實際返回結果，判斷過濾因子，執(zhí)行SQL如下所示：db2"selectDATA_DATE,count(*)fromMRODS.ODS_RESCONTgroupbyDATA_DATE"1db2"selectCALENDAR_DATE,coungt(*)fromMRRPT.DIM_DATEgroupbyCALENDAR_DATE"7440db2"selectcount(*)fromMRODS.ODS_RESCONTASQ4,MRODS.ODS_RESAGGRASQ6whereQ6."AGGRID"=Q4."AGGRID""856071db2"selectcount(*)fromMRODS.ODS_RESAGGRasQ6,MRODS.ODS_RESCONTASQ4whereQ6."AGGRID"=Q4."AGGRID""856071觀察第9步過濾因子，(Q4."DATA_DATE"=Q2."CALENDAR_DATE")為FilterFactor:0.000134409，(Q6."AGGRID"=Q4."AGGRID")為FilterFactor:9.87151e-07根據(jù)計算(Q4."DATA_DATE"=Q2."CALENDAR_DATE")的FF=1/MAX(1,7440)=0.0001344086(Q6."AGGRID"=Q4."AGGRID")的FF=1/MAX(856071,856071)=1.168123-06說明若表具有獨立性時，過濾因子是準確的。一般來說影響基值低估（或者過濾因子低估）有多種情況，總來說有一些幾種情況：統(tǒng)計信息沒有及時統(tǒng)計，表數(shù)據(jù)發(fā)生了較大變化，統(tǒng)計的時間為很久以前；表數(shù)據(jù)分布不均勻，統(tǒng)計信息分布或頻率的統(tǒng)計不準確；過濾因子低估；表和表之間的數(shù)據(jù)具有關聯(lián)性。針對基值低估的解決方法如下：更新統(tǒng)計信息；數(shù)據(jù)分布不均時，更新統(tǒng)計信息帶上分布和頻率參數(shù)，若統(tǒng)計仍沒有包含到條件數(shù)據(jù)可以適當增加NUM_FREQVALUES或NUM_QUANTILES的值；具有數(shù)據(jù)關聯(lián)性的表可采用列組統(tǒng)計信息（例如：RUNSTATSONTABLESKAPOOR.EMPLOYEEONALLCOLUMNS ANDCOLUMNS((JOB,WORKDEPT))WITHDISTRIBUTIONANDDETAILEDINDEXESALL）；創(chuàng)建統(tǒng)計視圖，糾正執(zhí)行計劃中某一步的基值低估，提高查詢執(zhí)行性能。根據(jù)上面提出的解決方面，重新執(zhí)行runstats帶distribution參數(shù)，分布和頻率可設置稍大些。新得執(zhí)行計劃沒有變化與之前一樣，此案例中調節(jié)分布和頻率參數(shù)后，基值依然低估問題。嘗試幾個列做列組的runstats，結果無改善。DB29.1不支持對視圖的runstat，后來嘗試在測試環(huán)境創(chuàng)建視圖，視圖沒有在執(zhí)行計劃中生效。至此關于基值低估的問題分析暫且結束。2.2.2COST值較高執(zhí)行計劃總cost偏高，尤其是IOcost值偏高，約占總cost值的2/3，觀察執(zhí)行計劃第3步（完整執(zhí)行計劃見附），觀察實際執(zhí)行結果：db2"SELECTcount(*)FROMMRRPT.DIM_DATEASQ2,MRODS.ODS_SECPASQ3,MRODS.ODS_RESCONTASQ4,MRODS.ODS_RESCONTDEFASQ5,MRODS.ODS_RESAGGRASQ6,MRODS.ODS_RESGROUPASQ8WHERE(Q2."CALENDAR_DATE"=Q6."DATA_DATE")AND(Q4."DATA_DATE"=Q6."DATA_DATE")AND(Q6."DATA_DATE"=Q3."DATA_DATE")AND(Q6."DATA_DATE"=Q5."DATA_DATE")AND(Q8."DATA_DATE"=Q6."DATA_DATE")AND(Q5."CODE"=Q3."SECID")AND(Q5."RUNID"=Q6."RUNID")AND(Q4."CONTDEFID"=Q5."CONTDEFID")AND(Q6."AGGRID"=Q4."AGGRID")AND(UPPER(Q8."CODE")='CASHFLOW_MARKET')AND(Q6."AGGR1"ISNULLOR(Q6."AGGR1"=''))"1308080522我們發(fā)現(xiàn)這6個表（該SQL8個關聯(lián)表總大小為370M）做關聯(lián)，執(zhí)行中間結果竟然達到3.08億條數(shù)據(jù)，超出了想象。在分析COST值較高問題之前，首先我們討論一下SQL操作對數(shù)據(jù)量增加的敏感度。當數(shù)據(jù)量增加時，對性能的影響程度因SQL不同而不同。有些SQL操作的性能對數(shù)據(jù)量增加的影響不大，有些SQL操作的性能則隨著數(shù)據(jù)量增加而線性增加，還有些SQL操作對大數(shù)據(jù)量增加會成指數(shù)上升，這中SQL盡量在生產(chǎn)中避免。受數(shù)據(jù)量增加的影響不大通常，基于主鍵的搜索受數(shù)據(jù)量的影響不大，無論是1000筆還是1,000,000筆不會有顯著差異。常見的B樹索引，其結構趨于扁平，效率很高，基于主鍵搜索單筆記錄的性能不會受到表大小的影響。受到數(shù)據(jù)量增加的線性影響最終用戶通常認為，要返回的記錄數(shù)量為原來的兩倍，則查詢會花更多的時間。實際上，許多SQL操作花了兩倍的執(zhí)行時間，用戶卻沒有意識到，就像通過全表掃描逐一返回記錄時發(fā)生的情況一樣?？紤]聚合函數(shù)，例如計算max()一定只返回單一記錄，但DBMS所操作的記錄數(shù)可能很多，不過最終用戶只看到單一的記錄被返回，所以他們會抱怨性能隨著時間而下降。確保情況不會變糟的唯一方法，就是使用另一個條件（例如日期范圍），對要處理的記錄數(shù)量設定上限。設定上限能讓數(shù)據(jù)量保持在控制范圍內。對于max()的例子，可以查詢給定日期之后的最大值，而不是所有值中的最大值。增加查詢條件不是單純的技術問題，還依賴于業(yè)務需求，但限定查詢范圍作為可選手段，值得在設計時進行討論。受到數(shù)據(jù)量增加的非線性影響數(shù)據(jù)量增加時，排序操作所收的影響比掃描操作還大，因為排序是復雜操作，一般而言需要多遍處理。對100條隨機的記錄排序，所需成本并不是10條記錄的10倍，而是大約20倍。排序1000條記錄所需成本，比排序10條記錄平均高300倍。然而在實際中，記錄很少是隨機存儲的，即使沒有使用“聚集索引”也是如此。DBMS有時使用有序索引，以預期的順序讀取紀錄，而不是先讀取后排序，讀取較大的有序集合時，性能降低一點并不奇怪。但排序性能降低常是間歇發(fā)生的，因為較小型的排序將全部在內存中執(zhí)行，而較大型的排序（涉及多個有序子集的合并）則需要經(jīng)有序子集臨時存儲到慢得多的硬盤中。通過調整分配給排序的內存數(shù)量來改善排序密集型操作的性能，是常見且有效的調優(yōu)技巧[1]。可見SQL操作與數(shù)據(jù)量成線性增長和非線性指數(shù)型增長的，隨著時間的增加，執(zhí)行效率會越來也低，執(zhí)行時間會越來越長，這部分也是我們經(jīng)常調優(yōu)的部分。解決方法為減少IOcost和NLJOIN的排序表掃描，我們可以通過db2advis推薦合適索引，挑選其中對執(zhí)行結果影響較大的索引。通過創(chuàng)建高效索引，減少多表關聯(lián)時全表掃描，減少排序和中間臨時數(shù)據(jù)在sortheap中的溢出，減少臨時表空間的的讀寫和占用。根據(jù)執(zhí)行計劃選出四個最能有效提高查詢的索引，具體如下：CREATEINDEXMRODS.IDX_ODS_RESCONTDEFONMRODS.ODS_RESCONTDEF("RUNID"ASC,"CODE"ASC,"DATA_DATE"ASC,"CONTDEFID"ASC)ALLOWREVERSESCANSRUNSTATSONTABLEMRODS.ODS_RESCONTDEFFORINDEXMRODS.IDX_ODS_RESCONTDEFCREATEINDEXMRODS.IDX_ODS_SECPONMRODS.ODS_SECP("EFFDATE"ASC,"SECID"ASC)ALLOWREVERSESCANSRUNSTATSONTABLEMRODS.ODS_SECPFORINDEXMRODS.IDX_ODS_SECPCREATEINDEXMRODS.IDX_ODS_RESAGGRONMRODS.ODS_RESAGGR("AGGR1"ASC)ALLOWREVERSESCANSRUNSTATSONTABLEMRODS.ODS_RESAGGRFORINDEXMRODS.IDX_ODS_RESAGGRCREATEINDEXMRODS.IDX_ODS_RESCONTONMRODS.ODS_RESCONT("CONTDEFID"ASC,"DATA_DATE"ASC,"AMOUNT"ASC,"AGGRID"ASC)ALLOWREVERSESCANSRUNSTATSONTABLEMRODS.ODS_RESCONTFORINDEXMRODS.IDX_ODS_RESCONT測試結果測試環(huán)境對創(chuàng)建索引前后SQL執(zhí)行時間和臨時表空間占用進行測試，以創(chuàng)建索引前SQL執(zhí)行為例，第一個窗口執(zhí)行該SQL并記錄執(zhí)行時間：$timedb2–tvf1.sql>1.out第二個窗口監(jiān)控臨時表空間增長情況：$db2pd-dfengxian-tab-repeat1|grep0x078000003F434E60|grep0x00000000|awk'{print$5}'>temptabspace.log抓取UsedPgs列的頁數(shù)變化（數(shù)據(jù)頁為32K），取最高值1596132*32/1024=49879.125M0x078000003F434E6015939360x078000003F434E6015945970x078000003F434E6015954820x078000003F434E6015964870x078000003F434E6015961270x078000003F434E6015961320x078000003F434E6015961320x078000003F434E6014360850x078000003F434E6014360850x078000003F434E601276040測試結果如下：SQL時間臨時表空間大小(M)創(chuàng)建索引前1h9m27.70s49879創(chuàng)建索引后1m0.55s45測試結果表明SQL的執(zhí)行時間和臨時表空間的使用都極大得減少，臨時表空間的使用從49879M減少到45M，大大提高SQL的執(zhí)行效率?？偨Y優(yōu)化工作要根據(jù)業(yè)務需求，從實際出發(fā)，進行分析與判斷；調優(yōu)無止境，需要一定的人力成本和時間成本，隨著調優(yōu)工作不斷深入，人力和時間成本也會不斷增加，有些時候可能付出與回報不成比例。在本案例中，執(zhí)行計劃中的基值存在低估問題，最終沒有直接去解決，而是解決問題的關鍵部分；臨時表空間偶發(fā)性增長，通過索引減少sortheap大量溢出到臨時表空間是優(yōu)化方向，從實際結果來看，完全滿足業(yè)務需求，也避免此類問題再次發(fā)生。此案例中，對于偶發(fā)性的SQL問題，偶發(fā)具體觸發(fā)條件無法重現(xiàn)，可以轉變思維，分析正常情況下SQL執(zhí)行情況，偶發(fā)只是低效執(zhí)行下更糟糕的表現(xiàn)。大家閱讀后能有所收獲和啟發(fā)，就是我最大得快樂。參考：DB2SQL性能(數(shù)據(jù)倉庫開發(fā)系列培訓)[1]趙堅密附：由于篇幅有限，此處只附上正常跑批時SQL執(zhí)行計劃，具體如下所示：RowsRETURN(1)CostI/O|0.0104146NLJOIN(2)18175.712019/+\0.01388610.75HSJOINTBSCAN(3)(16)18168.17.60297120181/+\|6348506.4743TBSCANHSJOINTABLE:MRRPT(4)(5)DIM_ETLDATADATE151.63218013.8Q11911999|/+\63481.3874e+060.969581TABLE:MRODSTBSCANHSJOINODS_RESRUN(6)(7)Q76550.91110548337166|/+\1.3874e+065101364962.41TABLE:MRODSTBSCANHSJOIN