《Oracle数据库性能优化》由会员分享,可在线阅读,更多相关《Oracle数据库性能优化(26页珍藏版)》请在金锄头文库上搜索。
1、1系统问题XX公司Bl系统上线运行以来,客户反映系统目前存在着下面的几个问题,涉及到数据库和ETL.问题一:表空间增长太快,每个月需增加35G空间。问题二:ETLJOB会经常导致数据库产生表空间不足错误。2系统优化分析2.1分析思路要解决表空间的问题,我们必须搞清楚下面几个问题:思路一:真正每个月数据仓库增量是多少空间目的:得出一个正确的月表空间增长量。思路二:目前的数据仓库表空间是是如何分布的。目的:找出那些对象是最占空间,分析其合理性。2.2分析过程要得到真实的数据分布必须对表进行分析,首先需要对数据仓库的oracle数据库进行表分析,。执行下面脚本可以对数据库进行表分析。analyzct
2、sblc.5ql脚本一analyzetable;SA_IMS_PRODUCT_GROUPcomputestatistics;analyzetable;SA_CONSUMP_ACT_DELcomputestatistics;analyzetableSA_FINANCE_ACTcomputestatistics;analyzetable;SA_CONSUMP_TGT_DELcomputestatistics;analyzetableSA_FACT_IScomputestatistics;analyzetableSA_CPAcomputestatistics;analyzetable;SA_REF_
3、TERR_ALIGNMENT_DELcomputestatistics;analyzetable;SA_IMS_MTHLC_BKcomputestatistics;analyzetableSA_IMS_CHPAcomputestatistics;analyzetable;SA_FINANCE_PNLcomputestatistics;analyzetable;SA_CUST_TARG_SEGcomputestatistics;analyzetable;SA_CONSUMP_ACTcomputestatistics;analyzetable;SA_FINANCE_BScomputestatist
4、ics;analyzetableSA_FINANCE_BGT_QTYcomputestatistics;analyzetable;SA_C0NSUMP_ACT0423computestatistics;analyzetableSA_CALLScomputestatistics;analyzetable;SA_COMPANY_DAILY_SALES_ALLcomputestatistics;analyzetable;SA_IMS_MTHLCcomputestatistics;analyzetable;SA_IMS_MTHUScomputestatistics;analyzetable;SA_CO
5、NSUMP_TGTcomputestatistics;analyzetable;TEST_TABLEcomputestatistics;analyzetable;SA_DOCTOR_CYCLE_EXTRACTcomputestatistics;analyzetable;SA_EXCHANGE_ACTcomputestatistics;analyzetableSA_IMS_MTHSTcomputestatistics;analyzetableSA_FINANCE_CONCUR_DETAILcomputestatistics;analyzetableWK_SA_CPAcomputestatisti
6、cs;analyzetableSA_REF_TERR_ALIGNMENTcomputestatistics;analyzetableSA_CONSUMP_TGT0316computestatistics;analyzetableSA_CUSTOMERcomputestatistics;analyzetableSA_CUSTcomputestatistics;analyzetableSA_HKAPIcomputestatistics;analyzetableSA_CONSUMP_TGT_AMTcomputestatistics;analyzetableSA_CUST0423computestat
7、istics;analyzetableSA_COMMUNITY_TGTcomputestatistics;analyzetableSA_CM_WORKING_DATEcomputestatistics;analyzetableSA_CM_IN_MARKET_SALES_CUcomputestatistics;analyzetableSA_DASH_SFEcomputestatistics;analyzetableSA_CPA_TERRcomputestatistics;analyzetableIDX_SA_CUSTcomputestatistics;analyzetableSA_REF_EMP
8、_TERRcomputestatistics;analyzetableSA_CM_IN_MARKET_SALES_OCMcomputestatistics;analyzetableSA_COMPANY_MONTHLY_SALEScomputestatistics;analyzetableSA_MAP_YEARMONTH_RATEcomputestatistics;analyzetableSA_FINANCE_ACT_BPCS_TESTcomputestatistics;analyzetableSA_REF_EMP_TERR0413computestatistics;analyzetableSA
9、_FINANCE_ACT_BPCScomputestatistics;analyzetableIDX$_143D0001computestatistics;analyzetableSA_COMPANY_DAILY_SALES_ALL_23computestatistics;analyzetableSA_COMMUNITY_TGT_AMTcomputestatistics;analyzetableSA_DASH_MONTHLY_MAT_SALEScomputestatistics;#analyzetableSA_DASH_ATTRITIONcomputestatistics;analyzetab
10、leSA_DASH_MARKET_SHAREcomputestatistics;analyzetableSA_CORPcomputestatistics;analyzetableSA_COMMUNITY_ACTcomputestatistics;analyzetableSA_CM_IN_MARKET_SALES_CU_DELcomputestatistics;analyzetableWK_SA_COMPETITOR_PRODUCTcomputestatistics;analyzetableSA_IMS_ANTI_HYPER_TESTcomputestatistics;analyzetableS
11、A_TERRITORYcomputestatistics;analyzetableTEST_CUSTOMER_TGTcomputestatistics;analyzetableSA_COMPETITOR_PRODUCTcomputestatistics;analyzetableSA_CM_IN_MARKET_SALES_OCM_DELcomputestatistics;analyzetableSA_COMPANY_DAILY_SALEScomputestatistics;analyzetableSA_REF_MR_CORPcomputestatistics;analyzetableSA_IS_
12、MATERIALcomputestatistics;analyzetableSA_IS_KEY_MESSAGEcomputestatistics;analyzetableSA_DRIVER_REASONcomputestatistics;analyzetableSA_REF_MR_CUSTcomputestatistics;analyzetableSA_BARRIER_REASONcomputestatistics;analyzetableSA_ACCOUNTcomputestatistics;analyzetableSA_REF_MR_PRODcomputestatistics;analyz
13、etableSA_REF_VENDOR_EMPcomputestatistics;analyzetableSA_FINANCE_ACT_ADJUSTMENTcomputestatistics;analyzetableSA_RANKING_MESSAGEcomputestatistics;analyzetableSA_TCcomputestatistics;analyzetableSA_CUST_PARENTcomputestatistics;analyzetableSA_EXCHANGE_RATE_ACT_MTHcomputestatistics;analyzetableSA_EXCHANGE_RATEcomputestatistics;analyzetableSA_DASH_GROWTH_BUBBLEcomputestatistics;analyzetableSA_COST_CENTERcomputestatistics;analyzetablePM_KEYcomputestatistics;analyzetableSA_CM_REF_TERR_OCMcomputestatistics;analyzetableSA_CM_REF_TERR_CUcom