Oracle数据库性能优化

1系统问题

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

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

问题二：ETL JOB会经常导致数据库产生表空间不足错误。

2系统优化分析

2.1分析思路

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

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

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

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

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

2.2分析过程

要得到真实的数据分布必须对表进行分析，首先需要对数据仓库的oracle数据库进行表分析，。执行下面脚本可以对数据库进行表分析。

v1.0 可编辑可修改

脚本一

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 statistics;

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 statistics;

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 statistics;

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_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 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 SA_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 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 compute statistics; analyze table SA_COMMUNITY_TGT_AMT compute statistics; analyze table SA_DASH_MONTHLY_MAT_SALES compute statistics;