Oracle数据库性能优化(碎片整理).doc
《Oracle数据库性能优化(碎片整理).doc》由会员分享,可在线阅读,更多相关《Oracle数据库性能优化(碎片整理).doc(17页珍藏版)》请在文库网上搜索。
1、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 statistics;analyze
3、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 t
4、able 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 ta
5、ble 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 table SA_FIN
6、ANCE_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 com
7、pute 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 c
8、ompute 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_YEARM
9、ONTH_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 statisti
10、cs;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
11、 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 S
12、A_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 stati
13、stics;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;an
14、alyze 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 statist
15、ics;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;a
16、nalyze 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;analyze table S
17、A_MAP_TEAM_BRAND compute statistics;analyze table SA_MAP_CUSTOMER compute statistics;analyze table SA_MAP_AGGR compute statistics;analyze table SA_LOCATION compute statistics;analyze table SA_INCREMENTAL_SHIFT compute statistics;analyze table SA_IMS_CITY compute statistics;analyze table SA_TGT_FREQ
18、compute statistics;analyze table SA_TGT_CALLS compute statistics;analyze table SA_FINANCE_ANP compute statistics;analyze table SA_COMPANY_DAILY_SALES_23 compute statistics;analyze table SA_GEOGRAPHY compute statistics;analyze table SA_MAP_PONUMBER_BPCSTERRCODE compute statistics;analyze table PK_SA_
19、MAP_PONUMBER_BPCSTERRCOD compute statistics;analyze table SA_MAP_SAP_BPCS_CUST compute statistics;analyze table PK_SA_MAP_SAP_BPCS_CUST compute statistics;analyze table SA_MAP_SAP_BPCS_SKU compute statistics;analyze table PK_SA_MAP_SAP_BPCS_SKU compute statistics;analyze table SA_REF_DAY compute sta
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 性能 优化 碎片 整理