Oracle数据库性能优化碎片整理

1、1系统问题XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问 题，涉及到数据库和ETL.问题一：表空间增长太快，每个月需增加 35G空间。问题二：ETL JOg经常导致数据库产生表空间不足错误。2系统优化分析分析思路要解决表空间的问题，我们必须搞清楚下面几个问题：思路一：真正每个月数据仓库增量是多少空间？目的：得出一个正确的月表空间增长量 。思路二：目前的数据仓库表空间是是如何分布的。目的：找出那些对象是最占空间，分析其合理性 。分析过程要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的 oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。脚本一compu

2、te statistics;compute statistics;compute statistics;analyze table SA_IMS_PRODUCT_GROUP analyze table SA_CONSUMP_ACT_DELanalyze table SA_FINANCE_ACTanalyze table SA_CONSUMP_TGT_DELanalyze table SA_FACT_ISanalyze table SA_CPAanalyze table SA_REF_TERR_ALIGNMENT_DELanalyze table SA_IMS_MTHLC_BKanalyze t

3、able SA_IMS_CHPAanalyze table SA_FINANCE_PNLanalyze table SA_CUST_TARG_SEGanalyze table SA_CONSUMP_ACTanalyze table SA_FINANCE_BSanalyze table SA_FINANCE_BGT_QTYanalyze table SA_CONSUMP_ACT0423analyze table SA_CALLSanalyze table SA_COMPANY_DAILY_SALES_ALLanalyze table SA_IMS_MTHLCanalyze table SA_IM

4、S_MTHUSanalyze table SA_CONSUMP_TGTanalyze table TEST_TABLEanalyze table SA_DOCTOR_CYCLE_EXTRACTanalyze table SA_EXCHANGE_ACTanalyze table SA_IMS_MTHSTanalyze table SA_FINANCE_CONCUR_DETAILanalyze table WK_SA_CPAanalyze table SA_REF_TERR_ALIGNMENTanalyze table SA_CONSUMP_TGT0316analyze table SA_CUST

5、OMERanalyze table SA_CUSTanalyze table SA_HKAPIanalyze table SA_CONSUMP_TGT_AMTanalyze table SA_CUST0423analyze table SA_COMMUNITY_TGTanalyze table SA_CM_WORKING_DATEanalyze table SA_CM_IN_MARKET_SALES_CUanalyze table SA_DASH_SFEanalyze table SA_CPA_TERRanalyze table IDX_SA_CUSTanalyze table SA_REF_

6、EMP_TERRcompute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute

7、 statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;com

8、pute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;analyze table SA_CM_IN_MARKET_SALES_OCM analyze table SA

9、_COMPANY_MONTHLY_SALES analyze table SA_MAP_YEARMONTH_RATE analyze table SA_FINANCE_ACT_BPCS_TEST analyze table SA_REF_EMP_TERR0413 analyze table SA_FINANCE_ACT_BPCS analyze table IDX$_143D0001analyze table SA_COMPANY_DAILY_SALES_ALL_23 compute statistics;analyze table SA_COMMUNITY_TGT_AMTcompute st

10、atistics;analyze table SA_DASH_MONTHLY_MAT_SALES compute statistics;analyze table SA_DASH_ATTRITIONanalyze table SA_DASH_MARKET_SHAREanalyze table SA_CORPanalyze table SA_COMMUNITY_ACTcompute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;co

11、mpute statistics;compute statistics;compute statistics;compute statistics;analyze table SA_CM_IN_MARKET_SALES_CU_DELanalyze table WK_SA_COMPETITOR_PRODUCTanalyze table SA_IMS_ANTI_HYPER_TESTanalyze table SA_TERRITORYanalyze table TEST_CUSTOMER_TGTanalyze table SA_COMPETITOR_PRODUCTanalyze table SA_C

12、M_IN_MARKET_SALES_OCM_DEL compute statistics;analyze table SA_COMPANY_DAILY_SALES analyze table SA_REF_MR_CORP analyze table SA_IS_MATERIAL analyze table SA_IS_KEY_MESSAGE analyze table SA_DRIVER_REASON analyze table SA_REF_MR_CUST analyze table SA_BARRIER_REASON analyze table SA_ACCOUNT analyze tab

13、le SA_REF_MR_PROD analyze table SA_REF_VENDOR_EMP analyze table SA_FINANCE_ACT_ADJUSTMENT analyze table SA_RANKING_MESSAGEanalyze table SA_TCanalyze table SA_CUST_PARENTcompute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistic

14、s;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;analyze table SA_EXCHANGE_RATE_ACT_MTH compute statistics;analyze table SA_EXCHANGE_RATEanalyze table SA_DASH_GROWTH_BUBBLEanalyze table SA_COST_CENTERanalyze table

15、PM_KEYanalyze table SA_CM_REF_TERR_OCManalyze table SA_CM_REF_TERR_CUanalyze table SA_BPCS_TO_ISMIanalyze table PRODUCTanalyze table SA_SHIFT_LEVELanalyze table SA_SFE_VARIABLESanalyze table SA_PRODUCTanalyze table SA_PATIENT_TYPE_ENanalyze table SA_MR_KEY_PRODUCTanalyze table SA_MAP_TEAM_BRANDcompu

16、te statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;analyze table SA_MAP

17、_CUSTOMERanalyze table SA_MAP_AGGRanalyze table SA_LOCATIONanalyze table SA_INCREMENTAL_SHIFTanalyze table SA_IMS_CITYanalyze table SA_TGT_FREQanalyze table SA_TGT_CALLSanalyze table SA_FINANCE_ANPcompute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute

18、statistics;compute statistics;analyze table SA_COMPANY_DAILY_SALES_23compute statistics;analyze table SA_GEOGRAPHYcompute statistics;analyze table SA_MAP_PONUMBER_BPCSTERRCODE compute statistics;analyze table SA_MAP_SAP_BPCS_CUST analyze table PK_SA_MAP_SAP_BPCS_CUST analyze table SA_MAP_SAP_BPCS_SK

19、U analyze table PK_SA_MAP_SAP_BPCS_SKU analyze table SA_REF_DAY analyze table STAGEPLANanalyze table SA_SPLIT_HOSPTIALanalyze table SA_USAGE_LEVELanalyze table TEST_CUSTOMERanalyze table SA_NEW_USAGE_LEVEL analyze table SA_PROD_GROUP_NEW通过表分析，我们可以得到数据仓库中每个表的记录行数,compute statistics;compute statistics

20、;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;BLOCK数，EMPTY BLOCKSanalyze table PK_SA_MAP_PONUMBER_BPCSTERRCOD:ompute statistics;数等等关键的数据分布数据，分析后，这些数据会存放在系统表，USER_TABLESmUSER_

21、SEGMENT阱。通过对这些系统查询，我们可以得到整个数据库的数据分布情况，从 而为分析问题原因提供充足基础。执行下面的脚本，可以得到一个数据库的数据分布报告:脚本二SELECT SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE,GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS,BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MA

22、RK, AVG_USED_BLOCKS,CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,N,Y) CAN_EXTEND_SPACE,NEXT_EXTENT, MAX_FREE_SPACE,O_TABLESPACE_NAME TABLESPACE_NAMEFROM(SELECT A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS

23、EMPTY_BLOCKS,A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,DECODE( ROUND(B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)/C.BLOCK_SIZE, 0), 0, 1,ROUND(B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)/C.BLOCK_SIZE, 0) )+ 2 AVG_USED_BLOCKS,ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1), 2) CHAIN_PER

24、,ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS, A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME FROM USER_SEGMENTS A, USER_TABLES B, user_tablespaces C WHERESEGMENT_NAME = TABLE_NAME andSEGMENT_TYPE = TABLE ANDB.TABLESPACE_NAME

25、= C.TABLESPACE_NAMEUNION ALLSELECT SEGMENT_NAME | . | B.PARTITION_NAME, SEGMENT_TYPE, BYTES,B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,DECODE(ROUND(B.AVG_ROW_LEN B.NUM_ROWS * (1 + (B.PCT_FREE/100)/C.BLOCK_SIZE, 0), 0, 1,ROUND(B.AVG_ROW_LEN * B.NUM_ROWS

26、 * (1 + (B.PCT_FREE/100)/C.BLOCK_SIZE, 0) )+ 2 AVG_USED_BLOCKS,ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1), 2) CHAIN_PER,ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,B.TABLESPACE_NAME O_TABLESPACE_NAMEFROM U

27、SER_SEGMENTS A,USER_TAB_PARTITIONS B,USER_TABLESPACES C,USER_TABLES DWHERESEGMENT_NAME = B.TABLE_NAME andSEGMENT_TYPE = TABLE PARTITION ANDB.TABLESPACE_NAME = C.TABLESPACE_NAME ANDD.TABLE_NAME = B.TABLE_NAME ANDA.PARTITION_NAME = B.PARTITION_NAME),(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES)

28、MAX_FREE_SPACEFROM USER_FREE_SPACEGROUP BY TABLESPACE_NAME)WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME ANDGREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2),0) 2AND BLOCKS 1ORDER BY 4 DESC, 3 DESC, 2 ASC;运行脚本二后，我们以生产环境的STAGE SCHEM的例。得到报告如下:报告的各列含义如下：WASTE_PE哙间浪费比率，实际用

29、到的数据块 /分配给该表的数据块。TABLE_KB 表占空间大小，以 KB为单位。NUM_ROWS表中记录行数。BLOCK S分配给该表的数据块数。EMPTY_BLOCKfi分配给该表但尚未使用的数据块。HIGHWATER_MA RK的高水位标志。AVG_USED_BLOC K陕际有数据的数据块数。CHAIN_PER发生数据行迁移的记录数。各列有如下关系：BLOCKS EMPTY_BLOCKS+ HIGHWATER_MARK+1WASTE_PER= (HIGHWATER_MARK - AVG_USED_BLOCKS)/ HIGHWATER_MARK用下面的一个图可以直观了解他们之间的关系：BL

30、OCKSHIGH WATER MARK图中红色块表示在 HIGHWATER_MA它分配但未被使用的块，形成空洞(该报告以表大小倒序排序)TABLE_NAME:WASTE_PERTAB LE_ KBNUM_ROWSBLOCKSEMPTY_ BLOCKSHIGHWATER_MARKAVG_USE :_BLOCKSdchai N_PERSA_FINANCE _BGT_AMT99.838330249128747912804791278360SA_IMS_PRO DUCT_GROU96.1 p289350412927513616880361687142360SA_CONSUM _ACT_DELp73.9

31、23296007722904291200657290542757040SA_FINANCE _ACT98.92323072249938290384029038332240SA_CONSUM _TGT_DELp10.221022722.3E+072627846222621612354330SA_FACT_IS55.21918784192707323984802398471073880SA_CPA94.81329152452886166144016614385770SA_REF_TER R_ALIGNMEN T_DEL31.811069441.3E+07138368537137830939840S

32、A_IMS_MTH LC_BK5.27455680365250056960156055399524800SA_IMS_CHP A95.94034562354905043205043120890SA_FINANCE _PNL10033414480641768041767140SA_CUST_TARG_SEG58.61843209608042304002303995490SA_CONSUM _ACTp24180224151310122528675015777119890SA_FINANCE _BS1001673601802092002091950SA_FINANCE _BGT_QTY99.9167

33、360335820920020919260SA_CONSUM _ACT0423p1001310720163844611592230SA_CALLS63.61173342146014687534305044188SA_COMPAN _DAILY_SAL ES_ALLY98.711078421619138480138471820SA_IMS_MTH LC14.1102464110663312808012807109980SA_IMS_MTH US14.9101888110576512736012735108410SA_CONSUM _TGTp13.8911361224797113921321125

34、997050TEST_TABLE93.387040110880108344530SA_EXCHAN(E_ACT10083968331049601049530SA_DOCTOR CYCLE_EXTF ACT58.7838402316771048001047943260SA_IMS_MTHST17.377632110672797040970380270SA_FINANCE _CONCUR_D TAILE9.05563203635867040112692763000WK_SA_CPA5.6512003344086400166623358840SA_REF_TER R_ALIGNMEN T25.935

35、840736393448072440732650SA_CONSUM _TGT0316p5.7348164942584352196415539180SA_CUSTOM R-5.97317441105823968128383936100SA_CUST5.49235521181532944994194918420SA_HKAPI95.7164487655205602055890SA_CONSUM _TGT_AMTp13.815360449502192096182315710SA_CUST04237.13143361130771792121167015510SA_COMMUNTY_TGTI7.1213

36、31240858166413543092870SA_CM_WORING_DATEK8612672629821584015832220SA_CM_IN_M90.11122909140013991390ARKET_SALE S_CU0080SA_DASH_SF E99.791524611440114330SA_CPA_TER R14.660806085876007596480SA_REF_EMP _TERR15.8409639120512105014220SA_CM_IN_M ARKET_SALE S_OCM97.6396819594960495120SA_COMPAN _MONTHLY_S AL

37、ESY；83.83264132654080407660SA_MAP_YEA RMONTH_RA ET99.332003400039930SA_FINANCE _ACT_BPCS_ TEST16.7307243568384723112590SA_REF_EMP _TERR04139.63307225484384823012720SA_FINANCE _ACT_BPCS17.123041558028802872380SA_COMMUNTY_TGT_AMTI21.6204840858256701851450SA_COMPAN _DAILY_SAL ES_ALL_23Y11.1204821024256

38、561991770SA_DASH_ATTRITION97.389629112011130SA_DASH_MARKET_SHARE卜97.389656112011130SA_DASH_M( NTHLY_MAT_ SALES)95.5896110112011150SA_CORP3.45768350096887840SA_CM_IN_M ARKET_SALE S_CU_DEL32.2704910288087590SA_COMMUNTY_ACTI12.77041728588879690WK_SA_COM ETITOR_PROp11.85762585723734300DUCTSA_IMS_ANT I_H

39、YPER_TE ST18.54488604562827220SA_TERRITO RY26.33842887482819140TEST_CUSTOMER_TGT25384634843430SA_CM_IN_M ARKET_SALE S_OCM_DEL51.6256195932031150SA_FINANCE _ACT_ADJUS TMENT82.61922762402340SA_ACCOUN82.61922272402340SA_BARRIER _REASON82.6192822402340SA_DRIVER_REASON78.31921102402350SA_IS_KEY_ MESSAGE7

40、8.31921002402350SA_IS_MATE RIAL73.91921252402360SA_REF_MR_PROD69.61923892402370SA_REF_MR_CORP69.61925092402370SA_REF_MR_CUST65.21924222402380SA_RANKING _MESSAGE60.91925842402390SA_TC52.219246324023110SA_REF_VENDOR_EMP5.26192265924419180SA_CUST_PA RENT5.26192651724419180SA_SFE_VARIABLES8012841601530S

41、A_SHIFT_L EVEL80128811601530SA_BPCS_TO _ISMI801282131601530SA_PATIENT _TYPE_EN80128251601530SA_MR_KEY_ PRODUCT80128381601530SA_CM_REF_TERR_CU80128521601530SA_MAP_TEAM_BRAND80128111601530SA_MAP_CUSTOMER8012861601530SA_LOCATIO N801281351601530SA_INCREMENTAL_SHIFT80128521601530SA_IMS_CIT Y8012831160153

42、0SA_CM_REF_TERR_OCM80128101601530SA_COMPAN _DAILY_SAL ESY80128311601530SA_EXCHANG E_RATE_ACT _MTH8012811601530SA_EXCHANGE_RATE8012841601530SA_DASH_GF OWTH_BUBB E,80128111601530SA_COST_CENTER801282281601530SA_TGT_FREQ801283051601530SA_TGT_CAL LS80128881601530SA_FINANCE _ANP41.712813201631270SA_MAP_AG

43、( R16.7128365416312100SA_COMPAN _DAILY_SAL ES_23Y11.1128744166980SA_DOCTOR POTENTIAL-57.164480730SA_CUST_CATEGORY57.1642780730SA_FINANCE _CONCUR_D TAIL_MTHE57.164180730SA_REF_MR_CITY57.164880730SA_PROD_PF ICE57.16426580730SA_SPLIT_HOSPTIAL256435583430SA_USAGE_L EVEL2564583430STAGEPLAN2564083430SA_PR

44、OD_GIOUP_NEWF256426983430TEST_CUSTO MER 一25642183430SA_MAP_SAF _BPCS_SKU25645083430SA_MAP_SAP _BPCS_CUST25645283430SA_MAP_POF UMBER_BPC TERRCODEJS256411483430SA_NEW_USGE_LEVELA2564483430分析结论从报告中，目前 STAGER空间存在以下几个问题：一：数据库表空间浪费比率很高，整个STAGED据库表空间总的浪费比率为：73.33%二：很多表记录不多，但占得空间巨大。比如占空间很大的几个表表名表大小浪费比率SA_FI

45、NANCE_BGT_AMT3.66 (G)99.83SA_IMS_PRODUCT_GROUP 2.76 (G)96.06SA_CONSUMP_ACT_DEL2.22 (G)73.94SA_FINANCE_ACT2.22 (G)98.89三：以DEL结尾的几个表，占的空间很大，跟用户访谈得知，这几个表是备份表，不做删除 清理，不合理。原因分析从上面的分析可以知道，目前数据库最主要的问题也是表空间浪费很高，造成空间浪费很多。那么造成浪费的原因是什么呢？一般来说，造成浪费的原因有如下几个方面：一：频繁的 DEL操作，造成表空间大量的空块，具体表现为表的HWM 很高，那么ORACLE在统计剩余空间时，

46、是以 HWM水位线上面的空间来计算的。也就是说HWM下面的空间不能被重新分配，尽管可能已经没有数据。那么表空间经常会爆满。Oracle表段中的高水位线 HWM在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线 (High-wartermark, HWM )。在数据库表刚建立的时候，由于没有任何数据， 所以这个时候水位线是空的，也就是说HWM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性， 就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低

47、， 还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。二：数据库发生行迁移。行迁移当修改不是行的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。在这种情况下，Oracle会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的新的位置，并且该行原先空间的剩余空间不再被数据库使用，这些剩余的空间我们将其称之为空洞，这就是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是我们可以将其降到一个我们可以接受的程度。从我们上面的分析来看，XX公司数据库发生行迁

48、移的记录很少(CHAIN_PERm低)。 所以这个不是造成空间浪费的原因原因。所以，造成空间浪费的真正原因是一：频繁的DEL操作，导致表的 HWM被拉高。二：备份表只插入，不做定期清理。卜面我们验证一下，删除操作对数据库表空间的影响。我们以SA_IMS_PRODUCT_GR由打表为例，然后创建一个新表，表的记录跟原来 是一样多的，然后比较删除记录前跟删除记录后，表空间的情况。创建表：create table SA_IMS_PRODUCT_GROUP_HHS as select * from SA_IMS_PRODUCT_GROUP对表 SA_IMS_PRODUCT_GROUP_HHS行表分析a

49、nalyze table SA_IMS_PRODUCT_GROUP_HHS compute statistics下面是删除操作之前的表的空间情况。可以发现，SA_IMS_PRODUCT_GROUP_HHS个表比SA_IMS_PRODUCT_GROUP个表占的空间少了 2G多。仅为122M左右，原表为 2.7G.拜5于用-际JGW厂稣BLOCKSELPK&LO HG-W虢1础12；雪捐0 JIE即削工都JQTY诲此IM335321335pE.队CO帖脚理1监掘此9036MG颂阳JSA_NS_PRQDUCT_GROUP_HHS TLE渊123275,W51 1IlfSAwC/ilS-W11T5C4

50、理俐口;LW疝OW删/aE如概LE艇1侬创0 1汉口号TOIABLE14.13底考嗣;0接下来，我们删除一些记录，然后看看HWM是否会下降。delete from SA_IMS_PRODUCT_GROUP_HHS where CITY_NAME=CHPA对表再进行分析，下面是分析结果，可以发现，表记录少了，但表的大小没有降下来, 表明表的HWM还是删除前的水位。TABLESEGMENT m 喉匕呻映邓比七二。股时况型TYJIQ第HG郦旧3乳卬倒彩品-TwLE241门T翻阳4虱H曲CE.脏TABLE9G,33E破祖门*j Ua巩邢出此济TLE99331邓器察卬现Q6 KCDNSUO：啦T限E叼3

51、3131MC耳吁工也仃_/R总FABLE2111侬5工毗G13皿血花LE网-F 副中一螂KCOBWffpn理皿TABLE解n醐面千0式郡事1HLCTABLEU13W-20D然后我们再把删除的记录插进去，看看表是否变化了。insert into SA_IMS_PRODUCT_GROUP_HHSselect * from SA_IMS_PRODUCT_GROUP where CITY_NAME=CHPA对表再进行分析，下面是分析结果，可以发现，表记录跟删除前一样多，但表的尺寸变大了，表明表的 HWM被拉高了，尽管记录还是一样多。T眼匕1周E国冏U舞流 TE/E 二侬邦如呼二战BLOCKSaPTYJ

52、LOCKS |HIWA7E-11 SAJUSLTO.SEG-侬施E物就承m53 巩COMSWP 立T-刖1侬1513K1姗6初工的5胆底瓶侬那!阪bU力强3-15队郦腌一机加-融腑够1:;e21骈3t ajDHSJtfP.Oia -侬施131哦n li踞冏i:4电PQ旗昭阳5二侬11W1血1觥3-1G WLS-田G362网Ml3X_CDWW耻七晅机侬跻21E13幻a电MHC-1侬1113m11旭啰：3:鬼D从上面的验证可以发现，如果我们对表进行反复删除，并且插入新记录，每次操作都会拉高表的HWM ,造成表空间的浪费。那么在我们informatica的JOB运行过程，是否存在着 删除表记录的操作呢？通过对JOB的检查，确实在 ETL JOB,很多地方在装载数据前，为了避免数据记录的重复装载，每次装载之前都会先进行删除操作。下面我们拿一个JOB来分析，在这个验证中，我们反复运行一个JOB,理论上记录是不会增多的，看对表空间的影响如何。我们以下面的ETL JO斯例:/1i旧工匕【匚i I J；助知忙U期in叫一睁盥！ It IE1强眈W:;工斯心_通工眨心帕(?匚3碎1匚c? fflljrn”川g*工EW工眦E卜 if-七工吸皿*用! 1FW璇一曲JI丁曜西为一一2D10-IM止打用曲上BUT ,01aaO-lM止IT：SS班卜怛? H：物/ZLO-ir LT-