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

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

2、本一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

3、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 t

4、able 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 ta

5、ble 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_FIN

6、ANCE_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 com

7、pute 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 c

8、ompute 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_YEARM

9、ONTH_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 statisti

10、cs;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

11、 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 S

12、A_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 stati

13、stics;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;an

14、alyze 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 statist

15、ics;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;a

16、nalyze 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 S

17、A_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

18、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_

19、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 sta

20、tistics;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;

21、通过表分析，我们可以得到数据仓库中每个表的记录行数，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)

22、, 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_NAMEFRO

23、M(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

24、_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_tab

25、lespaces 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 *

26、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 EX

27、TENTS,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

28、.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

29、 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+

30、 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.838330249128747912804791278360SA_IMS_

31、PRODUCT_GROUP96.1289350412927513616880361687142360SA_CONSUMP_ACT_DEL73.923296007722904291200657290542757040SA_FINANCE_ACT98.92323072249938290384029038332240SA_CONSUMP_TGT_DEL10.221022722.3E+072627846222621612354330SA_FACT_IS55.21918784192707323984802398471073880SA_CPA94.81329152452886166144016614385

32、770SA_REF_TERR_ALIGNMENT_DEL31.811069441.3E+07138368537137830939840SA_IMS_MTHLC_BK5.27455680365250056960156055399524800SA_IMS_CHPA95.94034562354905043205043120890SA_FINANCE_PNL10033414480641768041767140SA_CUST_TARG_SEG58.61843209608042304002303995490SA_CONSUMP_ACT24180224151310122528675015777119890S

33、A_FINANCE_BS1001673601802092002091950SA_FINANCE_BGT_QTY99.9167360335820920020919260SA_CONSUMP_ACT04231001310720163844611592230SA_CALLS63.61175043342411468801468753430SA_COMPANY_DAILY_SALES_ALL98.711078421619138480138471820SA_IMS_MTHLC14.1102464110663312808012807109980SA_IMS_MTHUS14.91018881105765127

34、36012735108410SA_CONSUMP_TGT13.8911361224797113921321125997050TEST_TABLE93.387040110880108344530SA_EXCHANGE_ACT10083968331049601049530SA_DOCTOR_CYCLE_EXTRACT58.7838402316771048001047943260SA_IMS_MTHST17.377632110672797040970380270SA_FINANCE_CONCUR_DETAIL9.05563203635867040112692763000WK_SA_CPA5.6512

35、003344086400166623358840SA_REF_TERR_ALIGNMENT25.935840736393448072440732650SA_CONSUMP_TGT03165.7348164942584352196415539180SA_CUSTOMER5.97317441105823968128383936100SA_CUST5.49235521181532944994194918420SA_HKAPI95.7164487655205602055890SA_CONSUMP_TGT_AMT13.815360449502192096182315710SA_CUST04237.131

36、43361130771792121167015510SA_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.

37、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_DAI

38、LY_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.8576258

39、5723734300SA_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_