oracle数据库优化报告Word格式文档下载.docx

1、修改“优化后效果”；修改“后续举措”1.概述从2013年9月份开始跟踪及分析，发现浙江的ITSM数据库在环境、设计及SQL三方面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度，从第5点的整改计划中来看，目前来看已经有效缓解高峰期CPU使用用率100%的情况，但IO、内存资源使用率依旧挺高，比较可疑的SQL语句已经罗列出来，将按整改计划表继续改造，应会有所缓解。随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库主机硬件扩容也亟待

2、解决。在跟进过程中出现了5次前台无法访问报错的故障，故障分析报告一并整理其中，详见第8点中的附件故障分析.zip，近段时间故障概况汇总如下：故障时间故障原因是否解决备注2013.10.17（两次）由于节点1系统被重启，业务访问高峰期时，节点2无法承载压力导致，待节点1恢复正常后，故障消失，当业务量上来之后，后台应用由于之前的故障导致session积压，再次产生性能问题。是故障所涉问题SQL都已汇集到本文2013.10.22由于SQL性能问题引起，当问题sql达到一定并发量后将引发主机资源耗尽，导致前台无法访问问题。2013.10.25同上2013.11.072.环境类优化122.1统计信息收集

3、被关闭浙江系统统计信息未自动收集，非常奇怪（看了集团、安徽、黑龙江等其他工程点，都是正常有收集,ENABLEED为TRUE）!这个关闭统计信息，将会对系统的性能造成巨大的影响。1.自动收集统计信息居然被关闭了，如下：JOB_NAME PROGRAM_NAME STATE ENABLED -GATHER_STATS_JOB GATHER_STATS_PROG DISABLED FALSE select owner, count（*） from dba_tab_statistics t where （t.last_analyzed is null or t.last_analyzed sysdat

4、e - 100） and table_name not like BIN$% group by owner order by owner;2.有如此多的对象显示很久或者根本就没有收集OWNER COUNT（*） -BASEDBA 80 BASEDBA_ZJ 73 BOSSWG 18122 BOSSWG_ZJ 7095 DBSNMP 22 OUTLN 3 PERFSTAT 1 SYS 1474 SYSTEM 165 TSMSYS 1 WAP_BOSSWG 12 WMSYS 42 2.2BOSSWG_ZJ用户需删除了浙江V3已经升级1年多了，BOSSWG_ZJ和BASEDB_ZJ可以删除了。2.3

5、部分大表要考虑建分区和分区清理以下表分区偏多，预示历史记录保留太久，如下：-以下4张都是拨测的表TABLE_OWNER TABLE_NAME CNT -BOSSWG DTP_TP_INST 867BOSSWG DTP_REAL_INST 366 BOSSWG DTP_ACTIVITY_INST 366 BOSSWG DTP_FLOW_INST 366以下是表大小超过10G未建分区的OWNER SEGMENT_NAME SEGMENT_TYPE OBJECT_SIZE -BOSSWG CI_APPLICATIONPROCESS_PERF TABLE 53.3808594BOSSWG DATA_G

6、ATHER_RESHLT TABLE 27.2890625 BOSSWG INP_FILE_LIST TABLE 20.7216797 BOSSWG LOGIC_ANALYZE_RESULT TABLE 14.92089842.4部分索引失效OWNER INDEX_NAME TABLE_NAME PARTITION_NAME STATUS - - - - -BOSSWG IDX_MERGE_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1212 UNUSABLEBOSSWGIDX_MERGE_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1211

7、 UNUSABLEBOSSWG IDX_MERGE_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1210 UNUSABLEBOSSWG IDX_MERGE_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1209 UNUSABLEBOSSWG IDX_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1212 UNUSABLEBOSSWG IDX_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1211 UNUSABLEBOSSWG IDX_ALARM_MSG_ID_NEW NE_ALARM_MSG PA

8、RT_1210 UNUSABLEBOSSWG IDX_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1209 UNUSABLEBOSSWG_ZJ IDX_ALARM_MSG_ID_NEW NE_ALARM_MSG PART_1212 UNUSABLE2.5SGA和PGA大小可加大从AWR报表看出，物理内存是30G而SGA为10G，PGA为3G从ADDM报告中看到如下说明：结合有30G大小物理内存的主机，只跑数据库没有跑其他应用，因此可以考虑将SGA和PGA都增大，减少以SGA增大到15G，PGA增大到5G。2.6PGA参数改小到3G10月22日，单个节点session数

9、达到700个，造成了数据库内存消耗严重，PGA参数改小后，能够提升一些数据库session数容量，每个节点能够多容纳大约100个session，达到550个左右，降低一些因连接数超载带来的风险。alter system set pga_aggregate_target=3G scope=both sid=*3.设计类优化33.1表的字段设计有问题 ZJ_KZH_DATE、ZJ_CRM_S_ORDER_GATHER等本地表，为什么设计了大量的V1，V2.,请开发人员和我们探讨一下业务的需求。这类设计对性能有影响。3.2函数索引设计有疑问ITSM_EVL_RESULT_INTF表的如下索引：cre

10、ateindex IDX_CALL_TIME on ITSM_EVL_RESULT_INTF （TO_CHAR（CAL_TIME,yyyymmddhh24mi）ZJ_CRM_S_ORDER_GATHER表的如下索引：create index GATHER_DATE_FUNC_INDEX on ZJ_CRM_S_ORDER_GATHER （TO_DATE（GATHER_DATE,yyyy-mm-dd hh24:mi:sscreate index INDEX_V13 on ZJ_CRM_S_ORDER_GATHER （TO_DATE（V13,create index INDEX_V18 on ZJ

11、_CRM_S_ORDER_GATHER （TO_DATE（V18,create index INDEX_V2 on ZJ_CRM_S_ORDER_GATHER （TO_DATE（V2,create index INDEX_V3 on ZJ_CRM_S_ORDER_GATHER （TO_DATE（V3,create index INDEX_V8 on ZJ_CRM_S_ORDER_GATHER （TO_DATE（V8,create index OPERATION_DT on ZJ_CRM_S_ORDER_GATHER （TO_DATE（OPERATION_DT,CTNBC_TRAN_DATA_0

12、08表的如下索引create index IDX_CTNBC_TRANS_DATETIME on CTNBC_TRAN_DATA_008 （TO_CHAR（TRANS_DATETIME,YYYYMMDDACT_RU_EXECUTION的如下索引（开源就算了）create unique index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION （CASE WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE PROC_DEF_ID_ END, CASE WHEN BUSINESS_KEY_ IS NULL THEN NULL ELS

13、E BUSINESS_KEY_ END）ACT_UNIQ_HI_BUS_KEY的如下索引create unique index ACT_UNIQ_HI_BUS_KEY on ACT_HI_PROCINST （CASE WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE PROC_DEF_ID_ END, CASE WHEN BUSINESS_KEY_ IS NULL THEN NULL ELSE BUSINESS_KEY_ END）3.3定时审核任务SQL优化（0542kf3ywcd6a）这个语句单次执行需要9秒，大概1分钟执行一次，由于对logic_analy

14、ze_task进行全表扫描，对I/O影响也很大。SELECT A.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 | -产生数据 FROM LOGIC_ANALYZE_TASK A, TA_GATHER_TASK_BATCH B, LOGIC_ANALYZE_RULE C, LO

15、GIC_DATA_GATHER D WHERE A.Gather_Batch_ID = B.Gather_Batch_ID AND A.State = 1 AND A.LOGIC_ANALYZE_RULE_ID = C.LOGIC_ANALYZE_RULE_ID AND C.LOGIC_DATA_GATHER_ID = D.LOGIC_DATA_GATHER_ID AND C.State = 0SA ORDER BY A.State_Date;分析排查：state是logic_analyze_task表处理的状态位，值为的记录占比极少，在state上建索引SQL执行时间降低到1秒以下，对I/O

16、消耗减小。create index idx_analyze_task_stat on logic_analyze_task （state） tablespace tbs_Bosswg_index;3.4URL权限代码改造（重点）SELECT | PKP_PRIVILEGE.GETPRIVILEGERULE（A.PRIVILEGE_URL_ID） | /privilege_url FROM PRIVILEGE_URL A, PRIVILEGE B WHERE A.PRIVILEGE_ID = B.PRIVILEGE_ID AND A.PRIVILEGE_URL = :B1 AND B.STATE

17、 = ORDER BY A.PRIVILEGE_URL_IDURL权限验证的SQL是weblogic执行频率最高的SQL，SQL性能以及业务逻辑对数据库性能影响很大。将部分数据缓存到weblogic内存后，能够减少大量数据库查询，降低数据库性能消耗。（修改后28日上午awr报告里没再出现这个SQL。）4.SQL类优化44.1SQL_ID=bdcfdz26x5hm9（本地化）语句情况：该语句仅节点1在7天内就执行 255130 次，平均每次2.2 秒，两节点合计7天执行50多万次。select KEY_WORD from cust_zj_declaration a where REASON_SU

18、B_TYPE is not nulland IS_RECOMMEND_CLERK = 1 and rownum = 10 order by CLERK_SORT;这个表是重点关注对象，如下：1.CUST_ZJ_DECLARATION表记录有100多万条。2.IS_RECOMMEND_CLERK的类型为VARCHAR2类型select data_type from user_tab_columns where table_name = CUST_ZJ_DECLARATION and column_name = IS_RECOMMEND_CLERK;DATA_TYPE-VARCHAR23. IS_

19、RECOMMEND_CLERK列有索引：select t.table_name,t.index_name, t.column_name, t.column_position, t.DESCENDfrom user_ind_columns t where table_name =order by table_name,index_name, column_position; TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION DESCEND-CUST_ZJ_DECLARATION IDX_CUST_ZJ_SUB_STAFF_ID SUBMIT_ST

20、AFF_ID 1 ASCCUST_ZJ_DECLARATION IDX_DECLARE_CLERK IS_RECOMMEND_CLERK 1 ASCCUST_ZJ_DECLARATION IDX_SUBMIT_TIME_DESC SUBMIT_TIME 1 ASCCUST_ZJ_DECLARATION INDEX_CUST_ZJ_DECLARATION FLOW_ID 1 ASCCUST_ZJ_DECLARATION INDEX_SERIAL SERIAL 1 ASC修改建议：将IS_RECOMMEND_CLERK列的VARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值，其中=

21、1仅返回10条以内，0几乎返回所有记录。适合用索引!IS_RECOMMEND_CLERK列有索引，只是因为是VARCHAR2类型的，产生了类型转换，用不到索引。如果实在无法修改类型，则只有将 and IS_RECOMMEND_CLERK = 1改为and IS_RECOMMEND_CLERK = 。4.2SQL_ID=45ksbh7qdf71t（本地化）该语句单节点1小时内就执行174次，平均每次3秒。select APPLY_DEPT, APPLY_EMAIL, APPLY_ID, APPLY_NAME, APPLY_PHONE, APPLY_R EGION_ID, CALL_VISIT_C

22、ONTENT, DECLARATION_AFFECT, DIS_SATISFY_DEASON, DUT Y_ID, DUTY_NAME, EVENT_DESC, EVENT_MANAGE_ID, EVENT_RESOVE_CASE, EVENT_SOU RCE, 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_S

23、ERIAL, PHENOMENON _MAIN_TYPE, PHENOMENON_MAIN_TYPE_NAME, PHENOMENON_SUB_TYPE, PHENOMENON_S UB_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, SATIS

24、FY_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 from CUST_ZJ_DECLARATIONwhere request_id =

25、:1;在分析SQL_ID=bdcfdz26x5hm9的案例时已经查了CUST_ZJ_DECLARATION列的索引情况，该表在REQUEST_ID列无索引。-修改建议：在request_id 列增加索引，当前来看，是没索引！4.3SQL_ID=bn5w1gv1d8jgk（统一版本）该语句单节点7天内就执行364次，平均每次507秒。SELECT A.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 FROM MAINTANCE_JOB A, MAINT_JOB_ITEM B, MAINT_JOB_INSTANCE C, JOB_ITEM_INSTANCE D WHERE B.MAINT_JOB_ITEM_ID = D.MAINT_JOB_ITEM_ID AND C.MAINT_JOB_INSTANCE_ID = D.MAINT_JOB_INSTA