Oracle数据库性能优化碎片整理Word文档格式.docx
《Oracle数据库性能优化碎片整理Word文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle数据库性能优化碎片整理Word文档格式.docx（21页珍藏版）》请在冰豆网上搜索。
analyzetableSA_REF_TERR_ALIGNMENT_DELcomputestatistics;
analyzetableSA_IMS_MTHLC_BKcomputestatistics;
analyzetableSA_IMS_CHPAcomputestatistics;
analyzetableSA_FINANCE_PNLcomputestatistics;
analyzetableSA_CUST_TARG_SEGcomputestatistics;
analyzetableSA_CONSUMP_ACTcomputestatistics;
analyzetableSA_FINANCE_BScomputestatistics;
analyzetableSA_FINANCE_BGT_QTYcomputestatistics;
analyzetableSA_CONSUMP_ACT0423computestatistics;
analyzetableSA_CALLScomputestatistics;
analyzetableSA_COMPANY_DAILY_SALES_ALLcomputestatistics;
analyzetableSA_IMS_MTHLCcomputestatistics;
analyzetableSA_IMS_MTHUScomputestatistics;
analyzetableSA_CONSUMP_TGTcomputestatistics;
analyzetableTEST_TABLEcomputestatistics;
analyzetableSA_DOCTOR_CYCLE_EXTRACTcomputestatistics;
analyzetableSA_EXCHANGE_ACTcomputestatistics;
analyzetableSA_IMS_MTHSTcomputestatistics;
analyzetableSA_FINANCE_CONCUR_DETAILcomputestatistics;
analyzetableWK_SA_CPAcomputestatistics;
analyzetableSA_REF_TERR_ALIGNMENTcomputestatistics;
analyzetableSA_CONSUMP_TGT0316computestatistics;
analyzetableSA_CUSTOMERcomputestatistics;
analyzetableSA_CUSTcomputestatistics;
analyzetableSA_HKAPIcomputestatistics;
analyzetableSA_CONSUMP_TGT_AMTcomputestatistics;
analyzetableSA_CUST0423computestatistics;
analyzetableSA_COMMUNITY_TGTcomputestatistics;
analyzetableSA_CM_WORKING_DATEcomputestatistics;
analyzetableSA_CM_IN_MARKET_SALES_CUcomputestatistics;
analyzetableSA_DASH_SFEcomputestatistics;
analyzetableSA_CPA_TERRcomputestatistics;
analyzetableIDX_SA_CUSTcomputestatistics;
analyzetableSA_REF_EMP_TERRcomputestatistics;
analyzetableSA_CM_IN_MARKET_SALES_OCMcomputestatistics;
analyzetableSA_COMPANY_MONTHLY_SALEScomputestatistics;
analyzetableSA_MAP_YEARMONTH_RATEcomputestatistics;
analyzetableSA_FINANCE_ACT_BPCS_TESTcomputestatistics;
analyzetableSA_REF_EMP_TERR0413computestatistics;
analyzetableSA_FINANCE_ACT_BPCScomputestatistics;
analyzetableIDX$$_143D0001computestatistics;
analyzetableSA_COMPANY_DAILY_SALES_ALL_23computestatistics;
analyzetableSA_COMMUNITY_TGT_AMTcomputestatistics;
analyzetableSA_DASH_MONTHLY_MAT_SALEScomputestatistics;
analyzetableSA_DASH_ATTRITIONcomputestatistics;
analyzetableSA_DASH_MARKET_SHAREcomputestatistics;
analyzetableSA_CORPcomputestatistics;
analyzetableSA_COMMUNITY_ACTcomputestatistics;
analyzetableSA_CM_IN_MARKET_SALES_CU_DELcomputestatistics;
analyzetableWK_SA_COMPETITOR_PRODUCTcomputestatistics;
analyzetableSA_IMS_ANTI_HYPER_TESTcomputestatistics;
analyzetableSA_TERRITORYcomputestatistics;
analyzetableTEST_CUSTOMER_TGTcomputestatistics;
analyzetableSA_COMPETITOR_PRODUCTcomputestatistics;
analyzetableSA_CM_IN_MARKET_SALES_OCM_DELcomputestatistics;
analyzetableSA_COMPANY_DAILY_SALEScomputestatistics;
analyzetableSA_REF_MR_CORPcomputestatistics;
analyzetableSA_IS_MATERIALcomputestatistics;
analyzetableSA_IS_KEY_MESSAGEcomputestatistics;
analyzetableSA_DRIVER_REASONcomputestatistics;
analyzetableSA_REF_MR_CUSTcomputestatistics;
analyzetableSA_BARRIER_REASONcomputestatistics;
analyzetableSA_ACCOUNTcomputestatistics;
analyzetableSA_REF_MR_PRODcomputestatistics;
analyzetableSA_REF_VENDOR_EMPcomputestatistics;
analyzetableSA_FINANCE_ACT_ADJUSTMENTcomputestatistics;
analyzetableSA_RANKING_MESSAGEcomputestatistics;
analyzetableSA_TCcomputestatistics;
analyzetableSA_CUST_PARENTcomputestatistics;
analyzetableSA_EXCHANGE_RATE_ACT_MTHcomputestatistics;
analyzetableSA_EXCHANGE_RATEcomputestatistics;
analyzetableSA_DASH_GROWTH_BUBBLEcomputestatistics;
analyzetableSA_COST_CENTERcomputestatistics;
analyzetablePM_KEYcomputestatistics;
analyzetableSA_CM_REF_TERR_OCMcomputestatistics;
analyzetableSA_CM_REF_TERR_CUcomputestatistics;
analyzetableSA_BPCS_TO_ISMIcomputestatistics;
analyzetablePRODUCTcomputestatistics;
analyzetableSA_SHIFT_LEVELcomputestatistics;
analyzetableSA_SFE_VARIABLEScomputestatistics;
analyzetableSA_PRODUCTcomputestatistics;
analyzetableSA_PATIENT_TYPE_ENcomputestatistics;
analyzetableSA_MR_KEY_PRODUCTcomputestatistics;
analyzetableSA_MAP_TEAM_BRANDcomputestatistics;
analyzetableSA_MAP_CUSTOMERcomputestatistics;
analyzetableSA_MAP_AGGRcomputestatistics;
analyzetableSA_LOCATIONcomputestatistics;
analyzetableSA_INCREMENTAL_SHIFTcomputestatistics;
analyzetableSA_IMS_CITYcomputestatistics;
analyzetableSA_TGT_FREQcomputestatistics;
analyzetableSA_TGT_CALLScomputestatistics;
analyzetableSA_FINANCE_ANPcomputestatistics;
analyzetableSA_COMPANY_DAILY_SALES_23computestatistics;
analyzetableSA_GEOGRAPHYcomputestatistics;
analyzetableSA_MAP_PONUMBER_BPCSTERRCODEcomputestatistics;
analyzetablePK_SA_MAP_PONUMBER_BPCSTERRCODcomputestatistics;
analyzetableSA_MAP_SAP_BPCS_CUSTcomputestatistics;
analyzetablePK_SA_MAP_SAP_BPCS_CUSTcomputestatistics;
analyzetableSA_MAP_SAP_BPCS_SKUcomputestatistics;
analyzetablePK_SA_MAP_SAP_BPCS_SKUcomputestatistics;
analyzetableSA_REF_DAYcomputestatistics;
analyzetableSTAGEPLANcomputestatistics;
analyzetableSA_SPLIT_HOSPTIALcomputestatistics;
analyzetableSA_USAGE_LEVELcomputestatistics;
analyzetableTEST_CUSTOMERcomputestatistics;
analyzetableSA_NEW_USAGE_LEVELcomputestatistics;
analyzetableSA_PROD_GROUP_NEWcomputestatistics;
通过表分析，我们可以得到数据仓库中每个表的记录行数，BLOCK数，EMPTYBLOCKS数等等关键的数据分布数据，分析后，这些数据会存放在系统表，USER_TABLES和USER_SEGMENTS中。
通过对这些系统查询，我们可以得到整个数据库的数据分布情况，从而为分析问题原因提供充足基础。
执行下面的脚本，可以得到一个数据库的数据分布报告：
脚本二
SELECTSEGMENT_NAMETABLE_NAME,SEGMENT_TYPE,
GREATEST（ROUND（100*（NVL（HWM-AVG_USED_BLOCKS,0）/GREATEST（NVL（HWM,1）,1））,2）,0）WASTE_PER,
ROUND（BYTES/1024,2）TABLE_KB,NUM_ROWS,
BLOCKS,EMPTY_BLOCKS,HWMHIGHWATER_MARK,AVG_USED_BLOCKS,
CHAIN_PER,EXTENTS,MAX_EXTENTS,ALLO_EXTENT_PER,
DECODE（GREATEST（MAX_FREE_SPACE-NEXT_EXTENT,0）,0,'
N'
'
Y'
）CAN_EXTEND_SPACE,
NEXT_EXTENT,MAX_FREE_SPACE,
O_TABLESPACE_NAMETABLESPACE_NAME
FROM
（SELECTA.SEGMENT_NAME,A.SEGMENT_TYPE,A.BYTES,
B.NUM_ROWS,A.BLOCKSBLOCKS,B.EMPTY_BLOCKSEMPTY_BLOCKS,
A.BLOCKS-B.EMPTY_BLOCKS-1HWM,
DECODE（ROUND（（B.AVG_ROW_LEN*NUM_ROWS*（1+（PCT_FREE/100）））/C.BLOCK_SIZE,0）,
0,1,
ROUND（（B.AVG_ROW_LEN*NUM_ROWS*（1+（PCT_FREE/100）））/C.BLOCK_SIZE,0）
）+2AVG_USED_BLOCKS,
ROUND（100*（NVL（B.CHAIN_CNT,0）/GREATEST（NVL（B.NUM_ROWS,1）,1））,2）CHAIN_PER,
ROUND（100*（A.EXTENTS/A.MAX_EXTENTS）,2）ALLO_EXTENT_PER,A.EXTENTSEXTENTS,
A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENTNEXT_EXTENT,B.TABLESPACE_NAMEO_TABLESPACE_NAME
FROMUSER_SEGMENTSA,
USER_TABLESB,
user_tablespacesC
WHERE
SEGMENT_NAME=TABLE_NAMEand
SEGMENT_TYPE='
TABLE'
AND
B.TABLESPACE_NAME=C.TABLESPACE_NAME
UNIONALL
SELECTSEGMENT_NAME||'
.'
||B.PARTITION_NAME,SEGMENT_TYPE,BYTES,
DECODE（ROUND（（B.AVG_ROW_LEN*B.NUM_ROWS*（1+（B.PCT_FREE/100）））/C.BLOCK_SIZE,0）,
ROUND（（B.AVG_ROW_LEN*B.NUM_ROWS*（1+（B.PCT_FREE/100）））/C.BLOCK_SIZE,0）
ROUND（100*（NVL（B.CHAIN_CNT,0）/GREATEST（NVL（B.NUM_ROWS,1）,1））,2）CHAIN_PER,
ROUND（100*（A.EXTENTS/A.MAX_EXTENTS）,2）ALLO_EXTENT_PER,A.EXTENTSEXTENTS,
A.MAX_EXTENTSMAX_EXTENTS,B.NEXT_EXTENT,
B.TABLESPACE_NAMEO_TABLESPACE_NAME
USER_TAB_PARTITIONSB,
USER_TABLESPACESC,
USER_TABLESD
WHERE
SEGMENT_NAME=B.TABLE_NAMEand
TABLEPARTITION'
B.TABLESPACE_NAME=C.TABLESPACE_NAMEAND
D.TABLE_NAME=B.TABLE_NAMEAND
A.PARTITION_NAME=B.PARTITION_NAME）,
（SELECTTABLESPACE_NAMEF_TABLESPACE_NAME,MAX（BYTES）
MAX_FREE_SPACE
FROMUSER_FREE_SPACE
GROUPBYTABLESPACE_NAME）
WHEREF_TABLESPACE_NAME=O_TABLESPACE_NAMEAND
GREATEST（ROUND（100*（NVL（HWM-AVG_USED_BLOCKS,0）/GREATEST（NVL（HWM,1）,1））,2）,0）>
2
ANDBLOCKS>
1
ORDERBY4DESC,3DESC,2ASC;
运行脚本二后，我们以生产环境的STAGESCHEMA为例。
得到报告如下：
报告的各列含义如下：
WASTE_PER：
空间浪费比率，实际用到的数据块/分配给该表的数据块。
TABLE_KB：
表占空间大小，以KB为单位。
NUM_ROWS:
表中记录行数。
BLOCKS：
分配给该表的数据块数。
EMPTY_BLOCKS：
已分配给该表但尚未使用的数据块。
HIGHWATER_MARK：
表的高水位标志。
AVG_USED_BLOCKS：
实际有数据的数据块数。
CHAIN_PER：
发生数据行迁移的记录数。
各列有如下关系：
BLOCKS=EMPTY_BLOCKS+HIGHWATER_MARK+1
WASTE_PER=（HIGHWATER_MARK-AVG_USED_BLOCKS）/HIGHWATER_MARK
用下面的一个图可以直观了解他们之间的关系：
图中红色块表示在HIGHWATER_MARK下已分配但未被使用的块，形成空洞。
（该报告以表大小倒序排序）
TABLE_NAME
WASTE_PER
TABLE_KB
NUM_ROWS
BLOCKS
EMPTY_BLOCKS
HIGHWATER_MARK
AVG_USED_BLOCKS
CHAIN_PER
SA_FINANCE_BGT_AMT
99.8
3833024
91287
479128
479127
836
SA_IMS_PRODUCT_GROUP
96.1
2893504
1292751
361688
361687
14236
SA_CONSUMP_ACT_DEL
73.9
2329600
7722904
291200
657
290542
75704
SA_FINANCE_ACT
98.9
2323072
249938
290384
290383
3224
SA_CONSUMP_TGT_DEL
10.2
2102272
2.3E+07
262784
622
262161
235433
SA_FACT_IS
55.2
1918784
1927073
239848
239847
107388
SA_CPA
94.8
1329152
452886
166144
166143
8577
SA_REF_TERR_ALIGNMENT_DEL
31.8
1106944
1.3E+07
138368
537
137830
939
升级会员 