内容发布更新时间 : 2024/6/24 21:02:33星期一 下面是文章的全部内容请认真阅读。

1 系统问题

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

问题一：表空间增长太快，每个月需增加3—5G空间。 问题二：ETL JOB会经常导致数据库产生表空间不足错误。

2 系统优化分析 2.1 分析思路

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

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

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

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

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

2.2 分析过程

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

脚本一

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 analyze table SA_CONSUMP_ACT analyze table SA_FINANCE_BS analyze table SA_FINANCE_BGT_QTY analyze table SA_CONSUMP_ACT0423 analyze table SA_CALLS analyze table SA_COMPANY_DAILY_SALES_ALL analyze table SA_IMS_MTHLC analyze table SA_IMS_MTHUS analyze table SA_CONSUMP_TGT analyze table TEST_TABLE analyze table SA_DOCTOR_CYCLE_EXTRACT analyze table SA_EXCHANGE_ACT analyze table SA_IMS_MTHST analyze table SA_FINANCE_CONCUR_DETAIL analyze table WK_SA_CPA analyze table SA_REF_TERR_ALIGNMENT analyze table SA_CONSUMP_TGT0316 analyze table SA_CUSTOMER analyze table SA_CUST analyze table SA_HKAPI analyze table SA_CONSUMP_TGT_AMT analyze table SA_CUST0423 analyze table SA_COMMUNITY_TGT analyze table SA_CM_WORKING_DATE analyze table SA_CM_IN_MARKET_SALES_CU analyze table SA_DASH_SFE analyze table SA_CPA_TERR analyze table IDX_SA_CUST analyze table SA_REF_EMP_TERR analyze table SA_CM_IN_MARKET_SALES_OCM analyze table SA_COMPANY_MONTHLY_SALES analyze table SA_MAP_YEARMONTH_RATE analyze table SA_FINANCE_ACT_BPCS_TEST analyze table SA_REF_EMP_TERR0413 compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; compute statistics; 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; 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_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; analyze 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 compute 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 statistics; 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 compute 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 compute statistics;