生产某系统临时表空间偶发性增长220G的问题分析与解决(终版)

生产某系统临时表空间偶发性增长220G的问题分析与解决问题描述近日生产环境某系统临时表空间迅速增长220G多，直到临时表空间所在文件系统达到100%的情况，前段会话错误，回滚所有目标；此类事件发生多次，上一次发生在2个月前。生产环境中3个业务系统共用一套数据库（Linux系统，数据版本V9.1.5）。每日半夜对其中某一系统自动跑批，自动跑批失败后，第二天会人工干预，手动对前一日和当日的数据重新跑批。项目组反映该系统自动跑批时，没有发生过任何问题，手动跑批时偶尔会出现临时表空间迅速增长220G多的问题，项目组无法重现问题，相关SQL无大表查询。临时表空间满后，回滚所有目标，会不断释放临时空间，系统文件系统发生问题时抓取如下，已释放近9G空间：文件系统容量已用可用已用%挂载点/dev/mapper/VolGroup00-LogVol0048G4.9G40G11%//dev/mapper/VolGroup00-LogVol0248G1.3G44G3%/opt/dev/cciss/c0d0p1965M25M891M3%/boottmpfs16G016G0%/dev/shm/dev/mapper/flsdbvg-dbdirl936G879G8.9G100%/data重建执行计划与问题分析此问题属于偶发性问题，抓取SQL快照，查找到一条SQL有较多的临时数据读写，临时数据读达到7334348次，Statementsorts和Statementsortoverflows为零，无索引读；此外并无其他较大临时数据读的SQL，与项目组沟通确认这段时间确实在执行该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个表做关联查询，统计信息日期为当天，统计值与实际值一致，8个表总大小370M。TABNAMECARDNPAGESFPAGESODS_RESRUN63391919ODS_SECP138239848164816ODS_RESCONT84170017011701ODS_RESGROUP001ODS_RESCONTDEF50808812461246ODS_RESAGGR103978441974197DIM_DATE74401932DIM_ETLDATADATE311观察数据库相关参数（如：SORTHEAP等）都是AUTOMATIC；数据和索引和BUFFERPOOL命中率都在90%以上，临时表空间对应的bufferpool读写值较大，也说明了有SQL查询存在sortheap溢出，具体如下所示：BufferpoolStatisticsforallbufferpools(whenBUFFERPOOLmonitorswitchisON):BPIDDatLRdsDatPRdsHitRatioTmpDatLRdsTmpDatPRdsHitRatio15983683112483899.79%789665343138292260.26%22718539252312684991.49%0000.00%根据项目组提供得信息，正常自动跑批情况下，不会出现文件系统达到100%的告警，手动跑批执行SQL查询时，有时会出现临时表空间快速增长（不一定每次都发生），导致文件系统满情况。根据跑批SQL快照截取，定位已出问题SQL。跑批正常时表ODS_RESGROUP有42条数据，手动跑批告警时查看ODS_RESGROUP表为0条，数据进一步分析需搭建测试环境，还原生产环境下SQL的执行情况。2.1测试环境重新创建执行计划2.1.1收集生产统计信息和表数据该系统生产环境运行较长时间，已经没有测试环境，为还原生产环境SQL的执行情况，需搭建和生产环境配置相同的环境，让SQL执行相同的执行计划。在理想的世界中，需要让测试环境尽可能接近地匹配生产；也就是说，需要在两个环境中使用完全相同的硬件、操作系统维护级别和配置、DB2级别和配置，以及在测试中使用与生产中相同的数据。然而，并非总是可以达到这种理想情况，测试机器硬件又很难与生产硬件匹配。我们可以在生产环境中导出影响执行计划生成的统计信息、环境参数及变量，在测试环境中导入，”欺骗”DB2优化器，从而达到生成和生产环境相同的执行计划。db2look

实用程序可以用于达到该目标，收集生产环境变量和统计信息。重新创建优化器问题的命令，生产环境中执行如下：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：生成数据库布局。这是用于数据库分区组、缓冲池和表空间的布局。-l

选项对于模拟生产环境十分重要。理想情况下，需要具有相同的缓冲池、数据库分区组（如果处于多分区环境中）和表空间信息（包括临时表空间）。但是，如果测试环境受到了内存约束，无法分配生产中所具有的大型缓冲池，那么就使用

db2fopt

命令，稍后将在本小节中详细地讨论该命令。下面是优化器为表空间所使用的重要信息。这就是您需要确保在测试和生产中相同的信息。PREFETCHSIZE16EXTENTSIZE16OVERHEAD12.670000TRANSFERRATE0.180000-f：提取配置参数和注册表变量。如果指定了该选项，就会忽略-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命令告诉优化器为“缓冲池大小（Bufferpoolsize）”使用指定的值，而非将可用缓冲池变量的页面加起来。由于测试系统上的内存约束，无法获得大型的缓冲池，并且希望将大小配置得相同，实际上却不是真正有这么大。使用将生成必要的

db2fopt

命令的

-fd

选项来告诉优化器使用指定大小，而非基于对该数据库可用的缓冲池进行计算。导出表数据，表数据是为以后SQL优化所用，比较优化前后的结果，若直接还原SQL的执行计划，则可以不用导出表数据。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搭建测试环境在测试环境上建库时，必须保证具有和生产环境相同的字符集和区域，执行生产中db2look命令生成的脚本和表数据。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环境变量和SVCENAME等必要初始参数，重启实例，连接数据库，执行/home/db2inst1/sqllib/misc/EXPLAIN.DDL。通过

db2exfmt命令

查看访问计划的输出，观察计划顶部的下列内容，必须保证生产和测试环境显示一致。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导出的执行计划必须和生产一致，如下所示： 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在测试环境生成跑批正常时的执行计划，测试环境导出的执行计划和生产中执行计划几乎一样，成功完成测试环境对生产环境SQL的模拟。将表ODS_RESGROUP数据清空时，测试执行计划和生产跑批失败时的执行计划也一致。完整执行计划见附。2.2问题分析8个关联表共370M，如何能使临时表空间快速增加到220G？抓取出现问题时SQL的执行计划和自动跑批正常时的执行计划对比，出现问题时ODS_RESGROUP表为空，正常情况该表有42条数据，说明表数据量发生变化，平时该表数据不变。也就是说这个只有在跑批失败时可能会出现不该变化的表发生了数据变化，也说明了触发临时表空间快速增长的条件是其中的8个表中某些关联表数据发生改变时，导致DB2执行了不同的执行计划。当ODS_RESGROUP表为空，多表关联的结果为零，不可能导致临时表空间快照增加，可能ODS_RESGROUP表为空之前有其他表数据变化。由于无法推断出直接导致表空间暴涨时表数据的变化，为便于SQL分析，我们分析正常跑批时SQL的执行计划，8表关联（HSJION、NLJION）会生产临时中间数据，在正常跑批时SQL执行应该比较低效，也已经存在sortheap溢出，临时表空间出现一定量的读写，已占用较多临时表空间的磁盘空间，只是没有达到告警阈值，没引起人们关注。当数据发生变化时，有时会出现效率更低的执行计划，也就是说SQL低效执行变得更低效，（同时可能多并发SQL也有临时读写），临时表空间继续增加，最终导致文件系统满。根据以上分析，我们分析跑批正常时得执行计划（以后均对跑批正常时的执行计划进行分析），提高SQL执行效率，减少临时数据溢出到表空间。通过对执行计划的分析，我们发现该执行计划有2个问题，第一是基值低估，第二是cost值较高。2.2.1基值低估我们从下到上逐一执行访问计划的每一步关联操作，观察比较实际返回值和执行计划的返回值是否一致，若一致表示执行计划信息准确，若不一致表示基值低估。其中第9步的SQL返回值如下所示（完整执行计划见附）：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和执行计划中9064相差较大。从第9步开始基值低估，按执行计划再往上，每一步返回基值都存在低估（完整执行计划见附）。观察第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")单独执行条件语句，观点实际返回结果，判断过滤因子，执行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根据计算(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说明若表具有独立性时，过滤因子是准确的。一般来说影响基值低估（或者过滤因子低估）有多种情况，总来说有一些几种情况：统计信息没有及时统计，表数据发生了较大变化，统计的时间为很久以前；表数据分布不均匀，统计信息分布或频率的统计不准确；过滤因子低估；表和表之间的数据具有关联性。针对基值低估的解决方法如下：更新统计信息；数据分布不均时，更新统计信息带上分布和频率参数，若统计仍没有包含到条件数据可以适当增加NUM_FREQVALUES或NUM_QUANTILES的值；具有数据关联性的表可采用列组统计信息（例如：RUNSTATSONTABLESKAPOOR.EMPLOYEEONALLCOLUMNS ANDCOLUMNS((JOB,WORKDEPT))WITHDISTRIBUTIONANDDETAILEDINDEXESALL）；创建统计视图，纠正执行计划中某一步的基值低估，提高查询执行性能。根据上面提出的解决方面，重新执行runstats带distribution参数，分布和频率可设置稍大些。新得执行计划没有变化与之前一样，此案例中调节分布和频率参数后，基值依然低估问题。尝试几个列做列组的runstats，结果无改善。DB29.1不支持对视图的runstat，后来尝试在测试环境创建视图，视图没有在执行计划中生效。至此关于基值低估的问题分析暂且结束。2.2.2COST值较高执行计划总cost偏高，尤其是IOcost值偏高，约占总cost值的2/3，观察执行计划第3步（完整执行计划见附），观察实际执行结果：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我们发现这6个表（该SQL8个关联表总大小为370M）做关联，执行中间结果竟然达到3.08亿条数据，超出了想象。在分析COST值较高问题之前，首先我们讨论一下SQL操作对数据量增加的敏感度。当数据量增加时，对性能的影响程度因SQL不同而不同。有些SQL操作的性能对数据量增加的影响不大，有些SQL操作的性能则随着数据量增加而线性增加，还有些SQL操作对大数据量增加会成指数上升，这中SQL尽量在生产中避免。受数据量增加的影响不大通常，基于主键的搜索受数据量的影响不大，无论是1000笔还是1,000,000笔不会有显著差异。常见的B树索引，其结构趋于扁平，效率很高，基于主键搜索单笔记录的性能不会受到表大小的影响。受到数据量增加的线性影响最终用户通常认为，要返回的记录数量为原来的两倍，则查询会花更多的时间。实际上，许多SQL操作花了两倍的执行时间，用户却没有意识到，就像通过全表扫描逐一返回记录时发生的情况一样。考虑聚合函数，例如计算max()一定只返回单一记录，但DBMS所操作的记录数可能很多，不过最终用户只看到单一的记录被返回，所以他们会抱怨性能随着时间而下降。确保情况不会变糟的唯一方法，就是使用另一个条件（例如日期范围），对要处理的记录数量设定上限。设定上限能让数据量保持在控制范围内。对于max()的例子，可以查询给定日期之后的最大值，而不是所有值中的最大值。增加查询条件不是单纯的技术问题，还依赖于业务需求，但限定查询范围作为可选手段，值得在设计时进行讨论。受到数据量增加的非线性影响数据量增加时，排序操作所收的影响比扫描操作还大，因为排序是复杂操作，一般而言需要多遍处理。对100条随机的记录排序，所需成本并不是10条记录的10倍，而是大约20倍。排序1000条记录所需成本，比排序10条记录平均高300倍。然而在实际中，记录很少是随机存储的，即使没有使用“聚集索引”也是如此。DBMS有时使用有序索引，以预期的顺序读取纪录，而不是先读取后排序，读取较大的有序集合时，性能降低一点并不奇怪。但排序性能降低常是间歇发生的，因为较小型的排序将全部在内存中执行，而较大型的排序（涉及多个有序子集的合并）则需要经有序子集临时存储到慢得多的硬盘中。通过调整分配给排序的内存数量来改善排序密集型操作的性能，是常见且有效的调优技巧[1]。可见SQL操作与数据量成线性增长和非线性指数型增长的，随着时间的增加，执行效率会越来也低，执行时间会越来越长，这部分也是我们经常调优的部分。解决方法为减少IOcost和NLJOIN的排序表扫描，我们可以通过db2advis推荐合适索引，挑选其中对执行结果影响较大的索引。通过创建高效索引，减少多表关联时全表扫描，减少排序和中间临时数据在sortheap中的溢出，减少临时表空间的的读写和占用。根据执行计划选出四个最能有效提高查询的索引，具体如下：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测试结果测试环境对创建索引前后SQL执行时间和临时表空间占用进行测试，以创建索引前SQL执行为例，第一个窗口执行该SQL并记录执行时间：$timedb2–tvf1.sql>1.out第二个窗口监控临时表空间增长情况：$db2pd-dfengxian-tab-repeat1|grep0x078000003F434E60|grep0x00000000|awk'{print$5}'>temptabspace.log抓取UsedPgs列的页数变化（数据页为32K），取最高值1596132*32/1024=49879.125M0x078000003F434E6015939360x078000003F434E6015945970x078000003F434E6015954820x078000003F434E6015964870x078000003F434E6015961270x078000003F434E6015961320x078000003F434E6015961320x078000003F434E6014360850x078000003F434E6014360850x078000003F434E601276040测试结果如下：SQL时间临时表空间大小(M)创建索引前1h9m27.70s49879创建索引后1m0.55s45测试结果表明SQL的执行时间和临时表空间的使用都极大得减少，临时表空间的使用从49879M减少到45M，大大提高SQL的执行效率。总结优化工作要根据业务需求，从实际出发，进行分析与判断；调优无止境，需要一定的人力成本和时间成本，随着调优工作不断深入，人力和时间成本也会不断增加，有些时候可能付出与回报不成比例。在本案例中，执行计划中的基值存在低估问题，最终没有直接去解决，而是解决问题的关键部分；临时表空间偶发性增长，通过索引减少sortheap大量溢出到临时表空间是优化方向，从实际结果来看，完全满足业务需求，也避免此类问题再次发生。此案例中，对于偶发性的SQL问题，偶发具体触发条件无法重现，可以转变思维，分析正常情况下SQL执行情况，偶发只是低效执行下更糟糕的表现。大家阅读后能有所收获和启发，就是我最大得快乐。参考：DB2SQL性能(数据仓库开发系列培训)[1]赵坚密附：由于篇幅有限，此处只附上正常跑批时SQL执行计划，具体如下所示：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