[size=22.0pt] [size=22.0pt] [size=22.0pt] 1. [size=15.0pt]概述从2013年9月份开始跟踪及分析，发现浙江的ITSM数据库在环境、设计及SQL三方面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，并给出相关建议、整改方案、整改进度，从第5点的整改计划中来看，目前来看已经有效缓解高峰期CPU使用用率100%的情况，但IO、内存资源使用率依旧挺高，比较可疑的SQL语句已经罗列出来，将按整改计划表继续改造，应会有所缓解。随着需求不断增加，特别是复杂逻辑的需求，一旦出现高并发量时，也将可能导致数据库主机无法承载，因此数据库主机硬件扩容也亟待解决。在跟进过程中出现了5次前台无法访问报错的故障，故障分析报告一并整理其中，详见第8点中的附件《故障分析.zip》，近段时间故障概况汇总如下：

[td=102] 故障时间 [td=246]故障原因[td=79]是否解决[td=142]备注
[td=102]2013.10.17 （两次）[td=246][align=right]由于节点1系统被重启，业务访问高峰期时，节点2无法承载压力导致，待节点1恢复正常后，故障消失，当业务量上来之后，后台应用由于之前的故障导致session积压，再次产生性能问题。[td=79][align=right]是[td=1,4,142][align=right]故障所涉问题SQL都已汇集到本文
[td=102]2013.10.22[td=246][align=right]由于SQL性能问题引起，当问题sql达到一定并发量后将引发主机资源耗尽，导致前台无法访问问题。[td=79][align=right]是
[td=102]2013.10.25[td=246][align=right]同上[td=79][align=right]是
[td=102]2013.11.07[td=246][align=right]同上[td=79][align=right]是
[font="][size=15.0pt]2. [size=15.0pt]环境类优化
2.1 统计信息收集被关闭浙江系统统计信息未自动收集，非常奇怪(看了集团、安徽、黑龙江等其他工程点，都是正常有收集,ENABLEED为TRUE）!这个关闭统计信息，将会对系统的性能造成巨大的影响。2.2BOSSWG_ZJ用户需删除了浙江V3已经升级1年多了，BOSSWG_ZJ和BASEDB_ZJ可以删除了。2.3部分大表要考虑建分区和分区清理从AWR报表看出，物理内存是30G 而SGA为10G，PGA为3G
从ADDM报告中看到如下说明：
[td=568]

结合有30G大小物理内存的主机，只跑数据库没有跑其他应用，因此可以考虑将SGA和PGA都增大，减少以SGA增大到15G，PGA增大到5G。2.6 PGA参数改小到3G10月22日，单个节点session数达到700个，造成了数据库内存消耗严重，PGA参数改小后，能够提升一些数据库session数容量，每个节点能够多容纳大约100个session，达到550个左右，降低一些因连接数超载带来的风险。[table=98%,rgb(221, 217, 195)]
[td=561] alter system set pga_aggregate_target=3G scope=both sid='*'

[font="][size=15.0pt]3. [size=15.0pt]设计类优化2 3 3.1 表的字段设计有问题 ZJ_KZH_DATE、ZJ_CRM_S_ORDER_GATHER等本地表，为什么设计了大量的V1，V2….,请开发人员和我们探讨一下业务的需求。这类设计对性能有影响。3.2 函数索引设计有疑问ITSM_EVL_RESULT_INTF表的如下索引：[table=98%,rgb(221, 217, 195)]
[td=593] createindex IDX_CALL_TIME on ITSM_EVL_RESULT_INTF (TO_CHAR(CAL_TIME,'yyyymmddhh24mi'))

ZJ_CRM_S_ORDER_GATHER表的如下索引：[table=98%,rgb(221, 217, 195)]
[td=595] create index GATHER_DATE_FUNC_INDEX on ZJ_CRM_S_ORDER_GATHER (TO_DATE(GATHER_DATE,'yyyy-mm-dd hh24:mi:ss')) create index INDEX_V13 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V13,'yyyy-mm-dd hh24:mi:ss')) create index INDEX_V18 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V18,'yyyy-mm-dd hh24:mi:ss')) create index INDEX_V2 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V2,'yyyy-mm-dd hh24:mi:ss')) create index INDEX_V3 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V3,'yyyy-mm-dd hh24:mi:ss')) create index INDEX_V8 on ZJ_CRM_S_ORDER_GATHER (TO_DATE(V8,'yyyy-mm-dd hh24:mi:ss')) create index OPERATION_DT on ZJ_CRM_S_ORDER_GATHER (TO_DATE(OPERATION_DT,'yyyy-mm-dd hh24:mi:ss'))
CTNBC_TRAN_DATA_008表的如下索引[table=98%,rgb(221, 217, 195)]
[td=593] create index IDX_CTNBC_TRANS_DATETIME on CTNBC_TRAN_DATA_008 (TO_CHAR(TRANS_DATETIME,'YYYYMMDD'))
ACT_RU_EXECUTION的如下索引（开源就算了）[table=98%,rgb(221, 217, 195)]
[td=593] 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 ELSE BUSINESS_KEY_ END)
ACT_UNIQ_HI_BUS_KEY的如下索引[table=98%,rgb(221, 217, 195)]
[td=593] 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_analyze_task进行全表扫描，对I/O影响也很大。[table=98%,rgb(221, 217, 195)]
[td=603] SELECT A.LOGIC_ANALYZE_RULE_ID, A.GATHER_BATCH_ID, TO_CHAR(B.SOURCE_BEGIN_DATE, 'YYYYMMDDHH24MISS'), TO_CHAR(B.SOURCE_END_DATE, 'YYYYMMDDHH24MISS'), 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, LOGIC_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表处理的状态位，值为'1'的记录占比极少，在state上建索引SQL执行时间降低到1秒以下，对I/O消耗减小。[table=98%,rgb(221, 217, 195)]
[td=603] create index idx_analyze_task_stat on logic_analyze_task (state) tablespace tbs_Bosswg_index;

3.4 URL权限代码改造（重点）

[table=98%,rgb(221, 217, 195)]
[td=621] [font="][size=8.0pt]SELECT '' || PKP_PRIVILEGE.GETPRIVILEGERULE(A.PRIVILEGE_URL_ID) || '' FROM PRIVILEGE_URL A, PRIVILEGE B WHERE A.PRIVILEGE_ID = B.PRIVILEGE_ID AND A.PRIVILEGE_URL = :B1 AND B.STATE = '0SA' ORDER BY A.PRIVILEGE_URL_ID

分析排查：URL权限验证的SQL是weblogic执行频率最高的SQL，SQL性能以及业务逻辑对数据库性能影响很大。将部分数据缓存到weblogic内存后，能够减少大量数据库查询，降低数据库性能消耗。(修改后28日上午awr报告里没再出现这个SQL。)
[font="][size=15.0pt]4. [size=15.0pt]SQL[size=15.0pt]类优化3 4 4.1 SQL_ID=bdcfdz26x5hm9（本地化）语句情况：该语句仅节点1在7天内就执行255130 次，平均每次2.2 秒，两节点合计7天执行50多万次。
[table=98%]
[td=624] select KEY_WORD from cust_zj_declaration a where REASON_SUB_TYPE is not null and IS_RECOMMEND_CLERK = 1 and rownum <= 10 order by CLERK_SORT; 这个表是重点关注对象，如下： 分析排查：[table=98%,rgb(221, 217, 195)] [td=652] 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 ---------------- VARCHAR2 3. IS_RECOMMEND_CLERK列有索引： select t.table_name,t.index_name, t.column_name, t.column_position, t.DESCEND from user_ind_columns t where table_name ='CUST_ZJ_DECLARATION' order by table_name,index_name, column_position; TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION DESCEND [size=7.5pt]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- [size=7.5pt]CUST_ZJ_DECLARATION IDX_CUST_ZJ_SUB_STAFF_ID SUBMIT_STAFF_ID 1 ASC [size=7.5pt]CUST_ZJ_DECLARATION IDX_DECLARE_CLERK IS_RECOMMEND_CLERK 1 ASC [size=7.5pt]CUST_ZJ_DECLARATION IDX_SUBMIT_TIME_DESC SUBMIT_TIME 1 ASC [size=7.5pt]CUST_ZJ_DECLARATION INDEX_CUST_ZJ_DECLARATION FLOW_ID 1 ASC [size=7.5pt]CUST_ZJ_DECLARATION INDEX_SERIAL SERIAL 1 ASC 修改建议：将IS_RECOMMEND_CLERK列的VARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值，其中=1仅返回10条以内，0几乎返回所有记录。适合用索引!IS_RECOMMEND_CLERK列有索引，只是因为是VARCHAR2类型的，产生了类型转换，用不到索引。如果实在无法修改类型，则只有将 and IS_RECOMMEND_CLERK = 1改为andIS_RECOMMEND_CLERK = '1' 。4.2 SQL_ID=45ksbh7qdf71t（本地化）语句情况：该语句单节点1小时内就执行174次，平均每次3秒。-修改建议：在分析SQL_ID=bdcfdz26x5hm9的案例时已经查了CUST_ZJ_DECLARATION列的索引情况，该表在REQUEST_ID列无索引。在request_id 列增加索引，当前来看，是没索引！4.3 SQL_ID=bn5w1gv1d8jgk（统一版本）语句情况：该语句单节点7天内就执行364次，平均每次507秒。修改建议：[table=98%,rgb(221, 217, 195)] [td=640] [size=9.0pt]SQL> select t.table_name,t.index_name, t.column_name, t.column_position, t.DESCEND [size=9.0pt] 2 from user_ind_columns t [size=9.0pt] 3 where table_name in ('MAINTANCE_JOB', [size=9.0pt] 4 'MAINT_JOB_ITEM', [size=9.0pt] 5 'MAINT_JOB_INSTANCE', [size=9.0pt] 6 'JOB_ITEM_INSTANCE') [size=9.0pt] 7 order by table_name,index_name, column_position; [size=9.0pt] [size=9.0pt]TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION DESCEND [size=9.0pt]-------------------------- ------------------------------ ------------------------- --------------- ---------------------------------------------- [size=9.0pt]JOB_ITEM_INSTANCE IDX_INST_JOB_ITEM_ID MAINT_JOB_ITEM_ID 1 ASC [size=9.0pt]JOB_ITEM_INSTANCE IDX_JOB_INST_ID MAINT_JOB_INSTANCE_ID 1 ASC [size=9.0pt]JOB_ITEM_INSTANCE PK_JOB_ITEM_INSTANCE JOB_ITEM_INSTANCE_ID 1 ASC [size=9.0pt]MAINTANCE_JOB PK_MAINTANCE_JOB MAINTANCE_JOB_ID 1 ASC [size=9.0pt]MAINT_JOB_INSTANCE IDX_PLAN_EXEC_DATE PLAN_EXEC_DATE 1 ASC [size=9.0pt]MAINT_JOB_INSTANCE PK_MAINT_JOB_INSTANCE MAINT_JOB_INSTANCE_ID 1 ASC [size=9.0pt]MAINT_JOB_ITEM PK_MAINT_JOB_ITEM MAINT_JOB_ITEM_ID 1 ASC [size=9.0pt]7 rows selected. [size=9.0pt] [size=9.0pt]SQL> select count(0) from MAINTANCE_JOB ; [size=9.0pt] COUNT(0) [size=9.0pt]-------------- [size=9.0pt] 768 [size=9.0pt]SQL> select count(0) from MAINT_JOB_ITEM ; [size=9.0pt] COUNT(0) [size=9.0pt]------------- [size=9.0pt] 1772 [size=9.0pt]SQL> select count(0) from MAINT_JOB_INSTANCE ; [size=9.0pt] COUNT(0) [size=9.0pt]--------------- [size=9.0pt] 215117 [size=9.0pt]SQL> select count(0) from JOB_ITEM_INSTANCE ; [size=9.0pt] COUNT(0) [size=9.0pt]---------------- [size=9.0pt] 825303
1.建议COMPLETE_TIME_LIMIT列有索引 2.确认COMPLETE_TIME_LIMIT列会有前后空格吗，如果不会，则不必加TRIM，直接写成D.COMPLETE_TIME_LIMIT>= TO_CHAR(SYSDATE, 'hh24:mi:ss') 3.说明一下为什么COMPLETE_TIME_LIMIT这个时间列用VARCHAR2类型 4.此处PKP_MAINT_JOB.GETHOLIDAYNUM的包产生很多递归调用，可以考虑改写，改写如下，之前我们系统组已经改写过这个语句了，由黄锏发出，请开发人员看看，是否出了补丁了。[table=98%,rgb(221, 217, 195)]
[td=621] with tmp_maintjob as (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.complete_day_limit, d.complete_time_limit, c.plan_exec_date 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_instance_id and a.maintance_job_id = b.maintance_job_id and a.state = '0SA' and a.ENABLED_DATE <= sysdate and nvl(a.IF_DEL, '0BF') = '0BF' and b.state = '0SA' and nvl(b.IF_DEL, '0BF') = '0BF' and nvl(b.execute_staff, b.duty_id) is not null and b.if_hasten = '0BT' and c.JOB_INSTANCE_STATE != '2' and c.JOB_INSTANCE_STATE != '3' and d.JOB_ITEM_STS != '2' and d.COMPLETE_TIME_LIMIT is not NULL and trim(d.COMPLETE_TIME_LIMIT) >= to_char(sysdate, 'hh24:mi:ss') and nvl(d.main_next_notify_time, to_date(to_char(sysdate, 'yyyy-mm-dd') || d.complete_time_limit, 'yyyy-mm-dd hh24:mi:ss') + b.hasten_time / (24 * 60)) <= sysdate) select s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date, sum(delay_count) delay_count from (select s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date, count(t.holiday_day) delay_count from tmp_maintjob s, holiday_cfg t where s.holiday_opr = 'DELAY_EXEC' and t.holiday_day >= s.plan_exec_date and t.holiday_day < sysdate and t.holiday_day > sysdate - 730 group by s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date union all select s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date, count(t.weekend) delay_cnt from tmp_maintjob s, (select thedate as weekend from (select sysdate - 730 + (level - 1) thedate from dual connect by level <= sysdate - trunc(sysdate - 730)) where to_char(thedate, 'd') = '7' or to_char(thedate, 'd') = '1') t where s.weekend_opr = 'DELAY_EXEC' and t.weekend >= s.plan_exec_date group by s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date union all select s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date, 0 delay_count from tmp_maintjob s where weekend_opr != 'DELAY_EXEC' and holiday_opr != 'DELAY_EXEC') s group by s.job_name, s.duty_id, s.execute_staff, s.item_name, s.if_mobile, s.if_voice, s.if_mail, s.if_phs, s.job_item_instance_id, s.weekend_opr, s.holiday_opr, s.complete_day_limit, s.complete_time_limit, s.plan_exec_date having trunc (trunc(plan_exec_date) + nvl(complete_day_limit, 0) + sum(delay_count)) = trunc (sysdate)

4.4 SQL_ID=cwzwax00wmm7q（本地化）语句情况：该语句单节点7天内就执行89次，平均每次995秒。[table=98%,rgb(221, 217, 195)]
[td=621] INSERT INTO BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE ( ALARM_ID , EXTERNAL_ID , PRODUCT_INSTANCE_CODE , EXTERNAL_ID_TYPE , CUSTOMER_NAME , STATUS , STATUS_DATE ) SELECT A2.ALARM_ID , A2.EXTERNAL_ID , A2.PRODUCT_INSTANCE_CODE , A2.EXTERNAL_ID_TYPE , A2.CUSTOMER_NAME , 1, SYSDATE FROM HIGH_USAGE_ALARM_MR_READ A2 WHERE A2.CITY_CODE = :B1 AND A2.ALARM_ID <> ALL (SELECT A3.ALARM_ID FROM BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE A3 );
分析排查：1. 这个语句应该是MERGE的需求，可以用MERGE来改写2. CITI_CODE的取值只有11个，表示11个地市。修改建议：[i]1. [i]建议HIGH_USAGE_ALARM_MR_READ[i]表的CITI_CODE[i]建分区2. SQL建议改写为如下：[table=98%,rgb(221, 217, 195)]
[td=568] MERGE INTO BOSSWG.HIGH_USAGE_ALARM_MR_RESPONSE A1 USING (select * from BOSSWG.HIGH_USAGE_ALARM_MR_READ where CITY_CODE=:B1) A2 ON (A1.ALARM_ID=A2.ALARM_ID) WHEN NOT MATCHED THEN INSERT (ALARM_ID,EXTERNAL_ID,PRODUCT_INSTANCE_CODE ,EXTERNAL_ID_TYPE ,CUSTOMER_NAME ,STATUS ,STATUS_DATE) VALUES (A2.ALARM_ID ,A2.EXTERNAL_ID ,A2.PRODUCT_INSTANCE_CODE , A2.EXTERNAL_ID_TYPE ,A2.CUSTOMER_NAME ,1 ,sysdate );

4.5 SQL_ID=ctmv0k8sgf907（外部DBLINK）语句情况：该语句单节点1小时内就执行12次，平均每次216秒。[table=98%,rgb(221, 217, 195)]
[td=568] select count(*), 10055 from pay.oocp_prepay_log@xzabm where action = 5 and state = 1 and state_time < sysdate - 1 / 1440 and state_time > sysdate - 5 / 1440
这个时段的网络相关等待事件相当严重，如下：
分析排查：[table=98%,rgb(221, 217, 195)]
[td=568] 1. 该表记录有46694050条，而where state_time < sysdate - 1 / 1440 and state_time > sysdate - 5 / 1440才548条。 2.查询一下在这个state_time列有无索引 [size=9.0pt]select t.table_owner,t.index_name, t.column_name, t.column_position [size=9.0pt] from dba_ind_columns@xzabm t [size=9.0pt] where table_name ='OOCP_PREPAY_LOG' [size=9.0pt] order by table_name,index_name, column_position; [size=9.0pt] [size=9.0pt]TABLE_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION [size=9.0pt]------------------------------------------------------------------------------------------------------------------------------------------- [size=9.0pt]PAY IDX_OOCP_PREPAYLOG_PISID PRODUCT_OFFER_INSTANCE_ID 1 [size=9.0pt]PAY IDX_OOCP_PREPAY_LOG_ACCCONBRACCT_CODE 1 [size=9.0pt]PAY IDX_OOCP_PREPAY_LOG_ACCCONBR ACC_NBR 2 [size=9.0pt]PAY IDX_OOCP_PREPAY_LOG_ACCNBR ACC_NBR 1 [size=9.0pt]PAY IDX_OOCP_PREPAY_LOG_SERIALNO SERIAL_NO 1 [size=9.0pt]PAY IDX_OOCP_PREPAY_LOG_STATE STATE 1 [size=9.0pt]PAY IDX_OOCP_PREPAY_PAYID PAYMENT_ID 1
修改建议：在state_time列建索引，不过这需要和对方沟通，这是对方的表。不过这个语句是通过DBLINK连接的，实际上也会影响对方系统的性能，所以建索引其实对对方也有利!4.6 SQL_ID=9s06gsx7snk73(本地化)[table=98%,rgb(221, 217, 195)]
[td=568] SELECT COUNT(1) FROM (SELECT (CASE WHEN V10 = '97成功' AND V11 IS NULL THEN SYSDATE - TO_DATE(T.V8, 'yyyy-mm-dd hh24:mi:ss') ELSE NVL(TO_DATE(T.V13, 'yyyy-mm-dd hh24:mi:ss'), SYSDATE) - NVL(TO_DATE(T.V12, 'yyyy-mm-dd hh24:mi:ss'), SYSDATE) END) * 24 * 3600 V_RN_LEN, T.* FROM ZJ_KZH_DATE T WHERE TO_DATE(T.V12, 'yyyy-mm-dd hh24:mi:ss') >= :B2 AND TO_DATE(T.V12, 'yyyy-mm-dd hh24:mi:ss') < :B1) ZJ_KZH_DATE T表和CUST_ZJ_DECLARATION一样，也是重点关注对象： 分析排查：[table=98%,rgb(221, 217, 195)] [td=568] 1. ZJ_KZH_DATE表记录有500多万，从条件来看，有时间字段，理应不用全表扫描，这里却用了全表扫描,不过这个语句的TO_DATE写法本来就用不到索引，此外这些V12的列，本身也没有索引，如下： [size=9.0pt] [size=9.0pt]select t.table_name,t.index_name, t.column_name, t.column_position [size=9.0pt] from user_ind_columns t [size=9.0pt] where table_name in='ZJ_KZH_DATE' [size=9.0pt] order by table_name,index_name, column_position; [size=9.0pt] [size=9.0pt]TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION [size=9.0pt]-------------------------------------------------------------------------------------------------------------------------------------------- [size=9.0pt]ZJ_KZH_DATE IDX_ZJ_KZH_DATE_G GATHER_DATE 1 [size=9.0pt]ZJ_KZH_DATE IDX_ZJ_KZH_DATE_O OU_NUM 1 [size=9.0pt]ZJ_KZH_DATE P_BUSINESS_ID BUSINESS_ID 1 [size=9.0pt] 2. 类似这样的语句太多了！ SQL_ID=f28942wm5y422 SQL_ID=f83ujj08gw7db SQL_ID=bxf706za5rsj1 SQL_ID=22crkv1dz69ny SQL_ID=du3k7qurjfp3x SQL_ID=52c3f75j9cc40 SQL_ID=dysqyswvzcusg SQL_ID=apcc9ya8gmqtn 修改建议：1.考虑一下，代码是否有加上GATHER_DATE或OU_NUM或BUSINESS_ID的机会。 2.关于字段V1，V2...的设计思路有点不太好，能否再多探讨一下。4.7 SQL_ID=a5wr8yd24r4wq（本地化）[table=98%,rgb(221, 217, 195)] [td=568] SELECT COUNT(1) FROM (SELECT ITEM_ROW_ID FROM (SELECT T.ITEM_ROW_ID FROM ZJ_CRM_S_ORDER_GATHER T WHERE T.V2 IS NOT NULL AND T.V8 IS NOT NULL AND T.V13 IS NOT NULL AND T.V18 IS NOT NULL ORDER BY T.OPERATION_DT DESC) WHERE ROWNUM < 2) 修改建议：1. ZJ_CRM_S_ORDER_GATHER对象和ZJ_KZH_DATE对象类似，关于字段V1，V2...的设计思路有点不太好，能否再多探讨一下。2. ZJ_CRM_S_ORDER_GATHER记录有2千多万条，是否有清理机制。4.8 调和语句大量DELETE可避免将delete 修正为commit即可，全局临时表中commit就是清除数据，这样可以避免大量无谓的多余动作，也可少产生大量redo。4.9 SQL_ID=a5wr8yd24r4wq（本地化） [td=568] insert into INP_DATA_PERF(FILE_ID, NE_ID, NE_TYPE_ID, AUDIT_ID, AREA_CODE, SYSLOC, MSG_SOURCE, KPI_ID, KPI_NAME, KBP, KBP_NAME, MIN, AVG, MAX, MSG_TIME) values (:1, pkp_inp_convert.convert_inp_data_perf(:2, :3), :4, :5, :6, :7, 1, replace(:8, '-', ''), :9, :10, :11, :12, :13, :14, to_date(:15, 'YYYYMMDDHH24MI'); 分析排查：其中这个函数如下：[table=98%,rgb(221, 217, 195)] [td=568] function convert_inp_data_perf(p_ne_id number,p_areacode varchar2) return number is v_ne_id CI_BASE_ELEMENT.INSTANCE_ID%type; begin begin /*select distinct a.ne_id into v_ne_id from net_element a where a.ne_type_id = p_ne_id and a.region_id = to_number(p_areacode) and a.state = '0SA';*/ SELECT distinct A.INSTANCE_ID INTO v_ne_id FROM CI_BASE_ELEMENT A WHERE A.CLASS_ID=p_ne_id AND A.REGION_ID = to_number(p_areacode) AND A.MARKASDELETED=0 AND A.DATASET_ID=6; exception when others then v_ne_id := -1; end ; return v_ne_id; end convert_inp_data_perf; 修改建议：取同一个文件的时候，其实返回的记录pkp_inp_convert.convert_inp_data_perf(:2, :3)都是相同的，所以这里被调用N此是没必要的，只要被调用1次即可。4.10 SQL_ID=9ghzcqq3y4x49（统一版本） [table=98%,rgb(221, 217, 195)] [td=568] select * from (select 'event' task_or_event, a.event_id id, a.staff_id, b.src_staff, nvl(e.staff_name, '...') as src_staff_name, b.exec_staff, nvl(c.staff_name, '...') as exec_staff_name, b.event_type type, DECODE(event_type, '2', PKP_FLOW_FUNCTION.get_flow_event_text(b.CONTENT_ID), f_get_all_state('EVENT_Q', 'EVENT_TYPE', b.event_type)) as type_name, b.content, b.grade, d.grade_name, b.state, f_get_all_state('EVENT_Q', 'STATE', b.state) as state_name, b.content_id, '' room_name, '...' dev_name, to_char(b.state_date, 'yy/mm/dd hh24:mi:ss') as state_date, IF_ORANGE_ALERT(b.event_id) alert_flag, DECODE(event_type, '3', PKP_FLOW.isBindForm(F_GET_FLOW_CUR_TCH_ID(b.content_id)), '2', PKP_FLOW.isBindForm(b.content_id), 0) as isBindForm, replace(replace(f.send_url, '=0000', '=' || b.content_id), 'serialnum', nvl(b.serial_num, '-1')) send_url, '0' sort_id from staff_event a, event_q b, grade d, staff c, staff e, workaccept_cfg f where a.event_id = b.event_id and b.grade = d.grade and b.exec_staff = c.staff_id(+) and b.src_staff = e.staff_id(+) and a.staff_id = 1 and not exists (select 1 from sys_config where sys_var = 'HIDDEN_EVENT_TYPE' and instr(', ' || sys_var_value || ', ', ', ' || b.event_type || ', ') > 0) and b.event_type = f.work_type and f.queue_type = 'EVENT_Q' and b.flag is null union all select 'task' task_or_event, a.task_id id, a.staff_id, b.src_staff, nvl(e.staff_name, '...') as src_staff_name, b.exec_staff, nvl(c.staff_name, '...') as exec_staff_name, b.task_type type, f_get_all_state('TASK_Q', 'TASK_TYPE', task_type) as type_name, b.content, b.grade, d.grade_name, b.state, f_get_all_state('TASK_Q', 'STATE', b.state) as state_name, b.content_id, nvl(b.room_name, '...') as room_name, nvl(b.dev_name, '...') as dev_name, to_char(b.state_date, 'yy/mm/dd hh24:mi:ss') as state_date, 0 alert_flag, 0 isBindForm, replace(f.send_url, '=0000', '=' || b.content_id) send_url, '0' sort_id from staff_task a, task_q b, grade d, staff c, staff e, workaccept_cfg f where a.task_id = b.task_id and b.grade = d.grade and b.exec_staff = c.staff_id(+) and b.src_staff = e.staff_id(+) and a.staff_id = 1 and b.task_type = f.work_type and f.queue_type = 'TASK_Q' order by state_date desc) where rownum <= 20 分析排查：由admin帐号历史代办信息过多引起，清理staff_event、event_q相关数据后SQL效率提高，CPU占用降低。 4.11 8p3wxmt13z5xv、3kbwc677sm94y（本地化） [table=98%,rgb(221, 217, 195)] [td=568] 8p3wxmt13z5xv begin pkp_itsm_upload_wyl.P_CUST_ZJ_require_yu(sysdate); end; 3kbwc677sm94y SELECT A.FLOW_ID, A.REQUEST_ID, A.SUBMIT_STAFF_NAME, A.SUBMIT_TIME, A.APPLY_DEPT, A.TITLE, A.REQUIRE_SOURCE, A.REQUIRE_URGENT, A.REQUIRE_INVOLVE_DOMAIN, A.PRODUCE_REASON, A.REQUIRE_CONTENT, A.REQUIRE_INVOLVE_SYSTEM, A.REQUIRE_TYPE_NAME, A.REQUIRE_INVOLVE_DOMAIN_NAME, A.REQUIRE_INVOLVE_SYSTEM_NAME, A.REQUIRE_TYPE, A.PATCH_SERIAL, PKP_ITSM_UPLOAD_WYL.GET_YU(D.STAFF_ID) BELONG_DOMAIN, D.STAFF_ID FROM CUST_ZJ_REQUIRE A, TACHE B, STAFF D WHERE A.FLOW_ID = B.FLOW_ID AND INSTR(', ' || B.PERSON || ', ', ', ' || D.STAFF_ID || ', ') > 0 AND B.STATE <> 'F' AND EXISTS (SELECT 1 FROM TACHE C WHERE C.FLOW_ID = A.FLOW_ID AND C.TCH_MOD = 11548) AND NOT EXISTS (SELECT 1 FROM TACHE C WHERE C.FLOW_ID = A.FLOW_ID AND C.TCH_MOD = 11595) AND PKP_ITSM_UPLOAD_WYL.GET_YU(D.STAFF_ID) IS NOT NULL

分析排查：SQL写法有问题，暂停这两个SQL相关的定时任务，本地优化后再启用，CPU占用降低。4.12 inp_file_list相关SQL
分析排查：Inp_file_list改成分区表后，涉及的脚本的没有修改完全，由于脚本没有使用分区字段作为查询条件，造成大量全表扫描。检查晚上的数据库awr报表，可以看出性能消耗排在前面的都是这些脚本，而上班时间这些脚本也有在跑，修改为分区字段作为查询条件后，资源消耗降低不少。4.13 首页待办的SQL（重点）[table=98%,rgb(221, 217, 195)]
[td=568] select src_staff_name 处理员工, exec_staff_name 接受员工, type_name 事务类型, content 事务标题, state_date 时间, id, content_id, type, task_or_event, isBindForm, send_url, '03' thetype, row_number () over ( order by state_date desc ) rn from (select 'event' task_or_event, a.event_id id, b.event_type type, b.content_id, DECODE(event_type, '3', PKP_FLOW.isBindForm(F_GET_FLOW_CUR_TCH_ID(b.content_id)), '2', PKP_FLOW.isBindForm(b.content_id), 0) as isBindForm, replace(replace(f.send_url, '=0000', '=' || b.content_id), 'serialnum', nvl(b.serial_num, '-1')) send_url, nvl(e.staff_name, '...') as src_staff_name, nvl(c.staff_name, '...') as exec_staff_name, DECODE(event_type, '2', PKP_FLOW_FUNCTION.get_flow_event_text(b.CONTENT_ID), f_get_all_state('EVENT_Q', 'EVENT_TYPE', b.event_type)) as type_name, b.content, to_char(b.state_date, 'yy/mm/dd hh24:mi:ss') as state_date from staff_event a, event_q b, grade d, staff c, staff e, workaccept_cfg f where a.event_id = b.event_id and b.grade = d.grade and b.flag is null and b.exec_staff = c.staff_id(+) and b.src_staff = e.staff_id(+) and a.staff_id = &CURRENT_STAFF_ID and b.event_type = f.work_type and f.queue_type = 'EVENT_Q' union all select 'task' task_or_event, a.task_id id, b.task_type type, b.content_id, 0 isBindForm, replace(f.send_url, '=0000', '=' || b.content_id) send_url, nvl(e.staff_name, '...') as src_staff_name, nvl(c.staff_name, '...') as exec_staff_name, f_get_all_state('TASK_Q', 'TASK_TYPE', task_type) as type_name, b.content, to_char(b.state_date, 'yy/mm/dd hh24:mi:ss') as state_date from staff_task a, task_q b, grade d, staff c, staff e, workaccept_cfg f where a.task_id = b.task_id and b.grade = d.grade and b.exec_staff = c.staff_id(+) and b.src_staff = e.staff_id(+) and a.staff_id = &CURRENT_STAFF_ID and b.task_type = f.work_type and f.queue_type = 'TASK_Q') a where 1 = 1 and type <> '3' and type<>'#' and content like :CONTENT and to_date('20'||state_date,'yyyy-MM-dd hh24:mi:ss') >= to_date(:START_DATE||' 00:00:00','yyyy-MM-dd hh24:mi:ss') and to_date('20'||state_date,'yyyy-MM-dd hh24:mi:ss') <= to_date(:END_DATE||' 23:59:59','yyyy-MM-dd hh24:mi:ss') ) where rn <= :P_ROWNUM 分析排查：由于select字段列表里有很多函数调用，当代办比较多的时候，SQL性能差，而且这些SQL在用户登录后都会被执行，对整体性能影响比较大。将内层递归调用改到外层，并限制外层返回行数量，降低了对系统资源的消耗。4.14 需求流程查询报表SQL优化[table=98%,rgb(221, 217, 195)] [td=568] select re.patch_serial "需求流水号", re.submit_staff_name "需求发起人", (select org_name from organization where org_id = re.apply_dept) "发起人部门", to_char(t1.create_date, 'yyyy-mm-dd hh24:mi:ss') "发起时间", re.title "需求标题", (select va.TCH_NAME from v_tache va where va.tch_id = f_get_flow_cur_tch_id(re.flow_id)) "当前处理环节", trim(',' from zj_pkp_basic.get_Maxtch_person(re.flow_id)) "当前处理人", decode(t1.state, 'A', '未竣工', 'C', '已接收处理', 'F', '已竣工', 'X') "流程状态", t1.flow_mod, t1.flow_id from (select r1.*, r2.staff_name from v_flow r1, staff r2 where r1.staff_id = r2.staff_id) t1, flow_model t4, cust_zj_require re where t1.flow_id = re.flow_id and t4.flow_mod = t1.flow_mod and t1.flow_mod = 11143 and re.patch_serial like '%' || :PATCH_SERIAL || '%' and re.title like '%' || TITLE || '%' and t1.create_date >= trunc(to_date(:BEGIN_DATE, 'yyyy-mm-dd')) and t1.create_date < trunc(to_date(:END_DATE, 'yyyy-mm-dd')) + 1 and t1.state = :STATE and re.submit_staff_name like '%' || :APPLY_NAME || '%' and re.flow_id in (select distinct (vt.FLOW_ID) from v_tache vt, tache_model tm where vt.TCH_MOD = tm.tch_mod and tm.flow_mod = 11143 and vt.STAFF_ID = &CURRENT_STAFF_ID) order by t1.create_date desc 由于补丁导致默认条件失效，这两个SQL导致两个节点CPU空闲降为0，出现数据库主机hang住的现象，修复默认查询条件后故障恢复。但是SQL存在递归查询，并且是全表扫描，对整体性能危害很大，例如语句一中：分析排查：(selectva.TCH_NAMEfromv_tache va where va.tch_id =f_get_flow_cur_tch_id(re.flow_id)) "当前处理环节",还需要进一步优化。 4.15 绑定变量SQL优化 分析排查：部分SQL占用大量共享池内存，并且做了不必要的硬解析。由于SQL比较多且业务比较复杂，需要研发人员修改，目前工程点已经提事件单。具体SQL以及大致修改方法可以看看附件<<以下SQL没有使用绑定变量.pdf>>。
4.16 工作台SQL脚本审核由于工作台是用户主要入口，访问频率很高，并且会定时刷新。工作台相关的SQL效率低，不但会导致页面访问速度慢，而且会影响到数据库的整体性能(例如10月25日的故障)。因此检查工作台相关SQL，分析潜在风险很有必要。4.17 SQL_ID=file:///E:/work/%E7%B3%BB%E7%BB%9F%E7%BB%84/2013-10-28%20%E6%B5%99%E6%B1%9F%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BC%98%E5%8C%96/1028_204/awrrpt_2390935337_1_21451_21452.html#d1w2d9p01b9hm]d1w2d9p01b9hm、adz3b055vju4sfile:///E:/work/%E7%B3%BB%E7%BB%9F%E7%BB%84/2013-10-28%20%E6%B5%99%E6%B1%9F%E6%95%B0%E6%8D%AE%E5%BA%93%E4%BC%98%E5%8C%96/1028_204/awrrpt_2390935337_1_21451_21452.html#d1w2d9p01b9hm]d1w2d9p01b9hm[table=98%,rgb(221, 217, 195)]
[td=568] [size=9.0pt]SELECT :B3 BATCH_ID, [size=9.0pt] B.DR_ID, [size=9.0pt] B.SYSTEM_ID, [size=9.0pt] B.AUDIT_ID, [size=9.0pt] B.PERIOD, [size=9.0pt] B.INFO_ID, [size=9.0pt] DECODE(SUBSTR(B.FILE_NAME, 1, 1), 'F', 30, 'E', 20, 10) FILE_TYPE, [size=9.0pt] B.FILE_DATE, [size=9.0pt] B.FILE_CNT_EARLY, [size=9.0pt] B.FILE_CNT_LAST, [size=9.0pt] B.FILE_NAME, [size=9.0pt] B.TRANS_DATETIME TRANS_DATE, [size=9.0pt] B.FILE_BLOCK, [size=9.0pt] (CASE [size=9.0pt] WHEN B.FILE_DATE - B.TRANS_DATETIME > 15 / 1440 THEN [size=9.0pt] 4 [size=9.0pt] WHEN B.FILE_DATE - B.TRANS_DATETIME + :B2 / 1440 < 0 THEN [size=9.0pt] 2 [size=9.0pt] ELSE [size=9.0pt] 1 [size=9.0pt] END) FLAG [size=9.0pt] FROM (SELECT 0 DR_ID, [size=9.0pt] SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 2) + 1, 4) SYSTEM_ID, [size=9.0pt] SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 3) + 1, 4) AUDIT_ID, [size=9.0pt] A.FILE_NAME, [size=9.0pt] A.BATCH_TIME + 15 / 1440 FILE_DATE, [size=9.0pt] TO_NUMBER(SUBSTR(A.FILE_NAME, [size=9.0pt] INSTR(A.FILE_NAME, '_', 1, 8) + 1, [size=9.0pt] 8)) PERIOD, [size=9.0pt] TO_NUMBER(SUBSTR(A.FILE_NAME, [size=9.0pt] INSTR(A.FILE_NAME, '_', 1, 4) + 1, [size=9.0pt] 2)) INFO_ID, [size=9.0pt] T.PUT_FILE_BLOCK_CNT FILE_BLOCK, [size=9.0pt] T.GOODS_SEND_TIME TRANS_DATETIME, [size=9.0pt] RANK() OVER(PARTITION BY A.FILE_NAME ORDER BY A.GENERATE_TIME ASC) FILE_CNT_EARLY, [size=9.0pt] RANK() OVER(PARTITION BY A.FILE_NAME ORDER BY A.GENERATE_TIME DESC) FILE_CNT_LAST [size=9.0pt] FROM INP_FILE_LIST A, INP_IDEP_DATA_FILES T [size=9.0pt] WHERE T.GOODS_SEND_TIME >= SYSDATE - 120 / 1440 [size=9.0pt] AND SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 5) + 1, 1) = :B1 [size=9.0pt] AND A.STATE IN ('0SB', '0SC', '0SA') [size=9.0pt] AND A.FILE_NAME = T.FILE_NAME) B [size=9.0pt] WHERE B.FILE_CNT_EARLY = 1 [size=9.0pt] AND B.PERIOD <= 96 adz3b055vju4s来自Audit[table=98%,rgb(221, 217, 195)] [td=568] SELECT B.DR_ID, DECODE(B.SYSTEM_ID, 3001, '综合采集(3001)', 3002, '离线计费(3002)', 3003, '在线计费(3003)', 3007, '统一充值平台(3007)', 3009, '智能交换平台(3009)', 3011, '余额管理(3011)', 3031, '综合结算(3031)') SYSTEM_NAME, B.AUDIT_ID, B.PERIOD, B.INFO_ID, DECODE(SUBSTR(B.FILE_NAME, 1, 1), 'F', 30, 'E', 20, 10) FILE_TYPE, B.FILE_DATE, B.FILE_CNT_EARLY, B.FILE_CNT_LAST, B.FILE_NAME, B.TRANS_DATETIME TRANS_DATE, B.FILE_BLOCK FROM (SELECT 0 DR_ID, SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 2) + 1, 4) SYSTEM_ID, SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 3) + 1, 4) AUDIT_ID, A.FILE_NAME, A.BATCH_TIME FILE_DATE, TO_NUMBER(SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 8) + 1, 8)) PERIOD, TO_NUMBER(SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 4) + 1, 2)) INFO_ID, A.FILE_SIZE FILE_BLOCK, C.GOODS_SEND_TIME TRANS_DATETIME, RANK() OVER(PARTITION BY A.FILE_NAME ORDER BY A.GENERATE_TIME ASC) FILE_CNT_EARLY, RANK() OVER(PARTITION BY A.FILE_NAME ORDER BY A.GENERATE_TIME DESC) FILE_CNT_LAST FROM INP_FILE_LIST A, INP_IDEP_DATA_FILES C WHERE A.FILE_NAME = C.FILE_NAME AND C.GOODS_SEND_TIME >= SYSDATE - 1 / 24 AND SUBSTR(A.FILE_NAME, INSTR(A.FILE_NAME, '_', 1, 5) + 1, 1) = :B1 AND A.STATE IN ('0SB', '0SC', '0SA')) B WHERE B.FILE_CNT_EARLY = 1 AND B.PERIOD <= :B2 AND SUBSTR(B.FILE_NAME, 26, 8) || '235959' - TO_CHAR(B.TRANS_DATETIME, 'YYYYMMDDHH24MISS') < 0) 分析排查： 这个两个语句来自后台进程，对INP_FILE_LIST进行了全表扫描，这个表很大22G。从C.GOODS_SEND_TIME这个条件范围看，记录不是很多，可以考虑改成用FILE_ID来连接。 4.18 SQL_ID=6r6kc73cc4qjs[table=98%,rgb(221, 217, 195)] [td=568] SELECT * FROM (SELECT ROWNUM SID, A.* FROM (SELECT rm.flow_id, 11303 flow_mod, rm.serial 权限编号, rm.title 标题, rm.apply_name 权限发起人, rm.submit_time 发起时间, (select mean from codelist where code_type = 'SWITCH_APPLY_TYPE' and code = rm.APPLY_type) "申请类型", decode(rm.LIMIT_TYPE, 1, '应用', 2, 'KEY', 3, '网络', 4, '数据库', 5, '主机', 6, '网络设备用户账户') "权限类型", (select va.TCH_NAME from v_tache va where va.tch_id = f_get_flow_cur_tch_id(rm.flow_id)) "当前停留环节", ZJ_PKP_TACHE_CONFIG_YUNTD.GET_PERSONSTAFF_NAME_ZJC(rm.flow_id) "当前停留人", (select mr.region_name from staff sf, manage_region mr where sf.region_id = mr.region_id and sf.staff_id = rm.submit_staff_id) 地区, decode(f.state, 'A', '未竣工', 'F', '已竣工', '已撤销') 流程状态 FROM cust_zj_switch rm, (select * from v_flow v where v.FLOW_MOD = 11303) f WHERE f.flow_id = rm.flow_id AND trunc(rm.submit_time) >= to_date(:1, 'yyyy-mm-dd') AND trunc(rm.submit_time) <= to_date(:2, 'yyyy-mm-dd') order by rm.submit_time desc) A) B WHERE B.SID > :3 AND B.SID <= :4 分析排查：[table=98%,rgb(221, 217, 195)] [td=568] (select va.TCH_NAME from v_tache va where va.tch_id = f_get_flow_cur_tch_id(rm.flow_id)) "当前停留环节", 这个地方改成v_tache改成tache后，能够减少全表扫描风险。修改后的CPU对比 4.19 SGA修改为11G11 月7日下午系统出现严重pagein/pageout，数据库hang住10分钟左右。在生产当前压力下，系统内存资源略显不足，故将SGA缩小为11G大小，以降低数据库hang住的风险。(11月10日已实施)。4.20 SQL_ID=dkrydgt7bdx4u执行计划： 修改后SQL_ID：原来对tache、tache_his的全表扫描，变成了索引范围扫描，效率提高了几十倍。执行计划： 4.21 SQL_ID= 6fvj6jgj3ttvp分析排查：这个JOB已经没有用，可以停止。