Oracle數(shù)據(jù)庫性能優(yōu)化碎片整理

1、1系統(tǒng)問題XX公司BI系統(tǒng)上線運(yùn)行以來，客戶反映系統(tǒng)目前存在著下面的幾個(gè)問 題，涉及到數(shù)據(jù)庫和ETL.問題一：表空間增長(zhǎng)太快，每個(gè)月需增加 35G空間。問題二：ETL JOg經(jīng)常導(dǎo)致數(shù)據(jù)庫產(chǎn)生表空間不足錯(cuò)誤。2系統(tǒng)優(yōu)化分析分析思路要解決表空間的問題，我們必須搞清楚下面幾個(gè)問題：思路一：真正每個(gè)月數(shù)據(jù)倉庫增量是多少空間？目的：得出一個(gè)正確的月表空間增長(zhǎng)量 。思路二：目前的數(shù)據(jù)倉庫表空間是是如何分布的。目的：找出那些對(duì)象是最占空間，分析其合理性 。分析過程要得到真實(shí)的數(shù)據(jù)分布必須對(duì)表進(jìn)行分析，首先需要對(duì)數(shù)據(jù)倉庫的 oracle數(shù)據(jù)庫進(jìn)行表分析，。執(zhí)行下面腳本可以對(duì)數(shù)據(jù)庫進(jìn)行表分析。腳本一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通過表分析，我們可以得到數(shù)據(jù)倉庫中每個(gè)表的記錄行數(shù),compute statistics;compute statistics

20、;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;compute statistics;BLOCK數(shù)，EMPTY BLOCKSanalyze table PK_SA_MAP_PONUMBER_BPCSTERRCOD:ompute statistics;數(shù)等等關(guān)鍵的數(shù)據(jù)分布數(shù)據(jù)，分析后，這些數(shù)據(jù)會(huì)存放在系統(tǒng)表，USER_TABLESmUSER_

21、SEGMENT阱。通過對(duì)這些系統(tǒng)查詢，我們可以得到整個(gè)數(shù)據(jù)庫的數(shù)據(jù)分布情況，從 而為分析問題原因提供充足基礎(chǔ)。執(zhí)行下面的腳本，可以得到一個(gè)數(shù)據(jù)庫的數(shù)據(jù)分布報(bào)告:腳本二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;運(yùn)行腳本二后，我們以生產(chǎn)環(huán)境的STAGE SCHEM的例。得到報(bào)告如下:報(bào)告的各列含義如下：WASTE_PE噲間浪費(fèi)比率，實(shí)際用

29、到的數(shù)據(jù)塊 /分配給該表的數(shù)據(jù)塊。TABLE_KB 表占空間大小，以 KB為單位。NUM_ROWS表中記錄行數(shù)。BLOCK S分配給該表的數(shù)據(jù)塊數(shù)。EMPTY_BLOCKfi分配給該表但尚未使用的數(shù)據(jù)塊。HIGHWATER_MA RK的高水位標(biāo)志。AVG_USED_BLOC K陜際有數(shù)據(jù)的數(shù)據(jù)塊數(shù)。CHAIN_PER發(fā)生數(shù)據(jù)行遷移的記錄數(shù)。各列有如下關(guān)系：BLOCKS EMPTY_BLOCKS+ HIGHWATER_MARK+1WASTE_PER= (HIGHWATER_MARK - AVG_USED_BLOCKS)/ HIGHWATER_MARK用下面的一個(gè)圖可以直觀了解他們之間的關(guān)系：BL

30、OCKSHIGH WATER MARK圖中紅色塊表示在 HIGHWATER_MA它分配但未被使用的塊，形成空洞(該報(bào)告以表大小倒序排序)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分析結(jié)論從報(bào)告中，目前 STAGER空間存在以下幾個(gè)問題：一：數(shù)據(jù)庫表空間浪費(fèi)比率很高，整個(gè)STAGED據(jù)庫表空間總的浪費(fèi)比率為：73.33%二：很多表記錄不多，但占得空間巨大。比如占空間很大的幾個(gè)表表名表大小浪費(fèi)比率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結(jié)尾的幾個(gè)表，占的空間很大，跟用戶訪談得知，這幾個(gè)表是備份表，不做刪除 清理，不合理。原因分析從上面的分析可以知道，目前數(shù)據(jù)庫最主要的問題也是表空間浪費(fèi)很高，造成空間浪費(fèi)很多。那么造成浪費(fèi)的原因是什么呢？一般來說，造成浪費(fèi)的原因有如下幾個(gè)方面：一：頻繁的 DEL操作，造成表空間大量的空塊，具體表現(xiàn)為表的HWM 很高，那么ORACLE在統(tǒng)計(jì)剩余空間時(shí)，

46、是以 HWM水位線上面的空間來計(jì)算的。也就是說HWM下面的空間不能被重新分配，盡管可能已經(jīng)沒有數(shù)據(jù)。那么表空間經(jīng)常會(huì)爆滿。Oracle表段中的高水位線 HWM在Oracle數(shù)據(jù)的存儲(chǔ)中，可以把存儲(chǔ)空間想象為一個(gè)水庫，數(shù)據(jù)想象為水庫中的水。水庫中的水的位置有一條線叫做水位線，在Oracle中，這條線被稱為高水位線 (High-wartermark, HWM )。在數(shù)據(jù)庫表剛建立的時(shí)候，由于沒有任何數(shù)據(jù)， 所以這個(gè)時(shí)候水位線是空的，也就是說HWM為最低值。當(dāng)插入了數(shù)據(jù)以后，高水位線就會(huì)上漲，但是這里也有一個(gè)特性， 就是如果你采用delete語句刪除數(shù)據(jù)的話，數(shù)據(jù)雖然被刪除了，但是高水位線卻沒有降低

47、， 還是你剛才刪除數(shù)據(jù)以前那么高的水位。也就是說，這條高水位線在日常的增刪操作中只會(huì)上漲，不會(huì)下跌。二：數(shù)據(jù)庫發(fā)生行遷移。行遷移當(dāng)修改不是行的行時(shí)，當(dāng)修改后的行長(zhǎng)度大于修改前的行長(zhǎng)度，并且該數(shù)據(jù)塊中的空閑空間已經(jīng)比較小而不能完全容納該行的數(shù)據(jù)時(shí)，就會(huì)發(fā)生行遷移。在這種情況下，Oracle會(huì)將整行的數(shù)據(jù)遷移到一個(gè)新的數(shù)據(jù)塊上，而將該行原先的空間只放一個(gè)指針，指向該行的新的位置，并且該行原先空間的剩余空間不再被數(shù)據(jù)庫使用，這些剩余的空間我們將其稱之為空洞，這就是產(chǎn)生表碎片的主要原因，表碎片基本上也是不可避免的，但是我們可以將其降到一個(gè)我們可以接受的程度。從我們上面的分析來看，XX公司數(shù)據(jù)庫發(fā)生行遷

48、移的記錄很少(CHAIN_PERm低)。 所以這個(gè)不是造成空間浪費(fèi)的原因原因。所以，造成空間浪費(fèi)的真正原因是一：頻繁的DEL操作，導(dǎo)致表的 HWM被拉高。二：備份表只插入，不做定期清理。卜面我們驗(yàn)證一下，刪除操作對(duì)數(shù)據(jù)庫表空間的影響。我們以SA_IMS_PRODUCT_GR由打表為例，然后創(chuàng)建一個(gè)新表，表的記錄跟原來 是一樣多的，然后比較刪除記錄前跟刪除記錄后，表空間的情況。創(chuàng)建表：create table SA_IMS_PRODUCT_GROUP_HHS as select * from SA_IMS_PRODUCT_GROUP對(duì)表 SA_IMS_PRODUCT_GROUP_HHS行表分析a

49、nalyze table SA_IMS_PRODUCT_GROUP_HHS compute statistics下面是刪除操作之前的表的空間情況。可以發(fā)現(xiàn)，SA_IMS_PRODUCT_GROUP_HHS個(gè)表比SA_IMS_PRODUCT_GROUP個(gè)表占的空間少了 2G多。僅為122M左右，原表為 2.7G.拜5于用-際JGW廠穌BLOCKSELPK&LO HG-W虢1礎(chǔ)12；雪捐0 JIE即削工都JQTY誨此IM335321335pE.隊(duì)CO帖腳理1監(jiān)掘此9036MG頌陽JSA_NS_PRQDUCT_GROUP_HHS TLE淵123275,W51 1IlfSAwC/ilS-W11T5C4

50、理俐口;LW疝OW刪/aE如概LE艇1儂創(chuàng)0 1漢口號(hào)TOIABLE14.13底考嗣;0接下來，我們刪除一些記錄，然后看看HWM是否會(huì)下降。delete from SA_IMS_PRODUCT_GROUP_HHS where CITY_NAME=CHPA對(duì)表再進(jìn)行分析，下面是分析結(jié)果，可以發(fā)現(xiàn)，表記錄少了，但表的大小沒有降下來, 表明表的HWM還是刪除前的水位。TABLESEGMENT m 喉匕呻映鄧比七二。股時(shí)況型TYJIQ第HG酈舊3乳卬倒彩品-TwLE241門T翻陽4虱H曲CE.臟TABLE9G,33E破祖門*j Ua鞏邢出此濟(jì)TLE99331鄧器察卬現(xiàn)Q6 KCDNSUO：啦T限E叼3

51、3131MC耳吁工也仃_/R總FABLE2111儂5工毗G13皿血花LE網(wǎng)-F 副中一螂KCOBWffpn理皿TABLE解n醐面千0式郡事1HLCTABLEU13W-20D然后我們?cè)侔褎h除的記錄插進(jìn)去，看看表是否變化了。insert into SA_IMS_PRODUCT_GROUP_HHSselect * from SA_IMS_PRODUCT_GROUP where CITY_NAME=CHPA對(duì)表再進(jìn)行分析，下面是分析結(jié)果，可以發(fā)現(xiàn)，表記錄跟刪除前一樣多，但表的尺寸變大了，表明表的 HWM被拉高了，盡管記錄還是一樣多。T眼匕1周E國(guó)冏U舞流 TE/E 二儂邦如呼二戰(zhàn)BLOCKSaPTYJ

52、LOCKS |HIWA7E-11 SAJUSLTO.SEG-儂施E物就承m53 鞏COMSWP 立T-刖1儂1513K1姍6初工的5膽底瓶?jī)z那!阪bU力強(qiáng)3-15隊(duì)酈腌一機(jī)加-融腑夠1:;e21駢3t ajDHSJtfP.Oia -儂施131哦n li踞冏i:4電PQ旗昭陽5二儂11W1血1觥3-1G WLS-田G362網(wǎng)Ml3X_CDWW恥七晅機(jī)儂躋21E13幻a電MHC-1儂1113m11旭啰：3:鬼D從上面的驗(yàn)證可以發(fā)現(xiàn)，如果我們對(duì)表進(jìn)行反復(fù)刪除，并且插入新記錄，每次操作都會(huì)拉高表的HWM ,造成表空間的浪費(fèi)。那么在我們informatica的JOB運(yùn)行過程，是否存在著 刪除表記錄的操作呢？通過對(duì)JOB的檢查，確實(shí)在 ETL JOB,很多地方在裝載數(shù)據(jù)前，為了避免數(shù)據(jù)記錄的重復(fù)裝載，每次裝載之前都會(huì)先進(jìn)行刪除操作。下面我們拿一個(gè)JOB來分析，在這個(gè)驗(yàn)證中，我們反復(fù)運(yùn)行一個(gè)JOB,理論上記錄是不會(huì)增多的，看對(duì)表空間的影響如何。我們以下面的ETL JO斯例:/1i舊工匕【匚i I J；助知忙U期in叫一睜盥！ It IE1強(qiáng)眈W:;工斯心_通工眨心帕(?匚3碎1匚c? fflljrn”川g*工EW工眥E卜 if-七工吸皿*用! 1FW璇一曲JI丁曜西為一一2D10-IM止打用曲上BUT ,01aaO-lM止IT：SS班卜怛? H：物/ZLO-ir LT-