数据库优化报告

浙江数据库优化报2013-11-修订历史记2013-10-新2013-10-2013-10-2013-11-2013-11-从2013年9月份开始及分析，发现浙江的ITSM数据库在环境、设计及SQL面，都存在不少问题。在SQL类优化中，本地化代码编写和设计不良，是比较明显的问题。下面将分成环境、设计、SQL优化三类进行持续分析，5点的整改计划中来看，目前来看已经有效缓解期CPU使用用率100%的情况，但IO、内存资源使用率依旧挺高，比较可疑的SQL语句已经罗列出来，将按整改计划表继续改造，应会有在跟进过程中出现了5次前台无法报错的故障故障分析报告一并整理8点中的附件《故障分析.zip（两次1点1恢复正常后故障当业务量上来之后应用由于之前的故障是SQL达到一定并发量后将主机资源耗是是是浙江系统统计信息未自动收集，非常奇怪(看了、、黑龙江等其他1.selectowner,fromdba_tab_statisticswhere(t.last_yzedisnullort.last_yzed<sysdate-100)andtable_namenotlike'BIN$%'groupbyownerorderby3112.2BOSSWG_ZJ用户需删除V31年多了，BOSSWG_ZJBASEDB_ZJ可--4--4 10G 部分索引失 IDX_ALARM_MSG_ID_NEWBOSSWG_ZJ SGAPGA从AWR而SGA10G，PGAADDMPGASGA15G，PGA5GPGA参数改小到1022日，单个节点session700个，造成了数据库内存消耗严重，PGAsession数容量，每个节点能够多容纳大100session550个左右，降低一些因连接数超载带来的风险。 altersystemsetpga_aggregate_target=3Gscope=both 大量的V1，V2….,请开发人员和我们探讨一下业务的需求。这类设计对性能有 ZJ_createcreateuniqueindexACT_UNIQ_RU_BUS_KEYonACT_RU_EXECUTION BUSINESS_KEY_ISNULLTHENNULLELSEPROC_DEF_ID_END,CASE BUSINESS_KEY_ISNULLTHENNULLELSEBUSINESS_KEY_END)createuniquecreateuniqueindexACT_UNIQ_HI_BUS_KEYonACT_HI_PROCINST(CASE BUSINESS_KEY_ISNULLTHENNULLELSEPROC_DEF_ID_END, BUSINESS_KEY_ISNULLTHENNULLELSEBUSINESS_KEY_定时审核任务SQL优化这个语句单次执行需要9秒大概1分钟执行一次由于对logic_进行全表扫描，对I/OTO_CHAR(B.SOURCE_END_DATE,'YYYYMMDDHH24MISS'), NVL(C.KPI_ID,0),C.Subject||产生数据FROMLOGIC_ TA_GATHER_TASK_BATCHB, DWHEREA.Gather_Batch_ID=B.Gather_Batch_IDANDA.State='1' YZE_RULE_ID=ANDC.State=ORDERORDERBY分析排查state是logic_yze_task表处理的状态位，值为'1'的记录占比极少，在上建索引SQL1秒以下，对I/Oyze_task_statyze_task_staton yze_task(state)tablespace createindexURL权限代码改造（重点SELECTSELECT'<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='0SA'ORDER分析排查weblogic内存后，能够减少大量数据库查询，降低数据库性能消耗。(28日上午awr报告里没再出现这个SQL。)SQLSQL_ID=bdcfdz26x5hm9（本地化语句情况17天内就执行2551302.2750selectselectfromcust_zj_declarationwhereREASON_SUB_TYPEisnot MEND_CLERK=andrownum<=10orderbyCLERK_SORT;分析排查1111selectfromwheretable_name='CUST_ZJ_DECLARATION'andcolumn_name=' t.table_name,t.index_name,t.column_name,t.column_position,t.DESCENDfromuser_ind_columnstwheretable_nameorderbytable_name,index_name,COLUMN_POSITION1 MEND_CLERK列的VARCHAR2类型修改为NUMBER型，因为里面只放0和1两个取值其中=1仅返回10条以内，0几乎返回所有记录。 MEND_CLERK列有索引只是因为是VARCHAR2类型 MEND_CLERK=1改为and MEND_CLERK='1' SQL_ID=45ksbh7qdf71t（本地化语句情况11743 APPLY_REGION_ID,DUTY_ID,EVENT_SOURCE,to_char(HAPPEN_DATE,'yyyy-mm-ddhh24:mi:ss')HAPPEN_DATE,PHENOMENON_MAIN_TYPE,PHENOMENON_SUB_TYPE_NAME,REASON_MAIN_TYPE,to_char(SUBMIT_TIME,'yyyy-mm-ddhh24:mi:ss')SUBMIT_TIME,fromwhererequest_id=分析排查在分 的案例时已经查CUST_ZJ_DECLARATION列的索引情况，该表在REQUEST_ID-修改建在request_idSQL_ID=bn5w1gv1d8jgk（统一版本语句情况7364507FROMMAINTANCE_JOB MAINT_JOB_INSTANCEC, DWHEREB.MAINT_JOB_ITEM_ID=ANDA.MAINTANCE_JOB_ID=B.MAINTANCE_JOB_IDANDNVL(A.IF_DEL,'0BF')='0BF'ANDB.STATE='0SA'ANDNVL(B.IF_DEL,'0BF')=ANDNVL(B.EXECUTE_STAFF,B.DUTY_ID)ISNOTNULLANDB.IF_HASTEN='0BT'ANDD.JOB_ITEM_STS!='2' ANDTO_CHAR(C.PLAN_EXEC_DATE+NVL( AND AND 'yyyy-mm-ddhh24:mi:ss')+B.HASTEN_TIME/(24*60))<=分析排查SQL>SQL>selectt.table_name,t.index_name,t.column_name,t.column_position,fromuser_ind_columnswheretable_nameinorderbytable_name,index_name,JOB_ITEM_INSTANCEIDX_INST_JOB_ITEM_ID 1ASC 1ASCJOB_ITEM_INSTANCEPK_JOB_ITEM_INSTANCE 1ASC 1ASC 1ASC7rows 1 1SQL>selectcount(0)from;SQL>selectcount(0)fromMAINT_JOB_ITEM;SQL>selectcount(0)fromMAINT_JOB_INSTANCE;SQL>selectcount(0)fromJOB_ITEM_INSTANCE;修改建议建 PLETE_TIME_LIMIT列有索确 PLETE_TIME_LIMIT列会有前后空格吗，如果不会，则不必TRIM，直接写 PLETE_TIME_LIMIT>=说明一下为什 4.PKP_MAINT_JOB.GETHOLIDAYNUM的包产生很多递归调用，可以withwithtmp_maintjobas(selecta.job_name,from maint_job_instancec, whereb.maint_job_item_id=andc.maint_job_instance_id=d.maint_job_instance_idanda.maintance_job_id=b.maintance_job_idanda.state=anda.ENABLED_DATE<=sysdateandnvl(a.IF_DEL,'0BF')='0BF'andb.state=andnvl(b.IF_DEL,'0BF')=andnvl(b.execute_staff,b.duty_id)isnotnullandb.if_hasten='0BT'andd.JOB_ITEM_STS!='2' PLETE_TIME_LIMITisnotandtrim( PLETE_TIME_LIMIT)>=to_char(sysdate,'hh24:mi:ss')andnvl(d.main_next_notify_time,to_date(to_char(sysdate,'yyyy-mm-dd')|| 'yyyy-mm-ddhh24:mi:ss')+b.hasten_time/(24*60))<=selects.job_name,sum(delay_count)delay_countfrom(selectcount(t.holiday_day)delay_countfromtmp_maintjobs,holiday_cfgtwheres.holiday_opr='DELAY_EXEC'andt.holiday_day>=s.plan_exec_dateandt.holiday_day<sysdateandt.holiday_day>sysdate-730groupbys.job_name,unionselect fromtmp_maintjob(selectthedateasfrom(selectsysdate-730+(level-1)thedatefromdualconnectbylevel<=sysdate-trunc(sysdate-whereto_char(thedate,'d')='7'orto_char(thedate,'d')='1')twheres.weekend_opr='DELAY_EXEC'andt.weekend>=s.plan_exec_dategroupbyunionselect0delay_countfromtmp_maintjobwhereweekend_opr!='DELAY_EXEC'andholiday_opr!='DELAY_EXEC')sgroupbyhavingtrunc(trunc(plan_exec_date)+nvl(complete_day_limit,0)+sum(delay_count))=truncSQL_ID=cwzwax00wmm7q（本地化语句情况789995 (ALARM_ID,EXTERNAL_ID,EXTERNAL_ID_TYPE,CUSTOMER_NAME,STATUS, A2.EXTERNAL_ID,A2.EXTERNAL_ID_TYPE,A2.CUSTOMER_NAME, = 分析排查修改建议建议HIGH_USAGE_ALARM_MR_READ表的CITI_CODE CITY_CODE=:B1)A2ON(ALARM_ID,EXTERNAL_ID,PRODUCT_INSTANCE_CODE,EXTERNAL_ID_TYPE,CUSTOMER_NAME,STATUS,STATUS_DATE) (A2.ALARM_ID,A2.EXTERNAL_ID,A2.PRODUCT_INSTANCE_CODESQL_ID=ctmv0k8sgf907（外部语句情况112216selectselectcount(*),whereaction=5andstate=andstate_time<sysdate-1/andstate_time>sysdate-5/分析排查 条而wherestate_time<sysdate-1/1440andstate_time>sysdate-5/1440才548条。selectt.table_owner,t.index_name,t.column_name,t.column_positionfromdba_ind_columns@xzabmtwheretable_nameorderbytable_name,index_name, 11 BR111修改建议state_time列建索引，不过这需要和对方沟通，这是对方的表。不过这个DBLINK连接的，实际上也会影响对方系统的性能，所以建索引其SQL_ID=9s06gsx7snk73(本地化SELECTCOUNT(1)FROMSELECTCOUNT(1)FROM(SELECT(CASEWHENV10'97成功ANDV11ISNULLNVL(TO_DATE(T.V12,'yyyy-mm-ddhh24:mi:ss'),SYSDATE)END)*24*3600WHERETO_DATE(T.V12,'yyyy-mm-ddhh24:mi:ss')>=:B2ANDTO_DATE(T.V12,'yyyy-mm-ddhh24:mi:ss')<:B1)分析排查1.1.ZJKZHDATE500表扫描，这里却用了全表扫描,不过这个语句的TO_DATE写法本来就用不到索引，此外这些V12的列，本身也没有索引，如下：selectt.table_name,t.index_name,t.column_name,t.column_positionfromuser_ind_columnstwheretable_nameorderbytable_name,index_name,1112.2.SQL_ID=a5wr8yd24r4wq（本地化SELECTSELECTFROM(SELECTFROMZJ_ WHERET.V2ISNOTNULLANDT.V8ISNOTNULLANDT.V13ISNOTNULLANDT.V18ISNOTWHEREROWNUM<修改建议ZJ__S_ORDER_GATHER对象和ZJ_KZH_DATE对象类似，关于字ZJ__S_ORDER_GATHER记录有2千多万条，是否有清理机制调和语句大量DELETE可避deletecommitcommitredoSQL_ID=a5wr8yd24r4wq（本地化insertinsertintoINP_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,分析排查其中这个函数如下functionfunctionconvert_inp_data_perf(p_ne_idnumber,p_areacodevarchar2)returnnumberis /*selectdistincta.ne_idintov_ne_idfromnet_elementawherea.ne_type_id=anda.region_id=to_number(p_areacode)anda.state='0SA';*/SELECTdistinctA.INSTANCE_IDINTOv_ne_idFROMCI_BASE_ELEMENTAWHEREA.CLASS_ID=p_ne_idANDA.REGION_ID=to_number(p_areacode)ANDA.MARKASDELETED=0exceptionwhenothersthenv_ne_id:=-1;endreturnend修改建议取同一个文件的时候，其实返回的记录1次即可。SQL_ID=9ghzcqq3y4x49（统一版本selectselectfrom(select'event'a.event_idid,nvl(e.staff_name,'...')assrc_staff_name,nvl(c.staff_name,'...')asexec_staff_name,b.event_typetype,f_get_all_state('EVENT_Q','STATE',b.state)asstate_name,''room_name,'...'dev_name,to_char(b.state_date,'yy/mm/ddhh24:mi:ss')asstate_date,IF_ORANGE_ALERT(b.event_id)alert_flag,0)asreplace(replace(f.send_url,'=0000','='||b.content_id),nvl(b.serial_num,'-1'))'0'fromstaff_event workaccept_cfgwherea.event_id=b.event_idandb.grade=d.gradeandb.exec_staff=c.staff_id(+)andb.src_staff=e.staff_id(+)anda.staff_id=1andnotexists(select1fromsys_configwheresys_var=andinstr(','||sys_var_value||',','||b.event_type||',')>0)andb.event_type=f.work_typeandf.queue_type='EVENT_Q'andb.flagisnullunionselect'task'task_or_event,a.task_idid,nvl(e.staff_name,'...')assrc_staff_name,nvl(c.staff_name,'...')asexec_staff_name,b.task_typetype,f_get_all_state('TASK_Q','TASK_TYPE',task_type)astype_name,f_get_all_state('TASK_Q','STATE',b.state)asstate_name,nvl(b.room_name,'...')asroom_name,nvl(b.dev_name,'...')asdev_name,to_char(b.state_date,'yy/mm/ddhh24:mi:ss')asstate_date,0alert_flag,0replace(f.send_url,'=0000','='||b.content_id)send_url,'0'sort_idfromstaff_task workaccept_cfgwherewherea.task_id=b.task_idandb.grade=d.gradeandb.exec_staff=c.staff_id(+)andb.src_staff=e.staff_id(+)anda.staff_id=1andb.task_type=f.work_typeandf.queue_type='TASK_Q'orderbystate_datedesc)whererownum<=20分析排查由admin帐号历史信息过多引起，清理staff_event、event_q相关数据后SQL效率提高，CPU占用降低。8p3wxmt13z5xv、3kbwc677sm94y（本地化, WHEREA.FLOW_ID=,ANDINSTR(','||B. ||',',','||D.STAFF_ID||',')>0ANDB.STATE<>'F'ANDEXISTS(SELECTWHEREC.FLOW_ID=A.FLOW_IDANDC.TCH_MOD=11548)ANDNOTEXISTS(SELECT1WHEREC.FLOW_ID=A.FLOW_IDANDC.TCH_MOD=11595)分析排查SQL写法有问题，暂停这两个SQLCPUinp_file_list分析排查Inp_file_list改成分区表后，涉及的的没有修改完全，由于没有使用首页待办的SQL（重点selectsrc_staff_name selectsrc_staff_name exec_staff_name接受员工, 事务标题 '03'row_number()over(orderbystate_datedesc)rnfrom(select'event'task_or_event,a.event_idid,b.event_typetype,0)asreplace(replace(f.send_url,'=0000','='||b.content_id),nvl(b.serial_num,'-1'))send_url,nvl(e.staff_name,'...')assrc_staff_name,nvl(c.staff_name,'...')asexec_staff_name, to_char(b.state_date,'yy/mm/ddhh24:mi:ss')asstate_datefromstaff_event workaccept_cfgfwhereworkaccept_cfgfwherea.event_id=b.event_idandb.grade=d.gradeandb.flagisnullandb.exec_staff=c.staff_id(+)andb.src_staff=e.staff_id(+)anda.staff_id=&CURRENT_STAFF_IDandb.event_type=f.work_typeandf.queue_type='EVENT_Q'unionallselect'task'a.task_idid,b.task_typetype,0replace(f.send_url,'=0000','='||b.content_id)send_url,nvl(e.staff_name,'...')assrc_staff_name,nvl(c.staff_name,'...')asexec_staff_name,f_get_all_state('TASK_Q','TASK_TYPE',task_type)astype_name,to_char(b.state_date,'yy/mm/ddhh24:mi:ss')asstate_datefromstaff_task workaccept_cfgfwherea.task_id=b.task_idandb.grade=andb.exec_staff=c.staff_id(+)andb.src_staff=e.staff_id(+)anda.staff_id=&CURRENT_STAFF_IDandb.task_type=f.work_typeandf.queue_type='TASK_Q')where1=1andtype<>'3'andtype<>'#'andcontentlike 00:00:00','yyyy-MM-ddhh24:mi:ss') 23:59:59','yyyy-MM-ddhh24:mi:ss'))wherern<=分析排查由于select字段列表里有很多函数调用，当比较多的时候，SQL性能差SQL在用户登录后都会被执行，对整体性能影响比较大。将内层递归需求流程查询报表SQL优selectre.patch_serial"selectre.patch_serial"需求流水号",re.submit_staff_name需求发起人",(selectorg_namefromorganizationwhereorg_idre.apply_dept发起人部门",to_char(t1.create_date,'yyyy-mm-ddhh24:mi:ss')"发起时间",re.title需求标题",(selectva.TCH_NAMEfromv_tachewhereva.tch_id=f_get_flow_cur_tch_id(re.flow_id))"当前处理环节",trim(','fromzj_pkp_basic.get_Maxtch_ (re.flow_id))"当前处理人",'X流程状态",from(selectr1.*,fromv_flowr1,staffwherer1.staff_id=r2.staff_id)t1,flow_modelt4,cust_zj_requirerewheret1.flow_id=re.flow_idandt4.flow_mod=t1.flow_modandt1.flow_mod=11143andre.patch_seriallike'%'||:PATCH_SERIAL||'%'andre.titlelike'%'||TITLE||'%'andt1.create_date>=trunc(to_date(:BEGIN_DATE,'yyyy-mm-dd'))andt1.create_date<trunc(to_date(:END_DATE,'yyyy-mm-dd'))+1andt1.state=:STATEandre.submit_staff_namelike'%'||:APPLY_NAME||'%'andre.flow_idin(selectdistinct(vt.FLOW_ID)fromv_tachevt,tache_modeltmwherevt.TCH_MOD=andtm.flow_mod=andvt.STAFF_ID=orderbyt1.create_dateselectre.patch_serial"需求流水号",re.submit_staff_name需求发起人",(__idre.apply_dept)发起人部门,to_char(t1.create_date,'yyyy-mm-ddhh24:mi:ss')"发起时间",re.title"需求标题",re.require_involve_system_name需求涉及系统", STAFF_NAME_ZJC(re.flow_id)"当前处理人decode(t1.stateA未竣工C已接收处理F已竣工X流程状态",(_namefromorganizationtwheret.parent_org_id=0_id=re.apply_deptconnectbypriort.parent_org_id=_id)"需 (selects.create_datefromflow_hisswhereflow_idre.flow_id)完成时间"from(selectr1.*,r2.staff_namefromv_flowr1,staffr2wherer1.staff_id=r2.staff_idandr1.flow_mod=11143)flow_model(select*fromcust_zj_require select1fromTACHE_ZJ_TP_TREE_TYPEwhereremark=: ANDINSTR(REQUIRE_INVOLVE_SYSTEM,SYSTEM_TYPE)>0)or _TYPE_NAMEisnull)t1.flow_id=andt1.flow_mod=t4.flow_modandt1.flow_mod=11143andre.titlelike:TITLEandre.submit_staff_namelike:submit_staff_nameandre.require_source=:require_sourceandre.require_type=andt1.create_date>=trunc(to_date(:BEGIN_DATE,'yyyy-mm-dd'))andt1.create_date< trunc(to_date(:END_DATE,'yyyy-mm-dd'))+1andt1.state=:STATE(_namefromorganizationtwheret.parent_org_id=0_id=re.apply_deptconnectbypriorandandorderbyt1.flow_id分析排查由于补丁导致默认条件失效这两个SQL导致两个节点CPU空闲降为0，出现数据库主机hangSQL存在递(selectva.TCH_NAMEfromv_tachevawhereva.tch_idf_get_flow_cur_tch_id(re.flow_id当前处理环节绑定变量SQL优分析排查部分SQL占用大量共享池内存，并且做了不必要的硬解析。由于SQL比较多且业务比较复杂，需要研发人员修改，目前工程点已经提事件单。具体SQL以及大致修改方法附件<<以下SQL没有使用绑定变量.pdf>>。工作台SQL审由于工作台是用户主要，频率很高，并且会定时刷新。工作台相关的SQL效率低不但会导致页面速度慢而且会影响到数据库的整体性能(例1025日的故障)。因此检查工作台相关SQL，分析潜在风险很有必要。SQL_ID=d1w2d9p01b9hm、d1w2d9p01b9hm来自DECODE(SUBSTR(B.FILE_NAME,1,1),'F',30,'E',20,10)FILE_TYPE,WHENB.FILE_DATE-B.TRANS_DATETIME>15/1440THENWHENB.FILE_DATE-B.TRANS_DATETIME+:B2/1440<0THENEND)FROM(SELECT0SUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,2)+1,4)SUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,3)+1,4)AUDIT_ID,A.BATCH_TIME+15/1440FILE_DATE,INSTR(A.FILE_NAME,'_',1,8)+8))PERIOD,INSTR(A.FILE_NAME,'_',1,4)+2)) TWHERET.GOODS_SEND_TIME>=SYSDATE-120/ANDANDSUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,5)+1,1)=:B1ANDA.STATEIN('0SB','0SC','0SA')ANDA.FILE_NAME=T.FILE_NAME)B T_EARLY=1ANDB.PERIOD<=adz3b055vju4s来自SELECT'综 '综合结算(3031SYSTEM_NAME,DECODE(SUBSTR(B.FILE_NAME,1,1),'F',30,'E',20,10)FILE_TYPE,FROM(SELECT0SUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,2)+1,

SUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,3)+1,4)A.BATCH_TIMEFILE_DATE,INSTR(A.FILE_NAME,'_',1,8)+8))PERIOD,INSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,4)+2))INFO_ID,A.FILE_SIZEFILE_BLOCK, WHEREA.FILE_NAME=C.FILE_NAMEANDC.GOODS_SEND_TIME>=SYSDATE-1/ANDSUBSTR(A.FILE_NAME,INSTR(A.FILE_NAME,'_',1,5)+1,1)=:B1ANDA.STATEIN('0SB','0SC','0SA'))B ANDB.PERIOD<=:B2ANDSUBSTR(B.FILE_NAME,26,8)||'235959'分析排查这个两个语句来自进程对INP_FILE_LIST进行了全表扫描这个表很大22G。从C.GOODS_SEND_TIME这个条件范围看，记录不是很多，可以考虑改成用FILE_ID来连接。SELECTSELECTFROM(SELECTROWNUMSID,FROM(SELECT11303flow_mod,rm.serial权限编号,rm.title标题,rm.apply_name权限发起人,rm.submit_time发起时间,(selectmeanfromwherecode_type=andandcoderm.APPLY_type申请类型",')"(selectfromv_tache=))"环节 (selectfromstaffsf,manage_regionmrwheresf.region_id=andsf.staff_idrm.submit_staff_id)地区decode(f.stateA未竣工F已竣工已撤销')FROMcust_zj_switch(select*fromv_flowvwherev.FLOW_MOD=11303)fWHEREf.flow_id=rm.flow_idANDtrunc(rm.submit_time)>=to_date(:1,'yyyy-mm-dd')ANDtrunc(rm.submit_time)<=to_date(:2,'yyyy-mm-orderbyrm.submit_timedesc)A)BWHEREB.SID>:3ANDB.SID<=分析排查(select(selectfromv_tache=))"环节v_tache改成tacheCPUSGA修改为117午系统出现严重pagein/pageouthang10分钟左右。在生产当前压力下，系统内存资源略显不足，故将SGA缩小为11G大小，以降低数据库hang住的风险。(1110SQL_ID=SELECTSELECTFROM(SELECTROWNUMSID,FROM(selectc.serial事件流水号,c.event_desc事件内容,t.tch_name当前环节,trim(','from人员))to_char(t.created_dateyyyy-mm-ddHH24:MI')进入环节时间b.v29市级环节累计时长,(selectmr.region_namefromstaffsf,manage_regionmrwheresf.region_id=mr.region_idandsf.staff_idc.submit_staff_id)fromcust_zj_declaration(select24)||':'min(CREATED_DATE))*1)*60)||':'||trunc(mod(((max(FINISH_DATE)-min(CREATED_DATE))*24)*1)*60)tch_iddesc)

fromv_tachewherea.FINISH_DATE(selectnvl(min(b.CRE