oracle数据库优化报告Word格式文档下载.docx
《oracle数据库优化报告Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《oracle数据库优化报告Word格式文档下载.docx（54页珍藏版）》请在冰点文库上搜索。
修改“优化后效果”；
修改“后续举措”
1.
概述
从2013年9月份开始跟踪及分析，发现浙江的ITSM数据库在环境、设计及SQL三方面，都存在不少问题。
在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。
下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度，从第5点的整改计划中来看，目前来看已经有效缓解高峰期CPU使用用率100%的情况，但IO、内存资源使用率依旧挺高，比较可疑的SQL语句已经罗列出来，将按整改计划表继续改造，应会有所缓解。
随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库主机硬件扩容也亟待解决。
在跟进过程中出现了5次前台无法访问报错的故障，故障分析报告一并整理其中，详见第8点中的附件《故障分析.zip》，近段时间故障概况汇总如下：
故障时间
故障原因
是否解决
备注
2013.10.17
（两次）
由于节点1系统被重启，业务访问高峰期时，节点2无法承载压力导致，待节点1恢复正常后，故障消失，当业务量上来之后，后台应用由于之前的故障导致session积压，再次产生性能问题。
是
故障所涉问题SQL都已汇集到本文
2013.10.22
由于SQL性能问题引起，当问题sql达到一定并发量后将引发主机资源耗尽，导致前台无法访问问题。
2013.10.25
同上
2013.11.07
2.环境类优化
1
2
2.1统计信息收集被关闭
浙江系统统计信息未自动收集，非常奇怪（看了集团、安徽、黑龙江等其他工程点，都是正常有收集,ENABLEED为TRUE）!
这个关闭统计信息，将会对系统的性能造成巨大的影响。
1.自动收集统计信息居然被关闭了，如下：
JOB_NAMEPROGRAM_NAMESTATEENABLED
--------------------------------------------------------------------------------------------------------------------
GATHER_STATS_JOBGATHER_STATS_PROGDISABLEDFALSE
selectowner,count（*）
fromdba_tab_statisticst
where（t.last_analyzedisnullort.last_analyzed<
sysdate-100）
andtable_namenotlike'
BIN$%'
groupbyowner
orderbyowner;
2.有如此多的对象显示很久或者根本就没有收集
OWNERCOUNT（*）
---------------------------------------------------------------------------------------------------------------------------
BASEDBA80
BASEDBA_ZJ73
BOSSWG18122
BOSSWG_ZJ7095
DBSNMP22
OUTLN3
PERFSTAT1
SYS1474
SYSTEM165
TSMSYS1
WAP_BOSSWG12
WMSYS42
2.2BOSSWG_ZJ用户需删除了
浙江V3已经升级1年多了，BOSSWG_ZJ和BASEDB_ZJ可以删除了。
2.3部分大表要考虑建分区和分区清理
以下表分区偏多，预示历史记录保留太久，如下：
--以下4张都是拨测的表
TABLE_OWNERTABLE_NAMECNT
--------------------------------------------------------------------------------------------------------
BOSSWGDTP_TP_INST867
BOSSWGDTP_REAL_INST366
BOSSWGDTP_ACTIVITY_INST366
BOSSWGDTP_FLOW_INST366
以下是表大小超过10G未建分区的
OWNERSEGMENT_NAMESEGMENT_TYPEOBJECT_SIZE
------------------------------------------------------------------------------------------------------------------------
BOSSWGCI_APPLICATIONPROCESS_PERFTABLE53.3808594
BOSSWGDATA_GATHER_RESHLTTABLE27.2890625
BOSSWGINP_FILE_LISTTABLE20.7216797
BOSSWGLOGIC_ANALYZE_RESULTTABLE14.9208984
2.4部分索引失效
OWNERINDEX_NAMETABLE_NAMEPARTITION_NAMESTATUS
---------------------------------------------------------------------------------------------------------------------------------------
BOSSWGIDX_MERGE_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1212UNUSABLE
BOSSWGIDX_MERGE_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1211UNUSABLE
BOSSWGIDX_MERGE_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1210UNUSABLE
BOSSWGIDX_MERGE_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1209UNUSABLE
BOSSWGIDX_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1212UNUSABLE
BOSSWGIDX_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1211UNUSABLE
BOSSWGIDX_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1210UNUSABLE
BOSSWGIDX_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1209UNUSABLE
BOSSWG_ZJIDX_ALARM_MSG_ID_NEWNE_ALARM_MSGPART_1212UNUSABLE
2.5SGA和PGA大小可加大
从AWR报表看出，物理内存是30G
而SGA为10G，PGA为3G
从ADDM报告中看到如下说明：
结合有30G大小物理内存的主机，只跑数据库没有跑其他应用，因此可以考虑将SGA和PGA都增大，减少以SGA增大到15G，PGA增大到5G。
2.6PGA参数改小到3G
10月22日，单个节点session数达到700个，造成了数据库内存消耗严重，PGA参数改小后，能够提升一些数据库session数容量，每个节点能够多容纳大约100个session，达到550个左右，降低一些因连接数超载带来的风险。
altersystemsetpga_aggregate_target=3Gscope=bothsid='
*'
3.设计类优化
3
3.1表的字段设计有问题
ZJ_KZH_DATE、ZJ_CRM_S_ORDER_GATHER等本地表，为什么设计了大量的V1，V2….,请开发人员和我们探讨一下业务的需求。
这类设计对性能有影响。
3.2函数索引设计有疑问
ITSM_EVL_RESULT_INTF表的如下索引：
createindexIDX_CALL_TIMEonITSM_EVL_RESULT_INTF（TO_CHAR（CAL_TIME,'
yyyymmddhh24mi'
））
ZJ_CRM_S_ORDER_GATHER表的如下索引：
createindexGATHER_DATE_FUNC_INDEXonZJ_CRM_S_ORDER_GATHER（TO_DATE（GATHER_DATE,'
yyyy-mm-ddhh24:
mi:
ss'
createindexINDEX_V13onZJ_CRM_S_ORDER_GATHER（TO_DATE（V13,'
createindexINDEX_V18onZJ_CRM_S_ORDER_GATHER（TO_DATE（V18,'
createindexINDEX_V2onZJ_CRM_S_ORDER_GATHER（TO_DATE（V2,'
createindexINDEX_V3onZJ_CRM_S_ORDER_GATHER（TO_DATE（V3,'
createindexINDEX_V8onZJ_CRM_S_ORDER_GATHER（TO_DATE（V8,'
createindexOPERATION_DTonZJ_CRM_S_ORDER_GATHER（TO_DATE（OPERATION_DT,'
CTNBC_TRAN_DATA_008表的如下索引
createindexIDX_CTNBC_TRANS_DATETIMEonCTNBC_TRAN_DATA_008（TO_CHAR（TRANS_DATETIME,'
YYYYMMDD'
ACT_RU_EXECUTION的如下索引（开源就算了）
createuniqueindexACT_UNIQ_RU_BUS_KEYonACT_RU_EXECUTION（CASEWHENBUSINESS_KEY_ISNULLTHENNULLELSEPROC_DEF_ID_END,CASEWHENBUSINESS_KEY_ISNULLTHENNULLELSEBUSINESS_KEY_END）
ACT_UNIQ_HI_BUS_KEY的如下索引
createuniqueindexACT_UNIQ_HI_BUS_KEYonACT_HI_PROCINST（CASEWHENBUSINESS_KEY_ISNULLTHENNULLELSEPROC_DEF_ID_END,CASEWHENBUSINESS_KEY_ISNULLTHENNULLELSEBUSINESS_KEY_END）
3.3定时审核任务SQL优化（0542kf3ywcd6a）
这个语句单次执行需要9秒，大概1分钟执行一次，由于对logic_analyze_task进行全表扫描，对I/O影响也很大。
SELECTA.LOGIC_ANALYZE_RULE_ID,
A.GATHER_BATCH_ID,
TO_CHAR（B.SOURCE_BEGIN_DATE,'
YYYYMMDDHH24MISS'
）,
TO_CHAR（B.SOURCE_END_DATE,'
C.ANALYZE_PLSQL,
C.REGION_ID,
C.NE_ID,
NVL（C.KPI_ID,0）,
C.Subject||'
--产生数据'
FROMLOGIC_ANALYZE_TASKA,
TA_GATHER_TASK_BATCHB,
LOGIC_ANALYZE_RULEC,
LOGIC_DATA_GATHERD
WHEREA.Gather_Batch_ID=B.Gather_Batch_ID
ANDA.State='
1'
ANDA.LOGIC_ANALYZE_RULE_ID=C.LOGIC_ANALYZE_RULE_ID
ANDC.LOGIC_DATA_GATHER_ID=D.LOGIC_DATA_GATHER_ID
ANDC.State='
0SA'
ORDERBYA.State_Date;
分析排查：
state是logic_analyze_task表处理的状态位，值为'
的记录占比极少，在state上建索引SQL执行时间降低到1秒以下，对I/O消耗减小。
createindexidx_analyze_task_statonlogic_analyze_task（state）tablespacetbs_Bosswg_index;
3.4URL权限代码改造（重点）
SELECT'
<
privilege_urlid="
'
||A.PRIVILEGE_ID||'
"
name="
||PKP_STRING_UTIL.XMLENCODE（B.PRIVILEGE_NAME）||'
>
||PKP_PRIVILEGE.GETPRIVILEGERULE（A.PRIVILEGE_URL_ID）||'
/privilege_url>
FROMPRIVILEGE_URLA,PRIVILEGEBWHEREA.PRIVILEGE_ID=B.PRIVILEGE_IDANDA.PRIVILEGE_URL=:
B1ANDB.STATE='
ORDERBYA.PRIVILEGE_URL_ID
URL权限验证的SQL是weblogic执行频率最高的SQL，SQL性能以及业务逻辑对数据库性能影响很大。
将部分数据缓存到weblogic内存后，能够减少大量数据库查询，降低数据库性能消耗。
（修改后28日上午awr报告里没再出现这个SQL。
）
4.SQL类优化
4
4.1SQL_ID=bdcfdz26x5hm9（本地化）
语句情况：
该语句仅节点1在7天内就执行255130次，平均每次2.2秒，两节点合计7天执行50多万次。
selectKEY_WORD
fromcust_zj_declarationa
whereREASON_SUB_TYPEisnotnull
andIS_RECOMMEND_CLERK=1
andrownum<
=10
orderbyCLERK_SORT;
这个表是重点关注对象，如下：
1.CUST_ZJ_DECLARATION表记录有100多万条。
2.IS_RECOMMEND_CLERK的类型为VARCHAR2类型
selectdata_type
fromuser_tab_columns
wheretable_name='
CUST_ZJ_DECLARATION'
andcolumn_name='
IS_RECOMMEND_CLERK'
;
DATA_TYPE
----------------
VARCHAR2
3.IS_RECOMMEND_CLERK列有索引：
selectt.table_name,t.index_name,t.column_name,t.column_position,t.DESCEND
fromuser_ind_columnst
wheretable_name='
orderbytable_name,index_name,column_position;
TABLE_NAMEINDEX_NAMECOLUMN_NAMECOLUMN_POSITIONDESCEND
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CUST_ZJ_DECLARATIONIDX_CUST_ZJ_SUB_STAFF_IDSUBMIT_STAFF_ID1ASC
CUST_ZJ_DECLARATIONIDX_DECLARE_CLERKIS_RECOMMEND_CLERK1ASC
CUST_ZJ_DECLARATIONIDX_SUBMIT_TIME_DESCSUBMIT_TIME1ASC
CUST_ZJ_DECLARATIONINDEX_CUST_ZJ_DECLARATIONFLOW_ID1ASC
CUST_ZJ_DECLARATIONINDEX_SERIALSERIAL1ASC
修改建议：
将IS_RECOMMEND_CLERK列的VARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值，其中=1仅返回10条以内，0几乎返回所有记录。
适合用索引!
IS_RECOMMEND_CLERK列有索引，只是因为是VARCHAR2类型的，产生了类型转换，用不到索引。
如果实在无法修改类型，则只有将andIS_RECOMMEND_CLERK=1改为andIS_RECOMMEND_CLERK='
。
4.2SQL_ID=45ksbh7qdf71t（本地化）
该语句单节点1小时内就执行174次，平均每次3秒。
selectAPPLY_DEPT,
APPLY_EMAIL,
APPLY_ID,
APPLY_NAME,
APPLY_PHONE,
APPLY_REGION_ID,
CALL_VISIT_CONTENT,
DECLARATION_AFFECT,
DIS_SATISFY_DEASON,
DUTY_ID,
DUTY_NAME,
EVENT_DESC,
EVENT_MANAGE_ID,
EVENT_RESOVE_CASE,
EVENT_SOURCE,
EVENT_TYPE,
FAULT_TYPE,
FLOW_ID,
FORM_STANDARD,
to_char（HAPPEN_DATE,'
）HAPPEN_DATE,
to_char（HOPE_FINISH_DATE,'
）HOPE_FINISH_DATE,
IS_OPEN,
IS_REASONABLE,
MAINSN,
ORDER_SERIAL,
PHENOMENON_MAIN_TYPE,
PHENOMENON_MAIN_TYPE_NAME,
PHENOMENON_SUB_TYPE,
PHENOMENON_SUB_TYPE_NAME,
PROCESS_TIME_LIMIT,
RAISE_RATION,
REASON_DESC,
REASON_MAIN_TYPE,
REASON_MAIN_TYPE_NAME,
REASON_MANAGE_TYPE,
REASON_MANAGE_TYPE_NAME,
REASON_SUB_TYPE,
REASON_SUB_TYPE_TYPE_NAME,
REQUEST_ID,
SATISFY_DEGREE,
SERIAL,
SERVICE_REQUEST,
SUBMIT_RATIONALE,
SUBMIT_STAFF_ID,
SUBMIT_STAFF_NAME,
to_char（SUBMIT_TIME,'
）SUBMIT_TIME,
SYSTEM_ID,
SYSTEM_NAME,
TITLE,
TS_AREA_CODE,
UPDATE_RATIONALE,
URG_DEGREE,
to_char（WRITE_FORM_DATE,'
）WRITE_FORM_DATE,
request_id
fromCUST_ZJ_DECLARATION
whererequest_id=:
1;
在分析SQL_ID=bdcfdz26x5hm9的案例时已经查了CUST_ZJ_DECLARATION列的索引情况，该表在REQUEST_ID列无索引。
-修改建议：
在request_id列增加索引，当前来看，是没索引！
4.3SQL_ID=bn5w1gv1d8jgk（统一版本）
该语句单节点7天内就执行364次，平均每次507秒。
SELECTA.JOB_NAME,
B.DUTY_ID,
B.EXECUTE_STAFF,
B.ITEM_NAME,
B.IF_MOBILE,
B.IF_VOICE,
B.IF_MAIL,
B.IF_PHS,
D.JOB_ITEM_INSTANCE_ID,
A.WEEKEND_OPR,
A.HOLIDAY_OPR,
B.PROJECT_GROUP_ID
FROMMAINTANCE_JOBA,
MAINT_JOB_ITEMB,
MAINT_JOB_INSTANCEC,
JOB_ITEM_INSTANCED
WHEREB.MAINT_JOB_ITEM_ID=D.MAINT_JOB_ITEM_ID
ANDC.MAINT_JOB_INSTANCE_ID=D.MAINT_JOB_INSTA
升级会员 