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

1 系统问题XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问题，涉及到数据库和ETL.问题一：表空间增长太快，每个月需增加35G空间。问题二：ETL JOB会经常导致数据库产生表空间不足错误。2 系统优化分析2.1 分析思路要解决表空间的问题，我们必须搞清楚下面几个问题：思路一：真正每个月数据仓库增量是多少空间？ 目的：得出一个正确的月表空间增长量。思路二：目前的数据仓库表空间是是如何分布的。目的：找出那些对象是最占空间，分析其合理性。2.2 分析过程要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。脚本一analyze table SA_IMS_PRODUCT_GROUP compute statistics;analyze table SA_CONSUMP_ACT_DEL compute statistics;analyze table SA_FINANCE_ACT compute statistics;analyze table SA_CONSUMP_TGT_DEL compute statistics;analyze table SA_FACT_IS compute statistics;analyze table SA_CPA compute statistics;analyze table SA_REF_TERR_ALIGNMENT_DEL compute statistics;analyze table SA_IMS_MTHLC_BK compute statistics;analyze table SA_IMS_CHPA compute statistics;analyze table SA_FINANCE_PNL compute statistics;analyze table SA_CUST_TARG_SEG compute statistics;analyze table SA_CONSUMP_ACT compute statistics;analyze table SA_FINANCE_BS compute statistics;analyze table SA_FINANCE_BGT_QTY compute statistics;analyze table SA_CONSUMP_ACT0423 compute statistics;analyze table SA_CALLS compute statistics;analyze table SA_COMPANY_DAILY_SALES_ALL compute statistics;analyze table SA_IMS_MTHLC compute statistics;analyze table SA_IMS_MTHUS compute statistics;analyze table SA_CONSUMP_TGT compute statistics;analyze table TEST_TABLE compute statistics;analyze table SA_DOCTOR_CYCLE_EXTRACT compute statistics;analyze table SA_EXCHANGE_ACT compute statistics;analyze table SA_IMS_MTHST compute statistics;analyze table SA_FINANCE_CONCUR_DETAIL compute statistics;analyze table WK_SA_CPA compute statistics;analyze table SA_REF_TERR_ALIGNMENT compute statistics;analyze table SA_CONSUMP_TGT0316 compute statistics;analyze table SA_CUSTOMER compute statistics;analyze table SA_CUST compute statistics;analyze table SA_HKAPI compute statistics;analyze table SA_CONSUMP_TGT_AMT compute statistics;analyze table SA_CUST0423 compute statistics;analyze table SA_COMMUNITY_TGT compute statistics;analyze table SA_CM_WORKING_DATE compute statistics;analyze table SA_CM_IN_MARKET_SALES_CU compute statistics;analyze table SA_DASH_SFE compute statistics;analyze table SA_CPA_TERR compute statistics;analyze table IDX_SA_CUST compute statistics;analyze table SA_REF_EMP_TERR compute statistics;analyze table SA_CM_IN_MARKET_SALES_OCM compute statistics;analyze table SA_COMPANY_MONTHLY_SALES compute statistics;analyze table SA_MAP_YEARMONTH_RATE compute statistics;analyze table SA_FINANCE_ACT_BPCS_TEST compute statistics;analyze table SA_REF_EMP_TERR0413 compute statistics;analyze table SA_FINANCE_ACT_BPCS compute statistics;analyze table IDX$_143D0001 compute statistics;analyze table SA_COMPANY_DAILY_SALES_ALL_23 compute statistics;analyze table SA_COMMUNITY_TGT_AMT compute statistics;analyze table SA_DASH_MONTHLY_MAT_SALES compute statistics;analyze table SA_DASH_ATTRITION compute statistics;analyze table SA_DASH_MARKET_SHARE compute statistics;analyze table SA_CORP compute statistics;analyze table SA_COMMUNITY_ACT compute statistics;analyze table SA_CM_IN_MARKET_SALES_CU_DEL compute statistics;analyze table WK_SA_COMPETITOR_PRODUCT compute statistics;analyze table SA_IMS_ANTI_HYPER_TEST compute statistics;analyze table SA_TERRITORY compute statistics;analyze table TEST_CUSTOMER_TGT compute statistics;analyze table SA_COMPETITOR_PRODUCT compute statistics;analyze table SA_CM_IN_MARKET_SALES_OCM_DEL compute statistics;analyze table SA_COMPANY_DAILY_SALES compute statistics;analyze table SA_REF_MR_CORP compute statistics;analyze table SA_IS_MATERIAL compute statistics;analyze table SA_IS_KEY_MESSAGE compute statistics;analyze table SA_DRIVER_REASON compute statistics;analyze table SA_REF_MR_CUST compute statistics;analyze table SA_BARRIER_REASON compute statistics;analyze table SA_ACCOUNT compute statistics;analyze table SA_REF_MR_PROD compute statistics;analyze table SA_REF_VENDOR_EMP compute statistics;analyze table SA_FINANCE_ACT_ADJUSTMENT compute statistics;analyze table SA_RANKING_MESSAGE compute statistics;analyze table SA_TC compute statistics;analyze table SA_CUST_PARENT compute statistics;analyze table SA_EXCHANGE_RATE_ACT_MTH compute statistics;analyze table SA_EXCHANGE_RATE compute statistics;analyze table SA_DASH_GROWTH_BUBBLE compute statistics;analyze table SA_COST_CENTER compute statistics;analyze table PM_KEY compute statistics;analyze table SA_CM_REF_TERR_OCM compute statistics;analyze table SA_CM_REF_TERR_CU compute statistics;analyze table SA_BPCS_TO_ISMI compute statistics;analyze table PRODUCT compute statistics;analyze table SA_SHIFT_LEVEL compute statistics;analyze table SA_SFE_VARIABLES compute statistics;analyze table SA_PRODUCT compute statistics;analyze table SA_PATIENT_TYPE_EN compute statistics;analyze table SA_MR_KEY_PRODUCT compute statistics;analyze table SA_MAP_TEAM_BRAND compute statistics;analyze table SA_MAP_CUSTOMER compute statistics;analyze table SA_MAP_AGGR compute statistics;analyze table SA_LOCATION compute statistics;analyze table SA_INCREMENTAL_SHIFT compute statistics;analyze table SA_IMS_CITY compute statistics;analyze table SA_TGT_FREQ compute statistics;analyze table SA_TGT_CALLS compute statistics;analyze table SA_FINANCE_ANP compute statistics;analyze table SA_COMPANY_DAILY_SALES_23 compute statistics;analyze table SA_GEOGRAPHY compute statistics;analyze table SA_MAP_PONUMBER_BPCSTERRCODE compute statistics;analyze table PK_SA_MAP_PONUMBER_BPCSTERRCOD compute statistics;analyze table SA_MAP_SAP_BPCS_CUST compute statistics;analyze table PK_SA_MAP_SAP_BPCS_CUST compute statistics;analyze table SA_MAP_SAP_BPCS_SKU compute statistics;analyze table PK_SA_MAP_SAP_BPCS_SKU compute statistics;analyze table SA_REF_DAY compute statistics;analyze table STAGEPLAN compute statistics;analyze table SA_SPLIT_HOSPTIAL compute statistics;analyze table SA_USAGE_LEVEL compute statistics;analyze table TEST_CUSTOMER compute statistics;analyze table SA_NEW_USAGE_LEVEL compute statistics;analyze table SA_PROD_GROUP_NEW compute statistics;通过表分析，我们可以得到数据仓库中每个表的记录行数，BLOCK数，EMPTY BLOCKS数等等关键的数据分布数据，分析后，这些数据会存放在系统表，USER_TABLES和USER_SEGMENTS中。通过对这些系统查询，我们可以得到整个数据库的数据分布情况，从而为分析问题原因提供充足基础。执行下面的脚本，可以得到一个数据库的数据分布报告： 脚本二 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_MARK, 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 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,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_NAMEFROM USER_SEGMENTS A,USER_TABLES B,user_tablespaces CWHERE SEGMENT_NAME = TABLE_NAME andSEGMENT_TYPE = TABLE ANDB.TABLESPACE_NAME = 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 * (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 USER_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)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) 2 AND BLOCKS 1ORDER BY 4 DESC, 3 DESC, 2 ASC;运行脚本二后，我们以生产环境的STAGE SCHEMA为例。得到报告如下：报告的各列含义如下：WASTE_PER：空间浪费比率，实际用到的数据块/分配给该表的数据块。TABLE_KB： 表占空间大小，以KB为单位。NUM_ROWS: 表中记录行数。BLOCKS： 分配给该表的数据块数。EMPTY_BLOCKS：已分配给该表但尚未使用的数据块。HIGHWATER_MARK：表的高水位标志。AVG_USED_BLOCKS：实际有数据的数据块数。CHAIN_PER：发生数据行迁移的记录数。各列有如下关系：BLOCKS = EMPTY_BLOCKS+ HIGHWATER_MARK+1WASTE_PER= (HIGHWATER_MARK - AVG_USED_BLOCKS)/ HIGHWATER_MARK用下面的一个图可以直观了解他们之间的关系：图中红色块表示在HIGHWATER_MARK下已分配但未被使用的块，形成空洞。（该报告以表大小倒序排序）TABLE_NAMEWASTE_PERTABLE_KBNUM_ROWSBLOCKSEMPTY_BLOCKSHIGHWATER_MARKAVG_USED_BLOCKSCHAIN_PERSA_FINANCE_BGT_AMT99.89128708360SA_IMS_PRODUCT_GROUP96.10142360SA_CONSUMP_ACT_DEL73.9657757040SA_FINANCE_ACT98.9032240SA_CONSUMP_TGT_DEL10.22.3E+076220SA_FACT_IS55.200SA_CPA94.8085770SA_REF_TERR_ALIGNMENT_DEL31.81.3E+07537939840SA_IMS_MTHLC_BK5.2756960156055399524800SA_IMS_CHPA95.95043205043120890SA_FINANCE_PNL10080641768041767140SA_CUST_TARG_SEG58.62304002303995490SA_CONSUMP_ACT2422528675015777119890SA_FINANCE_BS1001802092002091950SA_FINANCE_BGT_QTY99.9335820920020919260SA_CONSUMP_ACT04231000163844611592230SA_CALLS63.61468801468753430SA_COMPANY_DAILY_SALES_ALL98.721619138480138471820SA_IMS_MTHLC14.112808012807109980SA_IMS_MTHUS14.912736012735108410SA_CONSUMP_TGT13.891136113921321125997050TEST_TABLE93.387040110880108344530SA_EXCHANGE_ACT10083968331049601049530SA_DOCTOR_CYCLE_EXTRACT58.7838401048001047943260SA_IMS_MTHST17.37763297040970380270SA_FINANCE_CONCUR_DETAIL9.05563207040112692763000WK_SA_CPA5.6512006400166623358840SA_REF_TERR_ALIGNMENT25.935840448072440732650SA_CONSUMP_TGT03165.7348164352196415539180SA_CUSTOMER5.97317443968128383936100SA_CUST5.49235522944994194918420SA_HKAPI95.7164487655205602055890SA_CONSUMP_TGT_AMT13.815360192096182315710SA_CUST04237.13143361792121167015510SA_COMMUNITY_TGT7.121331240858166413543092870SA_CM_WORKING_DATE8612672629821584015832220SA_CM_IN_MARKET_SALES_CU90.111200290981400013991390SA_DASH_SFE99.791524611440114330SA_CPA_TERR14.660806085876007596480SA_REF_EMP_TERR15.8409639120512105014220SA_CM_IN_MARKET_SALES_OCM97.6396819594960495120SA_COMPANY_MONTHLY_SALES83.83264132654080407660SA_MAP_YEARMONTH_RATE99.332003400039930SA_FINANCE_ACT_BPCS_TEST16.7307243568384723112590SA_REF_EMP_TERR04139.63307225484384823012720SA_FINANCE_ACT_BPCS17.123041558028802872380SA_COMMUNITY_TGT_AMT21.6204840858256701851450SA_COMPANY_DAILY_SALES_ALL_2311.1204821024256561991770SA_DASH_ATTRITION97.389629112011130SA_DASH_MARKET_SHARE97.389656112011130SA_DASH_MONTHLY_MAT_SALES95.5896110112011150SA_CORP3.45768350096887840SA_CM_IN_MARKET_SALES_CU_DEL32.2704910288087590SA_COMMUNITY_ACT12.77041728588879690WK_SA_COMPETITOR_PRODUCT11.85762585723734300SA_IMS_ANTI_HYPER_TEST18.54488604562827220SA_TERRITORY26.33842887482819140TEST_CUSTOMER_TGT25384634843430SA_CM_IN_MARKET_SALES_OCM_DEL51.6256195932031150SA_FINANCE_ACT_ADJUSTMENT82.61922762402340SA_ACCOUNT82.61922272402340SA_BARRIER_REASON82.6192822402340SA_DRIVER_REASON78.31921102402350SA_IS_KEY_MESSAGE78.31921002402350SA_IS_MATERIAL73.91921252402360SA_REF_MR_PROD69.61923892402370SA_REF_MR_CORP69.61925092402370SA_REF_MR_CUST65.21924222402380SA_RANKING_MESSAGE60.91925842402390SA_TC52.219246324023110SA_REF_VENDOR_EMP5.26192265924419180SA_CUST_PARENT5.26192651724419180SA_SFE_VARIABLES8012841601530SA_SHIFT_LEVEL80128811601530SA_BPCS_TO_ISMI801282131601530SA_PATIENT_TYPE_EN80128251601530SA_MR_KEY_PRODUCT80128381601530SA_CM_REF_TERR_CU80128521601530SA_MAP_TEAM_BRAND80128111601530SA_MAP_CUSTOMER8012861601530SA_LOCATION801281351601530SA_INCREMENTAL_SHIFT80128521601530SA_IMS_CITY80128311601530SA_CM_REF_TERR_OCM80128101601530SA_COMPANY_DAILY_SALES80128311601530SA_EXCHANGE_RATE_ACT_MTH8012811601530SA_EXCHANGE_RATE8012841601530SA_DASH_GROWTH_BUBBLE80128111601530SA_COST_CENTER801282281601530SA_TGT_FREQ801283051601530SA_TGT_CALLS80128881601530SA_FINANCE_ANP41.712813201631270SA_MAP_AGGR16.7128365416312100SA_COMPANY_DAILY_SALES_2311.1128744166980SA_DOCTOR_POTENTIAL57.164480730SA_CUST_CATEGORY57.1642780730SA_FINANCE_CONCUR_DETAIL_MTH57.164180730SA_REF_MR_CITY57.164880730SA_PROD_PRICE57.16426580730SA_SPLIT_HOSPTIAL256435583430SA_USAGE_LEVEL2564583430STAGEPLAN2564083430SA_PROD_GROUP_NEW256426983430TEST_CUSTOMER25642183430SA_MAP_SAP_BPCS_SKU25645083430SA_MAP_SAP_BPCS_CUST25645283430SA_MAP_PONUMBER_BPCSTERRCODE256411483430SA_NEW_USAGE_LEVEL25644834302.3 分析结论从报告中，目前STAGE表空间存在以下几个问题：一：数据库表空间浪费比率很高，整个STAGE数据库表空间总的浪费比率为：73.33%二：很多表记录不多，但占得空间巨大。比如占空间很大的几个表 表名表大小浪费比率SA_FINANCE_BGT_AMT3.66（G）99.83SA_IMS_PRODUCT_GROUP2.76（G）96.06SA_CONSUMP_ACT_DEL2.22（G）73.94SA_FINANCE_ACT2.22（G）98.89三：以DEL结尾的几个表，占的空间很大，跟用户访谈得知，这几个表是备份表，不做删除清理，不合理。2.4 原因分析从上面的分析可以知道，目前数据库最主要的问题也是表空间浪费很高，造成空间浪费很多。那么造成浪费的原因是什么呢？一般来说，造成浪费的原因有如下几个方面：一：频繁的DEL操作，造成表空间大量的空块，具体表现为表的HWM很高，那么ORACLE在统计剩余空间时，是以HWM水位线上面的空间来计算的。也就是说HWM下面的空间不能被重新分配，尽管可能已经没有数据。那么表空间经常会爆满。Oracle表段中的高水位线HWM在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-warter mark, HWM）。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。二：数据库发生行迁移。行迁移当修改不是行链接的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。在这种情况下，Oracle会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的新的位置，并且该行原先空间的剩余空间不再被数据库使用，这些剩余的空间我们将其称之为空洞，这就是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是我们可以将其降到一个我们可以接受的程度。从我们上面的分析来看，XX公司数据库发生行迁移的记录很少（CHAIN_PER 很低）。所以这个不是造成空间浪费的原因原因。所以，造成空间浪费的真正原因是一：频繁的DEL操作，导致表的HWM被拉高。二：备份表只插入，不做定期清理。下面我们验证一下，删除操作对数据库表空间的影响。我们以SA_IMS_PRODUCT_GROUP这个打表为例，然后创建一个新表，表的记录跟原来是一样多的，然后比较删除记录前跟删除记录后，表空间的情况。创建表：create table SA_IMS_PRODUCT_GROUP_HHS