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

1、Oracle数据库性能优化碎片整理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 statist

3、ics;analyze 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 statisti

4、cs;analyze table 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 statistic

5、s;analyze table 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

6、table SA_FINANCE_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

7、SA_HKAPI compute 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 S

8、A_DASH_SFE compute 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

9、SA_MAP_YEARMONTH_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 comp

10、ute statistics;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

11、_ACT compute 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;ana

12、lyze table SA_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 c

13、ompute statistics;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 s

14、tatistics;analyze 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 com

15、pute statistics;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;analyze 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 com