《Oracle数据库性能优化(碎片整理)》由会员分享,可在线阅读,更多相关《Oracle数据库性能优化(碎片整理)(20页珍藏版)》请在金锄头文库上搜索。
1、1系统问题XX公司BI系统上线运行以来,客户反映系统目前存在着下面的几个问题,涉及到数据库和ETL.问题一:表空间增长太快,每个月需增加35G空间。问题二:ETLJOB会经常导致数据库产生表空间不足错误。2系统优化分析2.1分析思路要解决表空间的问题,我们必须搞清楚下面几个问题:思路一:真正每个月数据仓库增量是多少空间?目的:得出一个正确的月表空间增长量。思路二:目前的数据仓库表空间是是如何分布的。目的:找出那些对象是最占空间,分析其合理性。2.2分析过程要得到真实的数据分布必须对表进行分析,首先需要对数据仓库的oracle数据库进行表分析,。执行下面脚本可以对数据库进行表分析。脚本一comp
2、utestatistics;computestatistics;computestatistics;analyzetableSA_IMS_PRODUCT_GROUPanalyzetableSA_CONSUMP_ACT_DELanalyzetableSA_FINANCE_ACTanalyzetableSA_CONSUMP_TGT_DELanalyzetableSA_FACT_ISanalyzetableSA_CPAanalyzetableSA_REF_TERR_ALIGNMENT_DELanalyzetableSA_IMS_MTHLC_BKanalyzetableSA_IMS_CHPAanaly
3、zetableSA_FINANCE_PNLanalyzetableSA_CUST_TARG_SEGanalyzetableSA_CONSUMP_ACTanalyzetableSA_FINANCE_BSanalyzetableSA_FINANCE_BGT_QTYanalyzetableSA_CONSUMP_ACT0423analyzetableSA_CALLSanalyzetableSA_COMPANY_DAILY_SALES_ALLanalyzetableSA_IMS_MTHLCanalyzetableSA_IMS_MTHUSanalyzetableSA_CONSUMP_TGTanalyzet
4、ableTEST_TABLEanalyzetableSA_DOCTOR_CYCLE_EXTRACTanalyzetableSA_EXCHANGE_ACTanalyzetableSA_IMS_MTHSTanalyzetableSA_FINANCE_CONCUR_DETAILanalyzetableWK_SA_CPAanalyzetableSA_REF_TERR_ALIGNMENTanalyzetableSA_CONSUMP_TGT0316analyzetableSA_CUSTOMERanalyzetableSA_CUSTanalyzetableSA_HKAPIanalyzetableSA_CON
5、SUMP_TGT_AMTanalyzetableSA_CUST0423analyzetableSA_COMMUNITY_TGTanalyzetableSA_CM_WORKING_DATEanalyzetableSA_CM_IN_MARKET_SALES_CUanalyzetableSA_DASH_SFEanalyzetableSA_CPA_TERRanalyzetableIDX_SA_CUSTanalyzetableSA_REF_EMP_TERRanalyzetableSA_CM_IN_MARKET_SALES_OCManalyzetableSA_COMPANY_MONTHLY_SALESan
6、alyzetableSA_MAP_YEARMONTH_RATEanalyzetableSA_FINANCE_ACT_BPCS_TESTanalyzetableSA_REF_EMP_TERR0413analyzetableSA_FINANCE_ACT_BPCScomputestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computest
7、atistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;com
8、putestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatisti
9、cs;computestatistics;analyzetableIDX$_143D0001analyzetableSA_COMPANY_DAILY_SALES_ALL_23analyzetableSA_COMMUNITY_TGT_AMTanalyzetableSA_DASH_MONTHLY_MAT_SALESanalyzetableSA_DASH_ATTRITIONanalyzetableSA_DASH_MARKET_SHAREanalyzetableSA_CORPanalyzetableSA_COMMUNITY_ACTanalyzetableSA_CM_IN_MARKET_SALES_CU
10、_DELanalyzetableWK_SA_COMPETITOR_PRODUCTanalyzetableSA_IMS_ANTI_HYPER_TESTanalyzetableSA_TERRITORYanalyzetableTEST_CUSTOMER_TGTanalyzetableSA_COMPETITOR_PRODUCTanalyzetableSA_CM_IN_MARKET_SALES_OCM_DanalyzetableSA_COMPANY_DAILY_SALESanalyzetableSA_REF_MR_CORPanalyzetableSA_IS_MATERIALanalyzetableSA_
11、IS_KEY_MESSAGEanalyzetableSA_DRIVER_REASONanalyzetableSA_REF_MR_CUSTanalyzetableSA_BARRIER_REASONanalyzetableSA_ACCOUNTanalyzetableSA_REF_MR_PRODanalyzetableSA_REF_VENDOR_EMPanalyzetableSA_FINANCE_ACT_ADJUSTMENTanalyzetableSA_RANKING_MESSAGEanalyzetableSA_TCanalyzetableSA_CUST_PARENTanalyzetableSA_E
12、XCHANGE_RATE_ACT_MTHanalyzetableSA_EXCHANGE_RATEanalyzetableSA_DASH_GROWTH_BUBBLEanalyzetableSA_COST_CENTERanalyzetablePM_KEYanalyzetableSA_CM_REF_TERR_OCManalyzetableSA_CM_REF_TERR_CUanalyzetableSA_BPCS_TO_ISMIanalyzetablePRODUCTanalyzetableSA_SHIFT_LEVELanalyzetableSA_SFE_VARIABLESanalyzetableSA_P
13、RODUCTanalyzetableSA_PATIENT_TYPE_ENanalyzetableSA_MR_KEY_PRODUCTanalyzetableSA_MAP_TEAM_BRANDcomputestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computes
14、tatistics;computestatistics;Lcomputestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;c
15、omputestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;computestatistics;analyzetableSA_MAP_CUSTOMERanalyzetableSA_MAP_AGGRanalyzetableSA_LOCATIONanalyzetableSA_INCREMENTAL_SHIFTanalyzetableSA_IMS_CITYanalyzetableSA_TGT_FREQanalyzetableSA_TGT_CALLSanalyzetableSA_FINANCE_ANPanalyzetableSA_COMPANY_DAILY_SALES_23analyzetableS