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

《Oracle数据库性能优化(碎片整理)》由会员分享，可在线阅读，更多相关《Oracle数据库性能优化(碎片整理)（20页珍藏版）》请在装配图网上搜索。

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

2、库进行表分析。脚本一compute statistics; compute statistics; compute statistics;analyze table SA_IMS_PRODUCT_GROUP analyze table SA_CONSUMP_ACT_DEL analyze 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

3、_MTHLC_BKanalyze table 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_ALL analyze table SA_IMS_MTHLC

4、analyze table SA_IMS_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_DETAIL analyze table WK_SA_CPAanalyze table SA_REF_TERR_ALIGNMENT analyze table SA_CONSUMP_TGT0316

5、 analyze table SA_CUSTOMERanalyze 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_CU analyze table SA_DASH_SFEanalyze table SA_CPA_TERRanalyze table IDX_SA_CU

6、STanalyze table SA_REF_EMP_TERRanalyze table SA_CM_IN_MARKET_SALES_OCM analyze table SA_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_BPCScompute statistics; compute statistics;compute s

7、tatistics; 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 statis

8、tics; 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;co

9、mpute statistics;compute statistics;compute statistics; compute statistics;compute statistics;compute statistics; compute statistics;compute statistics; compute statistics; compute statistics; compute statistics;analyze table IDX$_143D0001analyze table SA_COMPANY_DAILY_SALES_ALL_23 analyze table SA_

10、COMMUNITY_TGT_AMT analyze table SA_DASH_MONTHLY_MAT_SALES analyze table SA_DASH_ATTRITIONanalyze table SA_DASH_MARKET_SHAREanalyze table SA_CORPanalyze table SA_COMMUNITY_ACTanalyze table SA_CM_IN_MARKET_SALES_CU_DELanalyze table WK_SA_COMPETITOR_PRODUCT analyze table SA_IMS_ANTI_HYPER_TEST analyze

11、table SA_TERRITORYanalyze table TEST_CUSTOMER_TGTanalyze table SA_COMPETITOR_PRODUCTanalyze table SA_CM_IN_MARKET_SALES_OCM_D analyze table SA_COMPANY_DAILY_SALES analyze table SA_REF_MR_CORPanalyze table SA_IS_MATERIALanalyze table SA_IS_KEY_MESSAGEanalyze table SA_DRIVER_REASONanalyze table SA_REF

12、_MR_CUSTanalyze table SA_BARRIER_REASONanalyze table SA_ACCOUNTanalyze table SA_REF_MR_PRODanalyze table SA_REF_VENDOR_EMPanalyze table SA_FINANCE_ACT_ADJUSTMENTanalyze table SA_RANKING_MESSAGEanalyze table SA_TCanalyze table SA_CUST_PARENTanalyze table SA_EXCHANGE_RATE_ACT_MTHanalyze table SA_EXCHA

13、NGE_RATEanalyze table SA_DASH_GROWTH_BUBBLEanalyze table SA_COST_CENTERanalyze table 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

14、SA_PATIENT_TYPE_ENanalyze table SA_MR_KEY_PRODUCTanalyze table SA_MAP_TEAM_BRANDcompute statistics; compute statistics; compute statistics;compute statistics; compute statistics;compute statistics; compute statistics; compute statistics;compute statistics; compute statistics; compute statistics;comp

15、ute statistics; compute statistics;L 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;compu

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;compute stati

17、stics; compute statistics; compute statistics;analyze table SA_MAP_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_ANPanalyze table SA_COMPANY_DAILY_SALES_

18、23 analyze table SA_GEOGRAPHYcompute statistics; compute statistics; compute statistics;compute statistics; compute statistics; compute statistics;compute statistics; compute statistics; compute statistics;analyze table SA_MAP_PONUMBER_BPCSTERRCODE compute statistics; analyze table PK_SA_MAP_PONUMBE

19、R_BPCSTERRCOD compute statistics;analyze table SA_MAP_SAP_BPCS_CUST analyze table PK_SA_MAP_SAP_BPCS_CUST analyze table SA_MAP_SAP_BPCS_SKU analyze table PK_SA_MAP_SAP_BPCS_SKU analyze table SA_REF_DAYanalyze table STAGEPLANanalyze table SA_SPLIT_HOSPTIALanalyze table SA_USAGE_LEVELanalyze table TES

20、T_CUSTOMER analyze table SA_NEW_USAGE_LEVEL analyze table SA_PROD_GROUP_NEWcompute statistics; compute statistics; compute statistics; compute statistics;compute statistics; compute statistics;compute statistics; compute statistics;compute statistics; compute statistics;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), 0)WAST

22、E_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

23、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_BL

24、OCKS,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_tablespac

25、es CWHERESEGMENT_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_R

26、OWS * (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,

27、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 AND D.TABLE_NAME = B.TABLE_NAME ANDA.PAR

28、TITION_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) 2AND BLOCKS 1ORDER BY 4

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

30、IGHWATER_MARK+1WASTE_PER= (HIGHWATER_MARK - AVG_USED_BLOCKS)/ HIGHWATER_MARK用下面的一个图可以直观了解他们之间的关系：BLOCKSEM PP_BLOCKSHIGH WATER MARKAVG_LSED_BLOCKS图中红色块表示在HIGHWATER_MARK下已分配但未被使用的块，形成空洞。该报告以表大小倒序排序)TABLE_NAMEWASTE_PERTABLE_KBNUM_ ROWSBLOCKSEMPTY_ BLOCKSHIGHWATER_MARKAVG_USED _BLOCKSCHAIN_PER383SA FINA

31、NCE912847999.830204791278360BGT AMT71284289SA IMS PRO129236196.13500361687142360DUCT GROUP7516884SA CONSUMP232772229173.9960657290542757040ACT DEL9042000232SA FINANCE249929098.9307029038332240ACT383842210SA CONSUMP2.3E26210.22276222621612354330TGT DEL+0778421911927239SA FACT IS55.2878023984710738804

32、0738481324528166SA CPA94.8915016614385770286144SA REF TER1101.3E138R ALIGNMEN31.8694537137830939840+07368T DEL4SA IMS MTH45536525695.27156055399524800LC BK68050060SA IMS CHP403235450495.905043120890A4569032SA FINANCE334417100806041767140PNL14468SA CUST TA184960823058.602303995490RG SEG3200440SA CONS

33、UMP180151322524675015777119890ACT22410128SA FINANCE16720910018002091950BS36020SA FINANCE16720999.93358020919260BGT QTY36020SA CONSUMP13116310004611592230ACT042307284SA CALLS63.6117334214601468753430SA_C0MPANY _DAILY_SAL ES ALL98.711078421619138480138471820SA IMS MTH102110612814.1012807109980LC464633

34、08SA IMS MTH101110512714.9012735108410US88876536SA CONSUMP911122411313.81321125997050TGT3679792870108TEST TABLE93.311083445304080SA EXCHANG8391041003301049530E ACT6896SA DOCTOR8382316104CYCLE EXTR58.701047943260407780ACTSA IMS MTH776110697017.30970380270ST327274SA FINANCE5633635704CONCUR DE9.0511269

35、276300020860TAIL5123344640WK SA CPA5.616662335884000080SA REF TER3587363448R ALIGNMEN25.97244073265040930TSA CONSUMP34849424355.7196415539180TGT031616582SA CUSTOME31711053965.97128383936100R448282351181294SA CUST5.4999419491842052534164205SA HKAPI95.7765502055890486SA CONSUMP153449519213.89618231571

36、0TGT AMT60020SA CUST04214311301797.13121167015510336772SA COMMUNI13340851667.1213543092870TY TGT1284SA CM WORK126629815886015832220ING DATE7224SA CM IN M90.11122909140013991390 ARKET_SALE0080S CUSA_DASH_SFE99.791524611440114330SA CPA TER608608514.676007596480R08SA REF EMP409391215.8512105014220TERR6

37、0SA CM IN M396ARKET SALE97.6195949604951208S OCMSA COMPANY3261326MONTHLY S83.8408040766045ALESSA MAP YEA320RMONTH RAT99.334000399300ESA FINANCE3074356ACT BPCS16.738472311259028TESTSA REF EMP30725489.63384823012720TERR041324SA FINANCE230155817.128802872380ACT BPCS40SA COMMUNI204408521.6256701851450TY

38、 TGT AMT88SA COMPANY2042102DAILY SAL11.125656199177084ES ALL 23SA DASH AT97.389629112011130TRITIONSA DASH MA97.389656112011130RKET SHARESA_DASH_MONTHLY_MAT_95.5896110112011150SALESSA CORP3.45768350096887840SA_CM_IN_MARKET_SALE32.2704910288087590S CU DELSA COMMUNI172812.770488879690TY ACT5WK SA COMP1

39、1.85762585723734300ETITOR PRODUCTSA_IMS_ANTI_HYPER_TEST18.54488604562827220SA_TERRITORY26.33842887482819140TEST_CUSTOMER TGT25384634843430SA_CM_IN_M ARKET_SALE S OCM DEL51.6256195932031150SA_FINANCE _ACT_ADJUS TMENT82.61922762402340SA ACCOUNT82.61922272402340SA_BARRIERREASON82.6192822402340SA_DRIVER

40、_REASON78.31921102402350SA_IS_KEY_MESSAGE78.31921002402350SA_IS_MATERIAL73.91921252402360SA_REF_MR_PROD69.61923892402370SA_REF_MR_CORP69.61925092402370SA_REF_MR_CUST65.21924222402380SA_RANKINGMESSAGE60.91925842402390SA TC52.219246324023110SA_REF_VENDOR EMP5.26192265924419180SA_CUST_PARENT5.261926517

41、24419180SA_SFE_VARIABLES8012841601530SA_SHIFT_LEVEL80128811601530SA_BPCS_TOISMI801282131601530SA_PATIENTTYPE EN80128251601530SA_MR_KEY_PRODUCT80128381601530SA_CM_REF_TERR CU80128521601530SA_MAP_TEAM BRAND80128111601530SA_MAP_CUSTOMER8012861601530SA_LOCATION801281351601530SA_INCREMENTAL SHIFT80128521

42、601530SA_IMS_CITY80128311601530SA_CM_REF_TERR OCM80128101601530SA_COMPANY _DAILY_SAL ES80128311601530SA_EXCHANGE_RATE_ACTMTH8012811601530SA_EXCHANGE RATE8012841601530SA_DASH_GROWTH_BUBBLE80128111601530SA_COST_CENTER801282281601530SA_TGT_FREQ801283051601530SA_TGT_CALLS80128881601530SA_FINANCEANP41.71

43、2813201631270SA_MAP_AGGR16.7128365416312100SA_COMPANY _DAILY_SAL ES 2311.1128744166980SA_DOCTOR_POTENTIAL57.164480730SA_CUST_CATEGORY57.164278073057.164SA_REF_MR_CITYSA_PROD_PR ICESA_SPLIT_H OSPTIALSA_USAGE_L EVEL STAGEPLANSA_PROD_GR OUP NEWTEST_CUSTO MERSA_MAP_SAP BPCS SKUSA_MAP_SAP _BPCS_CUST SA_M

44、AP_PON UMBER_BPCS TERRCODESA_NEW_USAGE LEVEL57.157.1252525256464646464642653552692564218252525256464646450521142.3分析结论从报告中，目前STAGE表空间存在以下几个问题：一：数据库表空间浪费比率很高，整个STAGE数据库表空间总的浪费比率为：73.33% 二：很多表记录不多，但占得空间巨大。比如占空间很大的几个表表名表大小浪费比率SA FINANCE BGT AMT3.66 （G）99.83SA IMS PRODUCT GROUP2.76（G）96.06SA CONSUMP ACT

45、 DEL2.22（G）73.94SA FINANCE ACT2.22（G）98.89三：以DEL结尾的几个表，占的空间很大，跟用户访谈得知，这几个表是备份表，不做删除 清理，不合理。2.4原因分析从上面的分析可以知道，目前数据库最主要的问题也是表空间浪费很高，造成空间浪费 很多。那么造成浪费的原因是什么呢？一般来说，造成浪费的原因有如下几个方面：一：频繁的 DEL 操作，造成表空间大量的空块，具体表现为表的 HWM 很高，那么 ORACLE在统计剩余空间时，是以HWM水位线上面的空间来计算的。也就是说HWM下 面的空间不能被重新分配，尽管可能已经没有数据。那么表空间经常会爆满。Oracle表段

46、中的高水位线HWM在 Oracle 数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线(High-warter mark, HWM)。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的, 也就是说 HWM 为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性， 就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低, 还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会 上涨，不会下跌。二：数据库发生行迁移。行迁移当修

47、改不是行的行时，当修改后的行长度大于修改前的行长度，并且该数据块中的空闲 空间已经比较小而不能完全容纳该行的数据时，就会发生行迁移。在这种情况下， Oracle 会将整行的数据迁移到一个新的数据块上，而将该行原先的空间只放一个指针，指向该行的 新的位置，并且该行原先空间的剩余空间不再被数据库使用，这些剩余的空间我们将其称之 为空洞，这就是产生表碎片的主要原因，表碎片基本上也是不可避免的，但是我们可以将其 降到一个我们可以接受的程度。从我们上面的分析来看，XX公司数据库发生行迁移的记录很少(CHAIN_PER很低)。 所以这个不是造成空间浪费的原因原因。所以，造成空间浪费的真正原因是一：频繁的D

48、EL操作，导致表的HWM被拉高。 二：备份表只插入，不做定期清理。面我们验证一下，删除操作对数据库表空间的影响。我们以SA_IMS_PRODUCT_GROUP这个打表为例，然后创建一个新表，表的记录跟原来 是一样多的，然后比较删除记录前跟删除记录后，表空间的情况。创建表：create table SA_IMS_PRODUCT_GROUP_HHS as select * from SA_IMS_PRODUCT_GROUP对表 SA_IMS_PRODUCT_GROUP_HHS 进行表分析analyze table SA_IMS_PRODUCT_GROUP_HHS compute statistic

49、s下面是删除操作之前的表的空间情况。可以发现，SA_IMS_PRODUCT_GROUP_HHS这个 表比SA_IMS_PRODUCT_GROUP这个表占的空间少了 2G多。仅为122M左右，原表为2.7G.；-3LE MA),ESEEr.TiPEWASTE.FERNUU R(7WSELOCKS丁广匕庶W-IG11W黜18C21搖:=SA/NflM2E_BGQIY-W99股2133G01ESA_CONSUMF_AC1C+2E-S9381丁咲&伽46117SA_NS_PRODUCWUP_HHS-63+122肌1目；1册13S.-.CALL8-W的11删超闻1熾:SA_COLPANY_DAILY_SAIEUL -W98.691W2E1S138S0W1-131醐TW12SDB0接下来，我们删除一些记录，然后看看 HWM 是否会下降。delete from SA_IMS_PRODUCT_GROUP_HHS where CITY_NAME=CHPA对表再进行分析，下