Oracle数据库性能优化碎片整理.docx

Oracle数据库性能优化碎片整理.docx

《Oracle数据库性能优化碎片整理.docx》由会员分享，可在线阅读，更多相关《Oracle数据库性能优化碎片整理.docx（21页珍藏版）》请在冰豆网上搜索。

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

1系统问题

XX公司BI系统上线运行以来，客户反映系统目前存在着下面的几个问题，涉及到数据库和ETL.

问题一：

表空间增长太快，每个月需增加3—5G空间。

问题二：

ETLJOB会经常导致数据库产生表空间不足错误。

2系统优化分析

2.1分析思路

要解决表空间的问题，我们必须搞清楚下面几个问题：

思路一：

真正每个月数据仓库增量是多少空间

目的：

得出一个正确的月表空间增长量。

思路二：

目前的数据仓库表空间是是如何分布的。

目的：

找出那些对象是最占空间，分析其合理性。

2.2分析过程

要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。

执行下面脚本可以对数据库进行表分析。

脚本一

analyzetableSA_IMS_PRODUCT_GROUPcomputestatistics;

analyzetableSA_CONSUMP_ACT_DELcomputestatistics;

analyzetableSA_FINANCE_ACTcomputestatistics;

analyzetableSA_CONSUMP_TGT_DELcomputestatistics;

analyzetableSA_FACT_IScomputestatistics;

analyzetableSA_CPAcomputestatistics;

analyzetableSA_REF_TERR_ALIGNMENT_DELcomputestatistics;

analyzetableSA_IMS_MTHLC_BKcomputestatistics;

analyzetableSA_IMS_CHPAcomputestatistics;

analyzetableSA_FINANCE_PNLcomputestatistics;

analyzetableSA_CUST_TARG_SEGcomputestatistics;

analyzetableSA_CONSUMP_ACTcomputestatistics;

analyzetableSA_FINANCE_BScomputestatistics;

analyzetableSA_FINANCE_BGT_QTYcomputestatistics;

analyzetableSA_CONSUMP_ACT0423computestatistics;

analyzetableSA_CALLScomputestatistics;

analyzetableSA_COMPANY_DAILY_SALES_ALLcomputestatistics;

analyzetableSA_IMS_MTHLCcomputestatistics;

analyzetableSA_IMS_MTHUScomputestatistics;

analyzetableSA_CONSUMP_TGTcomputestatistics;

analyzetableTEST_TABLEcomputestatistics;

analyzetableSA_DOCTOR_CYCLE_EXTRACTcomputestatistics;

analyzetableSA_EXCHANGE_ACTcomputestatistics;

analyzetableSA_IMS_MTHSTcomputestatistics;

analyzetableSA_FINANCE_CONCUR_DETAILcomputestatistics;

analyzetableWK_SA_CPAcomputestatistics;

analyzetableSA_REF_TERR_ALIGNMENTcomputestatistics;

analyzetableSA_CONSUMP_TGT0316computestatistics;

analyzetableSA_CUSTOMERcomputestatistics;

analyzetableSA_CUSTcomputestatistics;

analyzetableSA_HKAPIcomputestatistics;

analyzetableSA_CONSUMP_TGT_AMTcomputestatistics;

analyzetableSA_CUST0423computestatistics;

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_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_FINANCE_ACT_BPCScomputestatistics;

analyzetableIDX$$_143D0001computestatistics;

analyzetableSA_COMPANY_DAILY_SALES_ALL_23computestatistics;

analyzetableSA_COMMUNITY_TGT_AMTcomputestatistics;

analyzetableSA_DASH_MONTHLY_MAT_SALEScomputestatistics;

analyzetableSA_DASH_ATTRITIONcomputestatistics;

analyzetableSA_DASH_MARKET_SHAREcomputestatistics;

analyzetableSA_CORPcomputestatistics;

analyzetableSA_COMMUNITY_ACTcomputestatistics;

analyzetableSA_CM_IN_MARKET_SALES_CU_DELcomputestatistics;

analyzetableWK_SA_COMPETITOR_PRODUCTcomputestatistics;

analyzetableSA_IMS_ANTI_HYPER_TESTcomputestatistics;

analyzetableSA_TERRITORYcomputestatistics;

analyzetableTEST_CUSTOMER_TGTcomputestatistics;

analyzetableSA_COMPETITOR_PRODUCTcomputestatistics;

analyzetableSA_CM_IN_MARKET_SALES_OCM_DELcomputestatistics;

analyzetableSA_COMPANY_DAILY_SALEScomputestatistics;

analyzetableSA_REF_MR_CORPcomputestatistics;

analyzetableSA_IS_MATERIALcomputestatistics;

analyzetableSA_IS_KEY_MESSAGEcomputestatistics;

analyzetableSA_DRIVER_REASONcomputestatistics;

analyzetableSA_REF_MR_CUSTcomputestatistics;

analyzetableSA_BARRIER_REASONcomputestatistics;

analyzetableSA_ACCOUNTcomputestatistics;

analyzetableSA_REF_MR_PRODcomputestatistics;

analyzetableSA_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_CUcomputestatistics;

analyzetableSA_BPCS_TO_ISMIcomputestatistics;

analyzetablePRODUCTcomputestatistics;

analyzetableSA_SHIFT_LEVELcomputestatistics;

analyzetableSA_SFE_VARIABLEScomputestatistics;

analyzetableSA_PRODUCTcomputestatistics;

analyzetableSA_PATIENT_TYPE_ENcomputestatistics;

analyzetableSA_MR_KEY_PRODUCTcom